Database

FORGE uses PostgreSQL 15+ as the sole database engine, leveraging its advanced features like UUIDs, JSONB columns, and full-text search. The generated project includes a complete migration and seeder system that keeps your schema versioned and your development data reproducible.

Connection Configuration

Database connectivity is configured through environment variables:

# .env
DATABASE_URL=postgres://forge_user:forge_pass@localhost:5432/forge_db
DATABASE_MAX_CONNECTIONS=10
DATABASE_MIN_CONNECTIONS=2

The connection pool is created at startup using SQLx:

use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;

pub async fn create_pool(database_url: &str) -> PgPool {
    PgPoolOptions::new()
        .max_connections(10)
        .min_connections(2)
        .acquire_timeout(std::time::Duration::from_secs(5))
        .idle_timeout(std::time::Duration::from_secs(600))
        .connect(database_url)
        .await
        .expect("Failed to create database pool")
}

TIP

SQLx validates your SQL queries at compile time against the actual database schema. Run cargo sqlx prepare to generate offline query data for CI builds where a database is not available.

Schema Conventions

Every table in a FORGE-generated project follows these conventions:

Primary Keys

All tables use UUID primary keys generated by PostgreSQL:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- ...
);

Timestamps

Every table includes created_at and updated_at columns:

CREATE TABLE users (
    -- ...
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Soft Deletes

Tables that support soft deletion include a deleted_at column:

CREATE TABLE users (
    -- ...
    deleted_at TIMESTAMPTZ
);

WARNING

When querying soft-deletable tables, always include WHERE deleted_at IS NULL to exclude deleted records. The generated services handle this automatically, but be aware of it when writing custom queries.

JSONB Columns

Translatable content and flexible metadata use JSONB:

CREATE TABLE contents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug VARCHAR(255) NOT NULL UNIQUE,
    translations JSONB NOT NULL DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    -- ...
);

JSONB translations follow this structure:

{
  "en": {
    "title": "Welcome",
    "body": "Welcome to our site"
  },
  "ar": {
    "title": "مرحبا",
    "body": "مرحبا بكم في موقعنا"
  }
}

Migrations

Migrations live in database/migrations/ and are numbered sequentially:

database/migrations/
├── 00001_create_users_table.sql
├── 00002_create_roles_table.sql
├── 00003_create_permissions_table.sql
├── 00004_create_role_user_table.sql
├── 00005_create_permission_role_table.sql
├── 00006_create_settings_table.sql
├── 00007_create_password_resets_table.sql
├── 00008_create_otp_codes_table.sql
├── 00009_create_media_table.sql
├── 00010_create_audit_logs_table.sql
├── 00011_create_translations_table.sql
├── 00012_create_pages_table.sql
├── 00013_create_faqs_table.sql
├── 00014_create_contents_table.sql
├── 00015_create_menus_table.sql
├── 00016_migrate_media_polymorphic.sql
└── 00017_create_lookups_table.sql

Running Migrations

Migrations run automatically on application startup:

sqlx::migrate!("./database/migrations")
    .run(&pool)
    .await
    .expect("Failed to run migrations");

Writing a New Migration

Create a new SQL file with the next sequence number:

-- database/migrations/00018_create_products_table.sql
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    translations JSONB NOT NULL DEFAULT '{}',
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

-- Index on slug for fast lookups
CREATE INDEX idx_products_slug ON products(slug);

-- Index for active products
CREATE INDEX idx_products_active ON products(is_active) WHERE deleted_at IS NULL;

TIP

Use partial indexes (WHERE deleted_at IS NULL) to keep indexes small and efficient. PostgreSQL will use these indexes for queries that include the same WHERE clause.

Seeders

Seeders populate the database with initial data required for the application to function:

database/seeders/
├── 01_roles.sql           # Default roles (admin, user)
├── 02_permissions.sql     # All permissions
├── 03_admin_user.sql      # Default admin account
├── 04_settings.sql        # Application settings
├── 05_pages.sql           # Default pages
└── 06_translations.sql    # UI translations

Example seeder for the admin user:

-- database/seeders/03_admin_user.sql
INSERT INTO users (name, email, password, email_verified_at, is_active)
VALUES (
    'Admin',
    'admin@forge.dev',
    -- Argon2 hash of 'password'
    '$argon2id$v=19$m=19456,t=2,p=1$...',
    now(),
    true
)
ON CONFLICT (email) DO NOTHING;

-- Assign admin role
INSERT INTO role_user (user_id, role_id)
SELECT u.id, r.id
FROM users u
CROSS JOIN roles r
WHERE u.email = 'admin@forge.dev'
AND r.name = 'admin'
ON CONFLICT DO NOTHING;

Indexing Strategy

FORGE generates indexes for common query patterns:

-- Foreign key indexes (always indexed)
CREATE INDEX idx_role_user_user_id ON role_user(user_id);
CREATE INDEX idx_role_user_role_id ON role_user(role_id);

-- Unique constraints that serve as indexes
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_mobile ON users(mobile) WHERE mobile IS NOT NULL;

-- Slug lookups
CREATE UNIQUE INDEX idx_contents_slug ON contents(slug);

-- Soft delete filtering
CREATE INDEX idx_users_active ON users(is_active) WHERE deleted_at IS NULL;

-- JSONB indexes for translations
CREATE INDEX idx_contents_translations ON contents USING GIN(translations);

-- Timestamp ordering
CREATE INDEX idx_contents_created_at ON contents(created_at DESC);

Querying with Translations

Access translated fields using PostgreSQL's JSONB operators:

-- Get the Arabic title
SELECT translations->'ar'->>'title' AS title_ar
FROM contents
WHERE slug = 'about-us';

-- Search across translations
SELECT *
FROM contents
WHERE translations->'en'->>'title' ILIKE '%welcome%';

-- Get all translations for a locale
SELECT translations->'en' AS en_translation
FROM contents
WHERE deleted_at IS NULL;

In Rust with SQLx:

// Query with specific locale extraction
let contents = sqlx::query_as!(
    ContentWithTranslation,
    r#"
    SELECT
        id,
        slug,
        translations->$1->>'title' AS "title?",
        translations->$1->>'body' AS "body?",
        created_at
    FROM contents
    WHERE deleted_at IS NULL
    ORDER BY created_at DESC
    "#,
    locale
)
.fetch_all(&pool)
.await?;

Connection Pooling

The generated application uses SQLx's built-in connection pool. Key settings:

Setting	Default	Description
max_connections	10	Maximum number of connections in the pool
min_connections	2	Minimum idle connections maintained
acquire_timeout	5s	Max wait time to acquire a connection
idle_timeout	600s	Close idle connections after this duration

WARNING

Set max_connections based on your PostgreSQL max_connections setting divided by the number of application instances. For example, if PostgreSQL allows 100 connections and you run 5 instances, set max_connections to 15-18, leaving headroom for admin connections.

See Also

• Models — Rust structs that map to database tables
• Services — Business logic that queries the database
• Error Handling — Database error mapping