ORM and query builder

Orchid ORM consists of a query builder (such as Knex or Kysely) + layer on top of it for defining, querying and utilizing relations (as in Prisma).

The query builder is for building and executing SQL queries, such as select, create, update, and delete.

ORM allows defining belongsTo, hasMany and other relations, select and join them, create/update/delete records together with their related records and more.

setup

Install by running:

npm i orchid-orm pqb
# or
pnpm i orchid-orm pqb

orchidORM is an entry function of the ORM.

The first argument is a connection options object, the ORM-specific options are described below, see also options for a pg adapter that could be passed via the same object: client options + pool options.

The second argument is an object where keys are names and values are table classes (see next section for defining a table class).

Returns an instance with tables and some specific functions prefixed with a $ sign to not overlap with your tables.

import { orchidORM } from 'orchid-orm';

// import all tables
import { UserTable } from './tables/user';
import { MessageTable } from './tables/message';

export const db = orchidORM(
  {
    // details for databaseURL are below
    databaseURL: process.env.DATABASE_URL,

    // ssl and schema can be set here or as a databaseURL parameters:
    ssl: true,
    schema: 'my_schema',

    // retry connecting when db is starting up, no retry by default,
    // see `connectRetry` section below
    connectRetry: true,

    // option for logging, false by default
    log: true,

    // option to create named prepared statements implicitly, false by default
    autoPreparedStatements: true,
  },
  {
    user: UserTable,
    message: MessageTable,
  },
);

If needed, you can pass Adapter instance instead of connection options:

import { orchidORM, Adapter } from 'orchid-orm';

export const db = orchidORM(
  {
    adapter: new Adapter({ databaseURL: process.env.DATABASE_URL }),
    log: true,
  },
  {
    // ...tables
  },
);

defining a base table

First, need to create a base table class to extend from, this code should be separated from the db file:

import { createBaseTable } from 'orchid-orm';

export const BaseTable = createBaseTable();

Optionally, you can customize column types behavior here for all future tables:

import { createBaseTable } from 'orchid-orm';
// optionally, use one of the following validation integrations:
import { zodSchemaConfig } from 'orchid-orm-schema-to-zod';
import { valibotSchemaConfig } from 'orchid-orm-valibot';

export const BaseTable = createBaseTable({
  // set to true if columns in database are in snake_case
  snakeCase: true,

  // optional, but recommended: derive and use validation schemas from your tables
  schemaConfig: zodSchemaConfig,
  // or
  schemaConfig: valibotSchemaConfig,

  columnTypes: (t) => ({
    // by default timestamp is returned as a string, override to a number
    timestamp: () => t.timestamp().asNumber(),
  }),

  // export name of the base table, by default it is BaseTable
  // this is needed for a code generation, when you're using `appCodeUpdater` in `rakeDb`
  exportAs: 'BaseTable',
});

See override column types for details of customizing columns.

Tables are defined as classes table and columns required properties:

table is a table name and columns is for defining table column types (see Columns schema document for details).

Note that the table property is marked as readonly, this is needed for TypeScript to check the usage of the table in queries.

import { Selectable, Insertable, Updateable } from 'orchid-orm';
// import BaseTable from a file from the previous step:
import { BaseTable } from './baseTable';

// export types of User for various use-cases:
export type User = Selectable<UserTable>;
export type NewUser = Insertable<UserTable>;
export type UserUpdate = Updateable<UserTable>;

export class UserTable extends BaseTable {
  readonly table = 'user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.text(3, 100),
    password: t.text(8, 200),
    ...t.timestamps(),
  }));
}

After defining the table place it in the main db file as in setup step:

import { UserTable } from './tables/user';

export const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
  },
  {
    user: UserTable,
  },
);

And now it's available for querying:

import { db } from './db';

const user = await db.user.findBy({ name: 'John' });

Don't use table classes directly, this won't work:

// error
await UserTable.findBy({ name: 'John' });

snakeCase can be overridden for a table:

import { BaseTable } from './baseTable';

export class SnakeCaseTable extends BaseTable {
  readonly table = 'table';
  // override snakeCase:
  snakeCase = true;
  columns = this.setColumns((t) => ({
    // snake_column in db
    snakeColumn: t.text(),
  }));
}

table utility types

Utility types available for tables:

Selectable: record type returned from a database and parsed with column parsers. For instance, when using asDate for a timestamp column, Selectable will have Date type for this column.
Insertable: type of object you can create a new record with. Column type may be changed by encode function. Insertable type for timestamp column is a union string | number | Date.
Updateable: the same as Insertable but all fields are optional.
Queryable: disregarding if parse or encode functions are specified for the column, types that are accepted by where and other query methods remains the same. Use this type to accept data to query the table with.

