Database Guide

Varel provides a powerful yet simple interface for working with PostgreSQL databases. This guide covers everything from basic connections to advanced transactions and migrations.

Table of Contents

• Getting Started
• Configuration
• Single-Process Architecture
• Database Connections
• Basic Queries
• Query Builders
• Transactions
• Migrations
• Models
• Best Practices

Getting Started

Note: Database imports:

import leafscale.varel.vareldb  // Database module
import leafscale.varel.http      // For Context and Response

Why PostgreSQL?

Varel uses PostgreSQL as its primary database:

• Mature and Reliable - 30+ years of development
• Feature Rich - JSON, arrays, full-text search, geospatial
• ACID Compliant - Strong data integrity guarantees
• Open Source - Free and community-driven
• Excellent V Support - V has built-in PostgreSQL driver

Installing PostgreSQL

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

macOS:

brew install postgresql
brew services start postgresql

Create Database:

sudo -u postgres psql
postgres=# CREATE DATABASE varel_app;
postgres=# CREATE USER varel WITH PASSWORD 'your_password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE varel_app TO varel;
postgres=# \q

Configuration

Database Config File

Create config/config.toml:

[database]
host = "localhost"
port = 5432
database = "varel_app"
user = "varel"
password = "your_password"
sslmode = "prefer"           # disable, allow, prefer, require, verify-ca, verify-full
connect_timeout = 30         # Connection attempt timeout in seconds

# Note: v0.4.0 uses single-process architecture (no connection pooling)
# Single process creates one database connection (lazy initialization)

Loading Configuration

import varel
import vareldb
import toml
import os

fn load_db_config() varel.DBConfig {
    // Load from TOML file
    config_file := os.read_file('config/config.toml') or {
        panic('Failed to read database config')
    }

    doc := toml.parse_text(config_file) or {
        panic('Failed to parse database config')
    }

    return varel.DBConfig{
        host: doc.value('database.host').string()
        port: doc.value('database.port').int()
        database: doc.value('database.database').string()
        user: doc.value('database.user').string()
        password: doc.value('database.password').string()
        sslmode: doc.value('database.sslmode').string()
        connect_timeout: doc.value('database.connect_timeout').int()
    }
}

fn main() {
    mut app := varel.new('My App')

    config := load_db_config()
    app.database(config)

    // Single process creates connection automatically on first ctx.db call
    app.listen(':8080')
}

Environment Variables

Override config with environment variables:

fn load_db_config() varel.DBConfig {
    return varel.DBConfig{
        host: os.getenv('DB_HOST') or { 'localhost' }
        port: os.getenv('DB_PORT').int() or { 5432 }
        database: os.getenv('DB_NAME') or { 'varel_app' }
        user: os.getenv('DB_USER') or { 'varel' }
        password: os.getenv('DB_PASSWORD') or { '' }
        sslmode: os.getenv('DB_SSLMODE') or { 'prefer' }
        connect_timeout: os.getenv('DB_CONNECT_TIMEOUT').int() or { 30 }
    }
}

# Set environment variables
export DB_HOST=localhost
export DB_PORT=5432
export DB_NAME=varel_production
export DB_USER=varel
export DB_PASSWORD=secure_password
export DB_SSLMODE=require

# Run application
v run main.v

Single-Process Architecture

How Varel Handles Database Connections (v0.4.0)

Varel v0.4.0 uses a single-process architecture with async I/O:

• One Process - Single process handles all requests
• Picoev Event Loop - Async I/O with epoll (Linux) for concurrency
• One Database Connection - Created lazily on first ctx.db call
• No Connection Pooling - Simpler architecture, one connection per process

This architecture eliminates the need for connection pooling:

┌─────────────────────────────────┐
│  Varel Single Process (v0.4.0)  │
│  - Picoev event loop (epoll)    │
│  - Async I/O handles concurrency│
│  - 1 database connection        │
│  - Binds to port :8080          │
└─────────────────────────────────┘
             │
             ▼
    ┌────────────────────┐
    │  PostgreSQL        │
    │  1 connection total│
    └────────────────────┘

Why No Connection Pooling?

1. Simplicity - One connection = simpler code, no pool management
2. Performance - No connection acquisition overhead, async I/O handles concurrency
3. Sufficient - One persistent connection serves thousands of requests per second
4. Compatibility - Works WITH V's current maturity level

