SQLMX - Enhanced SQL Library for Go

SQLMX is a powerful extension library for Go's SQL capabilities that enhances SQL templating and expression building. It simplifies database operations with a rich set of features while maintaining high performance and flexibility.

Development Guide — Complete API reference, architecture overview, and usage patterns.

Features

• Database Factory: Manage multiple database connections with ease
• SQL Templates: Powerful template engine supporting dynamic SQL generation (text/template + embed.FS)
• SQL Expressions: Fluent API for building SQL expressions programmatically, including JOIN, EXISTS, UNION, and FOR UPDATE
• BaseMapper[T]: Generic CRUD operations with type-safe ORM capabilities
• TxMapper[T]: Shared-transaction mapper for executing multiple ORM operations within a single transaction
• BoostMapper: Reflection-driven mapper binding with automatic template discovery
• Context Propagation: All query/execution methods accept context.Context for timeout and cancellation support
• Lifecycle Hooks: BeforeInsert / AfterInsert / BeforeUpdate / AfterUpdate / BeforeDelete / AfterDelete (executed within transactions for atomicity)
• Transaction Support: Comprehensive transaction management with template awareness
• Multi-database Support: MySQL, PostgreSQL, SQL Server, and SQLite

Installation

go get github.com/gnodux/sqlmx

Quick Start

1. Database Connection Management

SQLMX uses a DBManager to handle database connections. Initialize with a default manager:

import "github.com/gnodux/sqlmx"

// Open a MySQL database connection named 'default'
db, err := sqlmx.Open("default", "mysql", "user:password@tcp(localhost:3306)/dbname")
if err != nil {
    log.Fatal(err)
}

// Parse SQL template files
db.ParseTemplateFS(os.DirFS("./sql"), "*.sql")

2. SQL Templates

SQLMX provides a powerful template system for dynamic SQL:

ctx := context.Background()

// Using templates for queries
users, err := db.SelectEx(ctx, &userList, "select_users.sql", map[string]interface{}{
    "name": "John",
    "age":  30,
})

Template example (select_users.sql):

SELECT * FROM users 
WHERE 1=1
{{if .name}} AND name = '{{.name}}'{{end}}
{{if .age}} AND age > {{.age}}{{end}}

3. SQL Expressions

Build SQL expressions fluently:

import "github.com/gnodux/sqlmx/expr"

ctx := context.Background()

// Build a query expression
query := expr.Select(
    expr.Name("id"),
    expr.Name("name"),
    expr.Name("email"),
).
From(expr.Name("users")).
Where(expr.And(
    expr.Gt(expr.Name("age"), 18),
    expr.Like(expr.Name("name"), "%john%"),
)).
OrderBy(expr.Desc(expr.Name("created_at"))).
Limit(10)

// Execute the query
var users []User
err := db.SelectExpr(ctx, &users, query)

4. Base Mapper (ORM Capabilities)

The BaseMapper provides common CRUD operations:

ctx := context.Background()

// Define an entity
type User struct {
    ID    int64  `dbx:"primaryKey"`
    Name  string `dbx:"name"`
    Email string `dbx:"email"`
    Age   int    `dbx:"age"`
}

// Create a mapper
mapper, err := sqlmx.NewMapper[sqlmx.BaseMapper[User]]("default")
if err != nil {
    log.Fatal(err)
}

// Create a user
user := User{Name: "John", Email: "john@example.com", Age: 30}
err = mapper.Create(ctx, user)

// Query users
users, totalCount, err := mapper.Select(ctx,
    expr.UseLimit(10),
    expr.UseOffset(0),
)

// Update a user
user.Age = 31
err = mapper.Update(ctx, true, user)

// Delete a user
err = mapper.DeleteById(ctx, tenantID, user.ID)

Advanced Features

Transactions

ctx := context.Background()

err := db.Batch(ctx, nil, func(tx *sqlmx.Tx) error {
    // Perform multiple operations in a transaction
    _, err := tx.ExecEx(ctx, "INSERT INTO users (name) VALUES (?)", "John")
    if err != nil {
        return err // Rollback transaction
    }
    
    _, err = tx.ExecEx(ctx, "UPDATE profiles SET active = ? WHERE user_id = ?", true, 1)
    if err != nil {
        return err // Rollback transaction
    }
    
    return nil // Commit transaction
})

