xb

xb (Extensible Builder)

OSCS Status workflow build GitHub tag Go Report Card

AI-First SQL Or JSON Builder for Relational and Vector Databases

A tool of sql or json query builder, build sql for sql.DB, sqlx, gorp, or build condition sql for some orm framework, like xorm, gorm…. also can build json for some json parameter db, like Qdrant ….

🎉 Latest: v1.2.1 released with unified API + Smart condition building enhancements!

🚀 NEW: Smart Condition Building (Latest)

Three-Layer Design for 99% of Real-World Scenarios

✨ Recent Updates:

🚀 Builder Pattern + Unified Entry (v1.2.1)

One configuration entry for all databases - minimum cognitive load!

✨ Core Features:

// Qdrant Vector Search (v1.2.1) - 统一的 Custom() 入口
built := xb.Of(&CodeVector{}).
    Custom(
        xb.NewQdrantBuilder().
            HnswEf(512).
            ScoreThreshold(0.85).
            WithVector(false).
            Build(),
    ).
    VectorSearch("embedding", queryVector, 10).
    Eq("language", "golang").
    Build()
json, _ := built.JsonOfSelect()

// MySQL UPSERT (v1.2.1) - 统一的 Custom() 入口
built := xb.Of(user).
    Custom(
        xb.NewMySQLBuilder().
            UseUpsert(true).
            Build(),
    ).
    Insert(func(ib *xb.InsertBuilder) {
        ib.Set("name", user.Name).
           Set("email", user.Email)
    }).
    Build()
sql, args := built.SqlOfInsert()
// INSERT INTO users ... ON DUPLICATE KEY UPDATE ...

// Qdrant CRUD (v1.1.0) - 与 SQL 完全一致的 API
// Insert
built := xb.Of(&CodeVector{}).
    Custom(xb.NewQdrantCustom()).
    Insert(func(ib *xb.InsertBuilder) {
        ib.Set("id", 123).
           Set("vector", []float32{0.1, 0.2, 0.3}).
           Set("language", "golang")
    }).
    Build()
json, _ := built.JsonOfInsert()

// Update
built := xb.Of(&CodeVector{}).
    Custom(xb.NewQdrantCustom()).
    Eq("id", 123).
    Update(func(ub *xb.UpdateBuilder) {
        ub.Set("language", "rust")
    }).
    Build()
json, _ := built.JsonOfUpdate()

// Delete
built := xb.Of(&CodeVector{}).
    Custom(xb.NewQdrantCustom()).
    Eq("id", 123).
    Build()
json, _ := built.JsonOfDelete()

// Standard SQL (no Custom needed)
built := xb.Of(&User{}).
    Eq("status", 1).
    Gt("age", 18).
    Build()
sql, args, _ := built.SqlOfSelect()
// SELECT * FROM users WHERE status = ? AND age > ?

📖 Read the Custom Interface Guide →

Architecture Highlights:

🔍 Qdrant Advanced API (since v0.10.0)

The first unified ORM for both Relational and Vector Databases!

✨ New in v0.10.0:

// MySQL (existing)
xb.Of(&Order{}).Eq("status", 1).Build().SqlOfSelect()

// VectorDB (v0.10.0) - Same API!
xb.Of(&CodeVector{}).
    Eq("language", "golang").
    VectorSearch("embedding", queryVector, 10).
    QdrantX(func(qx *QdrantBuilderX) {
        qx.Recommend(func(rb *RecommendBuilder) {
            rb.Positive(123, 456).Limit(20)
        })
    }).
    Build()

📖 Read the Vector Database Design Docs →

Features:

Development: AI-First approach (Claude AI + Human review)

🤖 AI-First Development

xb v0.8.0+ is developed using an innovative AI-First approach:

Maintenance Model (80/15/5)

v0.8.1 Vector Database Support

Achieved entirely through AI-First development:

This makes xb one of the first major Go ORM projects successfully maintained by AI.

🎯 Smart Condition Building (NEW)

Three-Layer Design for 99% of Real-World Scenarios

Layer 1: Auto-Filtering (90% cases)

// ✅ Automatically filters nil, 0, "", []
// User doesn't select filters → query returns more results
xb.Of("users").
    Eq("age", age).              // age=0 → ignored
    In("status", statuses...).   // []    → ignored
    Like("name", keyword).       // ""    → ignored
    Build()

Perfect for: User search forms, optional filters

Layer 2: Required Validation (5% cases)

// ✅ InRequired: Prevents accidental mass operations
selectedIDs := getUserSelectedIDs() // might be empty!
xb.Of("orders").
    InRequired("id", selectedIDs...). // [] → panic with clear message
    Build()

// Prevents: DELETE FROM orders (deleting ALL orders!)

Perfect for: Admin batch operations, critical updates

Layer 3: Ultimate Flexibility (5% cases)

// ✅ X(): Zero constraints for special values
xb.Of("users").
    X("age = 0").              // Query age = 0
    X("is_active = false").    // Query false values
    Build()

