xb (Extensible Builder)

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:

• 🛡️ InRequired() - Prevent accidental mass operations (batch delete/update)
• 🔧 Builder Validation - QdrantBuilder parameter validation with clear error messages
• 📖 Enhanced Docs - Comprehensive examples for X() and Sub() methods
• 🎯 Production Ready - Zero-constraint design with maximum flexibility

---

🚀 Builder Pattern + Unified Entry (v1.2.1)

One configuration entry for all databases - minimum cognitive load!

✨ Core Features:

• 🎯 Builder Pattern - NewQdrantBuilder(), NewMySQLBuilder() for fluent configuration
• 🔧 Unified Entry - Only Custom() for all operations (INSERT/UPDATE/DELETE/SELECT)
• 📉 Lower Cognitive Load - Humans only remember ONE rule, not two
• 🔗 Chain Style - .HnswEf().ScoreThreshold().Build() - fluent and readable
• ♻️ Config Reuse - Builder pattern naturally supports reusing configurations

// 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:

• ✅ One interface method for all operations (Select/Insert/Update/Delete)
• ✅ Supports both SQL databases (MySQL, Oracle) and vector databases (Qdrant, Milvus)
• ✅ Type-safe: SQLResult for SQL, string for JSON
• ✅ Easy to extend: Implement your own database in minutes

---

🔍 Qdrant Advanced API (since v0.10.0)

The first unified ORM for both Relational and Vector Databases!

✨ New in v0.10.0:

• 🎯 Recommend API - Personalized recommendations with positive/negative samples
• 🔍 Discover API - Explore common themes from user context
• 🔄 Scroll API - Efficient traversal for large datasets
• 🎨 Functional Parameters - Unified builder style
• 🔧 100% Backward Compatible - All existing features preserved

// 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:

• ✅ Unified API for MySQL + VectorDB
• ✅ Type-safe ORM for vectors
• ✅ Auto-optimized hybrid queries
• ✅ 100% backward compatible

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

---

🤖 AI-First Development

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

• 🤖 AI Assistant (Claude via Cursor): Architecture design, code implementation, testing, documentation
• 👨‍💻 Human Maintainer: Code review, strategic decisions, critical algorithm oversight

Maintenance Model (80/15/5)

• 80% of code: AI independently maintains (simple, clear patterns)
• 15% of code: AI assists, human reviews (medium complexity)
• 5% of code: Human leads, AI assists (critical algorithms like from_builder_optimization.go)

v0.8.1 Vector Database Support

Achieved entirely through AI-First development:

• Architecture & Design: AI Assistant (Claude)
• Code Implementation: AI Assistant (763 lines)
• Testing: AI Assistant (13 test cases, 100% passing)
• Documentation: AI Assistant (120+ pages)
• Review & Approval: Human Maintainer

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:

• ignore building nil or empty string
• Smart validation for critical operations
• Type-safe subquery building

Available field of struct:

• base: string, *bool, *int64, *float64, time.Time....
• json: struct, map, array, slice
• bytes: []byte

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:

• Vector Database Quick Start
• Vector Diversity + Qdrant Guide
• All Filtering Mechanisms
• Custom Vector DB Guide
• Custom JOINs Guide
• Contributors

AI Application Ecosystem:

• AI Application Docs → - Complete AI/RAG/Agent integration guide
• AI Agent Toolkit - JSON Schema, OpenAPI
• RAG Best Practices - Document retrieval guide
• LangChain Integration - Python LangChain
• Performance Optimization - AI app tuning

Complete Application Examples:

• Examples → - Full working applications
• PostgreSQL + pgvector App - Code search
• Qdrant Integration App - Document retrieval
• RAG Application - Full RAG system
• PageIndex App - Structured document retrieval

Contributing

We warmly welcome all forms of contributions! 🎉

• 🐛 Report bugs: GitHub Issues
• 💡 Request features: GitHub Issues
• 💬 Discuss ideas: GitHub Discussions
• 💻 Submit code: See CONTRIBUTING

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
• Join Example

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! ✅