Database Connections

The single process creates one connection when first needed via ctx.db:

import varel
import vareldb

fn main() {
    mut app := varel.new('My App')

    // Configure database (connection created lazily)
    app.database(varel.DBConfig{
        host: 'localhost'
        port: 5432
        database: 'varel_app'
        user: 'varel'
        password: os.getenv('DB_PASSWORD') or { '' }
        sslmode: 'prefer'
        connect_timeout: 30  // Only timeout setting - no pool config
    })

    // Process creates connection automatically on first ctx.db call
    app.listen(':8080')
}

Using with Controllers

Controllers get database via ctx.db (connection created lazily):

// controllers/products.v
module controllers

import varel
import vareldb

pub struct ProductsController {}

pub fn (c ProductsController) index(mut ctx varel.Context) varel.Response {
    // Get database connection (created on first call, reused thereafter)
    mut db := ctx.db!

    // Execute query
    rows := db.exec('SELECT * FROM products ORDER BY created_at DESC')!

    mut products := []Product{}
    for row in rows {
        products << Product{
            id: vareldb.to_int(row.vals[0])!
            name: vareldb.to_string(row.vals[1], '')
            price: vareldb.to_f64(row.vals[2])!
        }
    }

    return ctx.render('products/index', {'products': products})
}

Key Points:

• No database instance stored in controller struct
• Controllers are stateless (get DB from context each request)
• Connection created once per process, reused for all requests
• Total connections = 1 per application instance (v0.4.0)

Basic Queries

Execute Query

// Execute query without results
rows_affected := database.exec('DELETE FROM sessions WHERE expires_at < NOW()')!

println('Deleted ${rows_affected} expired sessions')

Execute with Parameters

Always use parameterized queries to prevent SQL injection:

// ✅ GOOD - Parameterized query
user_id := 123
rows := database.exec_params('SELECT * FROM users WHERE id = $1', [user_id.str()])!

// ❌ BAD - SQL injection vulnerability!
rows := database.exec('SELECT * FROM users WHERE id = ${user_id}')!

Query Single Row

row := database.exec_one('SELECT * FROM users WHERE id = $1', [user_id.str()]) or {
    return error('User not found')
}

name := row.get_string('name')
email := row.get_string('email')

Query Multiple Rows

rows := database.exec_params('SELECT * FROM products WHERE price > $1', ['100'])!

for row in rows {
    id := row.get_int('id')
    name := row.get_string('name')
    price := row.get_f64('price')

    println('Product ${id}: ${name} - $${price}')
}

Type Helpers

row := database.exec_one('SELECT * FROM users WHERE id = $1', [id.str()])!

// String
name := row.get_string('name')

// Integer
age := row.get_int('age')
user_id := row.get_i64('user_id')

// Float
price := row.get_f64('price')

// Boolean
is_active := row.get_bool('is_active')

// Nullable values
email := row.get_string('email') or { '' }

// Check if NULL
if row.is_null('deleted_at') {
    println('Not deleted')
}

Query Builders

Simple Queries

// SELECT * FROM users WHERE active = true
users := database.query('users')
    .where('active = $1', ['true'])
    .all()!

// SELECT * FROM products WHERE price > 100 ORDER BY created_at DESC LIMIT 10
products := database.query('products')
    .where('price > $1', ['100'])
    .order_by('created_at', 'desc')
    .limit(10)
    .all()!

Complex Queries

// Multiple conditions
products := database.query('products')
    .where('category_id = $1', [category_id.str()])
    .where('price BETWEEN $1 AND $2', ['10', '100'])
    .where('stock > $1', ['0'])
    .order_by('name', 'asc')
    .limit(20)
    .offset(page * 20)
    .all()!

// JOIN queries
orders := database.query('orders o')
    .select('o.*, u.name as user_name, u.email as user_email')
    .join('INNER JOIN users u ON o.user_id = u.id')
    .where('o.status = $1', ['pending'])
    .order_by('o.created_at', 'desc')
    .all()!

// Aggregates
result := database.query('products')
    .select('COUNT(*) as total, AVG(price) as avg_price')
    .where('category_id = $1', [category_id.str()])
    .one()!

total := result.get_int('total')
avg_price := result.get_f64('avg_price')