// ✅ Sub(): Type-safe subqueries
xb.Of("orders").
    Sub("user_id IN ?", func(sb *xb.BuilderX) {
        sb.Of(&VipUser{}).Select("id")
    }).
    Build()
// SQL: SELECT * FROM orders WHERE user_id IN (SELECT id FROM vip_users)

// ✅ Bool(): Conditional logic
xb.Of("orders").
    Bool(func() bool { return isAdmin }, func(cb *xb.CondBuilder) {
        cb.Eq("status", "deleted") // Only admins can see deleted
    }).
    Build()

Perfect for: Edge cases, complex queries, dynamic permissions

API Comparison

Method Auto-Filter Use Case Example
Eq/In/Like ✅ Yes Optional filters Eq("age", age)
InRequired ❌ Panic Required selection InRequired("id", ids...)
X ❌ No Special values X("age = 0")
Sub N/A Subqueries Sub("id IN ?", func...)

Program feature:

Available field of struct:

Example

SELECT * FROM t_cat WHERE id > ? AND (price >= ? OR is_sold = ?)

var Db *sqlx.DB
....

var c Cat
builder := xb.Of(&c).Gt("id", 10000).And(func(cb *CondBuilder) {
	cb.Gte("price", catRo.Price).OR().Eq("is_sold", catRo.IsSold)
})

countSql, dataSql, vs, _ := builder.Build().SqlOfPage()
var catList []Cat
err = Db.Select(&catList, dataSql, vs...)

📚 Documentation

Complete Documentation Index →

Quick links:

AI Application Ecosystem:

Complete Application Examples:

Contributing

We warmly welcome all forms of contributions! 🎉

In the era of rapid tech iteration, we embrace change and listen to the community. See VISION.md for our development philosophy.

Quickstart

Single Example


import (
    . "github.com/fndome/xb"
)

type Cat struct {
	Id       uint64    `db:"id"`
	Name     string    `db:"name"`
	Age      uint      `db:"age"`
	Color    string    `db:"color"`
	Weight   float64   `db:"weight"`
	IsSold   *bool     `db:"is_sold"`
	Price    *float64  `db:"price"`
	CreateAt time.Time `db:"create_at"`
}

func (*Cat) TableName() string {
	return "t_cat"
}

// IsSold, Price, fields can be zero, must be pointer, like Java Boolean....
// xb has func: Bool(true), Int(v) ....
// xb no relect, not support omitempty, should rewrite ro, dto
type CatRo struct {
	Name   string   `json:"name, string"`
	IsSold *bool    `json:"isSold, *bool"`
	Price  *float64 `json:"price, *float64"`
	Age    uint     `json:"age", unit`
}

func main() {
	cat := Cat{
		Id:       100002,
		Name:     "Tuanzi",
		Age:      1,
		Color:    "B",
		Weight:   8.5,
		IsSold:   Bool(true),
		Price:    Float64(10000.00),
		CreateAt: time.Now(),
	}
    // INSERT .....

    // PREPARE TO QUERY
	catRo := CatRo{
		Name:	"Tu",
		IsSold: nil,
		Price:  Float64(5000.00),
		Age:    1,
	}

	preCondition := func() bool {
		if cat.Color == "W" {
			return true
		} else if cat.Weight <= 3 {
			return false
		} else {
			return true
		}
	}

	var c Cat
	var builder = Of(&c)
	builder.LikeLeft("name",catRo.Name)
	builder.X("weight <> ?", 0) //X(k, v...), hardcode func, value 0 and nil will NOT ignore
    //Eq,Ne,Gt.... value 0 and nil will ignore, like as follow: OR().Eq("is_sold", catRo.IsSold)
	builder.And(func(cb *CondBuilder) {
            cb.Gte("price", catRo.Price).OR().Gte("age", catRo.Age).OR().Eq("is_sold", catRo.IsSold))
	    })
    //func Bool NOT designed for value nil or 0; designed to convert complex logic to bool
    //Decorator pattern suggest to use func Bool preCondition, like:
    //myBoolDecorator := NewMyBoolDecorator(para)
    //builder.Bool(myBoolDecorator.fooCondition, func(cb *CondBuilder) {
	builder.Bool(preCondition, func(cb *CondBuilder) {
            cb.Or(func(cb *CondBuilder) {
                cb.Lt("price", 5000)
            })
	})
	builder.Sort("id", ASC)
        builder.Paged(func(pb *PageBuilder) {
                pb.Page(1).Rows(10).IgnoreTotalRows()
            })
    countSql, dataSql, vs, _ := builder.Build().SqlOfPage()
    // ....

    //dataSql: SELECT * FROM t_cat WHERE id > ? AND name LIKE ? AND weight <> 0 AND (price >= ? OR age >= ?) OR (price < ?)
    //ORDER BY id ASC LIMIT 10

	//.IgnoreTotalRows(), will not output countSql
    //countSql: SELECT COUNT(*) FROM t_cat WHERE name LIKE ? AND weight <> 0 AND (price >= ? OR age >= ?) OR (price < ?)
    
    //sqlx: 	err = Db.Select(&catList, dataSql,vs...)
	joinSql, condSql, cvs := builder.Build().SqlOfCond()
    
    //conditionSql: id > ? AND name LIKE ? AND weight <> 0 AND (price >= ? OR age >= ?) OR (price < ?)

}

