# sqlxb

**Repository Path**: x-ream/sqlxb

## Basic Information

- **Project Name**: sqlxb
- **Description**: sqlxb is a tool of sql golang builder, build sql for sqlx,  build condition sql for gorm....
- **Primary Language**: Go
- **License**: Apache-2.0
- **Default Branch**: main
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-06-08
- **Last Updated**: 2025-10-27

## Categories & Tags

**Categories**: Uncategorized

**Tags**: sqlx, sqlxb, builder, query-builder

## README

# sqlxb  
[![OSCS Status](https://www.oscs1024.com/platform/badge/x-ream/sqlxb.svg?size=small)](https://www.oscs1024.com/project/x-ream/sqlxb?ref=badge_small)
![workflow build](https://github.com/x-ream/sqlxb/actions/workflows/go.yml/badge.svg)
[![GitHub tag](https://img.shields.io/github/tag/x-ream/sqlxb.svg?style=flat)](https://github.com/x-ream/sqlxb/tags)
[![Go Report Card](https://goreportcard.com/badge/github.com/x-ream/sqlxb)](https://goreportcard.com/report/github.com/x-ream/sqlxb)

**AI-First SQL Builder** for Relational and Vector Databases

A tool of sql query builder, build sql for sql.DB, [sqlx](https://github.com/jmoiron/sqlx), [gorp](https://github.com/go-gorp/gorp),
or build condition sql for some orm framework, like [xorm](https://github.com/go-xorm/xorm), [gorm](https://github.com/go-gorm/gorm)....
also can build json for some json parameter db, like [Qdrant](https://github.com/qdrant/qdrant) ....


> 📅 **v1.0.0 Roadmap**: Planning for release in June 2026. See [ROADMAP_v1.0.0.md](doc/ROADMAP_v1.0.0.md) for details.

---

## 🚀 NEW: Qdrant Advanced API (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

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

// VectorDB (v0.10.0) - Same API!
sqlxb.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 →](./doc/VECTOR_README.md)**

**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

sqlxb 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 sqlxb **one of the first major Go ORM projects successfully maintained by AI**.

---

## Program feature:
* ignore building nil or empty string

## 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 := sqlxb.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 →](./doc/README.md)**

Quick links:
- [Vector Database Quick Start](./doc/VECTOR_QUICKSTART.md)
- [Vector Diversity + Qdrant Guide](./doc/VECTOR_DIVERSITY_QDRANT.md)
- [All Filtering Mechanisms](./doc/ALL_FILTERING_MECHANISMS.md)
- [Custom Vector DB Guide](./doc/CUSTOM_VECTOR_DB_GUIDE.md) - 扩展其他向量数据库
- [Custom JOINs Guide](./doc/CUSTOM_JOINS_GUIDE.md) - 扩展自定义 JOIN
- [Contributors](./doc/CONTRIBUTORS.md)

## Contributing

Contributors are welcomed to join the sqlxb project. <br>
Please check [CONTRIBUTING](./doc/CONTRIBUTING.md)

## Quickstart

* [Single Example](#single-example)
* [Join Example](#join-example)


### Single Example

```Go

import (
    . "github.com/x-ream/sqlxb"
)

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....
// sqlxb has func: Bool(true), Int(v) ....
// sqlxb 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

```Go
import (
        . "github.com/x-ream/sqlxb"
    )
    
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")
                    })
                })
    
}


```