Orchid ORM
Stand With Ukraine

Appearance

Common column methods ​

All the following methods are available in any kind of column.

primaryKey ​

Mark the column as a primary key. This column type becomes an argument of the find method. So if the primary key is of integer type (identity or serial), find will accept the number, or if the primary key is of UUID type, find will expect a string.

Using primaryKey on a uuid column will automatically add a gen_random_uuid default.

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    id: t.uuid().primaryKey(),
    // optionally, specify a database-level constraint name:
    id: t.uuid().primaryKey('primary_key_name'),
  }));
}

// primary key can be used by `find` later:
db.table.find('97ba9e78-7510-415a-9c03-23d440aec443');

default ​

Set a default value to a column. Columns that have defaults become optional when creating a record.

If you provide a value or a raw SQL, such default should be set on the column in migration to be applied on a database level.

Or you can specify a callback that returns a value. This function will be called for each creating record. Such a default won't be applied to a database. If the column has an encoding function (json, timestamp columns have it), it will be used to serialize the returned default value.

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    // values as defaults:
    int: t.integer().default(123),
    text: t.text().default('text'),

    // raw SQL default:
    timestamp: t.timestamp().default(t.sql`now()`),

    // runtime default, each new records gets a new random value:
    random: t.numeric().default(() => Math.random()),
  }));
}

hasDefault ​

Use hasDefault to let the column be omitted when creating records.

It's better to use default instead so the value is explicit and serves as a hint.

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    column: t.text().hasDefault(),
  }));
}

nullable ​

Use nullable to mark the column as nullable. By default, all columns are required.

Nullable columns are optional when creating records.

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    name: t.integer().nullable(),
  }));
}

identity ​

Available for smallint, integer, bigint.

It's almost identical to using serial, but serial is officially discouraged by Postgres team, and identity is suggested as a preferred autoincrementing type.

t.identity() is a shortcut for t.integer().identity().

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    small: t.smallint().identity(),
    int: t.identity(),
    alsoInt: t.integer().identity(),
    big: t.bigint().identity(),
  }));
}

Postgres supports identity kind BY DEFAULT and ALWAYS. Identity BY DEFAULT is allowed to be set manually when creating and updating records, while ALWAYS is disallowed.

Orchid ORM decided to use BY DEFAULT by default in case you ever wish to set the id manually.

Supported options:

ts
type IdentityOptions = {
  // false by default, set to true for GENERATE ALWAYS
  always?: boolean;

  // identity sequence options, check postgres docs for details:
  incrementBy?: number;
  startWith?: number;
  min?: number;
  max?: number;
  cache?: number;
  cycle?: boolean;
};

exclude from select ​

Append select(false) to a column to exclude it from the default selection. It won't be selected with selectAll or select('*') as well.

ts
export class UserTable extends BaseTable {
  readonly table = 'user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.string(),
    password: t.string().select(false),
  }));
}

// only id and name are selected, without password
const user = await db.user.find(123);

// password is still omitted, even with the wildcard
const same = await db.user.find(123).select('*');

const comment = await db.comment.find(123).select({
  // password is omitted in the sub-selects as well
  author: (q) => q.author,
});

// password is omitted here as well
const created = await db.user.create(userData);

Such a column can only be selected explicitly.

ts
const userWithPassword = await db.user.find(123).select('*', 'password');

name ​

To specify a real name of column in a database:

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    nameInApp: t.name('name_in_database').integer(),
  }));
}

encode ​

Set a custom function to process value for the column when creating or updating a record.

The type of input argument will be used as the type of the column when creating and updating.

If you have a validation library installed and configured, first argument is a schema to validate the input.

ts
import { z } from 'zod';

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    // encode boolean, number, or string to text before saving
    column: t
      .string()
      // when having validation library, the first argument is a validation schema
      .encode(
        z.boolean().or(z.number()).or(z.string()),
        (input: boolean | number | string) => String(input),
      )
      // no schema argument otherwise
      .encode((input: boolean | number | string) => String(input)),
  }));
}

// numbers and booleans will be converted to a string:
await db.table.create({ column: 123 });
await db.table.create({ column: true });
await db.table.where({ column: 'true' }).update({ column: false });

parse ​

Set a custom function to process value after loading it from a database.

The type of input is the type of column before .parse, the resulting type will replace the type of column.

If you have a validation library installed and configured, first argument is a schema for validating the output.

For handling null values use parseNull instead or in addition.

