SQLE

https://github.com/yaitoo/sqle
Admin
SQLE is a Go package that enhances the `database/sql` package with SQL-first, ORM-like features,...
Tokens:22,914
Snippets:100
Trust Score:4.8
License:MIT
Update:11 months ago
Show doc for...
Tokens:
Raw
### SQLE Quickstart: Connect and Select

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates how to open a database connection using SQLE, construct a SELECT query with a WHERE clause, and bind the result into a User struct. This example uses the SQLBuilder to create parameterized queries.

```go
sqldb, _ := sql.Open("mysql", dsn)
db := sqle.Open(sqldb)
b := sqle.New().Select("users").Where("id={id}").Param("id", 1)
var u User
_ = db.QueryRowBuilder(ctx, b).Bind(&u)
```

--------------------------------

### Sqle Explicit Transaction Management

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Provides an example of managing database transactions explicitly using `BeginTx`. It shows how to start a transaction, execute commands, and handle commit or rollback.

```go
tx, _ := db.BeginTx(ctx, &sql.TxOptions{})
deftx.Rollback()
_, _ = tx.ExecContext(ctx, "UPDATE t SET v=? WHERE id=?", v, id)
if err := tx.Commit(); err != nil { /* handle */ }
```

--------------------------------

### Error Handling Examples in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Provides examples of potential errors when using Sqle, such as missing parameters and incorrect map key types, and how to check for them using `errors.Is`.

```go
_, _, err := sqle.New("SELECT * FROM t WHERE a={a}").Build() // missing Param("a", ...)
if errors.Is(err, sqle.ErrInvalidParamVariable) { /* handle */ }

var m map[int]any
err = db.QueryBuilder(ctx, sqle.New().Select("t")).Bind(&m)
// -> ErrMustStringKey because map key must be string
```

--------------------------------

### Sqle Driver Parameter Styles

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates how to configure Sqle to use different parameter styles for various database drivers. It shows examples for PostgreSQL, Oracle, and MySQL.

```go
b := sqle.New("SELECT * FROM t WHERE a={a} AND b={b}").Param("a",1).Param("b",2)
sqle.UsePostgres(b) // $1, $2 ...
// sqle.UseOracle(b) // :a, :b
// sqle.UseMySQL(b)  // ? (default)
```

--------------------------------

### SQLE Quickstart: Insert Data

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to construct an INSERT statement using SQLE's SQLBuilder and execute it. The `Set` method is used to specify column-value pairs, and `End` finalizes the statement.

```go
b = sqle.New().Insert("users").Set("email", email).Set("name", name).End()
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Distributed Transaction (DTC) Setup

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates the setup and basic usage of Distributed Transactions (DTC) in sqle. Participants are added using `Prepare`, defining `exec` and `revert` functions for each. Commit attempts to execute all participants' actions and commit their transactions, while Rollback handles cleanup.

```go
dtc, err := sqle.NewDTC(ctx, nil)

// Prepare participants
err = dtc.Prepare(client1, execFunc1, revertFunc1)
err = dtc.Prepare(client2, execFunc2, revertFunc2)

// Attempt to commit the transaction
err = dtc.Commit()

// If commit fails, rollback
if err != nil {
	dtc.Rollback()
}
```

--------------------------------

### Database Transactions with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates how to execute multiple database operations within a transaction using `db.Transaction`. It includes examples of updating data and reading within the transaction.

```go
_ = db.Transaction(ctx, nil, func(ctx context.Context, tx *sqle.Tx) error {
    if _, err := tx.Exec("UPDATE accounts SET balance=balance-? WHERE id=?", amt, from); err != nil { return err }
    if _, err := tx.Exec("UPDATE accounts SET balance=balance+? WHERE id=?", amt, to); err != nil { return err }
    // Read within tx
    var u User
    if err := tx.QueryRowBuilder(ctx, sqle.New().Select("users").Where("id={id}").Param("id", to)).Bind(&u); err != nil { return err }
    return nil
})
```

--------------------------------

### DHT-based Sharding with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates setting up DHT-based sharding for a 'users' table and obtaining a specific connector based on a key (e.g., email).

```go
db.NewDHT("users", 0,1,2,3)
c, _ := db.OnDHT(email, "users")
```

--------------------------------

### Sqle Limited Queries with LimitOptions/LimitResult

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to implement pagination and ordering for queries using `LimitOptions` and `LimitResult`. It defines allowed order columns, page size, and builds SQL with LIMIT and OFFSET clauses.

```go
// Define allowed order columns and page size
ob := sqle.New().Order(sqle.WithAllow("created_at","id"))
ob.ByDesc("created_at")

opts := &sqle.LimitOptions{Offset: (page-1)*size, Limit: size, OrderBy: ob}

// Build base query then apply order/limit
b := sqle.New().Select("users").Where().
    If(filter != "").And("name LIKE {kw}").Param("kw", "%"+filter+"%").End()
if opts.OrderBy != nil {
    b.WithOrderBy(opts.OrderBy)
}
if opts.Limit > 0 { b.SQL(" LIMIT {limit}").Param("limit", opts.Limit) }
if opts.Offset > 0 { b.SQL(" OFFSET {offset}").Param("offset", opts.Offset) }

