IndexesCopy page

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.

Index

index(...) takes column handles, in index order. It needs at least one. You do not pass 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 _. .name("lookup_title") stores that SQL name instead. okm pull writes .name() when the database name is not the generated one, including on a partial index. A predicate or expression is set on that same call. A non-default btree operator class, such as text_pattern_ops, is kept as using btree (...).

one

One column is one index. This one is notes_title_idx.

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.

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.

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.

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.

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().

Only a btree index can be unique. .unique() on a gin or gist index, such as trgm.gin(columns.title.name).unique(), fails when the schema is built with OKM1060 and names the index and its method.

one

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

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.

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.

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(), 
    ],
  },
);

Tenant tables

On a tenant table an ordinary index leads with the tenant key, unless this index calls .global("reason") or the table sets tenantIndexes: false. .scoped() puts the key first on that ordinary index. A unique index is then unique inside one tenant: two tenants may store the same value. A using index is not prefixed. The default and .scoped() both throw OKM1706 when the columns do not already lead with the tenant key: Index on notes starts with title. A using tail is not prefixed. .global("reason") is the call that works. Putting the tenant key first in the declaration does not prefix a using tail. An index that already leads with the key is left as written. See tenancy.