# Indexes

<Callout type="info" title="Where this fits">
The columns exist. This page adds an index the planner can use. A unique constraint stays on [Keys](/docs/schema/keys-and-identity#keys). A rule the row must pass is a [check](/docs/schema/checks).
</Callout>

`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.`

<Callout type="info" title="SQL name">
The handle's `name` is the SQL name `schema()` will store. A [casing](/docs/schema/names-and-casing#casing) of `"snake"` has already been applied by the time the catalog is written.
</Callout>

<Callout type="error" title="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.`
</Callout>

### 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`.

```ts title="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)] }, // [!code focus]
);
```

#### order

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

```ts title="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), // [!code focus]
      index(columns.title, columns.published), // [!code focus]
    ],
  },
);
```

#### several

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

```ts title="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), // [!code focus]
      index(columns.published, columns.title), // [!code focus]
    ],
  },
);
```

#### sql name

A [SQL name](/docs/schema/names-and-casing#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`.

```ts title="sql-name.ts"
import { index, table, t } from "okmodel/pg";

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

#### casing

[Casing](/docs/schema/names-and-casing#casing) `"snake"` rewrites a name you did not set. `publishedAt` is stored as `published_at`, and the index is `notes_published_at_idx`.

```ts title="casing.ts"
import { index, schema, table, t } from "okmodel/pg";

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

export const app = schema({
  tables: [notes],
  casing: "snake", // [!code focus]
});
```

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`.

```ts title="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()] }, // [!code focus]
);
```

#### several

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

```ts title="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()] }, // [!code focus]
);
```

#### 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.

```ts title="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), // [!code focus]
      index(columns.email).unique(), // [!code focus]
    ],
  },
);
```

<Callout type="warning" title="Not a constraint">
A unique constraint and a unique index are different objects. A constraint belongs on [keys](/docs/schema/keys-and-identity#keys). A foreign key waits for that constraint. `.unique()` here does not create it.
</Callout>

### 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](/docs/schema/tenancy#on-the-schema).

Index: [/llms.txt](/llms.txt)