Extensions | OKModel

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 column needs that name.

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

export default schema({
  tables: [people],
  extensions: [citext()], 
});

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.

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

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

index

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

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

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

unaccent

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

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é") }, 
    limit: 1,
  });
}

hstore

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

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") }, 
    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.

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") }, 
    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.

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

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

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) }, 
    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).

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",
      ),
    }, 
    limit: 1,
  });
}

name

extension("name") from okmodel/ext declares that name. There is no typed operator. uuid-ossp is this form. A ltree column can use ltree() from okmodel/pg/ltree instead.

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

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.

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.

OKM1813

The same name twice is Extension citext is defined twice.

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.

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

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

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

schema

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

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

relocatable

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

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

OKM1020

A version that is neither a dotted pin nor a >= floor is Extension version latest must be a dotted version or a >= floor.

OKM1811

An extension the server does not have is Extension citext is not available on the server. Nothing was changed.

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.

together

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

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

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.

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

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.

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", 
    encode: (value: string) => value,
    decode: (wire: string) => wire,
  }),
});

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

OKM1814

A later move of that extension is Extension hstore is not relocatable, so it cannot move to public.

On this page