Complete reference for createPool(), createDatabase(), migrations, and query builder helpers.

Database API Reference

Import database utilities from telaio/db:

import { createPool, createDatabase, registerCitextParser, applyFilter } from 'telaio/db';
import { createMigrator, migrateToLatest, runFrameworkMigrations } from 'telaio/db/migrations';
import { sortPaginateQuery } from 'telaio/db/query-builders';

createPool()

async function createPool(
  options: PoolOptions | Record<string, unknown>,
  poolLogger?: Logger,
): Promise<pg.Pool>

Creates and validates a node-postgres Pool. Accepts either explicit PoolOptions or a loaded config record. When passed a config record it reads DATABASE_URL and DATABASE_SSL automatically.

Auto-SSL for AWS RDS: if the connection string hostname contains rds.amazonaws.com, SSL is enabled automatically even without ssl: true.

// From explicit options
const pool = await createPool({
  connectionString: 'postgresql://localhost/mydb',
  ssl: false,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

// From a loaded config (reads DATABASE_URL and DATABASE_SSL)
const pool = await createPool(config);

PoolOptions

interface PoolOptions {
  connectionString: string;
  ssl?: boolean;
  idleTimeoutMillis?: number;
  connectionTimeoutMillis?: number;
  max?: number;
}

Option	Type	Default	Description
`connectionString`	`string`	--	Full Postgres connection URI
`ssl`	`boolean`	`false`	Enable SSL/TLS
`max`	`number`	pg default (10)	Maximum pool size
`idleTimeoutMillis`	`number`	pg default (10000)	How long an idle client is kept before closing
`connectionTimeoutMillis`	`number`	pg default (0 = no timeout)	How long to wait for a connection before erroring

createDatabase<DB>()

async function createDatabase<DB>(
  pool: pg.Pool,
  options?: DatabaseOptions,
): Promise<Kysely<DB>>

Wraps a pg Pool in a Kysely instance using PostgresDialect. The DB generic is your database interface -- typically generated with kysely-codegen or written by hand.

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

import type { DB } from './db-types.js'; // generated by kysely-codegen

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

db.destroy() also ends the underlying pg Pool via PostgresDialect. Never call both db.destroy() and pool.end() -- the second call throws "Called end on pool more than once".

DatabaseOptions

interface DatabaseOptions {
  plugins?: KyselyPlugin[];
}

Option	Type	Description
`plugins`	`KyselyPlugin[]`	Additional Kysely plugins. Appended after the built-in `CamelCasePlugin`.

import { DeduplicateJoinsPlugin } from 'kysely';

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

registerCitextParser()

async function registerCitextParser(
  pool: pg.Pool,
  logger?: Logger,
): Promise<void>

Registers a custom type parser for PostgreSQL's CITEXT type so that CITEXT[] columns are decoded as string[] rather than raw strings. Requires the postgres-array peer dependency.

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

Call this before any queries if your schema uses CITEXT[] columns.

Migration functions

Import from telaio/db/migrations:

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

Function	Signature	Description
`createMigrator`	`(options: MigratorOptions) => Migrator`	Creates a Kysely `Migrator` pointing at your migrations directory
`createMigrationFile`	`(name: string, dir: string) => Promise<string>`	Scaffolds a timestamped migration file. Returns the created filename.
`migrateToLatest`	`(options: MigratorOptions) => Promise<MigrateResult>`	Runs framework migrations then all pending app migrations
`migrateUp`	`(options: MigratorOptions) => Promise<MigrateResult>`	Runs framework migrations then the next pending app migration (single step)
`migrateDown`	`(options: MigratorOptions) => Promise<MigrateResult>`	Runs framework migrations then rolls back the last app migration (single step)
`runFrameworkMigrations`	`(db: Kysely<any>, logger?) => Promise<MigrationResult[]>`	Runs Telaio's own internal migrations only

interface MigratorOptions {
  db: Kysely<any>;
  migrationsDir: string;
  logger?: Logger;
}

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

// Correct usage:
await migrateToLatest({
  db,
  migrationsDir: 'src/db/migrations',
});

// The above is equivalent to:
// await runFrameworkMigrations(db);  ← done internally
// await userMigrator.migrateToLatest();

Telaio stores its internal migration state in _telaio_migrations. App migrations use Kysely's default kysely_migration table. The two never conflict.

applyFilter()

function applyFilter<DB, TB, O>(
  query: SelectQueryBuilder<DB, TB, O>,
  filterJson: string | Record<string, unknown>,
  filterableColumns: readonly ReferenceExpression<DB, TB>[],
): SelectQueryBuilder<DB, TB, O>

Translates a JSON filter object into Kysely where clauses. Designed for user-supplied filter parameters forwarded from an API client.

The filterableColumns argument is an allowlist. Keys in the filter object that are not in this list are silently ignored, preventing column injection.

Limits: max nesting depth 4, max conditions per query 20.

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

Also accepts a JSON string:

applyFilter(q, request.query.filter, ['status', 'createdAt']);

Comparison operators

Operator	SQL equivalent	Example
`$eq`	`= value` or `IS NULL`	`{ status: 'open' }` or `{ status: { $eq: null } }`
`$ne`	`!= value` or `IS NOT NULL`	`{ deletedAt: { $ne: null } }`
`$gt`	`>`	`{ age: { $gt: 18 } }`
`$gte`	`>=`	`{ createdAt: { $gte: '2024-01-01' } }`
`$lt`	`<`	`{ price: { $lt: 100 } }`
`$lte`	`<=`	`{ score: { $lte: 10 } }`
`$in`	`IN (...)`	`{ status: { $in: ['open', 'pending'] } }`
`$nin`	`NOT IN (...)`	`{ role: { $nin: ['admin'] } }`
`$contains`	`ILIKE '%val%'`	`{ name: { $contains: 'alice' } }`
`$startswith`	`ILIKE 'val%'`	`{ email: { $startswith: 'alice' } }`
`$endswith`	`ILIKE '%val'`	`{ email: { $endswith: '@example.com' } }`
`$exists`	`IS NOT NULL` / `IS NULL`	`{ deletedAt: { $exists: false } }`

Logical operators

Operator	Description
`$and`	All conditions must match
`$or`	At least one condition must match
`$not`	Negates the wrapped condition

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

sortPaginateQuery()

async function sortPaginateQuery<DB, TB, O>(
  query: SelectQueryBuilder<DB, TB, O>,
  options?: SortPaginationOptions,
): Promise<{ data: O[]; meta: PaginationMeta }>

Executes a query with sort, pagination, and total count concurrently. Returns both data rows and pagination metadata. The query and its count are run in parallel via Promise.all.

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: number, skip: number, limit: number }

SortPaginationOptions

Field	Type	Default	Description
`sort`	`string`	—	Comma-separated sort fields. Prefix `-` for descending. E.g. `-createdAt,name`
`limit`	`number \| string`	`20`	Rows per page. Clamped to 1–100.
`skip`	`number \| string`	`0`	Number of rows to skip.
`sortableColumns`	`readonly string[]`	—	Allowlist of valid sort fields. Sorts on unlisted columns throw `BadRequestError`.

Sort string format: -createdAt,name means ORDER BY createdAt DESC, name ASC. If no sort is provided and createdAt is in sortableColumns, defaults to createdAt DESC.

PaginationMeta

interface PaginationMeta {
  total: number;   // total row count (ignoring limit/skip)
  skip: number;    // actual skip applied
  limit: number;   // actual limit applied
}

Usage with route params

The built-in SortPaginationParamsSchema produces query params matching this interface:

fastify.get('/users', {
  schema: {
    querystring: AutoRef(SortPaginationParamsSchema),
    response: { 200: Paginated(UserSchema) },
  },
}, async (req) => {
  return sortPaginateQuery(
    db.selectFrom('users').selectAll(),
    { ...req.query, sortableColumns: ['createdAt', 'name', 'email'] },
  );
});

Database API Reference

On this page