Database Layer

Type-safe SQLite operations using Go generics.

Design Principles

Principle	Rationale
IDs are strings	UUIDs, never integers
PascalCase SQL	`WHERE UserID = ?` matches Go struct fields
N+1 is acceptable	SQLite reads from memory (nanoseconds)
Cache denormalized data	Avoid JOINs by storing related fields
Idempotent operations	All creates safe to retry
Zero-value defaults	Use `cmp.Or(value, "default")`

Database Engines

The engines package provides multiple database backends:

Engine	Use Case	Configuration
`NewAuto()`	Automatic selection based on environment	`DB_URL`, `DB_TOKEN`, `DB_PATH`
`NewMemory()`	Testing, development	None
`NewLocal(path)`	Single-server production	File path
`NewRemote(path, url, token)`	Multi-server with replicas	Turso/libSQL server

Auto Engine (Recommended)

NewAuto() selects the appropriate engine based on environment variables:

// models/db.go
var DB = engines.NewAuto()

Environment Variables:

Variable	Description
`DB_URL`	Remote libSQL server URL (e.g., `libsql://mydb.turso.io`)
`DB_TOKEN`	Authentication token for remote server
`DB_PATH`	Local database file path

Selection Logic:

DB_URL + DB_TOKEN set → Remote replica (syncs with Turso/libSQL)
Only DB_PATH set → Local file database
Neither set → In-memory database

Examples:

# Production (Turso)
DB_URL=libsql://mydb.turso.io
DB_TOKEN=eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9...
DB_PATH=/data/replica.db  # optional local replica path

# Development (local file)
DB_PATH=/data/app.db

# Testing (in-memory)
# No environment variables needed

Core Types

Model

Base type embedded in all entities. Provides ID, CreatedAt, UpdatedAt.

Collection[E]

Generic repository created via database.Manage(db, new(Entity)).

Methods:

Method	Returns	Description
`Get(id)`	`(*E, error)`	Find by ID
`First(where, args...)`	`(*E, error)`	Single result matching query
`Search(where, args...)`	`([]*E, error)`	Multiple results
`All()`	`([]*E, error)`	Return all records
`Insert(entity)`	`(string, error)`	Create new record (generates UUID, returns ID)
`Update(entity)`	`error`	Save changes (updates timestamp)
`Delete(entity)`	`error`	Remove record
`Count(where, args...)`	`int`	Count matching records (returns 0 on error)

Index Options:

Indexes are defined as options when creating collections with Manage():

Users = database.Manage(DB, new(User),
    database.WithUniqueIndex[User]("Email"),
)

Pages = database.Manage(DB, new(Page),
    database.WithIndex[Page]("ParentID"),
    database.WithIndex[Page]("Published"),
)

// Composite index on multiple columns
ACLRules = database.Manage(DB, new(ACLRule),
    database.WithIndex[ACLRule]("SubjectType", "SubjectID"),
)

Option	Description
`WithUniqueIndex[E](column)`	Unique constraint on single column
`WithIndex[E](columns...)`	Non-unique index (single or composite)

Query Patterns

Search and First accept any SQL clauses after the table name. Offload logic to the database - SQLite is fast and expressive.

Supported clauses: JOIN, WHERE, ORDER BY, LIMIT, OFFSET, GROUP BY, HAVING

// Simple query
Sites.Search("WHERE UserID = ? AND Status = ?", userID, "active")

// Full SQL power - JOINs, ordering, pagination
Sites.Search(`
    JOIN Domain ON Domain.SiteID = Site.ID
    WHERE Site.UserID = ? AND Domain.Verified = 1
    ORDER BY Site.CreatedAt DESC
    LIMIT ? OFFSET ?
`, userID, limit, offset)

// Aggregation
Sites.First("WHERE UserID = ? ORDER BY CreatedAt DESC LIMIT 1", userID)

Table names match struct names (Site, Domain, User). Column names match field names in PascalCase.

Security: SQL Injection Prevention

CRITICAL: Always use parameterized queries with ? placeholders. Never concatenate user input into SQL strings.

// CORRECT - parameterized query (safe)
Sites.Search("WHERE Name = ? AND UserID = ?", userInput, userID)

// WRONG - string concatenation (SQL injection vulnerability!)
Sites.Search("WHERE Name = '" + userInput + "'")  // NEVER DO THIS

// WRONG - fmt.Sprintf with user input (also vulnerable!)
Sites.Search(fmt.Sprintf("WHERE Name = '%s'", userInput))  // NEVER DO THIS

The ? placeholder ensures user input is properly escaped and can never be interpreted as SQL commands. This applies to all query methods: Search, First, Count, Delete.

Even for "internal" code, use parameterized queries. User input can flow through many layers before reaching your database call.

Caching Strategy

Store frequently-accessed related data directly on models:

Model	Cached Fields
Site	`OwnerHandle`, `OwnerName`, `OwnerAvatar`
Deploy	`SiteName`, `SiteDomain`

When source data changes, update all cached copies via direct SQL.

Remote Database (LibSQL)

For distributed deployments:

Primary database serves as LibSQL server
Application instances use embedded replicas
Reads are local (nanoseconds), writes go to primary
Call DB.Sync() after cross-server operations (e.g., OAuth redirects)

Schema Management

Collections auto-migrate when initialized via Manage():

Tables created if they don't exist
Missing columns added automatically
No manual migration files needed for development

// This automatically creates the Post table with all fields
Posts := database.Manage(DB, new(Post))

// Adding a new field to the struct? Just restart - column is added automatically
type Post struct {
    database.Model
    Title   string
    Content string
    Views   int  // New field - added on next restart
}

For production schema changes requiring data transformation, use raw SQL:

DB.Exec("ALTER TABLE Post ADD COLUMN Slug TEXT")
DB.Exec("UPDATE Post SET Slug = lower(replace(Title, ' ', '-'))")

Errors

database.ErrNotFound - Record doesn't exist
database.ErrDuplicate - Unique constraint violation

Zero-Value Defaults

Use cmp.Or (Go 1.21+) for zero-value defaults instead of if statements:

// Preferred
port := cmp.Or(cfg.Port, "5000")
region := cmp.Or(os.Getenv("REGION"), "sfo")

// Avoid
if cfg.Port == "" {
    cfg.Port = "5000"
}

Works with any comparable type - returns first non-zero value.