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{}.

✨ Features

Zero Production Dependencies - Uses only Go standard library
High Performance - LRU statement cache, batch operations (3.3x faster)
Type-Safe - Reflection-based struct scanning with compile-time checks
Model() API - ORM-style CRUD with auto-populated IDs, composite PKs (ozzo-dbx compatible)
NullStringMap - Dynamic scanning without predefined structs
Named Placeholders - {:name} syntax with Bind(Params{}) 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
Enterprise Security - SQL injection prevention, audit logging, compliance
Batch Operations - Efficient multi-row INSERT and UPDATE
JOIN Operations - INNER, LEFT, RIGHT, FULL, CROSS JOIN support
Sorting & Pagination - ORDER BY, LIMIT, OFFSET, DISTINCT
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 - 1500+ test cases, 88%+ coverage
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)

    // INSERT with Model() API (PREFERRED)
    newUser := User{Name: "Alice", Email: "[email protected]"}
    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: "[email protected]"}
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()

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

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 (prefix/suffix)
db.Select().From("files").
    Where(relica.Like("filename", ".txt").Match(false, true)).  // filename LIKE '%.txt'
    All(&files)

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

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.

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: "[email protected]"}
err := db.Model(&user).Insert()
fmt.Println(user.ID) // 1 (auto-populated!)

// INSERT - Selective fields
user := User{Name: "Bob", Email: "[email protected]", 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:  "[email protected]",
    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:

Field tagged db:"pk" — legacy single PK (maps to column "pk")
Field tagged db:"column,pk" — explicit PK with column name
Field named ID (no tag) — auto-detected
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

tx, err := db.Begin(ctx)
defer tx.Rollback()

user := User{Name: "Alice"}
err = tx.Model(&user).Insert()

if err == nil {
    tx.Commit()
}

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: "[email protected]"}
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).

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.

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", "[email protected]")).
    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

Database-agnostic error helpers — work with PostgreSQL, MySQL, and SQLite:

_, 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
}

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)).All(&users)

// Find users with at least one order (EXISTS is often faster)
orderCheck := db.Select("1").From("orders").Where("orders.user_id = users.id")
db.Select().From("users").Where(relica.Exists(orderCheck)).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)

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", "1 as level").
    From("employees").
    Where("manager_id IS NULL")

// Recursive: children
recursive := db.Select("e.id", "e.name", "e.manager_id", "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().
    SelectExpr("user_id", "country", "total",
        "RANK() OVER (PARTITION BY country ORDER BY total DESC) as rank").
    From("orders").
    All(&rankedOrders)

// Running totals with frame specification
db.Select().
    SelectExpr("date", "amount",
        "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()

Batch Operations

Batch INSERT (3.3x faster than individual inserts):

result, err := db.BatchInsert("users", []string{"name", "email"}).
    Values("Alice", "[email protected]").
    Values("Bob", "[email protected]").
    Values("Charlie", "[email protected]").
    Execute()

// Or from a slice
users := []User{
    {Name: "Alice", Email: "[email protected]"},
    {Name: "Bob", Email: "[email protected]"},
}

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

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
tx, _ := db.BeginTx(ctx, nil)
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),
)

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)

🛡️ Enterprise Security

Relica provides enterprise-grade security features for protecting your database operations:

SQL Injection Prevention

Pattern-based detection of OWASP Top 10 SQL injection attacks with <2% overhead.

Note: Security features (WithValidator, WithAuditLog) use internal types from internal/security. See Security Guide for integration instructions.

Relica's primary defense against SQL injection is the use of parameterized queries (placeholders ?). 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")

Detected attack vectors:

Tautology attacks (1 OR 1=1)
Comment injection (admin'--)
Stacked queries (; DROP TABLE)
UNION attacks
Command execution (xp_cmdshell, exec())
Information schema access
Timing attacks (pg_sleep, benchmark)

Audit Logging

Comprehensive operation tracking for GDPR, HIPAA, PCI-DSS, SOC2 compliance.

Note: Audit logging uses internal types. See Security Guide for integration instructions.

Audit log includes:

Timestamp, user, client IP, request ID
Operation (INSERT, UPDATE, DELETE, SELECT)
Query execution time
Success/failure status
Parameter hashing (NOT raw values) for GDPR compliance

Security Guides

Security Guide - Complete security features overview
Security Testing Guide - OWASP-based testing examples

📖 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

Zero Dependencies - Production code uses only Go standard library
Type Safety - Compile-time checks, runtime safety
Performance - Statement caching, batch operations, zero allocations in hot paths
Simplicity - Clean API, easy to learn, hard to misuse
Correctness - ACID transactions, proper error handling
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

Inspired by ozzo-dbx
Built with Go 1.25+ features
Zero-dependency philosophy inspired by Go standard library

📞 Support

Issues: GitHub Issues
Discussions: GitHub Discussions
Email: [email protected]

✨ 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

📚 relica - Awesome Go Library for Database

Detailed Description of relica

Relica

✨ Features

📌 API Usage Priority

🚀 Quick Start

Installation

Basic Usage

📚 Core Features

CRUD Operations

Model() API (PREFERRED)

SELECT with Expression API (PREFERRED)

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

Expression API

HashExp - Simple Conditions

Comparison Operators

IN and BETWEEN

LIKE with Automatic Escaping

Logical Combinators

Named Placeholders

Positional Placeholders

Model() API

Basic CRUD Operations

Auto-Populate ID

Selective Fields

Primary Key Detection

Transactions

Advanced Usage

Upsert (INSERT or UPDATE)

UpdateChanged (Dirty Field Detection)

JOIN Operations

Sorting and Pagination

Aggregate Functions

Query Helpers

Exists

Count

ToSQL (Query Preview)

Error Handling

ErrNotFound

Error Classification

Advanced SQL Features

Subqueries

Set Operations

Common Table Expressions (CTEs)

Window Functions

Transactions

Batch Operations

Context Support

🏗️ Database Support

⚡ Performance

Statement Cache

Batch Operations Performance

🔧 Configuration

Connection Management

Standard Connection

Wrap Existing Connection

🛡️ Enterprise Security

SQL Injection Prevention

Audit Logging

Security Guides

📖 Documentation

Migration Guides

Comprehensive User Guides

SQL Feature Guides

Additional Resources

🧪 Testing

🎯 Design Philosophy

📊 Project Status

🤝 Contributing

📝 License

🙏 Acknowledgments

📞 Support

✨ Special Thanks