// Fetch items and total
rows, _ := db.QueryBuilder(ctx, b)
var items []User
_ = rows.Bind(&items)

var total int64
_ = db.QueryRowBuilder(ctx, sqle.New("SELECT COUNT(*) FROM users")).Scan(&total)

res := sqle.LimitResult[User]{Items: items, Total: total}
```

--------------------------------

### Time Window Queries with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates querying data within a specific time window using `sqle.WithMonths` and providing a custom comparison function for sorting.

```go
q := sqle.NewQuery[Order](db, sqle.WithMonths(start, end))
b = sqle.New().Select("orders<rotate>").Where("member_id={mid}").Param("mid", mid)
list, _ := q.Query(ctx, b, func(i, j Order) bool { return i.CreatedAt.After(j.CreatedAt) })
```

--------------------------------

### Map/Reduce Querying Helpers in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates how to use Sqle's query builder for map/reduce-style operations. It shows counting across rotations/shards and fetching the first result from the fastest shard/rotation.

```go
// Count across rotations and shards
qb := sqle.New("SELECT COUNT(*) FROM logs<rotate> WHERE level={lv}").Param("lv", "warn")
q := sqle.NewQuery[int64](db, sqle.WithDays(start, end))
_, _ = q.Count(ctx, qb)

// First returns fastest shard/rotation result
var one Log
one, _ = sqle.NewQuery[Log](db, sqle.WithWeeks(w1, w2)).First(ctx, sqle.New().Select("logs<rotate>").Where("id={id}").Param("id", lid))
```

--------------------------------

### High-Level Querying with MapR

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to perform high-level queries across multiple shards and rotated tables using the MapR implementation. Supports operations like getting the first result (race conditions handled), summing counts, and applying limits per-database before final trimming.

```go
// Query across shards and rotated tables
results, err := sqle.NewQuery[User](db, options...).Query(ctx, builder, less)

// Get first completed result
firstResult, err := sqle.NewQuery[User](db, options...).First(ctx, builder)

// Count results across all shards/rotations
totalCount, err := sqle.NewQuery[User](db, options...).Count(ctx, builder)

// Query with limit applied per-db
limitedResults, err := sqle.NewQuery[User](db, options...).QueryLimit(ctx, builder, 10)
```

--------------------------------

### Database Migration and Rotation with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to manage database migrations and table rotations using Sqle's migrate package. It covers discovering migrations from embedded files, initializing, applying, and rotating tables.

```go
//go:embed db
var migrations embed.FS
m := migrate.New(db)
_ = m.Discover(migrations, migrate.WithModule("service"))
_ = m.Init(ctx)
_ = m.Migrate(ctx) // apply semver directories
_ = m.Rotate(ctx)  // create rotated tables for current/next periods
```

--------------------------------

### Order Builder Reuse and Pagination in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Explains how to reuse an order builder configuration and apply it to a base query. It also shows how to add LIMIT and OFFSET clauses for pagination.

```go
base := sqle.New().Select("users").Where().End()
ob := base.Order(sqle.WithAllow("created_at","name"))
ob.ByDesc("created_at")
base.WithOrderBy(ob)
rows, _ := db.QueryBuilder(ctx, base)

ob.ByDesc("created_at").ByAsc("name")
// Pagination
b.SQL(" LIMIT {limit} OFFSET {offset}").Param("limit", q.Limit).Param("offset", q.Offset)
rows, _ := db.QueryBuilder(ctx, b)
var users []User
_ = rows.Bind(&users)
```

--------------------------------

### Basic Querying with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates basic row querying and binding using Sqle's QueryRowBuilder. It selects profile data based on an email and binds it to a user struct.

```go
var u User
_ = c.QueryRowBuilder(ctx, sqle.New().Select("profiles").Where("email={email}").Param("email", email)).Bind(&u)
```

--------------------------------

### Install SQLE Package in Go

Source: https://github.com/yaitoo/sqle/blob/main/README.md

Provides instructions for installing the SQLE package in a Go project. Users can choose to install the latest commit from the main branch or the latest stable release.

```sh
go get github.com/yaitoo/sqle@main
```

```sh
go get github.com/yaitoo/sqle@latest
```

--------------------------------

### SQL Builder Construction

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates the basic construction of SQL queries using the sqle Builder. Supports SELECT, INSERT, UPDATE, and DELETE statements with WHERE clauses and parameter binding. Uses tokens like {name} for parameters and <name> for trusted inputs.

```go
b := sqle.New("SELECT * FROM users").Where("id={id}").Param("id", 1)

b.Select("users").Where().And("email={email}").Param("email", v)

// Insert
sqle.New().Insert("users").Set("email", v).End()

// Update
sqle.New().Update("users").Set("name", v).Where("id={id}").Param("id", id)

