Documentation

Database

Overview

The framework provides a flexible database layer supporting both SQLite (for development) and PostgreSQL (for production) through a unified interface.

Database Configuration

SQLite Setup (Default)

# Environment variables
DB_DRIVER=sqlite3
DB_PATH=data/db.db
DB_MIGRATION_PATH=data/schema.sqlite.sql

PostgreSQL Setup

# Environment variables
DB_DRIVER=postgres
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp
DB_USER=postgres
DB_PASS=password
DB_SSLMODE=disable
DB_MIGRATION_PATH=data/schema.pg.sql

Schema Definition

SQLite Schema

-- data/schema.sqlite.sql
CREATE TABLE IF NOT EXISTS gardens (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    name TEXT NOT NULL,
    description TEXT,
    location TEXT,
    user_id TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS plants (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    name TEXT NOT NULL,
    species TEXT NOT NULL,
    garden_id TEXT NOT NULL,
    height REAL DEFAULT 0.0,
    edible BOOLEAN DEFAULT FALSE,
    planted_at DATETIME,
    harvested_at DATETIME,
    
    FOREIGN KEY (garden_id) REFERENCES gardens(id) ON DELETE CASCADE
);

CREATE INDEX idx_plants_garden_id ON plants(garden_id);
CREATE INDEX idx_gardens_user_id ON gardens(user_id);

PostgreSQL Schema

-- data/schema.pg.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS gardens (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    location VARCHAR(255),
    user_id UUID NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    
    CONSTRAINT fk_gardens_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS plants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    name VARCHAR(100) NOT NULL,
    species VARCHAR(100) NOT NULL,
    garden_id UUID NOT NULL,
    height DECIMAL(5,2) DEFAULT 0.0,
    edible BOOLEAN DEFAULT FALSE,
    planted_at TIMESTAMP WITH TIME ZONE,
    harvested_at TIMESTAMP WITH TIME ZONE,
    
    CONSTRAINT fk_plants_garden FOREIGN KEY (garden_id) REFERENCES gardens(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_plants_garden_id ON plants(garden_id);
CREATE INDEX IF NOT EXISTS idx_gardens_user_id ON gardens(user_id);

Database Operations

Basic Queries with Fetchers

// Simple queries
gardens, err := gardenFetcher.FindAll(ctx, nil)

// Filtered queries
mod := gardenFetcher.Mod()
mod.ExactStringValueFilter("user_id", "123")
mod.ExactBooleanValueFilter("active", true)
gardens, err := gardenFetcher.FindSet(ctx, mod)

// Pagination
mod.Page = 1
mod.PerPage = 10
gardens, pagination, err := gardenFetcher.FindPage(ctx, mod)

// Single record
garden, exists, err := gardenFetcher.FindOneById(ctx, "garden123", nil)

Filtering Options

mod := gardenFetcher.Mod()

// String filters
mod.ExactStringValueFilter("name", "My Garden")
mod.ContainsStringValueFilter("description", "vegetables")
mod.UnequalStringValueFilter("status", "deleted")

// Numeric filters
mod.ExactIntegerValueFilter("plant_count", 5)

// Boolean filters
mod.ExactBooleanValueFilter("active", true)

// NULL checks
mod.AbsentValueFilter("deleted_at")     // IS NULL
mod.PresentValueFilter("harvested_at")  // IS NOT NULL

// IN clauses
mod.InStringValuesFilter("status", []string{"active", "maintenance"})
mod.NotInStringValuesFilter("type", []string{"temporary", "test"})

Ordering and Sorting

mod := gardenFetcher.Mod()

// Single ordering
mod.DescOrder("created_at")  // Most recent first
mod.AscOrder("name")         // Alphabetical

// Multiple ordering
mod.DescOrder("active")      // Active gardens first
mod.AscOrder("name")         // Then alphabetical

CRUD Operations with Handlers

Creating Records

garden := &mdl.Garden{
    Name:        "My New Garden",
    Description: "A beautiful garden",
    UserId:      "user123",
    Active:      true,
}

createdGarden, err := gardenHandler.Create(ctx, garden, nil)
if err != nil {
    return err
}
// createdGarden.Id is now set

Updating Records

// Load existing garden
garden, exists, err := gardenFetcher.FindOneById(ctx, "garden123", nil)
if err != nil || !exists {
    return errors.New("garden not found")
}

// Modify fields
garden.Name = "Updated Name"
garden.Description = "Updated description"

// Save changes
updatedGarden, err := gardenHandler.Update(ctx, garden, nil)

// Or update specific fields only
handlerMod := &handler.HandlerMod{
    Fields: []string{"Name", "Description"},
}
updatedGarden, err := gardenHandler.Update(ctx, garden, handlerMod)

Deleting Records

// Load garden to delete
garden, exists, err := gardenFetcher.FindOneById(ctx, "garden123", nil)
if err != nil || !exists {
    return errors.New("garden not found")
}

// Delete the garden
err = gardenHandler.Delete(ctx, garden, nil)
if err != nil {
    return err
}

Transactions

Transaction Management

func (this *ComplexGardenMae) Execute(ctx context.Context, input *ComplexGardenMaeInput) error {
    // Begin transaction
    tx, err := this.SqlDb.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback() // Auto-rollback if not committed
    
    // All operations use the same transaction
    handlerMod := &handler.HandlerMod{Tx: tx}
    
    // Create garden
    garden, err := this.GardenHandler.Create(ctx, gardenEntity, handlerMod)
    if err != nil {
        return err // Transaction will rollback
    }
    
    // Create plants
    for _, plantData := range input.Plants {
        plant := &mdl.Plant{
            Name:     plantData.Name,
            Species:  plantData.Species,
            GardenId: garden.Id,
        }
        
        _, err := this.PlantHandler.Create(ctx, plant, handlerMod)
        if err != nil {
            return err // Transaction will rollback
        }
    }
    
    // Commit transaction
    return tx.Commit()
}

Relationships

// Load garden with related data
garden, exists, err := gardenFetcher.FindOneById(ctx, "garden123", nil)
if err != nil || !exists {
    return err
}

// Hydrate relationships using presets
err = gardenHydrator.OneViaPreset(ctx, garden, "show", nil)
// garden.Plants and garden.User are now populated

// Or hydrate specific relationships
hydratorMod := &hydrator.HydratorMod{}
hydratorMod.AddHydratingPath("plants")
hydratorMod.AddHydratingPath("garden_tasks", "assignee")
err = gardenHydrator.One(ctx, garden, hydratorMod)

Batch Loading (Preventing N+1 Queries)

// Load multiple gardens
gardens, err := gardenFetcher.FindAll(ctx, nil)
if err != nil {
    return err
}

// Batch load all relationships at once
err = gardenHydrator.ManyViaPreset(ctx, gardens, "list", nil)
// All gardens now have their relationships loaded efficiently

Database Utilities

Raw Queries (When Needed)

func (this *CustomGardenFetcher) FindGardensWithMostPlants(ctx context.Context, limit int) ([]*mdl.Garden, error) {
    query := `
        SELECT g.id, g.name, g.description, g.created_at, g.updated_at,
               COUNT(p.id) as plant_count
        FROM gardens g
        LEFT JOIN plants p ON g.id = p.garden_id
        WHERE g.active = TRUE
        GROUP BY g.id, g.name, g.description, g.created_at, g.updated_at
        ORDER BY plant_count DESC
        LIMIT ?
    `
    
    rows, err := this.Fetcher.Query(ctx, query, limit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var gardens []*mdl.Garden
    for rows.Next() {
        eso := &tables.GardenEso{}
        var plantCount int
        
        err := rows.Scan(
            &eso.Id, &eso.Name, &eso.Description, 
            &eso.CreatedAt, &eso.UpdatedAt, &plantCount,
        )
        if err != nil {
            return nil, err
        }
        
        garden := this.GardenTable.EsoToEntity(eso)
        gardens = append(gardens, garden)
    }
    
    return gardens, nil
}

Database Migrations

// Simple migration system
func runMigrations(db *sql.DB, schemaPath string) error {
    // Read schema file
    schemaSQL, err := os.ReadFile(schemaPath)
    if err != nil {
        return err
    }
    
    // Execute schema
    _, err = db.Exec(string(schemaSQL))
    return err
}

Performance Optimization

Indexing

-- Add indexes for common queries
CREATE INDEX idx_gardens_user_active ON gardens(user_id, active);
CREATE INDEX idx_plants_garden_species ON plants(garden_id, species);
CREATE INDEX idx_garden_tasks_due_date ON garden_tasks(due_date) WHERE completed = FALSE;

Query Optimization

// Use specific field selection when possible
mod := gardenFetcher.Mod()
// Load only needed relationships
hydratorMod := &hydrator.HydratorMod{}
hydratorMod.AddHydratingPath("user") // Only load user, not plants

// Use pagination for large datasets
mod.Page = 1
mod.PerPage = 25
gardens, pagination, err := gardenFetcher.FindPage(ctx, mod)

Connection Pooling

func setupDatabase(config *config.Config) *sql.DB {
    db, err := sql.Open(config.Database.Driver, config.Database.URL)
    if err != nil {
        log.Fatal(err)
    }
    
    // Configure connection pool
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
    
    return db
}

The database layer provides a powerful yet simple interface for data persistence, supporting both SQLite for development and PostgreSQL for production while maintaining consistent APIs across all database operations.

Forms
Frontend