Raw SQL

For complex queries, use raw SQL:

sql := '
SELECT
    p.*,
    c.name as category_name,
    COUNT(r.id) as review_count,
    AVG(r.rating) as avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.published = true
GROUP BY p.id, c.name
HAVING AVG(r.rating) >= $1
ORDER BY avg_rating DESC
LIMIT $2
'

products := database.exec_params(sql, ['4.0', '10'])!

for product in products {
    name := product.get_string('name')
    category := product.get_string('category_name')
    rating := product.get_f64('avg_rating')
    reviews := product.get_int('review_count')

    println('${name} (${category}): ${rating} stars from ${reviews} reviews')
}

Transactions

What Are Transactions?

Transactions ensure atomicity - either all operations succeed or all fail:

// Without transaction - DANGER!
database.exec('INSERT INTO orders (user_id, total) VALUES ($1, $2)', [user_id, total])!
database.exec('INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)', [...])!
database.exec('UPDATE products SET stock = stock - $1 WHERE id = $2', [...])!
// If this fails, previous inserts are permanent! Inconsistent state!

// With transaction - SAFE!
database.transaction(fn (mut tx db.Transaction) ! {
    tx.exec('INSERT INTO orders ...')!
    tx.exec('INSERT INTO order_items ...')!
    tx.exec('UPDATE products ...')!
    // If any fails, ALL are rolled back automatically
})!

Basic Transaction

database.transaction(fn (mut tx db.Transaction) ! {
    // All operations in transaction
    tx.exec('INSERT INTO users (name, email) VALUES ($1, $2)', ['Alice', 'alice@example.com'])!

    user_id := tx.query_int('SELECT id FROM users WHERE email = $1', ['alice@example.com'])!

    tx.exec('INSERT INTO profiles (user_id, bio) VALUES ($1, $2)', [user_id.str(), 'Hello!'])!

    // Commit happens automatically if no error
})!

Transaction with Error Handling