// Delete
sqle.New().Delete("users").Where("id={id}").Param("id", id)
```

--------------------------------

### Database Transaction Management

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Provides an example of how to manage database transactions using sqle. The `db.Transaction` function handles automatic commit or rollback based on the provided function's execution. The `Tx` object supports Query/Exec and the SQL Builder, utilizing a local prepared statement cache.

```go
err := db.Transaction(ctx, nil, func(ctx context.Context, tx *sqle.Tx) error {
	// Perform operations within the transaction
	_, err := tx.Exec(ctx, "UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, accountID)
	if err != nil {
		return err // Rollback will be triggered
	}
	// ... other operations
	return nil // Commit will be triggered
})
```

--------------------------------

### SQLE: Postgres Placeholders and Building SQL

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates how to use SQLE with Postgres-specific placeholders (e.g., $1, $2). The `sqle.UsePostgres` function modifies the builder to use Postgres syntax, and `Build` generates the SQL and arguments.

```go
b := sqle.New("SELECT * FROM users WHERE id={id}")
sqle.UsePostgres(b)
_, _, _ = b.Build() // uses $1, $2 ...
```

--------------------------------

### Sqle Params and Params(map) for Multiple Parameters

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates setting multiple parameters at once using `Params` with a map. This is useful for updating multiple columns in a single statement.

```go
b := sqle.New("UPDATE t SET a={a}, b={b} WHERE id={id}").
    Params(map[string]any{"a":1, "b":2, "id": id})
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Sharding with Table Rotation and QueryLimit in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates configuring sharding with monthly table rotation using `shardid.New`. It shows writing to rotated tables and performing queries with a limit.

```go
// Configure generator: 4 databases, monthly rotation
gen := shardid.New(shardid.WithDatabase(4), shardid.WithMonthlyRotate())
id := gen.Next()

// Write to rotated table on selected DB
b := sqle.New().On(id).Insert("orders<rotate>").
    Set("order_id", oid).Set("amount", amt).End()
_, _ = db.On(id).ExecBuilder(ctx, b)

b := sqle.New().Select("users").Where("role={r}").Param("r", "admin")
list, _ := q.QueryLimit(ctx, b, func(i, j User) bool { return i.ID < j.ID }, 20)
```

--------------------------------

### SQLE: Configure Statement Cache and Multi-DB

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates configuring the statement cache idle time and opening connections to multiple database shards. It also shows how to add more database shards dynamically to the SQLE client.

```go
sqle.StmtMaxIdleTime = 2 * time.Minute
sqldb1, _ := sql.Open("mysql", dsn1)
sqldb2, _ := sql.Open("mysql", dsn2)
db := sqle.Open(sqldb1, sqldb2) // two shards
// later scale out
sqldb3, _ := sql.Open("mysql", dsn3)
db.Add(sqldb3)
```

--------------------------------

### Sqle OrderBy Builder

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates creating an `OrderBy` clause using the `NewOrderBy` builder. It allows specifying allowed columns and multiple sorting criteria (descending and ascending).

```go
ob := sqle.NewOrderBy(sqle.WithAllow("created_at","name")).ByDesc("created_at").ByAsc("name")
b := sqle.New().Select("users")
b.WithOrderBy(ob)
rows, _ := db.QueryBuilder(ctx, b)
```

--------------------------------

### Migration and Rotation Management

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates how to use the migration system for managing database schema changes and table rotations. It covers discovering migration files, applying rotations based on versioned folders and SQL files, and managing metadata tables like `sqle_migrations`.

```go
// Initialize migration system
migrate := sqle.New(db1, db2)

// Discover and apply migrations from a file system
err := migrate.Discover(fs, sqle.WithModule("auth"))

// Initialize metadata tables
err = migrate.Init(ctx)

// Ensure rotated tables exist for the current and next period
err = migrate.Rotate(ctx)
```

--------------------------------

### Sqle DHT Error Handling and Creation

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates handling `sqle.ErrMissingDHT` errors and creating a new DHT (Distributed Hash Table) if one is missing. It shows checking for DHT existence and creating it if necessary.

```go
_, err := db.OnDHT(email, "users")
if errors.Is(err, sqle.ErrMissingDHT) {
    db.NewDHT("users", 0,1,2,3)
}
c, _ := db.OnDHT(email, "users")
_ = c.QueryRow("SELECT 1").Err()
```

--------------------------------

### Custom Queryer Injection in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to inject a custom `Queryer[T]` implementation into `sqle.NewQuery` for flexible data retrieval logic.

```go
// Implement a custom Queryer[T] if needed and inject
var my Queryer[User] = &MyQueryer{}
q := sqle.NewQuery[User](db, sqle.WithQueryer(my))
users, _ := q.Query(ctx, sqle.New().Select("users"), nil)
```

--------------------------------

### SQL Query Builder with Named Parameters and Conditional Logic

Source: https://context7.com/yaitoo/sqle/llms.txt

This example illustrates using SQLE's `sqle.New` builder to construct SQL queries programmatically with named parameters for SQL injection prevention. It demonstrates basic query building, executing queries using `db.QueryBuilder`, and incorporating conditional logic (`.If()`) for dynamic query construction. The `setupDatabase()` function is assumed to be defined elsewhere, returning a `*sqle.DB` instance.

```go
package main

import (
    "context"
    "fmt"
    "github.com/yaitoo/sqle"
)

func main() {
    db := setupDatabase() // assume this returns *sqle.DB

    // Build a SELECT query with named parameters
    builder := sqle.New("SELECT * FROM users").
        SQL(" WHERE age > {min_age}").
        SQL(" AND city = {city}").
        Param("min_age", 18).
        Param("city", "New York")

    // Execute and get the built query (for debugging)
    query, args, err := builder.Build()
    if err != nil {
        panic(err)
    }
    fmt.Printf("Query: %s\nArgs: %v\n", query, args)
    // Output: Query: SELECT * FROM users WHERE age > ? AND city = ?
    // Args: [18 New York]

    // Execute the query
    rows, err := db.QueryBuilder(context.Background(), builder)
    if err != nil {
        panic(fmt.Sprintf("query failed: %v", err))
    }
    defer rows.Close()

    // Conditional query building
    searchName := "John"
    includeInactive := false

    conditionalBuilder := sqle.New("SELECT * FROM users WHERE 1=1").
        If(searchName != "").SQL(" AND name LIKE {name}").Param("name", "%"+searchName+"%").
        If(includeInactive).SQL(" AND status = 'inactive'")

    rows, err = db.QueryBuilder(context.Background(), conditionalBuilder)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
}
```

--------------------------------

### Update and Delete Operations in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates how to perform UPDATE and DELETE operations on a 'users' table using the Sqle builder. It shows setting specific columns and using WHERE clauses with parameters.

```go
// Update
b := sqle.New().Update("users").
    Set("name", name).
    Where("id={id}").Param("id", id)
_, _ = db.ExecBuilder(ctx, b)

// Delete
b = sqle.New().Delete("users").Where("id={id}").Param("id", id)
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Conditional WHERE and ORDER BY with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates building SELECT queries with conditional WHERE clauses and ordering. The `If` method allows for dynamic addition of conditions, and `WithAllow` restricts sortable columns.

```go
b := sqle.New().Select("users").Where().
    If(q.Name != "").And("name={name}").Param("name", q.Name).
    If(q.Email != "").And("email={email}").Param("email", q.Email).
    End()
// Restrict allowed columns
ob := b.Order(sqle.WithAllow("created_at","name"))
```

--------------------------------

### Update Operations with SetExpr and SetMap in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates advanced UPDATE capabilities, including setting values using SQL expressions (e.g., `NOW()`) and updating multiple columns from a map with an allow-list.

```go
// Expression update
b := sqle.New().Update("users").SetExpr("last_login = NOW()")
// Mixed with params
b.Set("name", name).Where("id={id}").Param("id", id)
_, _ = db.ExecBuilder(ctx, b)

// Map update (allow-list columns)
changes := map[string]any{"name": name, "email": email, "role": role}
b = sqle.New().Update("users").SetMap(changes, sqle.WithAllow("name","email"))
b.Where("id={id}").Param("id", id)
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Go: Database Sharding with `sqle` and ShardID

Source: https://context7.com/yaitoo/sqle/llms.txt

Illustrates horizontal scaling using database sharding with `sqle`. It demonstrates distributing data across multiple database instances based on ShardID. The example covers opening multiple database connections, configuring a ShardID generator for a specific number of shards, and performing insert and select operations, automatically directing them to the correct database. It also shows combining sharding with table rotation.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/yaitoo/sqle"
    "github.com/yaitoo/sqle/shardid"
)