import { Selectable, Insertable, Updateable, Queryable } from 'orchid-orm';

export type User = Selectable<UserTable>;
export type NewUser = Insertable<UserTable>;
export type UserUpdate = Updateable<UserTable>;
export type UserQueryable = Queryable<UserTable>;

createDb

For the case of using the query builder as a standalone tool, use createDb.

As Orchid ORM focuses on ORM usage, docs examples mostly demonstrates how to work with ORM-defined tables, but everything that's not related to table relations should also work with pqb query builder on its own.

It is accepting the same options as orchidORM + options of createBaseTable:

import { createDb } from 'orchid-orm';

import { zodSchemaConfig } from 'orchid-orm-schema-to-zod';
// or
import { SchemaConfig } from 'orchid-orm-valibot';

const db = createDb({
  // db connection options
  databaseURL: process.env.DATABASE_URL,
  log: true,

  // columns in db are in snake case:
  snakeCase: true,

  // override default SQL for timestamp, see `nowSQL` above
  nowSQL: `now() AT TIME ZONE 'UTC'`,

  // optional, but recommended: makes zod schemas for your tables
  schemaConfig: zodSchemaConfig,
  // or
  schemaConfig: valibotSchemaConfig,

  // override column types:
  columnTypes: (t) => ({
    // by default timestamp is returned as a string, override to a number
    timestamp: () => t.timestamp().asNumber(),
  }),
});

After db is defined, construct queryable tables in such way:

export const User = db('user', (t) => ({
  id: t.identity().primaryKey(),
  name: t.text(3, 100),
  password: t.text(8, 200),
  age: t.integer().nullable(),
  ...t.timestamps(),
}));

Now the User can be used for making type-safe queries:

const users = await User.select('id', 'name') // only known columns are allowed
  .where({ age: { gte: 20 } }) // gte is available only on the numeric field, and the only number is allowed
  .order({ createdAt: 'DESC' }) // type safe as well
  .limit(10);

// users array has a proper type of Array<{ id: number, name: string }>

The optional third argument is for table options:

const Table = db('table', (t) => ({ ...columns }), {
  // provide this value if the table belongs to a specific database schema:
  schema: 'customTableSchema',
  // override `log` option of `createDb`:
  log: true, // boolean or object described `createdDb` section
  logger: { ... }, // override logger
  noPrimaryKey: 'ignore', // override noPrimaryKey
  snakeCase: true, // override snakeCase
})

databaseURL option

databaseURL has the following format:

postgres://user:password@localhost:5432/dbname

schema and ssl option can be specified as a parameter:

postgres://user:password@localhost:5432/dbname?schema=my_schema&ssl=true

If schema is set and is different from public, the SET search_path = schema query will be performed before the first query run per each database connection.

snakeCase option

By default, all column names are expected to be named in camelCase.

If only some columns are named in snake_case, you can use name method to indicate it:

import { BaseTable } from './baseTable';

class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    camelCase: t.integer(),
    snakeCase: t.name('snake_case').integer(),
  }));
}

// all columns are available by a camelCase name,
// even though `snakeCase` has a diferent name in the database
const records = await table.select('camelCase', 'snakeCase');

Set snakeCase to true if you want all columns to be translated automatically into a snake_case.

Column name can still be overridden with a name method.

import { createBaseTable } from 'orchid-orm';

export const BaseTable = createBaseTable({
  snakeCase: true,
});

class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    // camelCase column requires an explicit name
    camelCase: t.name('camelCase').integer(),
    // snakeCase is snakerized automatically when generating SQL
    snakeCase: t.integer(),
  }));
}

// result is the same as before
const records = await table.select('camelCase', 'snakeCase');

log option

The log option is false by default, true or custom object can be provided:

type LogOption = {
  // for colorful log, true by default
  colors?: boolean;

  // callback to run before query
  // Query is a query object, sql is { text: string, values: unknown[] }
  // returned value will be passed to afterQuery and onError
  beforeQuery?(sql: Sql): unknown;

  // callback to run after query, logData is data returned by beforeQuery
  afterQuery?(sql: Sql, logData: unknown): void;

  // callback to run in case of error
  onError?(error: Error, sql: Sql, logData: unknown): void;
};

The log will use console.log and console.error by default, it can be overridden by passing the logger option:

export const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    log: true,
    logger: {
      log(message: string): void {
        // ...
      },
      error(message: string): void {
        // ...
      },
    },
  },
  {
    // ...tables
  },
);

connectRetry

This option may be useful in CI when database container has started, CI starts performing next steps, migrations begin to apply though database may be not fully ready for connections yet.

