# Extensions

`extensions` is a list. Each value contributes its catalog record when the catalog is first read. `schema()` does not install the extension. Migrate writes `create extension` before the table that needs it. An omitted list, an empty list, and a value that is not a list add no extension record.

`citext()`, `pgTrgm()`, and `extension("name")` take the same options.

### citext

`citext()` from `okmodel/pg/citext` declares `citext`. A [citext](/docs/define/table/columns#citext) column needs that name.

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

const people = table("people", {
  id: t.identity(),
  email: t.citext(), // [!code focus]
});

export default schema({
  tables: [people],
  extensions: [citext()], // [!code focus]
});
```

### trigram

`pgTrgm()` from `okmodel/pg/pg_trgm` declares `pg_trgm`. `similar` and `wordSimilar` compare a string. `gin` and `gist` build an index on a SQL column name.

#### where

`similar` is `column % query`. `wordSimilar` is `column <% query`. Both take the text to compare.

```ts title="where.ts"
import { connect } from "okmodel/pg/postgresjs";
import { pgTrgm } from "okmodel/pg/pg_trgm";
import { schema, table, t } from "okmodel/pg";

const trgm = pgTrgm();

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const app = schema({
  tables: [notes],
  extensions: [trgm], // [!code focus]
});

export async function search(url: string, title: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    where: { title: trgm.similar(title) }, // [!code focus]
    limit: 1,
  });
}
```

#### index

`gin` uses `gin_trgm_ops`. `gist` uses `gist_trgm_ops`. The argument is the SQL column name.

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

const trgm = pgTrgm();

const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
  },
  { indexes: (column) => [trgm.gin(column.title.name)] }, // [!code focus]
);

export default schema({
  tables: [notes],
  extensions: [trgm],
});
```

### unaccent

`unaccent()` from `okmodel/pg/unaccent` declares `unaccent`. `unaccent(value)` is `unaccent(column) = unaccent(value)`.

```ts title="unaccent.ts"
import { connect } from "okmodel/pg/postgresjs";
import { unaccent } from "okmodel/pg/unaccent";
import { schema, table, t } from "okmodel/pg";

const fold = unaccent();

const words = table("words", {
  id: t.identity(),
  title: t.text(),
});

const app = schema({
  tables: [words],
  extensions: [fold],
});

export async function folded(url: string) {
  await using db = connect(url, { schema: app });
  return db.words.find({
    where: { title: fold.unaccent("Café") }, // [!code focus]
    limit: 1,
  });
}
```

### hstore

`hstore()` from `okmodel/pg/hstore` declares `hstore`. `hasKey` is `column ? key`. `gin` uses `gin_hstore_ops`. `gist` uses `gist_hstore_ops`.

```ts title="hstore.ts"
import { connect } from "okmodel/pg/postgresjs";
import { hstore } from "okmodel/pg/hstore";
import { custom, schema, table, t } from "okmodel/pg";

const store = hstore();

const docs = table("docs", {
  id: t.identity(),
  attrs: custom<string>({
    sqlType: "hstore",
    encode: (value) => value,
    decode: (wire) => wire,
    extension: "hstore",
  }),
});

const app = schema({
  tables: [docs],
  extensions: [store],
});

export async function withKey(url: string) {
  await using db = connect(url, { schema: app });
  return db.docs.find({
    where: { attrs: store.hasKey("a") }, // [!code focus]
    limit: 1,
  });
}
```

### ltree

`ltree()` from `okmodel/pg/ltree` declares `ltree`. `ancestorOf` is `column @> path`. `descendantOf` is `column <@ path`. `gist` uses `gist_ltree_ops`. This is not the parent-column walk.

```ts title="ltree.ts"
import { connect } from "okmodel/pg/postgresjs";
import { ltree } from "okmodel/pg/ltree";
import { schema, table, t } from "okmodel/pg";

const tree = ltree();

const places = table("places", {
  id: t.identity(),
  path: t.ltree(),
});

const app = schema({
  tables: [places],
  extensions: [tree],
});

export async function parents(url: string) {
  await using db = connect(url, { schema: app });
  return db.places.find({
    where: { path: tree.ancestorOf("Top.Science.Physics") }, // [!code focus]
    limit: 1,
  });
}
```

### btree_gist and btree_gin