func main() {
    // Open multiple database connections
    sqldb1, _ := sql.Open("mysql", "user:pass@tcp(db1.example.com:3306)/orders")
    sqldb2, _ := sql.Open("mysql", "user:pass@tcp(db2.example.com:3306)/orders")
    sqldb3, _ := sql.Open("mysql", "user:pass@tcp(db3.example.com:3306)/orders")

    db := sqle.Open(sqldb1, sqldb2, sqldb3)
    ctx := context.Background()

    // Create generator with 3 database shards
    gen := shardid.New(shardid.WithDatabase(3))

    // Generate ID and insert into appropriate shard
    orderID := gen.Next()
    fmt.Printf("Generated order ID: %d for database shard: %d\n", orderID.Int64, orderID.DatabaseID)

    builder := sqle.New().Insert("orders").
        Set("order_id", orderID.Int64).
        Set("user_id", 456).
        Set("amount", 149.99).
        End()

    // db.On(orderID) automatically selects the correct database based on orderID.DatabaseID
    result, err := db.On(orderID).ExecBuilder(ctx, builder)
    if err != nil {
        panic(fmt.Sprintf("insert failed: %v", err))
    }
    fmt.Println("Inserted into correct database shard")

    // Query from the correct shard
    builder = sqle.New().Select("orders").
        Where("order_id = {order_id}").
        Param("order_id", orderID.Int64)

    var order map[string]any
    err = db.On(orderID).QueryRowBuilder(ctx, builder).Bind(&order)
    if err != nil {
        panic(err)
    }
    fmt.Printf("Retrieved order from shard %d: %+v\n", orderID.DatabaseID, order)

    // Combine sharding with table rotation
    genWithRotation := shardid.New(
        shardid.WithDatabase(3),
        shardid.WithMonthlyRotate(),
    )

    id := genWithRotation.Next()
    fmt.Printf("ID with sharding and rotation: %d (DB: %d, Rotate: %s)\n",
        id.Int64, id.DatabaseID, id.RotateName())

    builder = sqle.New().On(id).
        Insert("orders<rotate>").
        Set("order_id", id.Int64).
        Set("user_id", 789).
        Set("amount", 199.99).
        End()

    // This inserts into the correct database shard AND rotated table
    // e.g., db2.orders_202411
    result, err = db.On(id).ExecBuilder(ctx, builder)
    if err != nil {
        panic(err)
    }
    fmt.Println("Inserted into sharded and rotated table successfully")
}

