Database

Telaio's database layer wraps postgres.js and Kysely into a pair of factory functions. The builder's .withDatabase() method is a convenience wrapper around the same factories -- you can also call them directly when you need a connection before the builder is assembled (see better-auth chicken-and-egg).

createPool()

import { createPool } from 'telaio/db';

// Config-style: reads DATABASE_URL and DATABASE_SSL from your loaded config
const pool = await createPool(config);

// Direct options
const pool = await createPool({
  connectionString: process.env.DATABASE_URL,
  ssl: true,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

createPool accepts either a raw PoolOptions object or your loaded config record. When passed a config object it reads the DATABASE_URL, DATABASE_SSL, and DATABASE_POOL_MAX keys automatically.

Auto-SSL for AWS RDS: if the connection string contains rds.amazonaws.com, SSL is enabled automatically even if you do not set ssl: true.

PoolOptions

createDatabase<DB>()

import { createDatabase } from 'telaio/db';

const db = await createDatabase<DB>(pool);

createDatabase wraps the postgres.js Sql instance in a Kysely instance using PostgresJSDialect (from kysely-postgres-js). The DB generic is the database interface you generate with kysely-codegen or write by hand.

CamelCasePlugin is enabled by default. Database columns are snake_case; Kysely exposes them as camelCase in TypeScript.

Disabling CamelCasePlugin

If your database columns are already camelCase (or you want raw column names), disable the plugin:

// Via code
const db = await createDatabase<DB>(pool, { camelCase: false });

// Via builder (reads DATABASE_CAMEL_CASE from config)
const app = await createApp({ config })
  .withDatabase({ databaseOptions: { camelCase: false } })
  .buildApi();

Or set the DATABASE_CAMEL_CASE environment variable to false. The env var applies globally to the builder, CLI migrate commands, and db:types. Code-level camelCase in databaseOptions always takes precedence over the env var.

Additional plugins

import { createDatabase } from 'telaio/db';
import { DeduplicateJoinsPlugin } from 'kysely';

const db = await createDatabase<DB>(pool, {
  plugins: [new DeduplicateJoinsPlugin()],
});

When CamelCasePlugin is enabled (the default), any plugins you pass are appended after it.

Builder integration

const app = await createApp({ config })
  .withDatabase()       // pool + db created from config
  .buildApi();

app.pool  // postgres.Sql
app.db    // Kysely<unknown> -- cast to Kysely<DB> as needed

You can pass pre-built instances to skip internal construction:

const app = await createApp({ config })
  .withDatabase({ pool, db })
  .buildApi();

Migrations

Import migration utilities from telaio/db/migrations:

import {
  createMigrator,
  createMigrationFile,
  migrateToLatest,
  migrateUp,
  migrateDown,
  runFrameworkMigrations,
} from 'telaio/db/migrations';

Telaio stores its internal migration state in _telaio_migrations. Your app migrations use a separate table (Kysely defaults to kysely_migration). The two tables never conflict.

migrateToLatest runs framework migrations first automatically — you do not need to call runFrameworkMigrations before it.

Custom schema and table names

Pass migrationTableSchema, migrationTableName, and migrationLockTableName in MigratorOptions to override defaults:

await migrateToLatest({
  db,
  migrationsDir: 'src/db/migrations',
  migrationTableSchema: 'app',
  migrationTableName: 'my_migrations',
  migrationLockTableName: 'my_migrations_lock',
});

Or set the corresponding environment variables (DATABASE_MIGRATION_SCHEMA, DATABASE_MIGRATION_TABLE, DATABASE_MIGRATION_LOCK_TABLE) in your config. See CLI Migrations for details.

Dynamic filters (applyFilter)

applyFilter translates a JSON filter object into Kysely where clauses. It is designed for user-supplied filter parameters -- for example, query string filters forwarded from an API client.

import { createPool, createDatabase, applyFilter } from 'telaio/db';

const pool = await createPool(config);
const db = await createDatabase<DB>(pool);

const results = await db
  .selectFrom('users')
  .selectAll()
  .call((q) =>
    applyFilter(q, { status: 'active', age: { $gte: 18 } }, ['status', 'age'])
  )
  .execute();

The third argument, filterableColumns, is an allowlist. Any key in the filter object that is not in this list is silently ignored, preventing column-injection.

Limits: max depth 4, max conditions 20.

Operators

Logical operators

Wrap conditions in $and, $or, or $not:

applyFilter(q, {
  $or: [
    { status: 'active' },
    { role: { $in: ['admin', 'owner'] } },
  ],
}, ['status', 'role']);

Sort and pagination helpers

Import sortPaginateQuery from telaio/db/query-builders:

import { sortPaginateQuery } from 'telaio/db/query-builders';

const result = await sortPaginateQuery(
  db.selectFrom('users').selectAll(),
  {
    sort: '-createdAt,name',    // createdAt DESC, name ASC
    limit: 20,
    skip: 0,
    sortableColumns: ['createdAt', 'name'],
  },
);
// result.data → User[]
// result.meta → { total, skip, limit }

The function executes the data query and count query concurrently. All conditions must be applied to query before calling sortPaginateQuery.