`btreeGist()` from `okmodel/pg/btree_gist` indexes an integer with `gist_int4_ops`. `btreeGin()` from `okmodel/pg/btree_gin` indexes an integer with `int4_ops`.

```ts title="btree.ts"
import { btreeGin } from "okmodel/pg/btree_gin";
import { btreeGist } from "okmodel/pg/btree_gist";
import { schema, table, t } from "okmodel/pg";

const gist = btreeGist();
const gin = btreeGin();

const nums = table(
  "nums",
  {
    id: t.identity(),
    n: t.integer(),
    m: t.integer(),
  },
  { indexes: (column) => [gist.gist(column.n.name), gin.gin(column.m.name)] }, // [!code focus]
);

export default schema({
  tables: [nums],
  extensions: [gist, gin],
});
```

### fuzzystrmatch

`fuzzystrmatch()` from `okmodel/pg/fuzzystrmatch` declares `fuzzystrmatch`. `levenshtein(value, distance)` is `levenshtein(column, value) = distance`. `daitchMokotoff` needs extension 1.2 and PostgreSQL 16 (OKM1812).

```ts title="fuzzy.ts"
import { connect } from "okmodel/pg/postgresjs";
import { fuzzystrmatch } from "okmodel/pg/fuzzystrmatch";
import { schema, table, t } from "okmodel/pg";

const fuzzy = fuzzystrmatch();

const words = table("words", {
  id: t.identity(),
  title: t.text(),
});

const app = schema({
  tables: [words],
  extensions: [fuzzy],
});

export async function close(url: string) {
  await using db = connect(url, { schema: app });
  return db.words.find({
    where: { title: fuzzy.levenshtein("sitting", 3) }, // [!code focus]
    limit: 1,
  });
}
```

### pgcrypto

`pgcrypto()` from `okmodel/pg/pgcrypto` declares `pgcrypto`. `digest(algorithm, hex)` compares `encode(digest(column, algorithm), 'hex')` with that hex. `fipsMode` needs extension 1.4 and PostgreSQL 18 (OKM1812).

```ts title="digest.ts"
import { connect } from "okmodel/pg/postgresjs";
import { pgcrypto } from "okmodel/pg/pgcrypto";
import { schema, table, t } from "okmodel/pg";

const crypto = pgcrypto();

const words = table("words", {
  id: t.identity(),
  title: t.text(),
});

const app = schema({
  tables: [words],
  extensions: [crypto],
});

export async function hashed(url: string) {
  await using db = connect(url, { schema: app });
  return db.words.find({
    where: {
      title: crypto.digest(
        "sha256",
        "ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad",
      ),
    }, // [!code focus]
    limit: 1,
  });
}
```

### name