```

--------------------------------

### Data Binding Variations in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates different ways to bind query results into Go variables, including single primitives, single structs, slices of structs, and slices of maps.

```go
// Single primitive
var n int64
_ = db.QueryRow("SELECT COUNT(*) FROM users").Scan(&n)

// Single struct
var u User
_ = db.QueryRowBuilder(ctx, sqle.New().Select("users").Where("id={id}").Param("id", id)).Bind(&u)

// Slice of structs
var list []User
_ = db.QueryBuilder(ctx, sqle.New().Select("users")).Bind(&list)

// Slice of maps
var items []map[string]any
_ = db.QueryBuilder(ctx, sqle.New().Select("users")).Bind(&items)
```

--------------------------------

### Extending sqle with Custom Drivers

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Explains how to extend the sqle library by adding support for new database drivers or custom query execution logic. This involves implementing functions to set driver-specific quoting and parameterization, or providing a custom `Queryer` implementation.

```go
// Add a custom driver style
func UseMyDriver(b *sqle.Builder) {
	b.Parameterize = myParamFunc
	b.Quote = myQuoteFunc
}

// Use the custom driver
// b.UseMyDriver()

// Implement a custom query engine
// qr := &MyCustomQueryer{...}
// sqle.NewQuery[T](db, sqle.WithQueryer(qr))...
```

--------------------------------

### Driver Configuration for SQL Builder

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to configure the SQL Builder for different database drivers like PostgreSQL, MySQL, or Oracle. This affects parameterization style (e.g., ?, $1, :name) and quoting. This configuration should be called once per Builder instance before building the query.

```go
// Example for PostgreSQL
b.UsePostgres()

// Example for MySQL
b.UseMySQL()

// Example for Oracle
b.UseOracle()
```

--------------------------------

### Distributed Transactions (DTC) with Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to implement distributed transactions across multiple databases using Sqle's DTC. It involves preparing operations on different databases and committing or rolling back.

```go
dtc := sqle.NewDTC(ctx, nil)
dtc.Prepare(db1, func(ctx context.Context, c sqle.Connector) error {
    _, err := c.Exec("INSERT INTO a(id,val) VALUES(?,?)", id, v); return err
}, func(ctx context.Context, c sqle.Connector) error {
    _, err := c.Exec("DELETE FROM a WHERE id=?", id); return err
})
dtc.Prepare(db2, func(ctx context.Context, c sqle.Connector) error {
    _, err := c.Exec("INSERT INTO b(id,val) VALUES(?,?)", id, v); return err
}, func(ctx context.Context, c sqle.Connector) error {
    _, err := c.Exec("DELETE FROM b WHERE id=?", id); return err
})
if err := dtc.Commit(); err != nil { _ = dtc.Rollback() }
```

--------------------------------

### Sqle Inputs and Params for Trusted Identifiers

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates how to use `Input` for trusted identifiers like table names, ensuring they are correctly quoted and preventing SQL injection. `Param` is used for regular parameter binding.

```go
// Trusted identifier/table name via Input (ensure validated!)
b := sqle.New("SELECT * FROM <tbl> WHERE id={id}").
    Input("tbl", "`users`").Param("id", 1)
_, _, _ = b.Build()
```

--------------------------------

### Insert Data using Map and Model in Sqle

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows two ways to insert data into the 'users' table: using a map of column values and using a struct (ORM-like). Both methods utilize the Sqle builder.

```go
inputs := map[string]any{"email": email, "name": name}
b := sqle.New().Insert("users").SetMap(inputs).End()
_, _ = db.ExecBuilder(ctx, b)

// ORM-like: from struct
b = sqle.New().Insert("users").SetModel(user).End()
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Sqle Nullable, Time, Duration, and Bool Helpers

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Illustrates the use of Sqle's type helpers for nullable values, time, duration, and booleans. These types can be directly used in SQL statements and marshalled to JSON.

```go
var s sqle.String = sqle.NewString("v")
var t sqle.Time = sqle.NewTime(time.Now(), true)
var d sqle.Duration = sqle.Duration(5*time.Minute)
var bflag sqle.Bool = sqle.Bool(true)
_, _ = db.Exec("INSERT INTO x(s,t,d,b) VALUES(?,?,?,?)", s, t, d, bflag)
```

--------------------------------

### Sqle Update with SetModel

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates updating a record using `SetModel`, which automatically maps struct fields to SQL columns. The `user` struct fields are used to update the `users` table based on the provided ID.

