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.
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.
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.
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.
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()
.
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:
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.
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.
const userWithPassword = await db.user.find(123).select('*', 'password');
name
To specify a real name of column in a database:
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.
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.
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.
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.
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.
// 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:
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:
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 forcreate
,update
methods.OutputType
is for the data that is loaded from a database and parsed if the column hasparse
.QueryType
is used inwhere
and other query methods, it should be compatible with the actual database column type.
If when using a validation library, also provide validation schemas:
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:
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.
export class SomeTable extends BaseTable {
readonly table = 'someTable';
columns = this.setColumns((t) => ({
...t.timestamps(),
}));
}
Customizing columns names is possible in a such way:
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.
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, 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.