Relica

Relica is a lightweight, type-safe database query builder for Go with zero production dependencies.

🤖 AI Agents: Before generating code, read AGENTS.md for correct API patterns. Use Model() API for CRUD, Expression API for WHERE conditions. Avoid map[string]interface{}.

Agent skill: skills/relica/ (Agent Skills format) — version-exact docs, an audit script for a project's Relica code, the upgrade procedure. In Claude Code (v2.1.275+): /plugin install relica --marketplace coregx/relica (or /plugin marketplace add coregx/relica, then /plugin install relica@relica; the shell form claude plugin install needs v2.1.292+). Other agents: read <module Dir>/skills/relica/SKILL.md at your version (go list -m -f '{{.Dir}}' github.com/coregx/relica).

✨ Features

• Zero Dependencies - Truly empty go.mod, uses only Go standard library
• High Performance - LRU statement cache, batch operations (3.3x faster)
• Type-Safe - Generic One[T]/All[T]/Scalar[T] + reflection-based struct scanning
• Model() API - ORM-style CRUD with auto-populated IDs (int + UUID via autoincrement tag), composite PKs
• AutoID - Stripe-like prefixed IDs (usr_, ord_) with dual-key pattern, FindByPublicID(), prefix validation
• NullStringMap - Dynamic scanning without predefined structs
• Named Placeholders - {:name} syntax with relica.Params (Where, Having, NewQuery(...).BindParams) for readable queries
• Functional Expressions - CASE, COALESCE, NULLIF, GREATEST, LEAST, CONCAT
• Transactional() - Auto commit/rollback helper with panic recovery
• Dynamic WHERE - AndWhere() / OrWhere() for conditional query building
• Row() / Column() - Convenient scalar and single-column queries
• Prepare() / Close() - Manual statement control for batch operations
• Transaction Support - Full ACID with all isolation levels
• Security - Parameterized queries prevent SQL injection by design
• Batch Operations - Efficient multi-row INSERT and UPDATE
• JOIN Operations - INNER, LEFT, RIGHT, FULL, CROSS JOIN support
• Sorting & Pagination - ORDER BY, LIMIT, OFFSET, DISTINCT, FOR UPDATE/FOR SHARE
• Aggregate Functions - COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
• Subqueries - IN, EXISTS, FROM subqueries, scalar subqueries
• Set Operations - UNION, UNION ALL, INTERSECT, EXCEPT
• Common Table Expressions - WITH clause, recursive CTEs
• Multi-Database - PostgreSQL, MySQL 8.0+, SQLite 3.25+ support
• Well-Tested - 85%+ coverage, stdlib testing only
• Clean API - Fluent builder pattern with context support

Latest Release: See CHANGELOG.md for version history and GitHub Releases for release notes.

📌 API Usage Priority

Priority	API	When to Use
PREFERRED	db.Model(&struct).Insert/Update/Delete/Upsert()	All CRUD operations with structs
PREFERRED	relica.Eq(), relica.And(), relica.In(), etc.	WHERE conditions
PREFERRED	relica.HashExp{"col": val}	Simple equality conditions
ACCEPTABLE	Where("col = {:col}", relica.Params{"col": val})	Named placeholders
ACCEPTABLE	Where("col = ?", val)	Positional placeholders
AVOID	map[string]interface{}	Only for dynamic/unknown schemas

For AI Agents: See AGENTS.md for complete patterns and examples.

🚀 Quick Start

Installation

go get github.com/coregx/relica

Note: Always import only the main relica package. Internal packages are protected and not part of the public API.

Basic Usage

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/coregx/relica"
    _ "github.com/lib/pq" // PostgreSQL driver
)

type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