```go
b := sqle.New().Update("users").SetModel(user).Where("id={id}").Param("id", user.ID)
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Sharding and Rotation Configuration

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates how to configure sharding and table rotation using sqle. The `shardid.ID` type encodes database and rotation information. Placeholders like `<rotate>` in table names are used for rotation, and `db.On(id)` selects the appropriate client.

```go
// Set input for rotation
b.On(shardID)

// Select client by database ID
db.On(shardID)

// Example with rotated table name
// sqle.New("SELECT * FROM orders<rotate>")

// Querying with time windows
// NewQuery[T](db, options...).WithMonths(1).Query(ctx, builder, less)
```

--------------------------------

### Sqle Column Name Transformation with WithToName

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to transform column names using `WithToName` and a function like `strcase.ToSnake`. This is useful when inserting or updating data from a map where keys need to be converted to snake_case for the database.

```go
changes := map[string]any{"UserName": name, "CreatedAt": createdAt}
b := sqle.New().Insert("users").SetMap(changes, sqle.WithToName(strcase.ToSnake)).End()
_, _ = db.ExecBuilder(ctx, b)
```

--------------------------------

### Database Connection Initialization with SQLE Wrapper

Source: https://context7.com/yaitoo/sqle/llms.txt

This snippet demonstrates how to wrap a standard `sql.DB` instance using SQLE's `sqle.Open` function to enable enhanced features. It covers both single database connections and multiple connections for sharded environments. Ensure the appropriate database driver (e.g., `github.com/go-sql-driver/mysql`) is imported.

```go
package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/yaitoo/sqle"
)

func main() {
    // Open a single database connection
    sqldb, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname?parseTime=true")
    if err != nil {
        panic(fmt.Sprintf("failed to open database: %v", err))
    }

    db := sqle.Open(sqldb)

    // Verify connection
    if err := db.Ping(); err != nil {
        panic(fmt.Sprintf("database is unreachable: %v", err))
    }

    // Open multiple database connections for sharding
    sqldb1, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/db1")
    sqldb2, _ := sql.Open("mysql", "user:password@tcp(localhost:3307)/db2")
    sqldb3, _ := sql.Open("mysql", "user:password@tcp(localhost:3308)/db3")

    shardedDB := sqle.Open(sqldb1, sqldb2, sqldb3)

    fmt.Println("Database connections established successfully")
}
```

--------------------------------

### Sharded Write Operation

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Performs a write operation on a specific shard. This recipe demonstrates how to target a shard using `db.On(id)` and execute a builder operation on that shard.

```go
package main

import (
	"context"
	"fmt"
	"github.com/yaitoo/sqle"
	"github.com/yaitoo/sqle/sqlbuilder"
)

func main() {
	db := sqle.NewDB()
	ctx := context.Background()

	shardID := "user_shard_123"

	// Create a builder for the write operation
	builder := sqlbuilder.Update().Table("users").Set("status", "active").Where("id = ?", 1)

	// Execute the write operation on the specified shard
	_, err := db.On(shardID).ExecBuilder(ctx, builder)

	if err != nil {
		fmt.Printf("Error performing sharded write on shard %s: %v\n", shardID, err)
		return
	}

	fmt.Printf("Successfully performed sharded write on shard %s\n", shardID)
}

```

--------------------------------

### Connect to Database using SQLE in Go

Source: https://github.com/yaitoo/sqle/blob/main/README.md

Demonstrates how to establish a connection to various SQL databases (SQLite, MySQL) using the SQLE package. It utilizes a switch statement based on a driver configuration to open the appropriate database connection and then wraps it with `sqle.Open`.

```go
import (
    "database/sql"
    "fmt"
    "github.com/yaitoo/sqle"
    _ "github.com/go-sql-driver/mysql"
    _ "github.com/mattn/go-sqlite3"
    "github.com/spf13/viper"
)

// ... inside a function or method
driver := viper.GetString("db.driver")
dsnn := viper.GetString("db.dsn")

var db *sqle.DB

switch driver {
case "sqlite":
    sqldb, err := sql.Open("sqlite3", "file:"+dsn+"?cache=shared")
    if err != nil {
        panic(fmt.Sprintf("db: failed to open sqlite database %s", dsn))
    }
    db = sqle.Open(sqldb)

case "mysql":
    sqldb, err := sql.Open("mysql", dsn)
    if err != nil {
        panic(fmt.Sprintf("db: failed to open mysql database %s", dsn))
    }
    db = sqle.Open(sqldb)

default:
    panic(fmt.Sprintf("db: driver %s is not supported yet", driver))
}

if err := db.Ping(); err == nil {
    panic("db: database is unreachable")
}
```

--------------------------------

### Go: Table Rotation with Time-Based Partitioning using ShardID

Source: https://context7.com/yaitoo/sqle/llms.txt

Demonstrates how to use ShardID with `sqle` for automatic time-based partitioning of tables. It shows generating IDs, appending time-based suffixes to table names, and performing insert, select, and delete operations on rotated tables. Dependencies include `context`, `fmt`, `github.com/yaitoo/sqle`, and `github.com/yaitoo/sqle/shardid`.

```go
package main