Shared Transaction with TxMapper

Use TxMapper[T] to execute multiple BaseMapper operations within a single transaction:

ctx := context.Background()

db.Batch(ctx, nil, func(tx *sqlmx.Tx) error {
    userMapper := sqlmx.NewMapper[sqlmx.BaseMapper[*User]]("default")
    txMapper := userMapper.WithTx(tx)

    // Both operations share the same transaction
    if err := txMapper.Create(ctx, &User{Name: "alice"}); err != nil {
        return err
    }
    if err := txMapper.Create(ctx, &User{Name: "bob"}); err != nil {
        return err
    }
    return nil // Commit — both users are persisted atomically
})

JOIN Queries

// INNER JOIN
query := expr.Select(expr.All).
    From(expr.Name("user")).
    Join(expr.Name("order"),
        expr.Eq(expr.Name("id", "user"), expr.Name("user_id", "order"))).
    Where(expr.Eq(expr.Name("status"), expr.Const("active")))

// LEFT JOIN
query := expr.Select(expr.All).
    From(expr.Name("user")).
    LeftJoin(expr.Name("profile"),
        expr.Eq(expr.Name("id", "user"), expr.Name("user_id", "profile")))

EXISTS / NOT EXISTS

// Users who have at least one order
query := expr.Select(expr.All).From(expr.Name("user")).
    Where(expr.Exists(
        expr.Select(expr.All).From(expr.Name("order")).
            Where(expr.Eq(expr.Name("user_id"), expr.Name("id", "user"))),
    ))

UNION

combined := expr.Union(
    expr.Select(expr.All).From(expr.Name("user")).Where(expr.Eq(expr.Name("role"), expr.Const("admin"))),
    expr.Select(expr.All).From(expr.Name("user")).Where(expr.Eq(expr.Name("role"), expr.Const("manager"))),
)

// UNION ALL
combined := expr.UnionAll(query1, query2)

FOR UPDATE (Row Locking)

query := expr.Select(expr.All).From(expr.Name("user")).
    Where(expr.Eq(expr.Name("id"), expr.Const(1))).
    ForUpdate()
// Generates: SELECT * FROM `user` WHERE `id` = 1 FOR UPDATE

Custom Expressions

// Create a custom query
customQuery := expr.Select(
    expr.Fn("COUNT", expr.All).Alias("total"),
    expr.Name("status"),
).
From(expr.Name("orders")).
Where(expr.Gt(expr.Name("created_at"), "2023-01-01")).
GroupBy(expr.Name("status"))

var results []struct {
    Total  int64  `db:"total"`
    Status string `db:"status"`
}
err := db.SelectExpr(ctx, &results, customQuery)

v1.2 Changelog

This is a breaking change release. Key changes:

• sqlx migration: Switched from cookieY/sqlx to upstream jmoiron/sqlx v1.4.0
• Context propagation: All DB, Tx, and BaseMapper methods now accept context.Context as the first parameter
• TxMapper[T]: New WithTx(tx) method on BaseMapper for shared-transaction operations
• Expression extensions: Added JOIN, FOR UPDATE, EXISTS, NOT EXISTS, UNION, UNION ALL
• Hook consistency: Lifecycle hooks (Before/After Insert/Update) are now executed within the transaction for atomicity
• Bug fixes: Deferred statement close errors no longer overwrite original query errors

Supported Databases

Database	Driver	Dialect	Import
MySQL	github.com/go-sql-driver/mysql	dialect.MySQL	_ "github.com/go-sql-driver/mysql"
PostgreSQL	github.com/lib/pq	dialect.Postgres	_ "github.com/lib/pq"
SQL Server	github.com/denisenkom/go-mssqldb	dialect.SQLServer	_ "github.com/denisenkom/go-mssqldb"
SQLite	modernc.org/sqlite	dialect.SQLite	_ "modernc.org/sqlite"

Documentation

• Development Guide (English) — Complete API reference, architecture, design decisions, and advanced usage
• 开发手册（中文版） — 完整 API 参考、架构设计、设计决策和高级用法

Acknowledgments

SQLMX is built on top of the excellent jmoiron/sqlx library (v1.4.0). We gratefully acknowledge the jmoiron/sqlx project and its contributors for providing a solid foundation for SQL extensions in Go.

License

MIT