# 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/define/table/keys). A rule the row must pass is a [check](/docs/define/table/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/define/schema/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 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`.

```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/define/table/names#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/define/schema/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()`.

#### 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/define/table/keys). A foreign key waits for that constraint. `.unique()` here does not create it.
</Callout>