import (
    "context"
    "fmt"
    "github.com/yaitoo/sqle"
    "github.com/yaitoo/sqle/shardid"
)

func main() {
    db := setupDatabase()
    ctx := context.Background()

    // Create generator with monthly rotation
    gen := shardid.New(shardid.WithMonthlyRotate())
    orderID := gen.Next()

    fmt.Printf("Generated order ID: %d\n", orderID.Int64)
    fmt.Printf("Table rotation suffix: %s\n", orderID.RotateName())

    // Insert into rotated table
    // The <rotate> placeholder will be replaced with the rotation suffix (e.g., "_202411")
    builder := sqle.New().On(orderID). // Enable rotation with this ID
        Insert("orders<rotate>").
        Set("order_id", orderID.Int64).
        Set("user_id", 123).
        Set("amount", 99.99).
        Set("status", "pending").
        End()

    // This will execute: INSERT INTO orders_202411 (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)
    result, err := db.ExecBuilder(ctx, builder)
    if err != nil {
        panic(fmt.Sprintf("insert failed: %v", err))
    }
    fmt.Println("Inserted into rotated table successfully")

    // Query from rotated table
    builder = sqle.New().On(orderID).
        Select("orders<rotate>").
        Where("order_id = {order_id}").
        Param("order_id", orderID.Int64)

    // This will execute: SELECT * FROM orders_202411 WHERE order_id = ?
    var order map[string]any
    err = db.QueryRowBuilder(ctx, builder).Bind(&order)
    if err != nil {
        panic(err)
    }
    fmt.Printf("Retrieved order: %+v\n", order)

    // Delete from rotated table
    builder = sqle.New().On(orderID).
        Delete("orders<rotate>").
        Where("order_id = {order_id}").
        Param("order_id", orderID.Int64)

    // This will execute: DELETE FROM orders_202411 WHERE order_id = ?
    result, err = db.ExecBuilder(ctx, builder)
    if err != nil {
        panic(err)
    }

    rowsAffected, _ := result.RowsAffected()
    fmt.Printf("Deleted %d rows from rotated table\n", rowsAffected)
}

```

--------------------------------

### Database Sharding with DHT in Go

Source: https://context7.com/yaitoo/sqle/llms.txt

This Go code demonstrates how to set up and use Distributed Hash Table (DHT) for consistent hashing to shard databases. It shows how to open multiple database connections, initialize DHT for sharding based on string keys (like user emails), execute inserts and queries on the correct shard, and manage multiple DHTs for different sharding strategies.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/yaitoo/sqle"
)

func main() {
    // Open multiple database connections
    sqldb1, _ := sql.Open("mysql", "user:pass@tcp(db1:3306)/users")
    sqldb2, _ := sql.Open("mysql", "user:pass@tcp(db2:3306)/users")
    sqldb3, _ := sql.Open("mysql", "user:pass@tcp(db3:3306)/users")

    db := sqle.Open(sqldb1, sqldb2, sqldb3)
    ctx := context.Background()

    // Create DHT with database indices 0, 1, 2
    db.NewDHT("users", 0, 1, 2)

    // Shard by user email
    userEmail := "alice@example.com"

    // Get the appropriate database client for this key
    client, err := db.OnDHT(userEmail, "users")
    if err != nil {
        panic(fmt.Sprintf("DHT lookup failed: %v", err))
    }

    fmt.Printf("Email '%s' maps to database index: %d\n", userEmail, client.Index)

    // Insert user into the correct shard
    builder := sqle.New().Insert("users").
        Set("email", userEmail).
        Set("name", "Alice").
        Set("age", 30).
        End()

    result, err := client.ExecBuilder(ctx, builder)
    if err != nil {
        panic(err)
    }
    fmt.Println("User inserted into correct shard")

    // Query user from the correct shard
    builder = sqle.New().Select("users").
        Where("email = {email}").
        Param("email", userEmail)

    var user map[string]any
    err = client.QueryRowBuilder(ctx, builder).Bind(&user)
    if err != nil {
        panic(err)
    }
    fmt.Printf("Retrieved user from shard %d: %+v\n", client.Index, user)

    // Multiple DHTs for different sharding strategies
    db.NewDHT("orders", 0, 1) // Orders across 2 databases
    db.NewDHT("logs", 0, 1, 2) // Logs across 3 databases

    orderKey := "order_12345"
    client, err = db.OnDHT(orderKey, "orders")
    if err != nil {
        panic(err)
    }
    fmt.Printf("Order key '%s' maps to database index: %d\n", orderKey, client.Index)

    logKey := "user_789_activity"
    client, err = db.OnDHT(logKey, "logs")
    if err != nil {
        panic(err)
    }
    fmt.Printf("Log key '%s' maps to database index: %d\n", logKey, client.Index)
}

```

--------------------------------

### Run Unit Tests with Make

Source: https://github.com/yaitoo/sqle/blob/main/CONTRIBUTING.md

This command executes the project's unit tests using the 'make' utility. Ensure your code changes are tested before submitting a pull request.

```shell
make unit-tests
```

--------------------------------

