Column types

numeric

As not all database numeric types can fit into JS number type, some types will be returned as a string.

// signed two-byte integer
t.smallint() // -> number

// signed four-byte integer
t.integer() // -> number

// signed eight-byte integer
t.bigint() // -> string

// exact numeric of selectable precision
t.numeric(precision?: number, scale?: number) // -> string

// decimal is an alias for numeric
t.decimal(precision?: number, scale?: number) // -> string

// single-precision floating-point number (4 bytes)
t.real() // -> number

// double-precision floating-point number (8 bytes)
t.doublePrecision() // -> number

// autoincrementing integer (GENERATED BY DEFAULT AS IDENTITY)
t.identity() // -> number

// autoincrementing two-byte integer
t.smallSerial() // -> number

// autoincrementing four-byte integer
t.serial() // -> number

// autoincrementing eight-byte integer
t.bigSerial() // -> string

As listed in code comments above, bigint, numeric, decimal, and bigSerial have string output.

You can set up parsing to a number type, (remember this can cause bugs on large numbers):

t.bigint().parse(parseInt);

Or bigint Postgres type can be parsed to bigint JavaScript type, but be aware that such values should be explicitly turned to a string when preparing JSON response:

t.bigint().parse(BigInt);

Numeric-type columns support the following where operators:

db.someTable.where({
  numericColumn: {
    // lower than
    lt: value,
    // lower than or equal to
    lte: value,
    // greater than
    gt: value,
    // greater than or equal to
    gte: value,
    // between x and y
    between: [x, y],
  },
});

text

Use t.text(min, max) type as a go-to for strings, other types are for special cases.

min and max number parameters defines a validation of string length, they are required to ensure that the app won't accept empty or enormous values from user.

These parameters are not required on the text method in migrations, because they don't affect on a database column type.

// character varying(n), varchar(n) variable-length with limit
t.varchar(limit?: number) // -> string

// character(n), char(n) fixed-length, blank padded
t.chat(limit?: number) // -> string

// text variable unlimited length
t.text(min: number, max: number) // -> string

// `varchar` column with optional limit defaulting to 255.
t.string(limit?: number = 255) // -> string

Text type columns support the following where operators:

contains, startsWith, endsWith are case-insensitive.

db.someTable.where({
  textColumn: {
    // ILIKE '%string%'
    contains: 'string',
    // LIKE '%string%'
    containsSensitive: 'string',
    // ILIKE 'string%'
    startsWith: 'string',
    // LIKE 'string%'
    startsWithSensitive: 'string',
    // ILIKE '%string'
    endsWith: 'string',
    // LIKE '%string'
    endsWithSensitive: 'string',
  },
});

citext

citext is a database type that behaves almost exactly like text, but is case-insensitive in all operations.

To use it, first enable citext extension, create migration:

npm run db new enableCitext

import { change } from '../dbScript';

change(async (db) => {
  await db.createExtension('citext');
});

npm run db migrate

And now citext is available and can be used just as a text type.

It requires min and max, but can be overridden in the same way as the text.

// text variable unlimited length
t.citext(min: number, max: number) // -> string

tsvector

For full text search: define a generated column for a text vector.

See generated migration method.

// generate a `ts_vector` from other text columns
t.tsvector().generated(['title', 'body']).searchIndex();

binary

The bytea data type allows storage of binary strings, it is returned as a node.js Buffer object.

t.bytea(); // -> Buffer

date and time

// 4 bytes date (no time of day)
t.date() // -> string

// timestamp with time zone (8 bytes)
t.timestamp(precision?: number) // -> string

// timestamp without time zone (8 bytes), not recommended
t.timestampNoTZ(precision?: number) // -> string

// time without time zone (8 bytes)
// format is 00:00:00
t.time(precision?: number) // -> string

// time with time zone is not added because it should never be used, according to Postgres docs.

Time with time zone is not included because it's discouraged by Postgres docs.

date, timestamp, and timestampNoTZ can be customized with methods asNumber and asDate to parse database values into number and JS Date object respectively.

export const BaseTable = createBaseTable({
  columnTypes: (t) => ({
    ...t,
    // or use `.asDate()` to work with Date objects
    timestamp: () => t.timestamp().asNumber(),
  }),
});

// timestamp columns now are returned as numbers, or as Date objects if you choose `asDate`:
const { updatedAt, createdAt } = await db.table.take();

When filtering by timestamp fields, creating or updating records, you can use dates encoded as strings, numbers or Date objects:

// filter, update, create with a Date object:
const date = new Date();
db.table.where({ createdAt: date });
db.table.find(id).update({ ...data, createdAt: date });
db.table.create({ ...data, createdAt: date });

// filter, update, create with a ISO encoded date string
const string = new Date().toISOString();
db.table.where({ createdAt: string });
db.table.find(id).update({ ...data, createdAt: string });
db.table.create({ ...data, createdAt: string });

// filter, update, create with a number retrieved from `getTime`
const number = new Date().getTime();
db.table.where({ createdAt: number });
db.table.find(id).update({ ...data, createdAt: number });
db.table.create({ ...data, createdAt: number });

interval

// interval [ fields ] [ (p) ] 16 bytes   time interval  -178000000 years   178000000 years    1 microsecond
t.interval(fields?: string, precision?: number) // -> PostgresInterval object

The interval type takes two optional parameters:

The first parameter is a string containing YEAR, MONTH, DAY, HOUR, and so on, check the full list in Postgres docs here.

The second parameter specifies the number of fractional digits retained in the second field.

The output of the interval column is an object containing years, month, and other fields:

type Interval = {
  years?: number;
  months?: number;
  days?: number;
  hours?: number;
  minutes?: number;
  seconds?: number;
};

const result: Interval = await Table.get('intervalColumn');

