corm - Lightweight Go ORM

corm is a lightweight and easy-to-use ORM library for Go. It supports MySQL and PostgreSQL, providing a fluent Query Builder, struct mapping, and transaction management.

Concurrency note:

• Engine is safe to share across goroutines.
• Query builders (e.g. e.Select(...).Where(...)) are mutable and must not be shared across goroutines.

Features

• Fluent Query Builder: Intuitive API for building SELECT, INSERT, UPDATE, and DELETE queries.
• Struct Mapping: Automatically map database rows to structs (and slices of structs).
• Transaction Support: Easy-to-use transaction management with closure-based Transaction helper.
• Cross-Database: Supports MySQL and PostgreSQL (with dialect abstraction).
• Safety & Security: Built-in SQL injection protection (parameter binding) and safe identifier quoting.
• Performance: Optimized reflection and allocation reduction for result scanning.

Functional Scope

corm is strictly limited to supporting DQL (Data Query Language, such as SELECT) and DML (Data Manipulation Language, such as INSERT, UPDATE, DELETE) operations.

It does NOT provide:

• DDL (Data Definition Language): Creating/dropping tables, indexes, or altering schemas.
• DCL (Data Control Language): Granting or revoking permissions.

For AI/Agents

If you're using an AI coding tool or an AI agent to generate code with corm, read AI_AGENT_GUIDE.md first. It includes safe SQL rules, module map, and copy-paste templates.

Installation

go get github.com/nikola-chen/corm

Quick Start

Connection

package main

import (
	"context"
	"log"
	"time"

	"github.com/nikola-chen/corm/engine"
	_ "github.com/go-sql-driver/mysql"
	// _ "github.com/lib/pq" // for postgres
)

func main() {
	// Open connection
	e, err := engine.Open("mysql", "user:pass@tcp(localhost:3306)/dbname?parseTime=true",
		engine.WithConfig(engine.Config{
			MaxOpenConns: 10,
			MaxIdleConns: 5,
			LogSQL:       true, // Enable SQL logging
		}),
	)
	if err != nil {
		log.Fatalf("open db: %v", err)
	}
	defer e.Close()

	// Verify connection
	ctx := context.Background()
	if err := e.Ping(ctx); err != nil {
		log.Fatalf("ping db: %v", err)
	}

	// Optional: if you prefer builder-style in your own wrappers, bind dialect + executor once:
	qb := e.Builder()
	var rows []map[string]any
	if err := qb.Select("id").From("users").Limit(1).All(ctx, &rows); err != nil {
		log.Fatalf("select: %v", err)
	}
}

Struct Definition

type User struct {
	ID        int       `db:"id,pk"`
	Name      string    `db:"name"`
	Age       int       `db:"age"`
	CreatedAt time.Time `db:"created_at,readonly"`
}

func (u User) TableName() string {
	return "users"
}

CRUD Operations

Insert

ctx := context.Background()
user := User{Name: "Alice", Age: 30}

// Insert a record
_, err := e.Insert("users").
	Model(&user).
	Exec(ctx)

// Insert with specific columns
_, err := e.Insert("users").
	Columns("name", "age").
	Values("Bob", 25).
	Exec(ctx)

// Insert with map (map[string]any)
_, err := e.Insert("users").
	Map(map[string]any{"name": "Carol", "age": 20}).
	Exec(ctx)

// High-throughput inserts with predefined columns:
// If your map keys are already normalized to lower-case, prefer MapsLowerKeys to reduce per-row overhead.
rows := []map[string]any{
	{"name": "Alice", "age": 25},
	{"name": "Bob", "age": 28},
}
_, err = e.Insert("users").
	Columns("name", "age").
	MapsLowerKeys(rows).
	Exec(ctx)

Select

// Select one record
var u User
err := e.Select("id", "name", "age").
	From("users").
	Where("id = ?", 1).
	One(ctx, &u)

// Select multiple records
var users []User
err := e.Select().
	From("users").
	Where("age > ?", 18).
	OrderByDesc("age").
	Limit(10).
	Offset(0).
	All(ctx, &users)

// Select with IN clause
err := e.Select().
	From("users").
	WhereIn("id", []int{1, 2, 3}).
	All(ctx, &users)

Update

// Update with struct model (fields tagged with `omitempty` are skipped unless IncludeZero is enabled)
u.Age = 31
_, err := e.Update("users").
	Model(&u).
	Where("id = ?", u.ID).
	Exec(ctx)

// Update with explicit columns
_, err := e.Update("users").
	Set("age", 32).
	Where("name = ?", "Alice").
	WhereLike("email", "%@example.com").
	Exec(ctx)

// Update with map (keys must be valid column identifiers)
_, err = e.Update("users").
	Map(map[string]any{"age": 33}).
	Where("id = ?", 1).
	Exec(ctx)

// Batch update (single SQL via CASE WHEN)
batch := []User{
    {ID: 1, Name: "Alice", Age: 25},
    {ID: 2, Name: "Bob", Age: 28},
}
_, err = e.Update("").Models(batch).Exec(ctx)

Safety note:

• Update(table) requires a non-empty WHERE by default (to prevent updating the whole table).
• If you really want to update all rows, use AllowEmptyWhere() explicitly.

Delete

_, err := e.Delete("users").
	Where("id = ?", 1).
	Exec(ctx)

Safety note:

• Delete(table) requires a non-empty WHERE by default (to prevent deleting the whole table).
• If you really want to delete all rows, use AllowEmptyWhere() explicitly:

