Database

The database module provides SQL database connectivity via Bun.sql (defaults to PostgreSQL), database introspection, and a full migration system. While PostgreSQL is the default and best-supported database, Bun.sql also supports other SQL databases like MySQL and SQLite.

Database connection

The Database class wraps Bun.sql and reads connection settings from config/database.ts:

Using a service provider (recommended):

import { DatabaseProvider } from '@strav/database'

app.use(new DatabaseProvider())

The DatabaseProvider registers Database as a singleton and closes the connection on shutdown. It depends on the config provider.

Or manually:

import { Database } from '@strav/database'

app.singleton(Database)
const db = app.resolve(Database)

Running queries

Import the sql tagged-template from @strav/database and use it directly:

import { sql } from '@strav/database'

// Parameterized queries (safe by default)
const rows = await sqlSELECT * FROM "user" WHERE "role" = ${'admin'}

// Dynamic queries (use with caution)
const rows = await sql.unsafe(
  'SELECT * FROM "user" WHERE "pid" = $1',
  [userId]
)

The sql export is a transparent proxy to the Database singleton's underlying Bun SQL connection. It's available after the Database is resolved through the DI container during bootstrap.

Configuration

Complete database configuration options in config/database.ts:

export default {
  // Connection settings
  host: env('DB_HOST', '127.0.0.1'),
  port: env.int('DB_PORT', 5432),
  username: env('DB_USER', 'postgres'),
  password: env('DB_PASSWORD', ''),
  database: env('DB_DATABASE', 'strav'),

  // Connection pool settings
  pool: env.int('DB_POOL_MAX', 10),           // max connections in pool
  idleTimeout: env.int('DB_IDLE_TIMEOUT', 20), // idle timeout in seconds

  // Multi-schema support (for multi-tenant applications)
  multiSchema: {
    enabled: env.bool('DB_MULTI_SCHEMA', false),
  },
}

Multi-schema support

Enable multi-schema mode for multi-tenant applications where each tenant has its own database schema:

// config/database.ts
export default {
  multiSchema: {
    enabled: true,
  },
}

When enabled, the database automatically sets search_path based on the current schema context. Queries run within a schema context automatically target the correct tenant schema:

import { withSchema } from '@strav/database'

// All database operations within this block target the 'tenant_123' schema
await withSchema('tenant_123', async () => {
  const users = await sqlSELECT * FROM "user"  // searches tenant_123.user
  const orders = await Order.all()  // ORM queries also respect schema context
})

Transactions

Never use raw BEGIN/COMMIT — Bun throws ERR_POSTGRES_UNSAFE_TRANSACTION with connection pooling.

`transaction()` helper (recommended)

The transaction() helper wraps a callback in a database transaction. It commits on success and rolls back on error:

import { transaction } from '@strav/database'

await transaction(async (trx) => {
  await trxINSERT INTO "order" ("user_id") VALUES (${userId})
  await trxUPDATE "inventory" SET "stock" = "stock" - 1 WHERE "product_id" = ${productId}
})

The trx handle can be passed to ORM methods (query(), create(), save(), delete(), forceDelete()) so they run inside the same transaction — see the ORM guide.

`sql.begin()` (low-level)

You can also use sql.begin() directly:

import { sql } from '@strav/database'

await sql.begin(async (tx) => {
  await txINSERT INTO "order" ("user_id") VALUES (${userId})
  await txUPDATE "inventory" SET "stock" = "stock" - 1 WHERE "product_id" = ${productId}
})

Closing the connection

await db.close()

Database introspection

The DatabaseIntrospector reads the live database schema and produces a DatabaseRepresentation:

import { DatabaseIntrospector } from '@strav/database'

const introspector = new DatabaseIntrospector(db)
const actual = await introspector.introspect()

// actual.tables  — Map of table definitions
// actual.enums   — Map of enum definitions

The introspector automatically excludes the _strav_migrations tracking table.

Migration system

Overview

The migration pipeline works in three stages:

Diff — SchemaDiffer compares the desired state (from schemas) against the actual state (from introspection) and produces a list of changes.

Generate SQL — SqlGenerator converts the diff into executable SQL statements (both up and down).

Write/Run — MigrationFileGenerator writes the SQL to disk; MigrationRunner executes them.