Set connectRetry: true for the default backoff strategy. It performs 10 attempts starting with 50ms delay and increases delay exponentially according to this formula:

(factor, defaults to 1.5) ** (currentAttempt - 1) * (delay, defaults to 50)

So the 2nd attempt will happen in 50ms from start, 3rd attempt in 125ms, 3rd in 237ms, and so on.

You can customize max attempts to be made, factor multiplier and the starting delay by passing:

const options = {
  databaseURL: process.env.DATABASE_URL,
  connectRetry: {
    attempts: 15, // max attempts
    strategy: {
      delay: 100, // initial delay
      factor: 2, // multiplier for the formula above
    }
  }
};

rakeDb(options, { ... });

You can pass a custom function to strategy to customize delay behavior:

import { setTimeout } from 'timers/promises';

const options = {
  databaseURL: process.env.DATABASE_URL,
  connectRetry: {
    attempts: 5,
    stragegy(currentAttempt: number, maxAttempts: number) {
      // linear: wait 100ms after 1st attempt, then 200m after 2nd, and so on.
      return setTimeout(currentAttempt * 100);
    },
  },
};

nowSQL option

For the specific case you can use nowSQL option to specify SQL to override the default value of timestamps() method.

If you're using timestamp and not timestampNoTZ there is no problem, or if you're using timestampNoTZ in a database where time zone is UTC there is also no problem, but if you're using timestampNoTZ in a database with a different time zone, and you still want updatedAt and createdAt columns to automatically be saved with a current time in UTC, you can specify the nowSQL for the base table:

import { createBaseTable } from 'orchid-orm';

export const BaseTable = createBaseTable({
  nowSQL: `now() AT TIME ZONE 'UTC'`,

  // ...other options
});

This value is used:

for updatedAt column when updating a record
for the default value updatedAt and createdAt columns in a database, applied in the migrations

It's required to specify a baseTable parameter of rakeDb to make it work in the migrations.

By default, Orchid ORM is using now() for a timestamp value of updatedAt and createdAt, in the example above we override it to now() AT TIME ZONE 'UTC' so it produces UTC timestamp for timestampNoTZ columns even in database in different time zone.

autoPreparedStatements option

This option was meant to speed up the queries, but benchmarks cannot prove this, so simply ignore this option for now.

pg node module used under the hood is performing "unnamed" prepared statements by default (link to Postgres details about this).

When the option is set to true, the query builder will generate a name for each different query to make the statement named.

noPrimaryKey

All tables should have a primary key. Even if it is a join table, it should have a composite primary key consisting of foreign key columns.

If you forgot to define a primary key, ORM will send a friendly remained by throwing an error.

Disable the check for a specific table by setting noPrimaryKey property:

import { BaseTable } from './baseTable';

export class NoPrimaryKeyTable extends BaseTable {
  readonly table = 'table';
  noPrimaryKey = true; // set to `true` to ignore absence of primary key
  columns = this.setColumns((t) => ({
    // ...no primary key defined
  }));
}

Or, you can override this behavior for all tables by placing noPrimaryKey option into orchidORM config:

ignore will disable the check, warning will print a warning instead of throwing error.

// ignore absence of primary keys for all tables
const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    noPrimaryKey: 'ignore',
  },
  {
    // ...tables
  },
);

// print a warning for all tables without primary key
const db2 = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    noPrimaryKey: 'warning',
  },
  {
    // ...tables
  },
);

softDelete

softDelete configures the table to set deletedAt to current time instead of deleting records. All queries on such table will filter out deleted records by default.

import { BaseTable } from './baseTable';

export class SomeTable extends BaseTable {
  readonly table = 'some';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    deletedAt: t.timestamp().nullable(),
  }));

  // true is for using `deletedAt` column
  readonly softDelete = true;
  // or provide a different column name
  readonly softDelete = 'myDeletedAt';
}

const db = orchidORM(
  { databaseURL: '...' },
  {
    someTable: SomeTable,
  },
);

// deleted records are ignored by default
const onlyNonDeleted = await db.someTable;

includeDeleted disables the default deletedAt filter:

const allRecords = await db.someTable.includeDeleted();

delete behavior is altered:

await db.someTable.find(1).delete();
// is equivalent to:
await db.someTable.find(1).update({ deletedAt: sql`now()` });

hardDelete deletes records bypassing the softDelete behavior:

await db.someTable.find(1).hardDelete();

scopes

This feature allows defining a set of query modifiers to use it later. Only where conditions can be set in a scope. If you define a scope with name default, it will be applied for all table queries by default.

import { BaseTable } from './baseTable';