Join Example

import (
        . "github.com/fndome/xb"
    )
    
func main() {
	
	sub := func(sb *BuilderX) {
                sb.Select("id","type").From("t_pet").Gt("id", 10000) //....
            }
	
        builder := X().
		Select("p.id","p.weight").
		FromX(func(fb *FromBuilder) {
                    fb.
                        Sub(sub).As("p").
                        JOIN(INNER).Of("t_dog").As("d").On("d.pet_id = p.id").
                        JOIN(LEFT).Of("t_cat").As("c").On("c.pet_id = p.id").
                            Cond(func(on *ON) {
                                on.Gt("c.id", ro.MinCatId)
                            })
		    }).
	        Ne("p.type","PIG").
                Having(func(cb *CondBuilderX) {
                    cb.Sub("p.weight > ?", func(sb *BuilderX) {
                        sb.Select("AVG(weight)").From("t_dog")
                    })
                })
    
}

🎯 Use Case Decision Guide

Get direct answers without learning — Let AI decide for you

📖 中文版 (Chinese Version) →

Scenario 1️⃣: Semantic Search & Personalization

Use Vector Database (pgvector / Qdrant)

Applicable Use Cases:
  ✅ Product recommendations ("Users who bought A also liked...")
  ✅ Code search ("Find similar function implementations")
  ✅ Customer service ("Find similar historical tickets")
  ✅ Content recommendations ("Similar articles, videos")
  ✅ Image search ("Find similar images")

Characteristics:
  - Fragmented data (each record independent)
  - Requires similarity matching
  - No clear structure

Example:
  xb.Of(&Product{}).
      VectorSearch("embedding", userVector, 20).
      Eq("category", "electronics")

Scenario 2️⃣: Structured Long Document Analysis

Use PageIndex

Applicable Use Cases:
  ✅ Financial report analysis ("How is financial stability in 2024?")
  ✅ Legal contract retrieval ("Chapter 3 breach of contract terms")
  ✅ Technical manual queries ("Which page contains installation steps?")
  ✅ Academic paper reading ("Methodology section content")
  ✅ Policy document analysis ("Specific provisions in Section 2.3")

Characteristics:
  - Long documents (50+ pages)
  - Clear chapter structure
  - Context preservation required

Example:
  xb.Of(&PageIndexNode{}).
      Eq("doc_id", docID).
      Like("title", "Financial Stability").
      Eq("level", 1)

Scenario 3️⃣: Hybrid Retrieval (Structure + Semantics)

Use PageIndex + Vector Database

Applicable Use Cases:
  ✅ Research report Q&A ("Investment advice for tech sector")
  ✅ Knowledge base retrieval (need both structure and semantics)
  ✅ Medical literature analysis ("Treatment plan related chapters")
  ✅ Patent search ("Patents with similar technical solutions")

Characteristics:
  - Both structured and semantic needs
  - Long documents + precise matching requirements

Example:
  // Step 1: PageIndex locates chapter
  xb.Of(&PageIndexNode{}).
      Like("title", "Investment Advice").
      Eq("level", 2)
  
  // Step 2: Vector search within chapter
  xb.Of(&DocumentChunk{}).
      VectorSearch("embedding", queryVector, 10).
      Gte("page", chapterStartPage).
      Lte("page", chapterEndPage)

Scenario 4️⃣: Traditional Business Data

Use Standard SQL (No Vector/PageIndex needed)

Applicable Use Cases:
  ✅ User management ("Find users over 18")
  ✅ Order queries ("Orders in January 2024")
  ✅ Inventory management ("Products with low stock")
  ✅ Statistical reports ("Sales by region")

Characteristics:
  - Structured data
  - Exact condition matching
  - No semantic understanding needed

Example:
  xb.Of(&User{}).
      Gte("age", 18).
      Eq("status", "active").
      Paged(...)

🤔 Quick Decision Tree

Your data is...

├─ Fragmented (products, users, code snippets)
│  └─ Need "similarity" matching?
│     ├─ Yes → Vector Database ✅
│     └─ No  → Standard SQL ✅
│
└─ Long documents (reports, manuals, contracts)
   └─ Has clear chapter structure?
      ├─ Yes → PageIndex ✅
      │  └─ Also need semantic matching?
      │     └─ Yes → PageIndex + Vector ✅
      └─ No → Traditional RAG (chunking + vector) ✅

💡 Core Principles

Don't debate technology choices — Look at data characteristics:

1️⃣ Fragmented data + need similarity
   → Vector Database

2️⃣ Long documents + structured + need chapter location
   → PageIndex

3️⃣ Long documents + unstructured + need semantics
   → Traditional RAG (chunking + vector)

4️⃣ Structured data + exact matching
   → Standard SQL

5️⃣ Complex scenarios
   → Hybrid approach

xb supports all scenarios — One API for everything! ✅

This site is open source. Improve this page.