systemprompt-database

PostgreSQL database layer with SQLx, connection pooling, and migrations

On this page

PostgreSQL database layer providing connection pooling, migrations, and compile-time verified queries via SQLx.

Overview

The database crate provides the persistence layer for all SystemPrompt services. It abstracts database operations while maintaining type safety through SQLx's compile-time query verification.

Layer Position

Infrastructure Layer
├── systemprompt-database  ← You are here
├── systemprompt-logging
├── systemprompt-config
└── ...

Depends on: systemprompt-models, systemprompt-config Used by: All domain crates

Key Features

  • SQLx integration - Compile-time query verification
  • Connection pooling - Configurable pool with health monitoring
  • Migrations - Embedded migrations with rollback support
  • Multi-tenant - Row-level security for tenant isolation
  • Multiple backends - PostgreSQL (primary), SQLite, MySQL

Configuration

# services/config/database.yaml or profile
database:
  type: postgres
  host: localhost
  port: 5432
  database: systemprompt
  username: systemprompt
  # password from secrets
  pool:
    max_connections: 10
    min_connections: 2
    connection_timeout_seconds: 30
    idle_timeout_seconds: 600

Public API

DatabasePool

The main entry point for database operations:

use systemprompt_database::{DatabasePool, DatabaseConfig};

// Connect to database
let config = DatabaseConfig::from_profile(&profile)?;
let pool = DatabasePool::connect(&config).await?;

// Get connection from pool
let conn = pool.acquire().await?;

// Check health
let healthy = pool.is_healthy().await;

Query Execution

Use SQLx macros for compile-time verified queries:

// Fetch one
let user = sqlx::query_as!(User,
    "SELECT * FROM users WHERE id = $1",
    user_id
)
.fetch_one(&*pool)
.await?;

// Fetch optional
let user = sqlx::query_as!(User,
    "SELECT * FROM users WHERE email = $1",
    email
)
.fetch_optional(&*pool)
.await?;

// Fetch all
let users = sqlx::query_as!(User,
    "SELECT * FROM users WHERE tenant_id = $1",
    tenant_id
)
.fetch_all(&*pool)
.await?;

Transactions

// Start transaction
let mut tx = pool.begin().await?;

// Execute operations
sqlx::query!("INSERT INTO users (name) VALUES ($1)", name)
    .execute(&mut *tx)
    .await?;

sqlx::query!("INSERT INTO audit_log (action) VALUES ($1)", "user_created")
    .execute(&mut *tx)
    .await?;

// Commit (or rollback on drop)
tx.commit().await?;

Migrations

use systemprompt_database::migrations;

// Run all pending migrations
migrations::run(&pool).await?;

// Check migration status
let status = migrations::status(&pool).await?;
for migration in status {
    println!("{}: {}", migration.version, migration.name);
}

SQL File Convention

All SQL must be in .sql files, never inline strings:

crates/domain/users/
├── schema/
│   ├── users.sql          # Table definition
│   └── migrations/
│       ├── 001_init.sql
│       └── 002_add_roles.sql

Example schema file:

-- schema/users.sql
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    email TEXT NOT NULL,
    name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(tenant_id, email)
);

CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_users_email ON users(tenant_id, email);

Offline Mode

For CI/CD without database access, use SQLx offline mode:

# Generate query cache
cargo sqlx prepare --workspace

# Build with offline mode
SQLX_OFFLINE=true cargo build

The .sqlx/ directory contains cached query metadata.