_, err := e.Delete("users").AllowEmptyWhere().Exec(ctx)

Transactions

corm provides a handy Transaction method that automatically commits on success and rolls back on error or panic.

err := e.Transaction(ctx, func(tx *engine.Tx) error {
	// Operations inside transaction use 'tx' instead of 'e'
	if _, err := tx.Insert("users").Values("Dave", 40).Exec(ctx); err != nil {
		return err
	}

	if _, err := tx.Update("accounts").Set("balance", 100).Where("user_id = ?", 1).Exec(ctx); err != nil {
		return err
	}

	return nil // Commit
})

Comprehensive Example

Here is a complete example showcasing most of the features including configuration, complex queries, transactions, and advanced CRUD operations.

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/nikola-chen/corm/engine"
	"github.com/nikola-chen/corm/clause"
	_ "github.com/go-sql-driver/mysql"
)

// User schema definition
type User struct {
	ID        int       `db:"id,pk"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Age       int       `db:"age"`
	Status    int       `db:"status"` // 0: inactive, 1: active
	CreatedAt time.Time `db:"created_at,readonly"`
	UpdatedAt time.Time `db:"updated_at,omitempty"`
}

func (User) TableName() string { return "users" }

func main() {
	// 1. Initialize Engine with Configuration
	e, err := engine.Open("mysql", "user:pass@tcp(localhost:3306)/testdb?parseTime=true",
		engine.WithConfig(engine.Config{
			MaxOpenConns: 20,
			MaxIdleConns: 10,
			LogSQL:       true, // Print generated SQL to stdout
			SlowQuery:    100 * time.Millisecond,
		}),
	)
	if err != nil {
		log.Fatalf("open db: %v", err)
	}
	defer e.Close()

	ctx := context.Background()

	// 2. Insert with Model & Returning (PostgreSQL support)
	newUser := User{Name: "John Doe", Email: "john@example.com", Age: 30, Status: 1}
	newID, err := e.Insert("").Model(&newUser).ExecAndReturnID(ctx, "id")
	if err != nil {
		log.Fatalf("insert user: %v", err)
	}

	// 3. Batch Insert using Values
	e.Insert("users").
		Columns("name", "email", "age", "status").
		Values("Alice", "alice@test.com", 25, 1).
		Values("Bob", "bob@test.com", 28, 0).
		Exec(ctx)

	// 3.1 Batch Insert using struct slice
	users := []User{
		{Name: "Alice", Email: "alice@test.com", Age: 25, Status: 1},
		{Name: "Bob", Email: "bob@test.com", Age: 28, Status: 0},
	}
	e.Insert("").Models(users).Exec(ctx)

	// 3.2 Batch Insert using map slice
	rows := []map[string]any{
		{"name": "Alice", "email": "alice@test.com", "age": 25, "status": 1},
		{"name": "Bob", "email": "bob@test.com", "age": 28, "status": 0},
	}
	e.Insert("users").Columns("name", "email", "age", "status").Maps(rows).Exec(ctx)

	// 4. Complex Select Query
	// SELECT u.id, u.name, count(o.id) as order_count
	// FROM users AS u
	// LEFT JOIN orders o ON o.user_id = u.id
	// WHERE u.status = 1 AND u.age > 18
	// GROUP BY u.id
	// HAVING order_count >= 0
	// ORDER BY u.age DESC
	// LIMIT 10 OFFSET 0

	type UserStat struct {
		ID         int    `db:"id"`
		Name       string `db:"name"`
		OrderCount int    `db:"order_count"`
	}

	var stats []UserStat
	err = e.Select("u.id", "u.name").
		SelectExpr(clause.Raw("count(o.id) as order_count")).
		FromAs("users", "u").
		LeftJoinAs("orders", "o", clause.Raw("o.user_id = u.id")).
		Where("u.status = ?", 1).
		Where("u.age > ?", 18).
		WhereIn("u.id", []int{1, 2, 3, 4, 5}). // Helper for IN clause
		GroupBy("u.id", "u.name").
		Having("order_count >= ?", 0).
		OrderByDesc("u.age").
		Limit(10).
		Offset(0).
		All(ctx, &stats)

	if err != nil {
		fmt.Printf("Query failed: %v\n", err)
	}

	// 5. Update using Map and Model
	// Update via Struct (auto-infers table from struct)
	updateUser := User{ID: newID, Name: "John Updated"}
	e.Update("").
		Model(&updateUser).
		Where("id = ?", newID).
		Exec(ctx)

	// Update via Map or Set method
	e.Update("users").
		Map(map[string]any{"status": 0}).
		Set("updated_at", time.Now()).
		Where("age < ?", 20).
		Exec(ctx)

	// Update Batch using Maps
	updateRows := []map[string]any{
		{"id": 1, "status": 1, "age": 26},
		{"id": 2, "status": 0, "age": 29},
	}
	// CASE-WHEN bulk update based on 'id'
	e.Update("users").Key("id").Maps(updateRows).Exec(ctx)

	// Update Batch using Maps with Extra Where
	// This generates: UPDATE ... WHERE id IN (...) AND status = 1
	e.Update("users").
		Key("id").
		Maps(updateRows).
		Where("status = ?", 1).
		Exec(ctx)

	// 5. Update with Limit (MySQL only)
	_, err = e.Update("users").
		Set("status", 0).
		Where("age < ?", 18).
		Limit(100). // Limit affected rows
		Exec(ctx)

	// 6. Transaction
	err = e.Transaction(ctx, func(tx *engine.Tx) error {
		// Use 'tx' for all operations inside the transaction

		// 6.1 Lock row (if needed)
		// _ = tx.Select("*").From("users").Where("id = ?", newID).ForUpdate().One(ctx, &User{})

		// 6.2 Perform updates
		if _, err := tx.Delete("users").Where("status = ?", 0).Exec(ctx); err != nil {
			return err // Rollback
		}

		// 6.3 Insert log
		if _, err := tx.Insert("logs").Columns("msg").Values("Cleanup done").Exec(ctx); err != nil {
			return err // Rollback
		}

		return nil // Commit
	})

	if err != nil {
		fmt.Printf("Transaction failed: %v\n", err)
	}
}

Advanced Usage

SQL Logging

Enable logging via WithConfig:

engine.Open("mysql", dsn, engine.WithConfig(engine.Config{
    LogSQL:    true,
    LogArgs:   true, // Enable argument logging (redacted by default for security)
    SlowQuery: 200 * time.Millisecond,
}))

Raw SQL

For complex queries, you can use Raw clauses, but be careful with SQL injection if you manually concatenate strings.

e.Select().
    Where("age > ? AND name LIKE ?", 18, "A%").
    All(ctx, &users)

Safety note:

• Treat these as dangerous entry points unless the SQL is a trusted constant/whitelist: Where, JoinRaw, Having, OrderByRaw, SuffixRaw, clause.Raw.
• Prefer structured APIs like WhereEq, WhereIn, OrderByAsc/Desc, and Join/JoinAs whenever possible.

Note (PostgreSQL):

• When using string-based SQL fragments with args (e.g. Where("x = ?", v)), use ? as the placeholder in the fragment.
• Avoid mixing JSONB operators ?/?|/?& with ? placeholders in the same parameterized fragment. Prefer jsonb_exists/jsonb_exists_any/jsonb_exists_all functions.

SQL Builder (Without Execution)

If you only need to build SQL strings without executing them (e.g., for use with other libraries or testing), you can use the builder package directly with the new API helper.

import "github.com/nikola-chen/corm/builder"

// Initialize a builder for MySQL (or Postgres)
// Note: Ensure the DB driver is imported (e.g. _ "github.com/go-sql-driver/mysql").
qb := builder.MySQL()
// Or: qb := builder.Postgres()
// Or: qb := builder.Dialect(driverName)       // carries error until SQL()/Exec()/Query()
// Or: qb := builder.MustDialect(driverName)   // panics early if unsupported (avoid in request path)
// Or: qb := builder.For(dialect.MustGet(driverName), db)   // binds executor + dialect in one line
// Or: qb := builder.MustFor(dialect.MustGet(driverName), db) // panics early if unsupported

// Build UPDATE string
sqlStr, args, err := qb.Update("users").
    Set("name", "New Name").
    Where("id = ?", 1).
    SQL()

// Build SELECT string
sqlStr, args, err = qb.Select("id", "name").
    From("users").
    Where("age > ?", 18).
    SQL()

Advanced Features

corm now supports a wide range of advanced SQL features.

Security note:

• clause.Raw(...), JoinRaw(...), OrderByRaw(...), SuffixRaw(...) accept raw SQL. Never pass untrusted user input into these APIs.

Logical Operators

import "github.com/nikola-chen/corm/clause"

e.Select().From("users").
    WhereExpr(clause.Not(clause.Raw("age < ?", 18))).
    WhereExpr(clause.IsNull("deleted_at")).
    WhereExpr(clause.IsNotNull("email")).
    All(ctx, &users)

JOINs

Support for structured joins (Join/LeftJoin/RightJoin/InnerJoin/FullJoin/CrossJoin) and raw joins (JoinRaw).

Recommended usage with arguments (using FromAs + *JoinAs):

import "github.com/nikola-chen/corm/clause"

e.Select("u.name").
    FromAs("users", "u").
    LeftJoinAs("orders", "o", clause.And(
        clause.Raw("u.id = o.user_id"),
        clause.Eq("o.status", "active"), // Bind: "active"
    )).
    All(ctx, &results)

Nested Transactions (Savepoints)

corm supports nested transactions via SAVEPOINT. You can call tx.Transaction inside a transaction block.

import (
    "errors"
    "fmt"

    "github.com/nikola-chen/corm/engine"
)

err := e.Transaction(ctx, func(tx *engine.Tx) error {
    if _, err := tx.Insert("logs").Values("Start").Exec(ctx); err != nil {
        return fmt.Errorf("failed to insert log: %w", err)
    }

    // Nested transaction
    if err := tx.Transaction(ctx, func(subTx *engine.Tx) error {
        if _, err := subTx.Insert("users").Values("New User").Exec(ctx); err != nil {
            return err
        }
        return errors.New("oops") // Triggers rollback of sub-transaction
    }); err != nil {
        // Handle sub-transaction error (optional)
        return err
    }

    return nil
})

Subqueries

Nested SELECT in FROM:

sub := e.Select("id", "name").From("users").Where("age > ?", 18)

e.Select("u.name").
    FromSelect(sub, "u"). // SELECT ... FROM (SELECT ...) AS u
    All(ctx, &results)

Subquery in WHERE:

sub := e.Select("id").From("banned_users")

e.Select().From("users").
    WhereInSubquery("id", sub). // WHERE id IN (SELECT id FROM banned_users)
    All(ctx, &users)

INSERT INTO ... SELECT:

sub := e.Select("id", "name").From("old_users")

e.Insert("new_users").
    Columns("id", "name").
    FromSelect(sub).
    Exec(ctx)

Aggregates

Helpers for Count, Sum, Avg, Max, Min.

type Agg struct {
    Cnt    int     `db:"cnt"`
    AvgAge float64 `db:"avg_age"`
}
var a Agg
err := e.Select().
    SelectExpr(
        clause.Alias(clause.Count("id"), "cnt"),
        clause.Alias(clause.Avg("age"), "avg_age"),
    ).
    From("users").
    One(ctx, &a)

UNION / UNION ALL

q1 := e.Select("id").From("users_2023")
q2 := e.Select("id").From("users_2024")

// SELECT id FROM users_2023 UNION ALL SELECT id FROM users_2024
q1.UnionAll(q2).All(ctx, &ids)

DISTINCT & LIMIT

e.Select("name").From("users").Distinct().Limit(5).All(ctx, &names)

InsertIgnore (MySQL)

Skip rows that would cause duplicate key errors:

_, err := e.Insert("users").
    Columns("id", "name").
    Values(1, "Alice").
    InsertIgnore(). // Generates: INSERT IGNORE INTO ...
    Exec(ctx)

Note: InsertIgnore is only supported by MySQL. Calling it on PostgreSQL returns an error.

SetExpr (Update with Raw Expression)

Set a column to a raw SQL expression instead of a bound parameter:

_, err := e.Update("users").
    SetExpr("updated_at", clause.Raw("NOW()")).
    Set("name", "Alice").
    WhereEq("id", 1).
    Exec(ctx)
// Generates: UPDATE `users` SET `updated_at` = NOW(), `name` = ? WHERE (`id` = ?)

CountExpr (Custom Count Expression)

Count with a custom expression, such as COUNT(DISTINCT column):

count, err := e.Select().From("users").WhereEq("status", 1).
    CountExpr(ctx, clause.Raw("COUNT(DISTINCT `email`)"))

When the query has GROUP BY, CountExpr automatically wraps the query in a subquery:

// SELECT COUNT(*) FROM (SELECT ... FROM users GROUP BY status) AS sub
count, err := e.Select("status").From("users").GroupBy("status").
    Count(ctx)

// rows.Close() is called automatically even if fn panics

### Iter (Go 1.23+ Streaming)

Use Go 1.23+ range-over-function for the most elegant way to stream results.

```go
// SELECT id, name FROM users WHERE status = 1
query := e.Select("id", "name").From("users").WhereEq("status", 1)

// Iter automatically closes rows when loop ends or breaks
for u, err := range engine.Iter[User](ctx, query) {
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(u.Name)
}

Map Operations

// INSERT with Map (keys are sorted for determinism)
_, err := e.Insert("users").
	Map(map[string]any{"name": "Alice", "age": 30}).
	Exec(ctx)

// UPDATE with Map
_, err := e.Update("users").
	Map(map[string]any{
		"age": 31,
		"status": "active",
	}).
	Where("name = ?", "Alice").
	Exec(ctx)

// SELECT with WhereMap (automatic AND)
err := e.Select("id", "name").
	From("users").
	WhereMap(map[string]any{
		"age": 30,
		"active": true,
	}).
	All(ctx, &users)

Changelog

v2.1.14 (Fifteenth Round Deep Audit — Performance & Robustness)

Robustness:

• Added nested transaction depth limit (max 32) to prevent unbounded savepoint recursion and potential stack overflow.
• Added errSavepointDepth sentinel error for depth limit exceeded.

Performance:

• Replaced cache eviction strategy from clear() (full flush) to random partial eviction (25%) in scan/structPlanCache and schema/schemaCache to reduce cache thrashing in long-lived processes.

Test Coverage:

• Added comprehensive tests for UpdateBuilder.Where* methods (WhereEq, WhereIn, WhereLike, WhereMap, WhereSubquery, WhereInSubquery, WhereExpr, WhereNotIn, WhereBetween, WhereNotLike, WhereExists, WhereNotExists, MapsLowerKeys).
• Added TestTxTransactionDepthLimit to verify savepoint depth limit enforcement.

Audit Summary:

• go vet clean, all tests pass, go test -race clean.
• Coverage: overall 71.8%.

v2.1.13 (Performance Optimization — Table Name Caching)

Table Name Cache:

• Added independent tableNameCache in schema/schema.go with bounded capacity (1024 entries) and RWMutex-protected concurrent access.
• Added TableNameOf(model any) string and LookupTableName(t reflect.Type) string public API for zero-allocation table name lookup.
• Eliminated reflect.New(t) heap allocation in parseSlow() for non-TableNamer types by using reflect.PointerTo(t).Implements(tableNamerType) check.
• LookupTableName falls through: tableNameCache → schemaCache.Table → cachedTableName(), ensuring consistency with existing schema parse results.

Performance Results (Apple M2, cache hit):

• TableNameOf: ~15 ns/op, 0 allocs/op
• LookupTableName: ~12 ns/op, 0 allocs/op
• SchemaParse (existing): ~16 ns/op, 0 allocs/op (unchanged)

Tests: 13 new test cases covering TableNameOf, LookupTableName, cache consistency, concurrent safety, nil/non-struct inputs, pointer models, and schema cache fallback.

v2.1.12 (Thirteenth Round Deep Audit — golang-fullstack-best-practices Cross-Audit)

Error Handling Unification:

• Added 2 new sentinel errors in scan/errors.go (errNilInterfaceDest, errStructOrMapDest) and replaced 3 inline errors.New() calls in scan/iter.go.
• Replaced errors.New() + string concatenation with fmt.Errorf() + %s in schema/schema.go, removing unused errors import.

Comprehensive Cross-Audit (89 rules, 8 domains):

• Concurrency Safety (12/12): All sync.Pool, sync.RWMutex, single-flight parsing, goroutine patterns verified correct.
• Clean Architecture (9/9): Dependency chain clause/dialect/internal → schema/scan → builder → engine → corm fully inward, zero circular dependencies.
• Design Patterns (13/13): Fluent Builder pattern correct, no God Objects, type switches appropriately scoped.
• Idiomatic Go (6/6): All interfaces ≤4 methods, pointer receivers for mutations, clear() builtin used for cache reset.
• PostgreSQL Syntax (9/9): $N/? placeholders, double-quote escaping, RETURNING, ON CONFLICT, FOR SHARE, JSONB operator conflict detection all correct.
• Query Performance (7/7): Connection pool config (MaxOpenConns/MaxIdleConns/Lifetime/IdleTime), SlowQuery threshold support verified.
• Migration Safety (N/A): corm is a DQL/DML-only ORM with no DDL support (no AutoMigrate, ALTER TABLE, CREATE INDEX, or migration files). All 6 migration-safety rules are not applicable by design.

Audit Summary:

• go vet clean, all tests pass, go test -race clean, staticcheck zero warnings.
• No dead code or unused imports found.
• No deprecated API usage detected.
• All sync.Pool, sync.RWMutex patterns verified correct.
• Coverage: overall 70.7% (internal 100%, dialect 97.2%, schema 89.0%, engine 86.4%, clause 88.8%, scan 76.4%, builder 64.5%).

v2.1.11 (Twelfth Round Deep Audit — Cross-Audit Cleanup)

Dead Code Removal:

• Removed unused errUnsupportedDialect variable from builder/errors.go (confirmed by staticcheck U1000).

Code Style & Consistency:

• Moved errSQLTooLong sentinel from builder/arg_builder.go to centralized builder/errors.go for consistency with the v2.1.10 error unification policy.
• Removed trailing period from errConflictDoNothing error message to comply with Go error string convention (ST1005).

Audit Summary:

• go vet clean, all tests pass, staticcheck zero warnings.
• No dead code or unused imports found.
• No deprecated API usage detected.
• All sync.Pool, sync.RWMutex patterns verified correct.
• Coverage: overall 70.7% (internal 100%, dialect 97.2%, schema 89.0%, engine 86.4%, clause 88.8%, scan 76.4%, builder 64.5%).

v2.1.10 (Ninth, Tenth & Eleventh Round Deep Audits)

Bug Fixes:

• Fixed assignInt64 safety bug: uint/uint64 types were not checked for negative values before conversion, which could silently produce incorrect results. Added overflow checks for unsigned integer types.

Code Style & Consistency:

• Unified UpdateBuilder.Limit() and DeleteBuilder.Limit() behavior with SelectBuilder.Limit(): values ≤ 0 now mean "no limit" (LIMIT clause omitted) instead of silently accepting negative values.
• Simplified redundant nil check in batchUpdateBuilder.Models() for improved clarity.
• Replaced all inline errors.New("corm: ...") with sentinel errors across builder and engine packages for consistent, comparable error handling.
• Added engine/errors.go with centralized sentinel errors (errEngineNotInit, errContextCanceled).
• Replaced errors.New("corm: unsupported dialect: " + driverName) with fmt.Errorf for proper string formatting.

Dead Code Removal:

• Removed unused methods from batchUpdateBuilder: Columns(), IncludePrimaryKey(), IncludeAuto(), IncludeReadonly(), IncludeZero() — these were only set via field access from UpdateBuilder.

Architecture Refactoring:

• Removed redundant wrapper functions normalizeInsertColumnKey and scan.normalizeColumn, calling internal.NormalizeColumn directly.
• Extracted shared buildSetClause() and buildConflictPrefix() helpers from ConflictBuilder.DoUpdate(), eliminating duplicate SET-clause building logic between PostgreSQL and MySQL branches (~40 lines deduplicated).

Security & Robustness:

• Added savepoint name validation in transaction management to prevent SQL injection through crafted savepoint names.
• Enhanced defaultArgFormatter to properly redact sensitive types (errors, fmt.Stringer) in SQL logs.

Testing Enhancements:

• Added comprehensive tests for In() function, Like, Alias functions, and defaultArgFormatter.
• Added error-path tests for SelectBuilder.All/One/Scalar/Count/Exists, InsertBuilder.One, and Iter with nil executor.
• Updated LIMIT tests to match new "≤ 0 = no limit" semantics.
• Coverage: overall 70.7% (internal 100%, dialect 97.2%, schema 89.0%, engine 86.4%, clause 88.8%, scan 76.4%, builder 64.5%).

Audit Summary:

• go vet clean, all tests pass, go test -race clean.
• No dead code or unused imports found.
• No deprecated API usage detected.
• All sync.Pool, sync.RWMutex patterns verified correct.
• modernize static analysis tool clean.

v2.1.9 (Eighth Round Deep Audit)

Bug Fixes:

• Fixed batchUpdateBuilder.mapsInternal() column validation gap: when deriving columns from map keys, columns appearing after the key column in iteration order were not validated by quoteColumnStrict. Now all map keys are validated before the key column is removed, closing a potential edge case where unvalidated column identifiers could bypass checks.

Architecture Refactoring:

• Extracted generic SQL tokenizer (tokenizeSQL), eliminating ~200 lines of duplicated state machine parsing logic between countQuestionPlaceholders and rewritePlaceholders, unified into declarative token traversal pattern.
• Removed redundant wrapper function normalizeInsertColumnKey, calling internal.NormalizeColumn directly.
• Optimized special character detection in quoteIdentWithStar using [256]bool lookup table instead of inline multi-branch conditions, improving identifier validation performance.
• Simplified quoteColumnStrict, removing duplicated special character detection logic that overlapped with isSimpleIdent.

Audit Summary:

• go vet clean, all tests pass.
• No dead code or unused imports found.
• No deprecated API usage detected.
• All sync.Pool, sync.RWMutex patterns verified correct.
• modernize static analysis tool clean.
• Coverage: overall 65.7% (100% internal, 97.2% dialect, 89.0% schema, 83.2% engine, 76.4% scan, 58.5% builder, 77.6% clause).

v2.1.8 (Sixth Round Deep Audit)

LIMIT Syntax Audit & Fix:

• SelectBuilder.Limit(0) and Offset(0) now correctly omit the LIMIT/OFFSET clause (semantics: no limit/no offset) instead of generating LIMIT 0 (returns 0 rows).
• Fixed cap variable shadowing the built-in cap() function in colsKey, renamed to totalCap.

Code Style:

• No built-in function name conflicts.

v2.1.7 (Fifth Round Deep Audit)

Go 1.26 Modernizations Continued:

• Introduced generic flattenSlice[S ~[]E, E any] in clause.In() to eliminate 8 redundant type switch branches (~70 lines → 11 lines).
• Removed outdated performance commentary from In().
• Modernized for i := 0; i < rv.Len(); i++ → for i := range rv.Len() (insert_batch.go, batch_update.go, schema.go, clause/expr.go).
• Added empty slice guard to buildIn.

Testing Enhancements:

• Added TestIterEmpty, TestIterEarlyExit, TestIterWrongMapKeyType, TestIterNonStructNonMap to scan package (coverage 75.1% → 76.4%).
• Schema coverage 89.0% → 90.4%, clause coverage 77.2% → 77.6%.

Code Reduction:

• 8 duplicated type branches in In() ([]string/[]int/[]int64/[]uint64/[]int32/[]uint32/[]uint) unified with generics.

v2.1.6 (Fourth Round Deep Audit)

Go 1.26 Modernizations:

• Applied b.Loop() in all benchmark functions for cleaner benchmark loops.
• Adopted maps.Keys() + slices.Collect() for cleaner map key extraction in builder package.
• Modernized for i := 0; i < len(args); i++ to for i, arg := range args in executor formatting.
• All code passes modernize static analysis tool.

Performance Optimizations:

• Consolidated executor wrapping logic with shared wrapExecutor helper.
• Extracted trimSpaceASCII for code reuse in identifier quoting.
• Optimized colsKey buffer sizing to minimize reallocations.

Testing Enhancements:

• Added scan tests for unmapped columns, ScanOne variants, edge cases.
• Added builder tests for Union, subquery FROM, JOIN updates, USING deletes, DISTINCT, FOR UPDATE/FOR SHARE, HAVING.
• Coverage improvements: scan 60.2% → 75.1%, builder 54.8% → 58.6%, engine 31.1% → 83.2%, schema 71.8% → 89.0%.

v2.1.5 (Go 1.26 Modern Syntax Audit)

Go 1.26 Modernizations:

• Adopted new(expr) expression parameter syntax, replacing the intPtr helper function and &variable patterns in Limit/Offset builders.
• Replaced manual strings.Builder loop with strings.Join for placeholder generation in clause.In() (performance parity in Go 1.26).
• Applied modern for i := range n integer range syntax across the codebase.
• Replaced reflect.TypeOf("") with reflect.TypeFor[string]() for generic type retrieval.
• Adopted strings.Cut for cleaner string splitting in identifier parsing.
• Modernized loop patterns from for i := 0; i < len(x); i++ to for i := range x.

Performance Optimizations:

• Replaced strings.Contains(column, ".") with strings.IndexByte for single-character searching.
• Merged multiple strings.Contains checks into a single strings.ContainsAny.
• Optimized TrimSpace/ToUpper order to process fewer characters.
• Leveraged Green Tea GC (enabled by default in Go 1.26) for small object allocation speedup.

Code Quality:

• Updated interface{} references to any in comments.
• Removed redundant intPtr helper function.
• All 8 packages pass go vet and go test.

v2.1.4 (Third Round Deep Audit)

Robustness Enhancement:

• Added nil protection to Engine methods (Close/Stats/Ping/DB/Dialect) to prevent nil pointer dereference.
• Unified Returning() validation logic across Insert/Update/Delete builders using quoteColumnStrict for consistent column identifier validation.
• Added validation in Returning SQL generation to return error for invalid column identifiers instead of silently outputting empty strings.

Code Style Unification & Refactoring:

• Extracted dialect.quoteCache shared struct to eliminate duplicated caching logic between MySQL/PostgreSQL dialects.
• quoteCache.Get/Set encapsulates read-write lock operations, reducing ~40 lines of code duplication.
• Removed mu/cache/cacheLen fields and maxQuoteCacheSize/maxPgQuoteCacheSize constants from mysqlDialect/postgresDialect, unified to use dialect.quoteCache.

Performance Benchmarks:

• Added BenchmarkSelectBuild, BenchmarkInsertBuild, BenchmarkUpdateBuild, BenchmarkDeleteBuild covering core SQL build paths.
• Added BenchmarkScanAllStruct, BenchmarkScanAllMap, BenchmarkIterStruct, BenchmarkIterMap covering scan and iteration paths.
• dialect package test coverage improved to 97.2%.

Code Cleanup:

• Fixed redundant sql.RawBytes type switch case in scan.go/iter.go. sql.RawBytes is a type alias for []byte; the case []byte: branch already covers all cases, making case sql.RawBytes: dead code. Removed.
• scan package test coverage improved to 75.1%.

v2.1.3 (Second Round Deep Audit)

Test Coverage Enhancement:

• engine package test coverage significantly improved from 31.1% with comprehensive unit tests covering all core methods.
• scan package added tests for ScanOneStrict, pointer slice allocation, map key type validation (coverage 69.3% → 77.5%).
• schema package added tests for error handling, tag parsing (auto/identity/autoincr/pk/readonly/omitEmpty), default PK detection, ColumnsAndValues edge cases (coverage 73.2% → 89.5%).
• Created independent in-memory test driver to eliminate external database dependencies and improve test reliability.

Security Fix:

• Fixed potential SQL injection risk in Tx.Transaction where SAVEPOINT name was concatenated without validation (defensive validation added even though names are internally generated).

Code Robustness:

• All packages pass go vet static analysis with no warnings.
• All packages pass go test -race race detection with no data races.
• Unified error handling patterns, no unhandled errors in production code.

v2.1.2 (Bug Fixes & Performance)

Bug Fixes:

• Fixed scan.appendLowerASCII/writeLowerASCII not correctly handling remaining substring when encountering non-ASCII characters.
• Fixed Update/Delete Builder not returning an error when Returning() is used with MySQL dialect (MySQL does not support RETURNING).
• Implemented missing builder.For/MustFor/MustDialect functions that were referenced in documentation.

Performance Optimizations:

• NormalizeColumn now uses []byte instead of strings.Builder to reduce memory allocations.
• Added sync.Pool for strings.Builder reuse in builder/internal.go, reducing heap allocations during chain building.
• Unified duplicate placeholder rewriting logic in arg_builder.go into a single rewritePlaceholders function.

API Enhancements:

• Added API.Dialect() accessor to retrieve the bound dialect.
• Added API.Err() accessor to retrieve stored errors.
• Added dialect compatibility check for UpdateBuilder.Returning() (returns error on MySQL).
• Added dialect compatibility check for DeleteBuilder.Returning() (returns error on MySQL).

v2.1.1 (Optimization & Testing)

Performance Optimizations:

• Optimized clause.In() for []any type by eliminating unnecessary slice copy, reducing memory allocations.
• Improved colsKey buffer sizing to minimize reallocations during result scanning.
• Enhanced scanning logic by replacing shared dummy variables with per-row allocation to prevent potential race conditions.
• Extracted reusable trimSpaceASCII function to reduce code duplication in identifier quoting.
• Consolidated duplicate executor wrapping logic in engine/executor.go with shared wrapExecutor helper.

Testing Enhancements:

• Added comprehensive executor package tests (formatArgs, truncateSQL, loggingExecutor).
• Added mock executor tests for logging behavior validation.
• Added scan package tests for struct with unmapped columns, ScanOne variants, and edge cases.
• Added builder package tests for Union, UnionAll, subquery FROM, INSERT FROM SELECT, JOIN updates, USING deletes, DISTINCT, FOR UPDATE/FOR SHARE, and HAVING clauses.
• Improved test coverage: scan 60.2% → 69.3%, builder 54.8% → 55.1%, schema 71.8% → 73.2%.

v2.1.0 (Latest Audit)

Go 1.26.2 Readiness:

• Full support for Go 1.23+ iterators with engine.Iter[T] and builder.Iter[T].
• Completely removed sync.Pool usage across the library in favor of modern Go memory management.
• Hardened schema parsing with race-safe double-check locking.

Stability & Audit:

• Refactored all internal caches (Schema, SnakeCase, Dialect, StructPlan) to use consistent sync.RWMutex patterns with deterministic eviction.
• Optimized scan performance by reducing redundant reflection work.
• Consolidated identifier quoting and validation logic.

v2.0.2

Performance Optimizations:

• Replaced all sync.Map caches with sync.RWMutex + typed maps for better performance and type safety:
  • dialect/mysql.go and dialect/postgres.go: QuoteIdent cache now uses RWMutex with controlled eviction.
  • schema/schema.go: Schema parse cache and ToSnake cache now use RWMutex for better concurrent read performance.
  • scan/scan.go: Struct plan cache now uses RWMutex for faster lookup in high-concurrency scenarios.
• Reduced API surface by making builder factory functions private (newSelectBuilder, newInsertBuilder, etc.), enforcing use of builder.NewAPI() or engine/transaction methods.

Code Structure Improvements:

• Improved encapsulation by reducing public API surface in builder package.
• Unified cache eviction strategy across all packages (clear-all when threshold exceeded).
• Removed unused sync/atomic imports after cache refactoring.

Testing:

• Added comprehensive schema package tests (cache, ColumnsAndValues, edge cases).
• Added scan package tests (nil dest, non-slice, cache validation).
• All packages now have robust test coverage for error paths and edge cases.

v2.0.1

New Features:

• Added InsertIgnore() for MySQL bulk insert optimization (generates INSERT IGNORE INTO ...).
• Added SetExpr() for updating columns with raw SQL expressions (e.g., SET updated_at = NOW()).
• Added CountExpr() for custom count expressions like COUNT(DISTINCT column).
• Added CountExprSQL() for building count SQL without execution (useful for debugging/testing).
• Added QueryFunc() for safe row iteration with guaranteed rows.Close() cleanup.
• Added []uint32 support in clause.In() and clause.NotIn().

Bug Fixes:

• Fixed Count() method to correctly handle queries with GROUP BY by wrapping in a subquery.
• Fixed Count() method to no longer include GROUP BY/HAVING in simple count queries.

Performance:

• Optimized NormalizeColumn with fast path for ASCII-only column names, reducing allocations.

Testing:

• Added comprehensive SQL injection protection tests across all builder types.
• Added dialect unit tests (coverage: 17.2% → 96.9%).
• Added clause unit tests (coverage: 38.9% → 77.1%).
• Added builder unit tests for new features (coverage: 53.4% → 54.9%).

v2.0.0

Core Upgrades:

• Updated internal engine to use Go 1.25/1.26 standards (slices, maps) with advanced memory management.
• Re-architected builder logic to use Go's modernized stack allocation capabilities, discarding outdated sync.Pool usage to resolve lingering pool leak limits and unexpected behaviors under pressure.

DQL & DML Enhancements:

• Added structured Upsert functionality (OnConflict().DoUpdate()/DoNothing()) uniformly handling Postgres and MySQL variations.
• Added extensive Subquery checks and conditions (Exists, NotExists, Count).
• Added robust condition expansions (WhereBetween, WhereNotIn, WhereNotLike).
• Added SQL standard clauses across updates and deletes including JoinAs, FromAs, Using, UsingAs, and extensive Returning implementations.
• Exposed explicit RawExec, RawQuery, and RawQueryFunc APIs seamlessly within Engine and Tx contexts.

Security Hardening:

• Secured Dialect reflection caches with an atomic ceiling block (10,000 keys limit) paired with LRU-style eviction implementations to permanently resolve runtime memory bloating threats.
• Added complete coverage of connection pooling limit attributes (ConnMaxIdleTime).
• Injected strictly safe nil checks enforcing builder outputs against faulty executors.

v1.2.2

Code Style and Documentation:

• Applied gofmt formatting to all Go source files for consistent code style
• Removed duplicate 'Query Caching Considerations' section from README
• Fixed incomplete documentation text in caching section
• All tests passing with race detection (go test -race ./...)
• go vet clean with no warnings

v1.2.1

Code Quality Improvements:

• Comprehensive code audit to ensure no errors, omissions, or security vulnerabilities
• Enhanced code extensibility and usability
• Improved code robustness and reusability
• Unified code style and naming conventions
• Optimized chain API to be closer to SQL primitives
• All tests passing (including race detection tests)
• Performance benchmark validation showing significant memory allocation optimization

v1.2.0

Security Fixes:

• Fixed SAVEPOINT name validation to prevent potential SQL injection
• Enhanced HAVING clause validation to return explicit errors instead of silently skipping empty expressions
• Added SQL statement length limit (1MB) to prevent excessively long SQL from causing database rejection or memory exhaustion
• Added table name length limit (128 characters) to maintain consistency with SAVEPOINT name limits

Performance Optimizations:

• Extracted NormalizeColumn to internal package to eliminate code duplication
• Optimized memory allocation using sync.Pool (ToSnake, colsKey, argBuilder, whereBuilder)
• Pre-allocated argBuilder args slice to reduce expansion overhead
• Added QuoteIdent caching (MySQL/PostgreSQL) to reduce memory allocations for repeated identifier quoting
• Added ToSnake caching to reduce memory allocations for repeated snake_case conversions

API Improvements:

• Enhanced error messages with clearer debugging guidance
• Optimized chain API to be closer to SQL primitives
• Added Engine.Stats() method for connection pool monitoring
• Fixed nil slice bugs in SelectBuilder, UpdateBuilder, and DeleteBuilder constructors

v1.1.3

Security Fixes:

• Fixed SAVEPOINT name validation to prevent potential SQL injection
• Enhanced HAVING clause validation to return explicit errors instead of silently skipping empty expressions

Performance Optimizations:

• Extracted NormalizeColumn to internal package to eliminate code duplication
• Optimized memory allocation using sync.Pool (ToSnake, colsKey)
• Pre-allocated argBuilder args slice to reduce expansion overhead

API Improvements:

• Enhanced error messages with clearer debugging guidance
• Optimized chain API to be closer to SQL primitives

v1.1.2

• Refactored placeholder rewrite functions to eliminate duplication
• Unified column normalization functions
• Added comprehensive documentation and AI Agent Guide

License

MIT

Query Caching Considerations

Query caching is a complex feature that requires careful consideration of:

1. Cache Invalidation: When to invalidate cached results after write operations
2. Memory Management: How to limit memory usage and implement eviction policies
3. Result Serialization: How to serialize and deserialize query results efficiently
4. Concurrency: How to handle concurrent access to cached data

Due to these complexities, query caching is best implemented at the application level rather than in the ORM layer. The ORM provides all the necessary hooks (SQL logging, custom executors) for applications to implement their own caching strategies.

For simple use cases, consider using Go's built-in sync.Map or third-party caching libraries like ristretto or bigcache to cache scanned results rather than raw sql.Rows.