Indexes | OKModel

Where this fits

The columns exist. This page adds an index the planner can use. A unique constraint stays on Keys. A rule the row must pass is a check.

indexes is a function. It receives one handle per column and returns an array of index() calls. schema() stores those indexes. A return value that is not an array is OKM1060: Table notes indexes must return an array.

SQL name

The handle's name is the SQL name schema() will store. A casing of "snake" has already been applied by the time the catalog is written.

OKM1060

An empty call is Index on notes needs a column. A value that is not a handle is Index on notes must be built from column handles. A return value that is not an array is Table notes indexes must return an array.

Index

index(...) takes column handles, in index order. It needs at least one. You do not pass a name, a predicate, or a using method. Each argument is columns.field. The first argument is the first column of the index. The index is named {table}_{columns}_idx, using the SQL column names joined by _.

one

One column is one index. This one is notes_title_idx.

one-column.ts

import { index, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
  },
  { indexes: (columns) => [index(columns.title)] }, 
);

order

Column order is the index order. These two calls are different indexes: notes_published_title_idx and notes_title_published_idx.

order.ts

import { index, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
    published: t.boolean().default(false),
  },
  {
    indexes: (columns) => [
      index(columns.published, columns.title), 
      index(columns.title, columns.published), 
    ],
  },
);

several

Several indexes are several calls in the same array. A lookup on title and a lookup on published then title are stored separately.

several.ts

import { index, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
    published: t.boolean().default(false),
  },
  {
    indexes: (columns) => [
      index(columns.title), 
      index(columns.published, columns.title), 
    ],
  },
);

sql name

A SQL name you set is the name that is joined. Here the table is note and the column is published_at, so the index is note_published_at_idx.

sql-name.ts

import { index, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    publishedAt: t.boolean().sqlName("published_at"), 
  },
  {
    sqlName: "note", 
    indexes: (columns) => [index(columns.publishedAt)],
  },
);

casing

Casing "snake" rewrites a name you did not set. publishedAt is stored as published_at, and the index is notes_published_at_idx.

casing.ts

import { index, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  {
    id: t.identity(),
    publishedAt: t.boolean(), 
  },
  { indexes: (columns) => [index(columns.publishedAt)] },
);

export const app = schema({
  tables: [notes],
  casing: "snake", 
});

A new table adds the index with create index. A table that already exists adds it with create index concurrently. That step is not inside a transaction.

Unique

.unique() marks that index unique. The columns and their order stay the ones you passed to index().

one

One column is the usual case. This index is users_email_idx.

unique-column.ts

import { index, table, t } from "okmodel/pg";

export const users = table(
  "users",
  {
    id: t.identity(),
    email: t.text(),
  },
  { indexes: (columns) => [index(columns.email).unique()] }, 
);

several

Several columns are unique together, in the order you pass. This index is notes_published_title_idx.

unique-columns.ts

import { index, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
    published: t.boolean().default(false),
  },
  { indexes: (columns) => [index(columns.published, columns.title).unique()] }, 
);

beside

A plain index and a unique index can share the array. title is notes_title_idx. email is notes_email_idx, and only that one is unique.

mixed.ts

import { index, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
    email: t.text(),
  },
  {
    indexes: (columns) => [
      index(columns.title), 
      index(columns.email).unique(), 
    ],
  },
);

Not a constraint

A unique constraint and a unique index are different objects. A constraint belongs on keys. A foreign key waits for that constraint. .unique() here does not create it.

On this page