ts
import { z } from 'zod';
import { number, integer } from 'valibot';

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    columnZod: t
      .string()
      // when having validation library, the first argument is a schema
      .parse(z.number().int(), (input) => parseInt(input))
      // no schema argument otherwise
      .parse((input) => parseInt(input)),

    columnValibot: t
      .string()
      .parse(number([integer()]), (input) => parseInt(input))
      .parse((input) => parseInt(input)),
  }));
}

// column will be parsed to a number
const value: number = await db.table.get('column');

parseNull ​

Use parseNull to specify runtime defaults at selection time.

The parseNull function is only triggered for nullable columns.

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    column: t
      .integer()
      .parse(String) // parse non-nulls to string
      .parseNull(() => false), // replace nulls with false
      .nullable(),
  }));
}

const record = await db.table.take()
record.column // can be a string or boolean, not null

If you have a validation library installed and configured, first argument is a schema for validating the output.

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    column: t
      .integer()
      .parse(z.string(), String) // parse non-nulls to string
      .parseNull(z.literal(false), () => false), // replace nulls with false
    .nullable(),
  }));
}

const record = await db.table.take()
record.column // can be a string or boolean, not null

Table.outputSchema().parse({
  column: false, // the schema expects strings or `false` literals, not nulls
})

as ​

This method changes a column type to treat one column as another column, this affects on available column operations in where.

Before calling .as need to use .encode with the input of the same type as the input of the target column, and .parse which returns the correct type.

ts
// column has the same type as t.integer()
const column = t
  .string()
  .encode((input: number) => input)
  .parse((text) => parseInt(text))
  // schema argument is required if you included a validation library
  .encode(z.number(), (input: number) => input)
  .parse(z.number(), (text) => parseInt(text))
  .as(t.integer());

asType ​

Mark the column as to have specific Typescript type. This can be used to narrow generic column types, such as narrow string to a string literal union.

If you don't specify schemaConfig option for a validation library, the syntax as follows:

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    size: t.string().asType((t) => t<'small' | 'medium' | 'large'>()),
  }));
}

// size will be typed as 'small' | 'medium' | 'large'
const size = await db.table.get('size');

To alter the base, input, output and query types individually, pass them as generic parameters:

ts
const column = t
  .text()
  .asType((t) => t<Type, InputType, OutputType, QueryType>());
  • The first Type is the base one, used as a default for other types.
  • InputType is for create, update methods.
  • OutputType is for the data that is loaded from a database and parsed if the column has parse.
  • QueryType is used in where and other query methods, it should be compatible with the actual database column type.

If when using a validation library, also provide validation schemas:

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    size: t.text().asType({
      type: z.union([
        z.literal('small'),
        z.literal('medium'),
        z.literal('large'),
      ]),
    }),
  }));
}

// size will be typed as 'small' | 'medium' | 'large'
const size = await db.table.get('size');

The same schema will be assigned for input, output, and query.

You can set different schemas for different purposes:

ts
export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    size: t.text().asType({
      input: z.literal('input'),
      output: z.literal('output'),
      query: z.literal('query'),
    }),
  }));
}

timestamps ​

Adds createdAt and updatedAt columns of type timestamp (with time zone) with default SQL now().

Timestamp with timezone is preferred over the one without time zone because it's suggested so by Postgres docs.

The timestamps function is using timestamp internally. If timestamp is overridden to be parsed into Date, so will do timestamps.

updatedAt adds a hook to refresh its date on every update query, unless you set updatedAt explicitly when updating a record.

ts
export class SomeTable extends BaseTable {
  readonly table = 'someTable';
  columns = this.setColumns((t) => ({
    ...t.timestamps(),
  }));
}

Customizing columns names is possible in a such way:

ts
export class SomeTable extends BaseTable {
  readonly table = 'someTable';
  columns = this.setColumns((t) => ({
    // `created` will be also used to refer to this column in SQL
    created: t.timestamps().createdAt,
    updated: t.timestamps().updatedAt,
  }));
}

timestampsNoTZ ​

The same as timestamps, but without a time zone.

modifyQuery ​

Specify a callback that can modify a table class.

When mutating a query in this callback, the changes will be applied to all future queries of this table.

ts
export class SomeTable extends BaseTable {
  readonly table = 'someTable';
  columns = this.setColumns((t) => ({
    name: t.string().modifyQuery((table, column) => {
      // table argument is the query interface of SomeTable
      // column object contains data with column name and other properties
    }),
  }));
}

methods for migration ​

Column methods such as foreignKey, index, exclude, unique, comment and others have effects only when used in migrations, read more about it in migration column methods document.

Though unique is used for deriving types for findBy and onConflict.