### SQL Builder: Order By Clause Sanitization

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Ensures safe and correct ordering of query results by sanitizing the ORDER BY clause. Uses OrderByBuilder with WithAllow to prevent SQL injection vulnerabilities.

```go
package main

import (
	"context"
	"fmt"
	"github.com/yaitoo/sqle/sqlbuilder"
)

func main() {
	db := sqlbuilder.NewDB()
	// Example: Safely order by 'name' or 'age' fields
	query := db.Table("users").Select("*")
	orderClause := sqlbuilder.OrderByBuilder{}.WithAllow("name", "age").ByAsc("name")
	query.OrderBy(orderClause)

	sql, args := query.BuildQuery()
	fmt.Println("SQL:", sql)
	fmt.Println("Args:", args)

	// Example of potentially unsafe ordering (demonstration only, avoid this)
	// unsafeQuery := db.Table("users").Select("*")
	// unsafeQuery.OrderByRaw("name; DROP TABLE users;") // This would be prevented by WithAllow
}

```

--------------------------------

### Sqle WhereBuilder Reuse with WithWhere

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows how to reuse a `WhereBuilder` across multiple queries. A common `WhereBuilder` with status and role conditions is applied to a base query that also includes an organization ID filter.

```go
wb := sqle.NewWhere().And("status={st}").And("role={r}").
    Param("st", "active").Param("r", "admin")
base := sqle.New().Select("users").WithWhere(wb).Where("org_id={org}").Param("org", orgID)
rows, _ := db.QueryBuilder(ctx, base)
```

--------------------------------

### Discover and Initialize Database Migrations in Go

Source: https://github.com/yaitoo/sqle/blob/main/README.md

This Go code snippet demonstrates how to discover migration scripts from an embedded file system and initialize the migration process. It requires the 'migrate' package and uses 'embed.FS' for loading migration files. The output is error handling for discovery and initialization failures.

```go
//go:embed db
	var migrations embed.FS

m := migrate.New(db)
    // m.Discover(os.DirFS("./db"))
	if err := m.Discover(migrations); err != nil {
		panic(" db: migrate " + err.Error())
	}

	err = m.Init(context.TODO())
	if err != nil {
		panic(" db: migrate " + err.Error())
	}

	err = m.Migrate(context.TODO())
	if err != nil {
		panic(" db: migrate " + err.Error())
	}
```

--------------------------------

### Sqle Generic Null[T] and JSON Handling

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Demonstrates the use of Sqle's generic `Null[T]` type for nullable values and its JSON marshaling/unmarshaling capabilities. It shows creating a nullable integer and handling JSON representation.

```go
var n sqle.Null[int64] = sqle.NewNull[int64](123, true)
buf, _ := json.Marshal(n)   // => 123
_ = n.UnmarshalJSON([]byte("null")) // sets Valid=false
```

--------------------------------

### Insert Records using Sqle Go Builder

Source: https://context7.com/yaitoo/sqle/llms.txt

Demonstrates inserting data into a 'users' table using Sqle's InsertBuilder. Supports insertion via individual fields, a map of key-value pairs, or a struct model with automatic field mapping. Requires context and a database connection.

```go
package main

import (
    "context"
    "fmt"
    "github.com/yaitoo/sqle"
)

type User struct {
    Name  string
    Email string
    Age   int
}

func main() {
    db := setupDatabase()
    ctx := context.Background()

    // Method 1: Insert with individual Set calls
    builder := sqle.New().Insert("users").
        Set("name", "Alice").
        Set("email", "alice@example.com").
        Set("age", 30).
        End()

    result, err := db.ExecBuilder(ctx, builder)
    if err != nil {
        panic(fmt.Sprintf("insert failed: %v", err))
    }

    id, err := result.LastInsertId()
    if err != nil {
        panic(err)
    }
    fmt.Printf("Inserted user with ID: %d\n", id)

    // Method 2: Insert from map
    userData := map[string]any{
        "name":  "Bob",
        "email": "bob@example.com",
        "age":   25,
    }

    builder = sqle.New().Insert("users").
        SetMap(userData).
        End()

    result, err = db.ExecBuilder(ctx, builder)
    if err != nil {
        panic(err)
    }

    // Method 3: Insert from struct (ORM-like)
    user := User{
        Name:  "Charlie",
        Email: "charlie@example.com",
        Age:   28,
    }

    builder = sqle.New().Insert("users").
        SetModel(user).
        End()

    result, err = db.ExecBuilder(ctx, builder)
    if err != nil {
        panic(err)
    }

    rowsAffected, _ := result.RowsAffected()
    fmt.Printf("Rows affected: %d\n", rowsAffected)
}

```

--------------------------------

### Sqle Migration Rotated Script Header

Source: https://github.com/yaitoo/sqle/blob/main/AGENTS.md

Shows the header format for a rotated SQL migration script. The comment `/* rotate: monthly = 20240201 - 20240401 */` indicates the rotation strategy and date range for the table creation.

```sql
/* rotate: monthly = 20240201 - 20240401 */
CREATE TABLE IF NOT EXISTS monthly_logs<rotate> (
  id BIGINT PRIMARY KEY,
  msg VARCHAR(64) NOT NULL
);
```
Tokens:
Raw