Skip to content

GETTING_STARTED.md

Latest commit

 

History

History
842 lines (624 loc) · 17.6 KB

GETTING_STARTED.md

File metadata and controls

842 lines (624 loc) · 17.6 KB
Raw

Getting Started with Relica

Quick Start Guide - From Zero to Production in 15 Minutes

🤖 AI Agents: See AGENTS.md for correct API patterns before generating code.

📋 What is Relica?

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

Key Features:

  • 🚀 Zero dependencies (only Go standard library)
  • ⚡ High performance (LRU statement cache, batch operations)
  • 🎯 Type-safe (compile-time checks, runtime safety)
  • 🔒 ACID transactions with all isolation levels
  • 🌐 Multi-database (PostgreSQL, MySQL, SQLite)
  • 📝 Clean fluent API

What Relica is NOT:

  • ❌ Not a full ORM (no auto-migrations, no model associations)
  • ❌ Not a schema migration tool (use golang-migrate, goose, etc.)
  • ❌ Not a replacement for SQL (it's a query builder)

When to use Relica:

  • ✅ You want explicit control over queries
  • ✅ Performance is critical
  • ✅ You prefer SQL-like syntax
  • ✅ You want zero dependencies

🚀 Installation

Step 1: Install Relica

go get github.com/coregx/relica

Step 2: Install Database Driver

Choose your database:

PostgreSQL:

go get github.com/lib/pq

MySQL:

go get github.com/go-sql-driver/mysql

SQLite:

go get modernc.org/sqlite

Step 3: Verify Installation

go mod tidy
go mod verify

💻 Your First Query

Basic Setup

Create main.go:

package main

import (
    "context"
    "fmt"
    "log"

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

func main() {
    // 1. Connect to database
    db, err := relica.Open("postgres",
        "postgres://user:password@localhost:5432/mydb?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 2. Define your struct
    type User struct {
        ID    int    `db:"id"`
        Name  string `db:"name"`
        Email string `db:"email"`
    }

    ctx := context.Background()

    // 3. Query data
    var users []User
    err = db.Select().
        From("users").
        Where("age > ?", 18).
        All(&users)

    if err != nil {
        log.Fatal(err)
    }

    // 4. Use the data
    for _, user := range users {
        fmt.Printf("User: %s (%s)\n", user.Name, user.Email)
    }
}

Run It

go run main.go

Congratulations! You just ran your first Relica query.

📚 Core Concepts

1. Database Connection

Open a new connection:

db, err := relica.Open("postgres", dsn)
defer db.Close()

Wrap existing connection:

sqlDB, _ := sql.Open("postgres", dsn)
db := relica.WrapDB(sqlDB, "postgres")

Configure connection pool:

db, err := relica.Open("postgres", dsn,
    relica.WithMaxOpenConns(25),
    relica.WithMaxIdleConns(5),
    relica.WithConnMaxLifetime(300), // 5 minutes
)

2. Struct Mapping

Use db tags to map columns:

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

Important:

  • ✅ Fields WITH db tags will be scanned
  • ❌ Fields WITHOUT db tags will be ignored

3. Query Building

Fluent API:

// Direct API (recommended)
db.Select().From("users").All(&users)

Chaining methods:

db.Select("id", "name", "email").
    From("users").
    Where("status = ?", "active").
    OrderBy("created_at DESC").
    Limit(10).
    All(&users)

🔍 CRUD Operations

SELECT - Query Multiple Rows

var users []User
err := db.Select().
    From("users").
    Where("age > ?", 18).
    All(&users)

SELECT - Query Single Row

var user User

// Expression API (PREFERRED)
err := db.Select().From("users").
    Where(relica.Eq("id", 1)).
    One(&user)

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

// Positional placeholders
err = db.Select().From("users").
    Where("id = ?", 1).
    One(&user)

INSERT (PREFERRED: Model API)

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

// Alternative: Map-based (use only for dynamic data)
result, err := db.Insert("users", map[string]interface{}{
    "name":  "Alice",
    "email": "alice@example.com",
    "age":   30,
}).Execute()

UPDATE (PREFERRED: Model API)

// PREFERRED: Model() API - auto WHERE by primary key
user.Name = "Alice Updated"
user.Email = "alice.new@example.com"
err := db.Model(&user).Update()

// Alternative: Map-based with explicit WHERE
result, err := db.Update("users").
    Set(map[string]interface{}{
        "name":  "Alice Updated",
        "email": "alice.new@example.com",
    }).
    Where("id = ?", 1).
    Execute()

DELETE (PREFERRED: Model API)

// PREFERRED: Model() API - auto WHERE by primary key
err := db.Model(&user).Delete()

// Alternative: Explicit WHERE
result, err := db.Delete("users").
    Where("id = ?", 1).
    Execute()

🏗️ Struct-Based Operations

Type-safe struct operations that eliminate manual map construction.

InsertStruct - Single Struct Insert

Insert a struct directly without building a map:

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

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

result, err := db.InsertStruct("users", &user).Execute()

Benefits:

  • ✅ Type-safe at compile time
  • ✅ No manual map construction
  • ✅ Uses db tags automatically
  • ✅ Works with db:"-" to exclude fields

BatchInsertStruct - Batch Insert

Insert multiple structs in a single query:

users := []User{
    {Name: "Alice", Email: "alice@example.com", Age: 30},
    {Name: "Bob", Email: "bob@example.com", Age: 25},
    {Name: "Carol", Email: "carol@example.com", Age: 28},
}

result, err := db.BatchInsertStruct("users", users).Execute()

Performance: Same as map-based BatchInsert() - significantly faster than individual inserts.

UpdateStruct - Struct-Based Update

Update using a struct with explicit WHERE clause:

user := User{
    Name:  "Alice Updated",
    Email: "alice.new@example.com",
    Age:   31,
}

result, err := db.UpdateStruct("users", &user).
    Where("id = ?", 1).
    Execute()

Model() API - Domain Models

For domain models that map directly to tables, use the Model() API for even less boilerplate:

INSERT with Auto Table Name

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

// Implement TableName() interface
func (User) TableName() string {
    return "users"
}

// INSERT - auto-detects table name + auto-populates ID
user := User{Name: "Alice", Email: "alice@example.com", Age: 30}
err := db.Model(&user).Insert()
// SQL: INSERT INTO users (name, email, age) VALUES (?, ?, ?)
fmt.Println(user.ID) // 1 (auto-populated!)

✨ Auto-Populate ID (v0.7.0+)

Primary key is automatically populated after INSERT across all databases:

user := User{Name: "Bob"}
err := db.Model(&user).Insert()
fmt.Println(user.ID) // Auto-generated ID (works with PostgreSQL, MySQL, SQLite)

// PostgreSQL: Uses RETURNING clause
// MySQL/SQLite: Uses LastInsertId()

Supports all numeric types (int, int8, int16, int32, int64, uint*) and pointer variants.

UPDATE with Auto WHERE

Auto-generates WHERE clause using primary key:

user := User{ID: 1, Name: "Alice Updated", Email: "alice.new@example.com"}
err := db.Model(&user).Update()
// SQL: UPDATE users SET name=?, email=?, age=? WHERE id=?

Primary key detection:

  • Looks for db:"id" tag
  • Falls back to field named ID

DELETE with Auto WHERE

user := User{ID: 1}
err := db.Model(&user).Delete()
// SQL: DELETE FROM users WHERE id=?

Field Control

✨ Selective Fields (v0.7.0+)

Control exactly which fields are inserted or updated:

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

// INSERT - Only name and email (status remains default)
err := db.Model(&user).Insert("name", "email")

// UPDATE - Only update status (name and email unchanged)
user.Status = "active"
err := db.Model(&user).Update("status")

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

// Exclude auto-managed timestamps
err := db.Model(&user).Exclude("created_at", "updated_at").Insert()

Empty args = all fields (backward compatible)

Table Override

Override the table name dynamically:

// Insert into archive table
err := db.Model(&user).Table("users_archive").Insert()

When to Use Each Approach?

Feature InsertStruct() Model().Insert()
Table name Manual Auto-detected
Primary key Manual WHERE Auto WHERE
Best for Ad-hoc data, DTOs Domain models
Boilerplate More explicit Minimal
Control Full control Convention-based

Use InsertStruct() when:

  • Working with ad-hoc data structures
  • Need explicit WHERE clause control
  • Batch operations without PK logic

Use Model() when:

  • Working with domain models (structs map to tables)
  • Need automatic WHERE by primary key
  • Want minimal boilerplate for CRUD

🎯 Common Patterns

Pattern 1: Dynamic Filters

Build queries conditionally:

func searchUsers(db *relica.DB, name string, minAge int) ([]User, error) {
    qb := db.Select().From("users")

    if name != "" {
        qb = qb.Where("name LIKE ?", "%"+name+"%")
    }

    if minAge > 0 {
        qb = qb.Where("age >= ?", minAge)
    }

    var users []User
    err := qb.All(&users)
    return users, err
}

Pattern 2: Pagination

func getUsers(db *relica.DB, page, pageSize int) ([]User, error) {
    offset := (page - 1) * pageSize

    var users []User
    err := db.Select().
        From("users").
        OrderBy("id ASC").
        Limit(pageSize).
        Offset(offset).
        All(&users)

    return users, err
}

// Usage
users, err := getUsers(db, 1, 20) // Page 1, 20 users per page

Pattern 3: Check Existence

// Check if record exists without fetching data
func userExists(db *relica.DB, email string) (bool, error) {
    return db.Select().From("users").
        Where(relica.Eq("email", email)).
        Exists()
}

Pattern 4: Count Rows

// Count matching rows — returns int64
func countActiveUsers(db *relica.DB) (int64, error) {
    return db.Select().From("users").
        Where(relica.Eq("status", "active")).
        Count()
}

Pattern 5: Bulk Insert

func bulkInsertUsers(db *relica.DB, users []User) error {
    batch := db.BatchInsert("users", []string{"name", "email", "age"})

    for _, user := range users {
        batch.Values(user.Name, user.Email, user.Age)
    }

    _, err := batch.Execute()
    return err
}

🔒 Transactions

Basic Transaction

func transferMoney(db *relica.DB, fromID, toID int, amount float64) error {
    tx, err := db.Begin(context.Background())
    if err != nil {
        return err
    }
    defer tx.Rollback() // Auto-rollback if not committed

    // Deduct from sender
    _, err = tx.Update("accounts").
        Set(map[string]interface{}{"balance": "balance - ?"}).
        Where("id = ?", fromID).
        Execute()
    if err != nil {
        return err
    }

    // Add to receiver
    _, err = tx.Update("accounts").
        Set(map[string]interface{}{"balance": "balance + ?"}).
        Where("id = ?", toID).
        Execute()
    if err != nil {
        return err
    }

    // Commit transaction
    return tx.Commit()
}

Transaction with Isolation Level

tx, err := db.BeginTx(ctx, &relica.TxOptions{
    Isolation: sql.LevelSerializable,
})

🌐 Multi-Database Support

PostgreSQL

db, err := relica.Open("postgres",
    "postgres://user:pass@localhost:5432/mydb?sslmode=disable")

Placeholders: $1, $2, $3 Identifiers: "users", "table_name" UPSERT: ON CONFLICT

MySQL

db, err := relica.Open("mysql",
    "user:pass@tcp(localhost:3306)/mydb?parseTime=true")

Placeholders: ?, ?, ? Identifiers: `users`, `table_name` UPSERT: ON DUPLICATE KEY UPDATE

SQLite

db, err := relica.Open("sqlite", "./mydb.db")

Placeholders: ?, ?, ? Identifiers: "users", "table_name" UPSERT: ON CONFLICT

Note: Relica automatically converts ? to the correct placeholder for your database.

⚡ Performance Tips

Tip 1: Use Statement Cache (Automatic)

Relica caches prepared statements automatically:

// First call: prepares statement
db.Select().From("users").Where("id = ?", 1).One(&user)

// Subsequent calls: uses cached statement (<60ns lookup)
db.Select().From("users").Where("id = ?", 2).One(&user)

Tip 2: Batch Operations

Replace loops with batch operations:

// ❌ Slow (N queries)
for _, user := range users {
    db.Insert("users", map[string]interface{}{
        "name":  user.Name,
        "email": user.Email,
    }).Execute()
}

// ✅ Fast (1 query, 3.3x faster)
batch := db.BatchInsert("users", []string{"name", "email"})
for _, user := range users {
    batch.Values(user.Name, user.Email)
}
batch.Execute()

Tip 3: Use Context for Timeouts

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

err := db.Select().
    From("users").
    WithContext(ctx).
    All(&users)

Tip 4: Connection Pooling

db, err := relica.Open("postgres", dsn,
    relica.WithMaxOpenConns(25),     // Max concurrent connections
    relica.WithMaxIdleConns(5),      // Idle connections in pool
    relica.WithConnMaxLifetime(300), // 5 minutes
)

🚨 Common Mistakes

Mistake 1: Missing db Tags

// ❌ WRONG: No db tags
type User struct {
    ID    int
    Name  string
    Email string
}

// ✅ CORRECT: db tags present
type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

Mistake 2: Forgetting defer Close()

// ❌ WRONG: Connection leak
db, err := relica.Open("postgres", dsn)
// ... use db

// ✅ CORRECT: Always close
db, err := relica.Open("postgres", dsn)
defer db.Close()

Mistake 3: Using sql.ErrNoRows Directly

Starting with v0.11.0, One() returns relica.ErrNotFound (which wraps sql.ErrNoRows). Use errors.Is to check for either:

import "errors"

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

// ✅ CORRECT: works with both relica.ErrNotFound and sql.ErrNoRows
if errors.Is(err, relica.ErrNotFound) {
    // user not found — return 404 or default value
    return nil, ErrUserNotFound
}
if err != nil {
    return nil, fmt.Errorf("query failed: %w", err)
}

Mistake 4: Not Checking Errors

// ❌ WRONG: Ignoring errors
db.Select().From("users").All(&users)

// ✅ CORRECT: Check errors
err := db.Select().From("users").All(&users)
if err != nil {
    log.Fatal(err)
}

Mistake 5: Using LastInsertId() on PostgreSQL

// ❌ WRONG: PostgreSQL doesn't support LastInsertId with lib/pq
result, _ := db.Insert("users", data).Execute()
id, _ := result.LastInsertId() // ERROR!

// ✅ CORRECT: Use RETURNING clause
var id int
db.QueryRowContext(ctx,
    `INSERT INTO users (name) VALUES ($1) RETURNING id`,
    "Alice",
).Scan(&id)

🔧 Troubleshooting

Error: "no such table"

Problem: Table doesn't exist in database.

Solution:

  1. Create table manually or use migration tool
  2. Verify database connection (check DSN)
  3. Check table name spelling

Error: "sql: Scan error on column index X"

Problem: Struct field type doesn't match database column type.

Solution:

  1. Verify db tags match column names
  2. Check field types (int vs string, etc.)
  3. Use sql.NullString, sql.NullInt64 for nullable columns

Error: "pq: invalid input syntax for type integer"

Problem: Passing wrong type to placeholder.

Solution:

  1. Verify placeholder values match expected types
  2. Convert types before passing: strconv.Atoi(), etc.

📖 Next Steps

Learn More

  1. Best Practices Guide - Production-ready patterns
  2. Advanced Patterns Guide - Complex queries
  3. Performance Tuning Guide - Optimization tips
  4. Security Guide - SQL injection prevention, audit logging

Explore Features

Community

🎉 You're Ready!

You now know the basics of Relica:

  • ✅ Installation and setup
  • ✅ CRUD operations
  • ✅ Query building
  • ✅ Transactions
  • ✅ Common patterns
  • ✅ Performance tips

Start building! Relica is designed to be simple, fast, and safe.

For issues or questions, see GitHub Issues