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: "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()

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