Migration file structure

Migrations are stored in database/migrations/ as timestamped directories:

database/migrations/
  20250115_120000_create_users/
    manifest.json          # metadata: version, message, steps
    01_enums/
      up.sql               # CREATE TYPE ...
      down.sql             # DROP TYPE ...
    02_tables/
      user/
        up.sql             # CREATE TABLE ...
        down.sql           # DROP TABLE ...
    03_constraints/
      up.sql               # ALTER TABLE ADD CONSTRAINT ...
      down.sql             # ALTER TABLE DROP CONSTRAINT ...
    04_indexes/
      up.sql               # CREATE INDEX ...
      down.sql             # DROP INDEX ...

Tracking

The MigrationTracker uses a _strav_migrations table to track which migrations have been applied. Migrations are grouped into batches — each migrate invocation creates a new batch.

CLI commands

All migration operations are available through the CLI:

Generate migration files from schema changes
bun strav generate:migration -m "add user roles"

Apply pending migrations
bun strav migrate

Roll back the last batch
bun strav rollback

Roll back a specific batch
bun strav rollback --batch 3

Compare schemas vs live database (read-only)
bun strav compare

Drop everything and rebuild (local env only, requires confirmation)
bun strav fresh

SchemaDiffer

Compares two DatabaseRepresentation objects and returns categorized changes:

import { SchemaDiffer } from '@strav/database'

const differ = new SchemaDiffer()
const diff = differ.diff(desired, actual)

// diff.enums    — enum changes (create, drop, modify)
// diff.tables   — table changes (create, drop, modify with column-level detail)
// diff.indexes  — index changes

SqlGenerator

Converts a diff into SQL:

import { SqlGenerator } from '@strav/database'

const generator = new SqlGenerator()
const statements = generator.generate(diff)

// statements.up   — SQL to apply the migration
// statements.down — SQL to revert the migration

Important: PostgreSQL serial columns (serial, smallserial, bigserial) are handled specially when using PostgreSQL — no NOT NULL or DEFAULT is emitted for them, as PostgreSQL manages this automatically.

Seeding

The Seeder base class provides a structured way to populate the database with dev/test data.

Creating seeders

Generate a seeder with the CLI:

bun strav generate:seeder DatabaseSeeder
bun strav generate:seeder UserSeeder

This creates files in database/seeders/:

import { Seeder } from '@strav/database'
import { UserFactory, PostFactory } from '../factories'

export default class DatabaseSeeder extends Seeder {
  async run(): Promise {
    await this.call(UserSeeder)
    await this.call(PostSeeder)
  }
}

Sub-seeders focus on a single model or concern:

import { Seeder } from '@strav/database'
import { UserFactory } from '../factories'

export default class UserSeeder extends Seeder {
  async run(): Promise {
    await UserFactory.createMany(10)
    await UserFactory.create({ name: 'Admin', role: 'admin' })
  }
}

Running seeders

Run the default seeder (database/seeders/database_seeder.ts)
bun strav seed

Run a specific seeder
bun strav seed --class UserSeeder

Drop everything, re-migrate, then seed (APP_ENV=local only)
bun strav seed --fresh

Factories

Seeders work with the Factory class from @strav/testing. Define factories in database/factories/ so both seeders and tests can share them:

database/
  factories/
    user_factory.ts
    post_factory.ts
    index.ts
  seeders/
    database_seeder.ts
    user_seeder.ts

// database/factories/user_factory.ts
import { Factory } from '@strav/testing'
import User from '../../app/models/user'

export const UserFactory = Factory.define(User, (seq) => ({
  pid: crypto.randomUUID(),
  name: User ${seq},
  email: user-${seq}@test.com,
  passwordHash: 'hashed',
}))

See the Testing guide for full factory API documentation.

Database

Database connection

Running queries

Configuration

Multi-schema support

Transactions

transaction() helper (recommended)

sql.begin() (low-level)

Closing the connection

Database introspection

Migration system

Overview

Migration file structure

Tracking

CLI commands

Generate migration files from schema changes

Apply pending migrations

Roll back the last batch

Roll back a specific batch

Compare schemas vs live database (read-only)

Drop everything and rebuild (local env only, requires confirmation)