export class SomeTable extends BaseTable {
  readonly table = 'some';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    hidden: t.boolean(),
    active: t.boolean(),
  }));

  scopes = this.setScopes({
    default: (q) => q.where({ hidden: false }),
    active: (q) => q.where({ active: true }),
  });
}

const db = orchidORM(
  { databaseURL: '...' },
  {
    some: SomeTable,
  },
);

// the default scope is applied for all queries:
const nonHiddenRecords = await db.some;

scope

Use the scope method to apply a pre-defined scope.

// use the `active` scope that is defined in the table:
await db.some.scope('active');

unscope

Remove conditions that were added by the scope from the query.

// SomeTable has a default scope, ignore it for this query:
await db.some.unscope('default');

computed columns

You can add a generated column in the migration (see generated), such column will persist in the database, it can be indexed.

Or you can add a computed column on the ORM level, without adding it to the database, in such a way:

import { BaseTable } from './baseTable';

export class UserTable extends BaseTable {
  readonly table = 'user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    firstName: t.string(),
    lastName: t.string(),
  }));

  computed = this.setComputed({
    fullName: (q) =>
      q.sql`${q.column('firstName')} || ' ' || ${q.column('lastName')}`.type(
        (t) => t.string(),
      ),
  });
}

setComputed takes an object where keys are computed column names, and values are functions returning raw SQL.

Use q.column as shown above to reference a table column, it will be prefixed with a correct table name even if the table is joined under a different name.

Computed columns are not selected by default, only on demand:

const a = await db.user.take();
a.fullName; // not selected

const b = await db.user.select('*', 'fullName');
b.fullName; // selected

// Table post belongs to user as an author.
// it's possible to select joined computed column:
const posts = await db.post
  .join('author')
  .select('post.title', 'author.fullName');

SQL query can be generated dynamically based on the current request context.

Imagine we are using AsyncLocalStorage to keep track of current user's language.

And we have articles translated to different languages, each article has title_en, title_uk, title_be and so on.

We can define a computed title by passing a function into sql method:

type Locale = 'en' | 'uk' | 'be';
const asyncLanguageStorage = new AsyncLocalStorage<Locale>();
const defaultLocale: Locale = 'en';

export class ArticleTable extends BaseTable {
  readonly table = 'article';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    title_en: t.text(),
    title_uk: t.text().nullable(),
    title_be: t.text().nullable(),
  }));

  computed = this.setComputed({
    title: (q) =>
      q
        // .sql can take a function that accepts `sql` argument and must return SQL
        .sql((sql) => {
          // get locale dynamically based on current storage value
          const locale = asyncLanguageStorage.getStore() || defaultLocale;

          // use COALESCE in case when localized title is NULL, use title_en
          return sql`COALESCE(
            ${q.column(`title_${locale}`)},
            ${q.column(`title_${defaultLocale}`)}
          )`;
        })
        .type((t) => t.text()),
  });
}

$query

Use $query to perform raw SQL queries.

const value = 1;

// it is safe to interpolate inside the backticks (``):
const result = await db.$query<{ one: number }>`SELECT ${value} AS one`;
// data is inside `rows` array:
result.rows[0].one;

If the query is executing inside a transaction, it will use the transaction connection automatically.

await db.$transaction(async () => {
  // both queries will execute in the same transaction
  await db.$query`SELECT 1`;
  await db.$query`SELECT 2`;
});

Alternatively, provide a raw SQL object created with raw function:

import { raw } from 'orchid-orm';

// it is NOT safe to interpolate inside a simple string, use `values` to pass the values.
const result = await db.$query<{ one: number }>(
  raw({
    raw: 'SELECT $value AS one',
    values: {
      value: 123,
    },
  }),
);

// data is inside `rows` array:
result.rows[0].one;

$queryArrays

The same as the $query, but returns an array of arrays instead of objects:

const value = 1;

// it is safe to interpolate inside the backticks (``):
const result = await db.$queryArrays<[number]>`SELECT ${value} AS one`;
// `rows` is an array of arrays:
const row = result.rows[0];
row[0]; // our value

$from

Use $from to build a queries around sub queries similar to the following:

const subQuery = db.someTable.select('name', {
  relatedCount: (q) => q.related.count(),
});

const result = await db
  .$from(subQuery)
  .where({ relatedCount: { gte: 5 } })
  .limit(10);

For a standalone query builder, the method is from.

$close

Call $clone to end a database connection:

await db.$close();

For a standalone query builder, the method is close.

ORM and query builder #

setup #

defining a base table #

table utility types #

createDb #

databaseURL option #

snakeCase option #

log option #

connectRetry #

nowSQL option #

autoPreparedStatements option #

noPrimaryKey #

softDelete #

scopes #

scope #

unscope #

computed columns #

$query #

$queryArrays #

$from #

$close #