Database

Telaio's database layer wraps node-postgres (pg) 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 and DATABASE_SSL 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 pool in a Kysely instance using PostgresDialect. The DB generic is the database interface you generate with kysely-codegen or write by hand.

CamelCasePlugin is always enabled. Database columns are snake_case; Kysely exposes them as camelCase in TypeScript. You do not need to configure this -- it is on by default and cannot be turned off without bypassing the factory.

Additional plugins

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

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

Any plugins you pass are appended after CamelCasePlugin.

registerCitextParser()

If your schema uses PostgreSQL's CITEXT type (case-insensitive text), register the array parser so that CITEXT[] columns are decoded correctly:

import { createPool, registerCitextParser } from 'telaio/db';

const pool = await createPool(config);
await registerCitextParser(pool);

This requires the postgres-array peer dependency to be installed.

Builder integration

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

app.pool  // pg.Pool
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 })
  .build();

Migrations

Import migration utilities from telaio/db/migrations:

import {
  createMigrator,
  createMigrationFile,
  migrateToLatest,
  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.

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.