`extension("name")` from `okmodel/ext` declares that name. There is no typed operator. `uuid-ossp` is this form. A [ltree](/docs/define/table/columns#search) column can use `ltree()` from `okmodel/pg/ltree` instead.

```ts title="name.ts"
import { extension } from "okmodel/ext";
import { schema, table, t } from "okmodel/pg";

const keys = table("keys", {
  id: t.identity(),
});

export default schema({
  tables: [keys],
  extensions: [extension("uuid-ossp")], // [!code focus]
});
```

<Callout type="error" title="OKM1020">
A `citext` or `ltree` column, when this list is omitted or empty, is `column email depends on extension citext, which is not in the catalog.` The name in that sentence is the column, then the extension.
</Callout>

<Callout type="error" title="OKM1810">
A list that declares a different extension is `Extension citext is used but not declared.` A value in the list that is not an extension is `schema({ extensions }) must be a list of extensions.`
</Callout>

<Callout type="error" title="OKM1813">
The same name twice is `Extension citext is defined twice.`
</Callout>

### options

Every call takes `version`, `schema`, and `relocatable`. Omitted, the version is unpinned, the schema is `public`, and `relocatable` is `true`. An unpinned record matches any installed version. Schema and `relocatable` still have to match.

#### version

`1.6` is an exact pin. Migrate writes `version '1.6'`. `>=1.6` is a floor. It is stored and is not written. A shorter tail compares as zero.

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

const people = table("people", {
  id: t.identity(),
  email: t.citext(),
});

export default schema({
  tables: [people],
  extensions: [citext({ version: "1.6" })], // [!code focus]
});
```

A floor asks for that version or newer and does not pin `create extension`.

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

const people = table("people", {
  id: t.identity(),
  email: t.citext(),
});

export default schema({
  tables: [people],
  extensions: [citext({ version: ">=1.6" })], // [!code focus]
});
```

#### schema

`schema` is where the extension's objects are installed. The default is `public`.

```ts title="schema-name.ts"
import { extension } from "okmodel/ext";
import { schema, table, t } from "okmodel/pg";

const places = table("places", {
  id: t.identity(),
  path: t.ltree(),
});

export default schema({
  tables: [places],
  extensions: [extension("ltree", { schema: "extensions" })], // [!code focus]
});
```

#### relocatable

`relocatable: false` refuses a later move. The default `true` allows `alter extension … set schema`.

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

const people = table("people", {
  id: t.identity(),
  email: t.citext(),
});

export default schema({
  tables: [people],
  extensions: [citext({ relocatable: false })], // [!code focus]
});
```

<Callout type="error" title="OKM1020">
A version that is neither a dotted pin nor a `>=` floor is `Extension version latest must be a dotted version or a >= floor.`
</Callout>

<Callout type="error" title="OKM1811">
An extension the server does not have is `Extension citext is not available on the server. Nothing was changed.`
</Callout>

<Callout type="error" title="OKM1814">
A move of a non-relocatable extension is `Extension citext is not relocatable, so it cannot move to extensions.` A lower pin is `Extension citext cannot move from 1.6 to 1.5. Postgres cannot lower an extension version.` No update path is `Extension citext has no update path from 1.4 to 1.6. Nothing was changed.` Dependents still using it are `Extension citext still has dependents. Nothing was dropped.`
</Callout>

### together

`citext` for the column, `pg_trgm` for the index, and `ltree` by name.

```ts title="together.ts"
import { extension } from "okmodel/ext";
import { citext } from "okmodel/pg/citext";
import { pgTrgm } from "okmodel/pg/pg_trgm";
import { schema, table, t } from "okmodel/pg";

const trgm = pgTrgm({ version: "1.6" });

const people = table(
  "people",
  {
    id: t.identity(),
    email: t.citext(),
    title: t.text(),
    path: t.ltree(),
  },
  { indexes: (column) => [trgm.gin(column.title.name)] },
);

export default schema({
  tables: [people],
  extensions: [citext(), trgm, extension("ltree")], // [!code focus]
});
```

### Create your own

`citext()` and `pgTrgm()` are the built-ins above. A name they do not cover is `extension()` from `okmodel/ext`. That call has the same options and no typed operator.

#### Simple

`unaccent` is one name. `okm generate` writes `create extension "unaccent" schema "public"` before the table.

```ts title="own.ts"
import { extension } from "okmodel/ext";
import { schema, table, t } from "okmodel/pg";

const documents = table("documents", {
  id: t.identity(),
  body: t.text(),
});

export default schema({
  tables: [documents],
  extensions: [extension("unaccent")], // [!code focus]
});
```

#### Advanced

`version` is an exact pin and is written. `schema` is the install schema. It has to exist already: `okm migrate apply` does not create it. Run `create schema "extensions"` on the database before apply. The type then lives in that schema, not on the search path, so the column names `extensions.hstore`. `relocatable: false` refuses a later move. `t.custom({ extension })` records that the column needs this name, so the list has to declare it.

`okm generate` writes `create extension "hstore" schema "extensions" version '1.8'` before the table.

```ts title="own-advanced.ts"
import { extension } from "okmodel/ext";
import { schema, table, t } from "okmodel/pg";

const places = table("places", {
  id: t.identity(),
  attrs: t.custom({
    sqlType: "extensions.hstore",
    extension: "hstore", // [!code focus]
    encode: (value: string) => value,
    decode: (wire: string) => wire,
  }),
});

export default schema({
  tables: [places],
  extensions: [
    extension("hstore", {
      version: "1.8", // [!code focus]
      schema: "extensions", // [!code focus]
      relocatable: false, // [!code focus]
    }),
  ],
});
```

<Callout type="error" title="OKM1814">
A later move of that extension is `Extension hstore is not relocatable, so it cannot move to public.`
</Callout>