func main() {
    // Connect to database
    db, err := relica.Open("postgres", "postgres://user:pass@localhost/db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    ctx := context.Background()

    // SELECT with Expression API (PREFERRED)
    var user User
    err = db.Select().
        From("users").
        Where(relica.Eq("id", 1)).
        WithContext(ctx).
        One(&user)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("User: %+v\n", user)

    // SELECT with multiple conditions (PREFERRED)
    var users []User
    err = db.Select().
        From("users").
        Where(relica.And(
            relica.GreaterThan("age", 18),
            relica.Eq("status", "active"),
        )).
        All(&users)

    // FIND by primary key (one-liner)
    var user User
    err = db.Model(&user).Find(42)

    // FIND with column selection and filters (PK from struct)
    user = User{ID: 42}
    err = db.Select("name", "email").Where(relica.IsNull("deleted_at")).Model(&user)

    // INSERT with Model() API (PREFERRED)
    newUser := User{Name: "Alice", Email: "alice@example.com"}
    err = db.Model(&newUser).Insert()
    fmt.Println(newUser.ID) // Auto-populated!

    // UPDATE with Model() API (PREFERRED)
    newUser.Name = "Alice Updated"
    err = db.Model(&newUser).Update()

    // DELETE with Model() API (PREFERRED)
    err = db.Model(&newUser).Delete()

    // CTE (Common Table Expression)
    statsQuery := db.Select("user_id", "COUNT(*) as order_count").
        From("orders").
        GroupBy("user_id")

    var results []struct {
        UserID     int `db:"user_id"`
        OrderCount int `db:"order_count"`
    }
    err = db.Select().
        With("stats", statsQuery).
        From("stats").
        All(&results)
}

📚 Core Features

CRUD Operations

Model() API (PREFERRED)

Use Model() API for all struct-based CRUD operations:

// INSERT - Auto-populates ID (PREFERRED)
user := User{Name: "Bob", Email: "bob@example.com"}
db.Model(&user).Insert()
fmt.Println(user.ID) // Auto-populated!

// INSERT - Selective fields
db.Model(&user).Insert("name", "email") // Only these fields

// UPDATE - By primary key (PREFERRED)
user.Status = "inactive"
db.Model(&user).Update()

// UPDATE - Selective fields
db.Model(&user).Update("status") // Only update status

// DELETE - By primary key (PREFERRED)
db.Model(&user).Delete()

SELECT with Expression API (PREFERRED)

// Simple equality
db.Select().From("users").
    Where(relica.Eq("id", 1)).
    One(&user)

// Multiple conditions
db.Select().From("users").
    Where(relica.And(
        relica.GreaterThan("age", 18),
        relica.Eq("status", "active"),
    )).
    All(&users)

// HashExp for simple equality
db.Select().From("users").
    Where(relica.HashExp{"status": "active", "role": "admin"}).
    All(&users)

Map-based Operations (AVOID - Use Only for Dynamic Data)

Warning: Use map[string]interface{} ONLY when struct is not available (dynamic schemas, JSON payloads).

// AVOID - Only for dynamic/unknown schemas
db.Insert("users", map[string]interface{}{
    "name": dynamicData["name"],
}).Execute()

// PREFER - Use Model() API instead
user := User{Name: dynamicData["name"].(string)}
db.Model(&user).Insert()

UPDATE / DELETE need Where (v0.20.0+)

A builder Update or Delete without a condition — or with one that renders empty, such as relica.HashExp{} — is an error instead of a statement that hits every row. Say so when you mean the whole table:

db.Delete("sessions").Where(relica.LessThan("expires_at", now)).Execute()
db.Delete("sessions").AllRows().Execute() // the whole table, on purpose

db.Model(&m).Update() / Delete() always use the primary key and are unaffected.

Expression API

Relica supports fluent expression builders for type-safe, complex WHERE clauses:

HashExp - Simple Conditions

// Simple equality
db.Select().From("users").
    Where(relica.HashExp{"status": 1}).
    All(&users)

// Multiple conditions (AND)
db.Select().From("users").
    Where(relica.HashExp{
        "status": 1,
        "age":    30,
    }).
    All(&users)

// IN clause (slice values)
db.Select().From("users").
    Where(relica.HashExp{
        "status": []interface{}{1, 2, 3},
    }).
    All(&users)

// NULL handling
db.Select().From("users").
    Where(relica.HashExp{
        "deleted_at": nil,  // IS NULL
    }).
    All(&users)

// Combined: IN + NULL + equality
db.Select().From("users").
    Where(relica.HashExp{
        "status":     []interface{}{1, 2},
        "deleted_at": nil,
        "role":       "admin",
    }).
    All(&users)

Comparison Operators

// Greater than
db.Select().From("users").
    Where(relica.GreaterThan("age", 18)).
    All(&users)

// Less than or equal
db.Select().From("users").
    Where(relica.LessOrEqual("price", 100.0)).
    All(&products)

// Available: Eq, NotEq, GreaterThan, LessThan, GreaterOrEqual, LessOrEqual

NULL Checks

// IS NULL / IS NOT NULL
db.Select().From("users").
    Where(relica.IsNull("deleted_at")).
    All(&activeUsers)

db.Select().From("orders").
    Where(relica.IsNotNull("shipped_at")).
    All(&shippedOrders)

IN and BETWEEN

// IN
db.Select().From("users").
    Where(relica.In("role", "admin", "moderator")).
    All(&users)

// NOT IN
db.Select().From("users").
    Where(relica.NotIn("status", 0, 99)).
    All(&users)

// BETWEEN
db.Select().From("orders").
    Where(relica.Between("created_at", startDate, endDate)).
    All(&orders)

LIKE with Automatic Escaping

// Default: %value% (partial match)
db.Select().From("users").
    Where(relica.Like("name", "john")).  // name LIKE '%john%'
    All(&users)

// Multiple values (AND)
db.Select().From("articles").
    Where(relica.Like("title", "go", "database")).  // title LIKE '%go%' AND title LIKE '%database%'
    All(&articles)

// Custom matching: Match(left, right) sets the % on each side
db.Select().From("files").
    Where(relica.Like("filename", ".txt").Match(true, false)).  // filename LIKE '%.txt' (suffix)
    All(&files)
db.Select().From("files").
    Where(relica.Like("filename", "report").Match(false, true)).  // filename LIKE 'report%' (prefix)
    All(&files)

// OR logic
db.Select().From("users").
    Where(relica.OrLike("email", "gmail", "yahoo")).  // email LIKE '%gmail%' OR email LIKE '%yahoo%'
    All(&users)

// Case-insensitive on every database (v0.19.1+): ILIKE on PostgreSQL,
// LOWER(col) LIKE LOWER(?) on MySQL and SQLite. Also NotILike, OrILike, OrNotILike.
db.Select().From("hosts").
    Where(relica.ILike("cpu_model", "xeon")).
    All(&hosts)

Logical Combinators

// AND
db.Select().From("users").
    Where(relica.And(
        relica.Eq("status", 1),
        relica.GreaterThan("age", 18),
    )).
    All(&users)

// OR
db.Select().From("users").
    Where(relica.Or(
        relica.Eq("role", "admin"),
        relica.Eq("role", "moderator"),
    )).
    All(&users)

// NOT
db.Select().From("users").
    Where(relica.Not(
        relica.In("status", 0, 99),
    )).
    All(&users)

// Nested combinations
db.Select().From("users").
    Where(relica.And(
        relica.Eq("status", 1),
        relica.Or(
            relica.Eq("role", "admin"),
            relica.GreaterThan("age", 30),
        ),
    )).
    All(&users)

Named Placeholders

Named parameters use {:name} syntax with relica.Params map — readable, safe, and reusable:

// Single parameter
db.Select().From("users").
    Where("id = {:id}", relica.Params{"id": 1}).
    One(&user)

// Multiple parameters
db.Select().From("users").
    Where("status = {:status} AND role = {:role}", relica.Params{
        "status": "active",
        "role":   "admin",
    }).
    All(&users)

// Same parameter used multiple times
db.Select().From("categories").
    Where("parent_id = {:id} OR id = {:id}", relica.Params{"id": categoryID}).
    All(&categories)

Named placeholders work in Where, AndWhere, OrWhere on Select, Update, and Delete queries.

Why {:name} and not :name or @name? Curly braces are not part of SQL syntax, so {:name} never conflicts with PostgreSQL type casts (::), MySQL user variables (@var), or JSONB operators (?). This syntax is inherited from ozzo-dbx, Relica's predecessor.

Positional Placeholders

Positional ? parameters also work (backward compatible):

db.Select().From("users").
    Where("status = ? AND age > ?", 1, 18).
    All(&users)

// Can mix styles
db.Select().From("users").
    Where("status = ?", 1).
    Where(relica.GreaterThan("age", 18)).
    All(&users)

Model() API

ORM-style operations with automatic struct mapping, auto-populated IDs, and selective field control.

Basic CRUD Operations

type User struct {
    ID    int64  `db:"id"`     // Auto-populated after INSERT
    Name  string `db:"name"`
    Email string `db:"email"`
    Status string `db:"status"`
}

// INSERT - Auto-populates ID after insert
user := User{Name: "Alice", Email: "alice@example.com"}
err := db.Model(&user).Insert()
fmt.Println(user.ID) // 1 (auto-populated!)

// INSERT - Selective fields
user := User{Name: "Bob", Email: "bob@example.com", Status: "pending"}
err := db.Model(&user).Insert("name", "email") // Only name and email inserted

// UPDATE - Updates all fields by primary key
user.Name = "Alice Updated"
err := db.Model(&user).Update()

// UPDATE - Selective fields
err := db.Model(&user).Update("status") // Only update status field

// DELETE - By primary key
err := db.Model(&user).Delete()

Auto-Populate ID

Works across all databases (PostgreSQL, MySQL, SQLite):

user := User{Name: "Charlie"}
err := db.Model(&user).Insert()

// PostgreSQL: Uses RETURNING clause
// MySQL/SQLite: Uses LastInsertId()
// ID automatically populated in all cases
fmt.Println(user.ID) // Auto-generated ID

Selective Fields

Control exactly which fields are inserted/updated:

user := User{
    Name:   "Alice",
    Email:  "alice@example.com",
    Status: "pending",
}

// Insert only name and email (status remains default)
db.Model(&user).Insert("name", "email")

// Update only status (name and email unchanged)
user.Status = "active"
db.Model(&user).Update("status")

// Exclude fields (Exclude takes precedence)
db.Model(&user).Exclude("status").Insert("name", "email", "status")
// Result: Only name and email inserted

Primary Key Detection

Priority order:

1. Field tagged db:"pk" — legacy single PK (maps to column "pk")
2. Field tagged db:"column,pk" — explicit PK with column name
3. Field named ID (no tag) — auto-detected
4. Field named Id (no tag) — last resort

type Product struct {
    ProductID int    `db:"product_id,pk"` // Explicit PK with column name
    Name      string `db:"name"`
}

type Order struct {
    ID int // Detected automatically (field named "ID")
}

Transactions

err := db.Transactional(ctx, func(tx *relica.Tx) error {
    user := User{Name: "Alice"}
    return tx.Model(&user).Insert() // error → rollback, nil → commit
})

Every statement of the transaction must go through tx (or a repository that receives it as a relica.Executor) — see Transactions in AGENTS.md for the rules.

Advanced Usage

// Custom table name
db.Model(&user).Table("custom_users").Insert()

// Exclude fields from operation
db.Model(&user).Exclude("created_at", "updated_at").Update()

// Pre-set IDs (won't be overwritten)
user := User{ID: 999, Name: "System"}
db.Model(&user).Insert() // ID stays 999

Upsert (INSERT or UPDATE)

// INSERT ... ON CONFLICT (id) DO UPDATE SET all non-PK fields
user := User{ID: 1, Name: "Alice", Email: "alice@example.com"}
err := db.Model(&user).Upsert()

// Selective: only update specific fields on conflict
err = db.Model(&user).Upsert("name", "email")

Works with PostgreSQL (ON CONFLICT DO UPDATE), MySQL (ON DUPLICATE KEY UPDATE), SQLite (ON CONFLICT DO UPDATE). Exclude("created_at") leaves a column out of both the insert and the update.

The builder form needs exactly one conflict action — otherwise Execute returns an error and nothing is sent:

_, err := db.Upsert("users", values).OnConflict("id").DoUpdateAll().Execute()    // every non-target column
_, err = db.Upsert("users", values).OnConflict("id").DoUpdate("name").Execute()  // listed columns only
_, err = db.Upsert("users", values).OnConflict("id").DoNothing().Execute()       // keep the existing row

Updated columns take the value of the row proposed for insertion. On MySQL the default is col = VALUES(col); relica.WithMySQLUpsertSyntax(relica.MySQLUpsertRowAlias) switches to the 8.0.19+ row alias.

UpdateChanged (Dirty Field Detection)

// Load user
var user User
db.Select().From("users").Where(relica.Eq("id", 1)).One(&user)

// Save original, modify fields
original := user
user.Name = "Alice Updated"
user.Status = 2

// UPDATE only changed fields (name, status) — not all fields
err := db.Model(&user).UpdateChanged(&original)
// Generates: UPDATE users SET name=?, status=? WHERE id=?

// If nothing changed — returns nil, no query executed

JOIN Operations

Solve N+1 query problems with JOIN support - reduces 101 queries to 1 query (100x improvement).

// Simple INNER JOIN
var results []struct {
    UserID   int    `db:"user_id"`
    UserName string `db:"user_name"`
    PostID   int    `db:"post_id"`
    Title    string `db:"title"`
}

db.Select("u.id as user_id", "u.name as user_name", "p.id as post_id", "p.title").
    From("users u").
    InnerJoin("posts p", "p.user_id = u.id").
    All(&results)

// Multiple JOINs with aggregates
db.Select("messages.*", "users.name", "COUNT(attachments.id) as attachment_count").
    From("messages m").
    InnerJoin("users u", "m.user_id = u.id").
    LeftJoin("attachments a", "m.id = a.message_id").
    Where("m.status = ?", 1).
    GroupBy("messages.id").
    All(&results)

// All JOIN types supported
db.Select().From(table1).InnerJoin(table2, on)  // INNER JOIN
db.Select().From(table1).LeftJoin(table2, on)   // LEFT OUTER JOIN
db.Select().From(table1).RightJoin(table2, on)  // RIGHT OUTER JOIN
db.Select().From(table1).FullJoin(table2, on)   // FULL OUTER JOIN (PostgreSQL, SQLite)
db.Select().From(table1).CrossJoin(table2)      // CROSS JOIN (no ON condition)

// JOIN with Expression API
db.Select().
    From("messages m").
    InnerJoin("users u", relica.And(
        relica.NewExp("m.user_id = u.id"),
        relica.GreaterThan("u.status", 0),
    )).
    All(&results)

Performance: 100x query reduction (N+1 problem solved), 6-25x faster depending on database.

See the Advanced Patterns Guide for comprehensive JOIN examples and best practices.

Sorting and Pagination

Database-side sorting and pagination for efficient data retrieval - 100x memory reduction.

// ORDER BY with multiple columns
db.Select().
    From("messages").
    OrderBy("created_at DESC", "id ASC").
    All(&messages)

// Pagination with LIMIT and OFFSET
const pageSize = 100
const pageNumber = 2 // Third page (0-indexed)

db.Select().
    From("users").
    OrderBy("age DESC").
    Limit(pageSize).
    Offset(pageNumber * pageSize).
    All(&users)

// Table column references
db.Select().
    From("messages m").
    InnerJoin("users u", "m.user_id = u.id").
    OrderBy("m.created_at DESC", "u.name ASC").
    Limit(50).
    All(&results)

Performance: 100x memory reduction (fetch only what you need vs all rows), 6x faster.

DISTINCT

// Eliminate duplicate rows
db.Select("category").From("products").Distinct().All(&categories)
// SELECT DISTINCT "category" FROM "products"

Row Locking (FOR UPDATE / FOR SHARE)

Pessimistic locking for concurrent data access. A lock lives as long as its transaction, so lock only through tx: on db the SELECT autocommits and the lock is released at once. Omitted from the SQL on SQLite (database-level locking).

err := db.Transactional(ctx, func(tx *relica.Tx) error {
    // Exclusive lock — other transactions wait to update or lock these rows
    if err := tx.Select().From("accounts").
        Where(relica.Eq("id", accountID)).
        ForUpdate().
        One(&account); err != nil {
        return err
    }

    // Shared lock — others may read and share-lock, but not update
    if err := tx.Select().From("products").
        Where(relica.Eq("id", productID)).
        ForShare().
        One(&product); err != nil {
        return err
    }

    // Non-blocking lock — skip rows locked by other workers (job queue pattern)
    return tx.Select().From("tasks").
        Where(relica.Eq("status", "pending")).
        ForUpdateSkipLocked().
        Limit(10).
        All(&tasks)
})

Aggregate Functions

Database-side aggregations for COUNT, SUM, AVG, MIN, MAX - 2,500,000x memory reduction.

// Simple COUNT
var count struct{ Total int `db:"total"` }
db.Select("COUNT(*) as total").
    From("messages").
    One(&count)

// Multiple aggregates
type Stats struct {
    Count int     `db:"count"`
    Sum   int64   `db:"sum"`
    Avg   float64 `db:"avg"`
    Min   int     `db:"min"`
    Max   int     `db:"max"`
}

var stats Stats
db.Select("COUNT(*) as count", "SUM(size) as sum", "AVG(size) as avg", "MIN(size) as min", "MAX(size) as max").
    From("messages").
    One(&stats)

// GROUP BY with HAVING
type UserStats struct {
    UserID       int `db:"user_id"`
    MessageCount int `db:"message_count"`
}

var userStats []UserStats
db.Select("user_id", "COUNT(*) as message_count").
    From("messages").
    GroupBy("user_id").
    Having("COUNT(*) > ?", 100).
    OrderBy("message_count DESC").
    All(&userStats)

Performance: 2,500,000x memory reduction (database aggregation vs fetching all rows), 20x faster.

See the Advanced Patterns Guide for comprehensive aggregate examples and patterns.

Query Helpers

Exists

exists, err := db.Select().From("users").
    Where(relica.Eq("email", "alice@example.com")).
    Exists()
// SELECT EXISTS(SELECT 1 FROM "users" WHERE "email" = $1)

Count

count, err := db.Select().From("users").
    Where(relica.Eq("status", "active")).
    Count()
// SELECT COUNT(*) FROM "users" WHERE "status" = $1

ToSQL (Query Preview)

// Preview SQL without executing
sql, params := db.Select().From("users").
    Where(relica.Eq("id", 1)).
    ToSQL()
// sql = `SELECT * FROM "users" WHERE "id" = $1`
// params = []interface{}{1}

// Works on Update and Delete too
sql, params = db.Update("users").
    Set(map[string]interface{}{"status": "active"}).
    Where(relica.Eq("id", 1)).
    ToSQL()

Error Handling

ErrNotFound

One() returns relica.ErrNotFound instead of raw sql.ErrNoRows:

var user User
err := db.Select().From("users").Where(relica.Eq("id", 999)).One(&user)

if errors.Is(err, relica.ErrNotFound) {
    // record not found — clean, library-level error
}

// sql.ErrNoRows still works too (wrapped inside)
if errors.Is(err, sql.ErrNoRows) {
    // also true
}

Note: All() returns empty slice for no results, not an error.

Error Classification

Error helpers read the driver's error code (PostgreSQL SQLSTATE, MySQL/MariaDB error number, SQLite extended result code) anywhere in the error tree, so they work with pgx and lib/pq, go-sql-driver/mysql, modernc.org/sqlite and mattn/go-sqlite3 — also on a non-English server:

err := db.Model(&user).Insert()

if relica.IsUniqueViolation(err) {
    // duplicate key (e.g., email already exists)
}
if relica.IsForeignKeyViolation(err) {
    // referenced record doesn't exist
}
if relica.IsNotNullViolation(err) {
    // required field is missing
}
if relica.IsCheckViolation(err) {
    // CHECK constraint failed
}

// Concurrency (v0.19.1+): the database rolled the transaction back — run it again as a whole
if relica.IsDeadlock(err) || relica.IsSerializationFailure(err) { /* retry with backoff */ }
if relica.IsLockTimeout(err) { /* lock not acquired in time (lock_timeout, NOWAIT, SQLite busy) */ }

A complete retry helper is in AGENTS.md.

Advanced SQL Features

Relica adds powerful SQL features for complex queries.

Subqueries

IN/EXISTS Subqueries:

// Find users who have placed orders
sub := db.Select("user_id").From("orders").Where("status = ?", "completed")
db.Select().From("users").Where(relica.In("id", sub.AsExpression())).All(&users)

// Find users with at least one order (EXISTS is often faster)
orderCheck := db.Select().SelectExpr("1").From("orders").Where("orders.user_id = users.id")
db.Select().From("users").Where(relica.Exists(orderCheck.AsExpression())).All(&users)

FROM Subqueries:

// Calculate aggregates, then filter
stats := db.Select("user_id", "COUNT(*) as order_count", "SUM(total) as total_spent").
    From("orders").
    GroupBy("user_id")

db.Select("user_id", "order_count", "total_spent").
    FromSelect(stats, "order_stats").
    Where("order_count > ? AND total_spent > ?", 10, 5000).
    All(&topCustomers)

Scalar Subqueries in SELECT (type-safe, no raw SQL):

// Count orders per user — correlated subquery
sub := db.Select("COUNT(*)").From("orders").
    Where(relica.EqCol("orders.user_id", "users.id"))
db.Select("id", "name").
    SelectSub(sub.AsExpression(), "order_count").
    From("users").All(&users)

See Subquery Guide for complete examples and performance tips.

Set Operations

UNION/UNION ALL:

// Combine active and archived users (UNION removes duplicates)
active := db.Select("name").From("users").Where("status = ?", 1)
archived := db.Select("name").From("archived_users").Where("status = ?", 1)
active.Union(archived).All(&allNames)

// UNION ALL is 2-3x faster (keeps duplicates)
active.UnionAll(archived).All(&allNames)

INTERSECT/EXCEPT (PostgreSQL, MySQL 8.0.31+, SQLite):

// Find users who have placed orders (INTERSECT)
allUsers := db.Select("id").From("users")
orderUsers := db.Select("user_id").From("orders")
allUsers.Intersect(orderUsers).All(&activeUsers)

// Find users without orders (EXCEPT)
allUsers.Except(orderUsers).All(&inactiveUsers)

See Set Operations Guide for database compatibility and workarounds.

Common Table Expressions (CTEs)

Basic CTEs:

// Define reusable query
orderTotals := db.Select("user_id", "SUM(total) as total").
    From("orders").
    GroupBy("user_id")

// Use CTE in main query
db.Select().
    With("order_totals", orderTotals).
    From("order_totals").
    Where("total > ?", 1000).
    All(&premiumUsers)

Recursive CTEs (organizational hierarchies, trees):

// Anchor: top-level employees
anchor := db.Select("id", "name", "manager_id").SelectExpr("1 AS level").
    From("employees").
    Where("manager_id IS NULL")

// Recursive: children
recursive := db.Select("e.id", "e.name", "e.manager_id").SelectExpr("h.level + 1").
    From("employees e").
    InnerJoin("hierarchy h", "e.manager_id = h.id")

// Build hierarchy
db.Select().
    WithRecursive("hierarchy", anchor.UnionAll(recursive)).
    From("hierarchy").
    OrderBy("level", "name").
    All(&orgChart)

See CTE Guide for hierarchical data examples (org charts, bill of materials, category trees).

Window Functions

Relica supports window functions via SelectExpr() for advanced analytics:

// Rank users by order total within each country
db.Select("user_id", "country", "total").
    SelectExpr("RANK() OVER (PARTITION BY country ORDER BY total DESC) AS rank_no").
    From("orders").
    All(&rankedOrders)

// Running totals with frame specification
db.Select("date", "amount").
    SelectExpr("SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total").
    From("transactions").
    OrderBy("date").
    All(&runningTotals)

See Window Functions Guide for complete reference with RANK(), ROW_NUMBER(), LAG(), LEAD(), and frame specifications.

Transactions

// Start transaction
tx, err := db.BeginTx(ctx, &relica.TxOptions{
    Isolation: sql.LevelSerializable,
})
if err != nil {
    return err
}
defer tx.Rollback() // Rollback if not committed

// Execute queries within transaction
_, err = tx.Insert("users", userData).Execute()
if err != nil {
    return err
}

_, err = tx.Update("accounts").
    Set(map[string]interface{}{"balance": newBalance}).
    Where("user_id = ?", userID).
    Execute()
if err != nil {
    return err
}

// Commit transaction
return tx.Commit()

Executor — one type for *DB and *Tx

relica.Executor is implemented by both *relica.DB and *relica.Tx, so repository code can run inside or outside a transaction without knowing which:

func (r *UserRepo) Save(ctx context.Context, ex relica.Executor, u *User) error {
    _, err := ex.Insert("users", map[string]any{"name": u.Name}).Execute()
    return err
}

repo.Save(ctx, db, &u)                                                        // plain connection
db.Transactional(ctx, func(tx *relica.Tx) error { return repo.Save(ctx, tx, &u) }) // same code, in a tx

It also enables the go-rest-api / ozzo-dbx dbcontext pattern — store the transaction in the context and let With(ctx) hand it back:

func (d *DB) With(ctx context.Context) relica.Executor {
    if tx, ok := ctx.Value(txKey).(*relica.Tx); ok {
        return tx // repositories join the active transaction
    }
    return d.db.WithContext(ctx)
}

Executor covers all query builders, Model, NewQuery and raw ExecContext/QueryContext/QueryRowContext. Lifecycle methods (Begin, Commit, Rollback, Transactional) are intentionally excluded, and the method set is frozen so test doubles keep compiling.

A transaction started outside Relica joins the same pattern through WrapTx:

sqlTx, _ := sqlDB.BeginTx(ctx, nil)      // opened by another library, a migration tool, sqlc…
tx := db.WrapTx(ctx, sqlTx)              // *relica.Tx — and therefore an Executor
err := repo.Save(ctx, tx, &u)            // repository code is unchanged
// commit or roll back on sqlTx or tx — both act on the same transaction

AutoID — Enterprise ID Pattern

Stripe-like prefixed IDs with dual-key pattern. First query builder with native support.

// 1. Define model — one tag per field:
type User struct {
    ID       int64  `db:"id,pk"`                    // internal PK (auto-increment)
    PublicID string `db:"public_id,autoid:usr"`      // external ID (auto-generated)
    Name     string `db:"name"`
}

// 2. Insert — PublicID auto-generated, ID auto-populated:
user := User{Name: "Alice"}
db.Model(&user).Insert()
fmt.Println(user.PublicID) // "usr_019078fa-b37e-7abc-..."
fmt.Println(user.ID)       // 42

// 3. Lookup by public ID (one-liner with prefix validation):
var found User
err := db.Model(&found).FindByPublicID("usr_019078fa-b37e-7abc-...")

// 4. Wrong prefix → safe error (prevents cross-model lookups):
err = db.Model(&found).FindByPublicID("ord_019078fa-...")
// errors.Is(err, relica.ErrAutoIDPrefixMismatch) == true

// 5. Pre-set ID for testing (not overwritten):
user := User{PublicID: "usr_test-id", Name: "Test"}
db.Model(&user).Insert() // PublicID stays "usr_test-id"

BeforeInserter hook for custom pre-insert logic:

func (u *User) BeforeInsert() error {
    u.CreatedAt = time.Now()
    return nil // return error to abort insert
}

Custom generators (ULID, Snowflake, etc.):

relica.RegisterIDGenerator("ulid", func() string {
    return ulid.Make().String()
})

type Event struct {
    ID       int64  `db:"id,pk"`
    PublicID string `db:"public_id,autoid:evt,gen=ulid"` // "evt_01H9XZ..."
}

See Primary Key Strategy for when to use autoincrement vs UUID v7 vs dual-key pattern.

Batch Operations

Batch INSERT (3.3x faster than individual inserts):

result, err := db.BatchInsert("users", []string{"name", "email"}).
    Values("Alice", "alice@example.com").
    Values("Bob", "bob@example.com").
    Values("Charlie", "charlie@example.com").
    Execute()

// Or from a slice
users := []User{
    {Name: "Alice", Email: "alice@example.com"},
    {Name: "Bob", Email: "bob@example.com"},
}

batch := db.BatchInsert("users", []string{"name", "email"})
for _, user := range users {
    batch.Values(user.Name, user.Email)
}
result, err := batch.Execute()

// Or straight from the structs, as an upsert (v0.20.1+)
result, err = db.BatchInsert("users", nil). // nil: columns from the struct
    ValuesStructs(users).
    OnConflict("email").
    DoUpdateAll().
    Execute()

One batch is one statement: rows × columns may not exceed 65535 bind parameters on PostgreSQL and MySQL, 32766 on SQLite. A larger batch is an error returned before anything is sent — Relica does not split it for you; send the rows in slices, inside Transactional when they must be atomic (AGENTS.md › Batch Operations).

Batch UPDATE (updates multiple rows with different values):

result, err := db.BatchUpdate("users", "id").
    Set(1, map[string]interface{}{"name": "Alice Updated", "status": "active"}).
    Set(2, map[string]interface{}{"name": "Bob Updated", "status": "active"}).
    Set(3, map[string]interface{}{"age": 30}).
    Execute()

Context Support

// Query with timeout
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

var users []User
err := db.Select().
    From("users").
    WithContext(ctx).
    All(&users)

// Context on query level
err = db.Select().
    From("users").
    WithContext(ctx).
    One(&user)

// Transaction context auto-propagates: every query on tx runs under the ctx given to Begin
err = db.Transactional(ctx, func(tx *relica.Tx) error {
    return tx.Select().From("users").One(&user) // uses ctx automatically
})

🏗️ Database Support

Database	Status	Placeholders	Identifiers	UPSERT
PostgreSQL	✅ Full	$1, $2, $3	"users"	ON CONFLICT
MySQL	✅ Full	?, ?, ?	`users`	ON DUPLICATE KEY
SQLite	✅ Full	?, ?, ?	`users`	ON CONFLICT

⚡ Performance

Statement Cache

• Default capacity: 1000 prepared statements
• Hit latency: <60ns
• Thread-safe: Concurrent access optimized
• Metrics: Hit rate, evictions, cache size

// Configure cache capacity
db, err := relica.Open("postgres", dsn,
    relica.WithStmtCacheCapacity(2000),
    relica.WithMaxOpenConns(25),
    relica.WithMaxIdleConns(5),
)

// Check connection pool statistics
stats := db.Stats()
fmt.Printf("Open: %d, Idle: %d, InUse: %d\n",
    stats.OpenConnections, stats.Idle, stats.InUse)

Batch Operations Performance

Operation	Rows	Time	vs Single	Memory
Batch INSERT	100	327ms	3.3x faster	-15%
Single INSERT	100	1094ms	Baseline	Baseline
Batch UPDATE	100	1370ms	2.5x faster	-55% allocs

🔧 Configuration

db, err := relica.Open("postgres", dsn,
    // Connection pool
    relica.WithMaxOpenConns(25),
    relica.WithMaxIdleConns(5),

    // Statement cache
    relica.WithStmtCacheCapacity(1000),
)

Table and Column Names, Struct Scanning (v0.20.0+)

An untagged field maps to the snake_case of its name (CreatedAt → created_at), a type without TableName() to its snake_case singular name (UserProfile → user_profile), and embedded structs are flattened — the ozzo-dbx rules. An embedded single value (time.Time, sql.NullString, a type with its own Scan/Value) is one column (null_string); read single values with Row/Column, not One/All. db:"col" and TableName() override them; relica.WithNaming changes them per DB:

db, err := relica.Open("postgres", dsn,
    relica.WithNaming(relica.Snake(relica.Initialisms(relica.GoInitialisms()...)))) // HTTPLog → http_log

Reading into a struct, a field with no column in the result is an error by default (relica.ScanRequireFields) instead of a silent zero value; so is a column named in Select("id", "email") that no field takes, and two result columns for one field (Select() over a join where both tables have id: alias one). relica.WithScanMode(relica.ScanLenient) restores the ozzo-dbx behaviour per DB, .ScanMode(...) per query; relica.ScanStrict also rejects result columns that have no field. Details: AGENTS.md › Table and Column Names.

Connection Management

Standard Connection

// Create new connection with Relica managing the pool
db, err := relica.Open("postgres", dsn)
defer db.Close()

Wrap Existing Connection

Use WrapDB() when you need to integrate Relica with an existing *sql.DB connection:

import (
    "database/sql"
    "time"

    "github.com/coregx/relica"
    _ "github.com/lib/pq"
)

// Create and configure external connection pool
sqlDB, err := sql.Open("postgres", dsn)
if err != nil {
    log.Fatal(err)
}

// Apply custom pool settings
sqlDB.SetMaxOpenConns(100)
sqlDB.SetMaxIdleConns(50)
sqlDB.SetConnMaxLifetime(time.Hour)
sqlDB.SetConnMaxIdleTime(10 * time.Minute)

// Wrap with Relica query builder
db := relica.WrapDB(sqlDB, "postgres")

// Use Relica's fluent API
var users []User
err = db.Select().
    From("users").
    Where("status = ?", 1).
    All(&users)

// Caller is responsible for closing the connection
defer sqlDB.Close()  // NOT db.Close()

Use Cases for WrapDB:

• Existing Codebase Integration: Add Relica to projects with established *sql.DB connections
• Custom Pool Configuration: Apply advanced connection pool settings before wrapping
• Shared Connections: Multiple parts of your application can share the same pool
• Testing: Wrap test database connections without managing lifecycle

Important Notes:

• Each WrapDB() call creates a new Relica instance with its own statement cache
• The caller is responsible for closing the underlying *sql.DB connection
• Multiple wraps of the same connection are isolated (separate caches)

Joining an existing transaction — the same idea one level down. When another library, a migration tool or sqlc has already opened a *sql.Tx, WrapTx lets Relica run inside it instead of starting its own:

sqlTx, err := sqlDB.BeginTx(ctx, nil)
tx := db.WrapTx(ctx, sqlTx)   // *relica.Tx on the caller's transaction
_, err = tx.Insert("users", data).Execute()
err = sqlTx.Commit()          // or tx.Commit() — same transaction, caller decides

Connection Inspection

Access the underlying connection for pool tuning, health checks, or driver identification:

// Get underlying *sql.DB for direct pool configuration
sqlDB := db.SqlDB()
sqlDB.SetMaxOpenConns(100)

// Health check
if err := db.PingContext(ctx); err != nil {
    log.Fatal("database unreachable:", err)
}

// Driver name (useful for dialect-specific logic)
fmt.Println(db.DriverName()) // "postgres", "mysql", or "sqlite3"

🛡️ Security

Relica's primary defense against SQL injection is parameterized queries by design. All query builder methods pass values as parameters, never interpolated into SQL strings:

// Safe — values are always parameterized
db.Select().From("users").
    Where(relica.Eq("id", userInput)).
    One(&user)

// Safe — Model() API uses parameterized queries internally
db.Model(&user).Insert()
db.Model(&user).Update("status")

See Security Guide for more details.

📖 Documentation

Migration Guides

Switching from another library? We've got you covered:

• Migration from GORM - Complete guide for GORM users

  • ORM vs Query Builder philosophy
  • Side-by-side API comparisons
  • Association handling (Preload → JOIN)
  • Gradual migration strategies

• Migration from sqlx - Complete guide for sqlx users

  • Drop-in replacement patterns
  • Query builder advantages
  • Statement caching benefits
  • Using both together

Comprehensive User Guides

Getting Started:

• Getting Started Guide - Installation, first query, CRUD operations, common patterns
• Best Practices Guide - Repository pattern, error handling, testing strategies

Production:

• Production Deployment Guide - Configuration, health checks, Docker/Kubernetes, monitoring
• Performance Tuning Guide - Query optimization, connection pooling, caching strategies
• Troubleshooting Guide - Common errors and solutions

Advanced:

• Advanced Patterns Guide - Complex queries, CTEs, window functions, UPSERT

SQL Feature Guides

• Subquery Guide - IN, EXISTS, FROM, scalar subqueries with performance tips
• Set Operations Guide - UNION, INTERSECT, EXCEPT with database compatibility
• CTE Guide - WITH clauses, recursive CTEs for hierarchical data
• Window Functions Guide - Analytics with RANK(), ROW_NUMBER(), LAG(), LEAD()

Additional Resources

• Performance Comparison - Benchmarks vs GORM, sqlx, sqlc, database/sql
• API Reference

🧪 Testing

# Run unit tests
go test ./...

# Run with coverage
go test -cover ./...

# Run integration tests (requires Docker)
go test -tags=integration ./test/...

# Run benchmarks
go test -bench=. -benchmem ./benchmark/...

🎯 Design Philosophy

1. Zero Dependencies - Production code uses only Go standard library
2. Type Safety - Compile-time checks, runtime safety
3. Performance - Statement caching, batch operations, zero allocations in hot paths
4. Simplicity - Clean API, easy to learn, hard to misuse
5. Correctness - ACID transactions, proper error handling
6. Observability - Built-in metrics, context support for tracing

📊 Project Status

• Go Version: 1.25+
• Production Ready: Yes (beta)
• Test Coverage: 88.2%
• Dependencies: 0 (production), test-only: testify + DB drivers
• API: Stable public API, internal packages protected

🤝 Contributing

Contributions are welcome! Please read our Contributing Guide first.

📝 License

Relica is released under the MIT License.

🙏 Acknowledgments

• Successor to ozzo-dbx by Qiang Xue (Yii framework). Andrey Kolkov maintains both go-ozzo and coregx organizations
• Built with Go 1.25+ features
• Zero-dependency philosophy inspired by Go standard library

📞 Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Email: support@coregx.dev

✨ Special Thanks

Professor Ancha Baranova - This project would not have been possible without her invaluable help and support. Her assistance was crucial in bringing Relica to life.

---

Made with ❤️ by Andrey Kolkov and CoreGX contributors

Relica - Lightweight, Fast, Zero-Dependency Database Query Builder for Go

Star History