relica

About relica

This large README is shown as a plain-text preview. Read the full README at the source

# Relica

[![CI](https://github.com/coregx/relica/actions/workflows/test.yml/badge.svg?branch=main)](https://github.com/coregx/relica/actions/workflows/test.yml)
[![codecov](https://codecov.io/gh/coregx/relica/graph/badge.svg)](https://codecov.io/gh/coregx/relica)
[![Go Version](https://img.shields.io/badge/Go-1.25%2B-00ADD8?style=flat&logo=go)](https://go.dev/)
[![Go Report Card](https://goreportcard.com/badge/github.com/coregx/relica)](https://goreportcard.com/report/github.com/coregx/relica)
[![License](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)
[![Release](https://img.shields.io/github/v/release/coregx/relica?include_prereleases&style=flat)](https://github.com/coregx/relica/releases)
[![Go Reference](https://pkg.go.dev/badge/github.com/coregx/relica.svg)](https://pkg.go.dev/github.com/coregx/relica)

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

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

## ✨ 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](CHANGELOG.md) for version history and [GitHub Releases](https://github.com/coregx/relica/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](AGENTS.md) for complete patterns and examples.

## 🚀 Quick Start

### Installation

```bash
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

```go
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: "[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:**

```go
// 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)

```go
// 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).

```go
// 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

```go
// 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

```go
// 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

```go
// 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

```go
// 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

```go
// 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

```go
// 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:

```go
// 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](https://github.com/go-ozzo/ozzo-dbx), Relica's predecessor.

#### Positional Placeholders

Positional `?` parameters also work (backward compatible):

```go
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

```go
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):

```go
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:

```go
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:
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

```go
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

```go
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](AGENTS.md#transactions) for the rules.

#### Advanced Usage

```go
// 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)

```go
// 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)

```go
// 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).

```go
// 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](docs/guides/ADVANCED_PATTERNS.md) for comprehensive JOIN examples and best practices.

### Sorting and Pagination

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

```go
// 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

```go
// 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).

```go
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)

Frequently Asked Questions