Indexes

Indexes allow you to speed up the retrieval of data from a table.

Index are defined with the index function.

You add an index to a table by adding it to the indexes property in the table definition.

Index on column(s)

Single column

import { table, text, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    name: text(),
  },
  indexes: [index(["name"])], 
});

WARNING

The optional columns argument of the index function is always an array.

Multiple columns

import { table, text, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    email: text(),
    name: text(),
  },
  indexes: [index(["email", "name"])], 
});

Expression indexes

An expression index allows an index field to be a computed value of one or more columns of the table row. This can be used to obtain fast access to data based on some transformation of the basic data.

import { sql } from "kysely";
import { table, text, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    name: text(),
  },
  indexes: [index().expression(sql`upper(${sql.ref("name")})`);], 
});

DANGER

It's recommended to reference column names with the sql.ref function. This function takes care of:

• Double quote the column name (PostgreSQL lower cases all names unless they are "double quoted" ).
• Transform to the column name to snake_case when the camelCase option is active.

You can read more about how to build expressions for indexes in the Kysely Docs

Unique index

An unique index does not allow duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries generate an error.

import { table, text, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    name: text(),
  },
  indexes: [index(["name"]).unique()], 
});

Nulls not distinct

In SQL NULL values (which represent missing or unknown data) are considered to be distinct. This means that if you index by a column that contains NULL values, the unique index will would allow multiple rows with NULL in that column.

You may want to change the behaviour and consider NULL values as not distinct, only allowing one row with a NULL value. If another row tries to have NULL for the column, it will be rejected because every NULL is treated as the same.

import { table, text, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    name: text(),
  },
  indexes: [index(["name"]).unique().nullsNotDistinct()], 
});

Index with a specific index method

You can specify the index to be created with a specific index method.

import { table, text, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    name: text(),
  },
  indexes: [index(["name"]).using("gist")], 
});

Partial index

A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table.

import { table, integer, index } from "@monolayer/pg/schema";

const users = table({
  columns: {
    age: integer(),
  },
  indexes: [
    index(["name"]).where(sql.ref("age"), ">=", 18) 
  ],
});

Read more about how to buid where expressions in the Kysely Docs