database.transaction(fn (mut tx db.Transaction) ! {
    // Create order
    order_result := tx.exec_one('
        INSERT INTO orders (user_id, total, status)
        VALUES ($1, $2, $3)
        RETURNING id
    ', [user_id.str(), total.str(), 'pending'])!

    order_id := order_result.get_int('id')

    // Add order items
    for item in cart_items {
        // Check stock
        stock_row := tx.exec_one('SELECT stock FROM products WHERE id = $1', [item.product_id.str()])!
        stock := stock_row.get_int('stock')

        if stock < item.quantity {
            // Return error - transaction will rollback
            return error('Insufficient stock for product ${item.product_id}')
        }

        // Insert order item
        tx.exec('
            INSERT INTO order_items (order_id, product_id, quantity, price)
            VALUES ($1, $2, $3, $4)
        ', [order_id.str(), item.product_id.str(), item.quantity.str(), item.price.str()])!

        // Update stock
        tx.exec('
            UPDATE products
            SET stock = stock - $1
            WHERE id = $2
        ', [item.quantity.str(), item.product_id.str()])!
    }

    // All succeeded - transaction commits
})!

Nested Transactions (Savepoints)

database.transaction(fn (mut tx db.Transaction) ! {
    tx.exec('INSERT INTO orders (user_id) VALUES ($1)', [user_id])!

    // Savepoint
    tx.savepoint('before_items')!

    // Try to add items
    for item in items {
        tx.exec('INSERT INTO order_items ...', [...]) or {
            // Rollback to savepoint (keep order, discard items)
            tx.rollback_to('before_items')!
            break
        }
    }

    // Continue transaction
    tx.exec('UPDATE orders SET status = $1', ['pending'])!
})!

Migrations

What Are Migrations?

Migrations are version-controlled database schema changes:

• Track schema history - See how database evolved
• Reproducible - Same schema across dev/staging/production
• Reversible - Roll back changes if needed
• Collaborative - Team shares schema changes via git

Creating a Migration

# Generate a new migration with the Varel CLI
varel generate migration create_products

# Output:
# Created: db/migrations/20250113120000_create_products.up.sql
# Created: db/migrations/20250113120000_create_products.down.sql

Creates two files:

db/migrations/20250113120000_create_products.up.sql:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    stock INTEGER DEFAULT 0,
    category_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_created_at ON products(created_at);

db/migrations/20250113120000_create_products.down.sql:

DROP TABLE IF EXISTS products;

Running Migrations

# Run all pending migrations
varel db migrate

# Output:
# Running migrations...
#   ✓ 20250113120000_create_products.up.sql
#   ✓ 20250113121000_create_reviews.up.sql
# Migrations complete!

Rolling Back

# Rollback the last migration
varel db rollback

# Output:
# Rolling back last migration...
#   ✓ 20250113121000_create_reviews.down.sql
# Rollback complete!

Migration Status

# Check migration status
varel db status

# Output:
# Migration Status:
#   [x] 20250113120000_create_products.up.sql (applied)
#   [x] 20250113121000_create_reviews.up.sql (applied)
#   [ ] 20250113122000_add_featured_to_products.up.sql (pending)
#
# Applied: 2
# Pending: 1

Other Database Commands

# Create database (from config/config.toml)
varel db create

# Drop database (careful!)
varel db drop

# Reset database (drop + create + migrate)
varel db reset

Common Migration Patterns

Add Column:

-- up
ALTER TABLE products ADD COLUMN featured BOOLEAN DEFAULT false;
CREATE INDEX idx_products_featured ON products(featured);

-- down
DROP INDEX IF EXISTS idx_products_featured;
ALTER TABLE products DROP COLUMN featured;

Add Foreign Key:

-- up
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

-- down
ALTER TABLE orders DROP CONSTRAINT fk_orders_user_id;

Add Unique Constraint:

-- up
ALTER TABLE users ADD CONSTRAINT unique_users_email UNIQUE (email);

-- down
ALTER TABLE users DROP CONSTRAINT unique_users_email;

Rename Column:

-- up
ALTER TABLE products RENAME COLUMN price TO unit_price;

-- down
ALTER TABLE products RENAME COLUMN unit_price TO price;

Data Migration:

-- up
UPDATE products SET category_id = 1 WHERE category_id IS NULL;
ALTER TABLE products ALTER COLUMN category_id SET NOT NULL;

-- down
ALTER TABLE products ALTER COLUMN category_id DROP NOT NULL;

Models

What Are Models?

Models represent database tables as V structs:

// models/product.v
module models

import leafscale.varel.vareldb as db

pub struct Product {
pub mut:
    id          int
    name        string
    price       f64
    description string
    stock       int
    category_id int
    created_at  string
    updated_at  string
}

CRUD Operations

// models/product.v

// Create
pub fn Product.create(db db.Database, product Product) !Product {
    result := db.exec_one('
        INSERT INTO products (name, price, description, stock, category_id)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
    ', [product.name, product.price.str(), product.description, product.stock.str(), product.category_id.str()])!

    return Product{
        id: result.get_int('id')
        name: result.get_string('name')
        price: result.get_f64('price')
        description: result.get_string('description')
        stock: result.get_int('stock')
        category_id: result.get_int('category_id')
        created_at: result.get_string('created_at')
        updated_at: result.get_string('updated_at')
    }
}

// Find by ID
pub fn Product.find(db db.Database, id int) !Product {
    row := db.exec_one('SELECT * FROM products WHERE id = $1', [id.str()])!

    return Product{
        id: row.get_int('id')
        name: row.get_string('name')
        price: row.get_f64('price')
        description: row.get_string('description')
        stock: row.get_int('stock')
        category_id: row.get_int('category_id')
        created_at: row.get_string('created_at')
        updated_at: row.get_string('updated_at')
    }
}

// Find all
pub fn Product.all(db db.Database) ![]Product {
    rows := db.exec('SELECT * FROM products ORDER BY created_at DESC')!

    mut products := []Product{}
    for row in rows {
        products << Product{
            id: row.get_int('id')
            name: row.get_string('name')
            price: row.get_f64('price')
            description: row.get_string('description')
            stock: row.get_int('stock')
            category_id: row.get_int('category_id')
            created_at: row.get_string('created_at')
            updated_at: row.get_string('updated_at')
        }
    }

    return products
}

// Update
pub fn Product.update(db db.Database, product Product) ! {
    db.exec('
        UPDATE products
        SET name = $1, price = $2, description = $3, stock = $4, category_id = $5, updated_at = NOW()
        WHERE id = $6
    ', [product.name, product.price.str(), product.description, product.stock.str(), product.category_id.str(), product.id.str()])!
}

// Delete
pub fn Product.delete(db db.Database, id int) ! {
    db.exec('DELETE FROM products WHERE id = $1', [id.str()])!
}

Query Methods

// Find by condition
pub fn Product.where(db db.Database, condition string, params []string) ![]Product {
    rows := db.exec_params('SELECT * FROM products WHERE ${condition}', params)!

    mut products := []Product{}
    for row in rows {
        products << row_to_product(row)
    }

    return products
}

// Pagination
pub fn Product.paginate(db db.Database, page int, per_page int) ![]Product {
    offset := (page - 1) * per_page

    rows := db.exec_params('
        SELECT * FROM products
        ORDER BY created_at DESC
        LIMIT $1 OFFSET $2
    ', [per_page.str(), offset.str()])!

    mut products := []Product{}
    for row in rows {
        products << row_to_product(row)
    }

    return products
}

// Count
pub fn Product.count(db db.Database) !int {
    row := db.exec_one('SELECT COUNT(*) as total FROM products')!
    return row.get_int('total')
}

// Helper to convert row to struct
fn row_to_product(row db.Row) Product {
    return Product{
        id: row.get_int('id')
        name: row.get_string('name')
        price: row.get_f64('price')
        description: row.get_string('description')
        stock: row.get_int('stock')
        category_id: row.get_int('category_id')
        created_at: row.get_string('created_at')
        updated_at: row.get_string('updated_at')
    }
}

Associations

// models/product.v

// Belongs to category
pub fn (p Product) category(db db.Database) !Category {
    return Category.find(db, p.category_id)
}

// Has many reviews
pub fn (p Product) reviews(db db.Database) ![]Review {
    return Review.for_product(db, p.id)
}

// models/review.v

// Belongs to product
pub fn (r Review) product(db db.Database) !Product {
    return Product.find(db, r.product_id)
}

// Scope: for product
pub fn Review.for_product(db db.Database, product_id int) ![]Review {
    return Review.where(db, 'product_id = $1', [product_id.str()])
}

Best Practices

1. Always Use Parameterized Queries

// ✅ GOOD - Safe from SQL injection
email := ctx.form('email')
user := db.exec_one('SELECT * FROM users WHERE email = $1', [email])!

// ❌ BAD - SQL injection vulnerability!
user := db.exec('SELECT * FROM users WHERE email = \'${email}\'')!

2. Use Transactions for Multiple Writes

// ✅ GOOD - Atomic operation
db.transaction(fn (mut tx db.Transaction) ! {
    tx.exec('INSERT INTO orders ...')!
    tx.exec('INSERT INTO order_items ...')!
    tx.exec('UPDATE products ...')!
})!

// ❌ BAD - Not atomic, can leave inconsistent state
db.exec('INSERT INTO orders ...')!
db.exec('INSERT INTO order_items ...')!  // If this fails, order exists but has no items!
db.exec('UPDATE products ...')!

3. Add Indexes for Queries

-- Add indexes for columns used in WHERE, ORDER BY, JOIN
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_created_at ON products(created_at);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Composite indexes for multiple columns
CREATE INDEX idx_products_category_published ON products(category_id, published);

4. Use Constraints

-- Primary keys
PRIMARY KEY (id)

-- Foreign keys
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE

-- Unique constraints
UNIQUE (email)

-- Check constraints
CHECK (price >= 0)
CHECK (stock >= 0)

-- Not null constraints
NOT NULL

5. Handle Null Values

// Check if null
if row.is_null('deleted_at') {
    println('Not deleted')
}

// Get with default
email := row.get_string('email') or { 'no-email@example.com' }

// Optional field
deleted_at := if row.is_null('deleted_at') {
    none
} else {
    row.get_string('deleted_at')
}

Summary

You've learned:

✅ PostgreSQL setup and configuration ✅ Single-process architecture with one database connection (v0.4.0) ✅ Basic queries with parameterization ✅ Query builders for complex queries ✅ Transactions for data integrity ✅ Migrations for schema management ✅ Models and CRUD operations ✅ Best practices for security and performance

Continue to the Templates Guide to learn about rendering views with VeeMarker!