boolean

Boolean returns true or false.

// 1 byte, true or false
t.boolean(); // -> boolean

UUID

The data type uuid stores Universally Unique Identifiers (UUID).

// UUID stores Universally Unique Identifiers (UUID)
t.uuid(); // -> string, example: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

When using it as a primary key, it will automatically get a gen_random_uuid default.

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    id: t.uuid().primaryKey(),
    name: t.text(),
  }));
}

// id is generated in the database
db.table.create({ name: 'Joe' });

To discard the default, use default(null):

id: t.uuid().primaryKey().default(null),

If you'd like to use a different default, primaryKey will respect it:

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    id: t
      .uuid()
      .default(() => makeOwnUUID())
      .primaryKey(),
    name: t.text(),
  }));
}

// custom function will be used for the id
db.table.create({ name: 'Joe' });

enum

First argument is the name of an enum in the database, the second is an array of possible values:

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    enumColumn: t.enum('enumName', ['value1', 'value2', 'value3']),
  }));
}

For convenience and to avoid duplication, you can define enum column in columnTypes of BaseTable, then reuse it in multiple tables:

export const BaseTable = createBaseTable({
  columnTypes: (t) => ({
    ...t,
    orderStatus: () =>
      t.enum('orderStatus', ['pending', 'cancelled', 'processed']),
  }),
});

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    // it still can be chained with common column methods
    orderStatus: t.orderStatus().nullable(),
  }));
}

json

Postgres supports two types of JSON: json is for storing JSON strings as they were saved, and jsonb is stored in binary format and allows additional methods.

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    json: t.jsonText(),
    jsonB: t.json(),
  }));
}

When using ORM without a validation library, you can set an arbitrary type to json. Make sure to only save properly validated data.

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    data: t.json<{
      age: number;
      name: string;
      description: string | null;
      tags: string[];
    }>(),
  }));
}

When having a validation library enabled, json accepts a callback where you can define a validation schema. If omitted, the type is unknown.

import { z } from 'zod';
import { object, number, string, optional, array } from 'valibot';

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    dataZod: t.json(() =>
      z.object({
        age: z.number(),
        name: z.string(),
        description: z.string().optional(),
        tags: z.string().array(),
      }),
    ),
    // or
    dataValibot: t.json(() =>
      object({
        age: number(),
        name: string(),
        description: optional(string()),
        tags: array(string()),
      }),
    ),
  }));
}

json columns support the following where operators:

db.someTable.where({
  jsonColumn: {
    // first element is JSON path,
    // second is a compare operator,
    // third value can be of any type, or a subquery, or a raw SQL query
    jsonPath: ['$.name', '=', value],

    // use `is` or `is not` for the null
    jsonPath: ['$.name', 'is', null],
    jsonPath: ['$.name', 'is not', null],

    // check if the JSON value in the column is a superset of the provided value
    jsonSupersetOf: { key: 'value' },

    // check if the JSON value in the column is a subset of the provided value
    jsonSubsetOf: { key: 'value' },
  },
});

geometry

Geometric types are not parsed and returned as strings as the database returns them.

// point   16 bytes   Point on a plane   (x,y)
t.point(); // -> string

// line    32 bytes   Infinite line  {A,B,C}
t.line(); // -> string

// lseg    32 bytes   Finite line segment    [(x1,y1),(x2,y2)]
t.lseg(); // -> string

// box 32 bytes   Rectangular box    ((x1,y1),(x2,y2))
t.box(); // -> string

// path    16+16n bytes   Closed path (similar to polygon)   ((x1,y1),...)
// path    16+16n bytes   Open path  [(x1,y1),...]
t.path(); // -> string

// polygon 40+16n bytes   Polygon (similar to closed path)   ((x1,y1),...)
t.polygon(); // -> string

// circle  24 bytes   Circle <(x,y),r> (center point and radius)
t.circle(); // -> string

network addresses

// CIDR    7 or 19 bytes  IPv4 and IPv6 networks
t.cidr(); // -> string, example: 192.168.100.128/25

// inet    7 or 19 bytes  IPv4 and IPv6 hosts and networks
t.inet(); // -> string, example: 192.168.100.128/25

// macaddr 6 bytes    MAC addresses
t.macaddr(); // -> string, example: 08:00:2b:01:02:03

// macaddr8    8 bytes    MAC addresses (EUI-64 format)
t.macaddr8(); // -> string, example: 08:00:2b:ff:fe:01:02:03

bit string

it strings are strings of 1's and 0's. They can be used to store or visualize bit masks.

// Bit strings are strings of 1's and 0's.
// They can be used to store or visualize bit masks.
// There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer.
t.bit(); // -> string

// bit varying(n), where n is a positive integer
t.bitVarying(); // -> string

full text search

// A tsvector value is a sorted list of distinct lexemes
t.tsvector(); // -> string

// A tsquery value stores lexemes that are to be searched for
t.tsquery(); // -> string

array

// array of another column type
t.array(item: ColumnType) // -> array of argument type

unsupported types

For user-defined custom types, or if some database type is not supported yet, use type and as to treat this column as other type:

t.type('type_name').as(t.integer());

domain

Domain is a custom database type that allows to predefine a NOT NULL and a CHECK (see postgres tutorial).

In same way as with type, specify as(otherType) to treat this column in queries as the other type:

t.domain('domainName').as(t.integer());

money

For currency amount (8 bytes)

t.money(); // -> string, example: '$12.34'

xml

XML data type can be used to store XML data

t.xml(); // -> string

Column types #

numeric #

text #

citext #

tsvector #

binary #

date and time #

interval #

boolean #

UUID #

enum #

json #

geometry #

network addresses #

bit string #

full text search #

array #

unsupported types #

domain #

money #

xml #