Postgres extensions

Typed wrappers for the four extensions Zanith treats as first-class. Compose them inside the relational query builder; the engine emits the right operator for the right dialect and parameterises every value.

#Declaring an extension

emitCreateExtension emits the DDL the migration runner uses; you rarely need to call it by hand. KNOWN_EXTENSIONS lists the ones the engine has typed wrappers for.

4 lines


1import { emitCreateExtension } from 'zanith';
2 
3await adapter.executeRaw(emitCreateExtension('pgvector'));
4// CREATE EXTENSION IF NOT EXISTS "vector";

#pgvector — embeddings

Five distance helpers that produce expressions usable in where, orderBy, and select callbacks.

Helper	Operator	Use when
`vectorLit(values)`	`'[1, 2, 3]'::vector`	Bind a query vector as a parameter.
`l2Distance(a, b)`	`a <-> b`	Euclidean — geometric similarity.
`innerProductDistance(a, b)`	`a <#> b`	Negative inner product.
`cosineDistance(a, b)`	`a <=> b`	Direction-only — the default for embeddings.
`l1Distance(a, b)`	`a <+> b`	Manhattan — sparse / count vectors.

13 lines


1import { cosineDistance, vectorLit, fieldRef } from 'zanith';
2 
3const nearest = await db.document.query()
4  .select(({ document }) => ({
5    id: document.id,
6    title: document.title,
7    distance: cosineDistance(document.embedding, vectorLit(queryEmbedding)),
8  }))
9  .orderBy(({ document }) =>
10    cosineDistance(document.embedding, vectorLit(queryEmbedding)).asc(),
11  )
12  .limit(10)
13  .execute();

#pg_trgm — trigram similarity

Helper	SQL
`trigramSimilar(a, b)`	`a % b` — boolean above the `pg_trgm.similarity_threshold`.
`trigramDistance(a, b)`	`a <-> b` — distance, lower is closer.
`wordSimilar(a, b)`	`a <% b` — best-substring match.
`similarity(a, b)`	`similarity(a, b)` — score in [0, 1].
`wordSimilarity(a, b)`	`word_similarity(a, b)`.

7 lines


1import { trigramSimilar, similarity, lit } from 'zanith';
2 
3const fuzzy = await db.user.query()
4  .where(({ user }) => trigramSimilar(user.name, lit('alic')))
5  .orderBy(({ user }) => similarity(user.name, lit('alic')).desc())
6  .limit(10)
7  .execute();

#pgcrypto — hashes, HMAC, encryption, UUIDs

Helper	SQL
`genRandomUuid()`	`gen_random_uuid()`
`digest(data, type)`	`digest(data, type)` — `'sha256'` etc.
`hmac(data, key, type)`	`hmac(data, key, type)`
`crypt(password, salt)`	`crypt(password, salt)` — bcrypt-style hashing.
`genSalt(algorithm, rounds?)`	`gen_salt('bf', 12)`
`pgpSymEncrypt(data, key)`	`pgp_sym_encrypt(data, key)`
`pgpSymDecrypt(data, key)`	`pgp_sym_decrypt(data, key)`

6 lines


1import { crypt, genSalt, lit } from 'zanith';
2 
3await db.user.insert({
4  email: '[email protected]',
5  passwordHash: crypt(lit(password), genSalt(lit('bf'), lit(12))),
6}).execute();

#pg_cron — scheduled jobs

pg_cron runs SQL on a schedule from inside Postgres itself. The wrappers produce CompiledQuery objects so you can schedule, list, and unschedule jobs from your application.

Helper	Purpose
`cronSchedule(name, schedule, command)`	`cron.schedule(name, schedule, command)`
`cronScheduleInDatabase(name, schedule, command, database)`	Same, but pin to another database.
`cronUnschedule(name)`	`cron.unschedule(name)`
`cronUnscheduleById(jobId)`	`cron.unschedule(jobid)`
`cronListJobs()`	`SELECT … FROM cron.job`
`cronJobHistory(name, limit?)`	`SELECT … FROM cron.job_run_details WHERE jobid = …`

11 lines


1import { cronSchedule, cronJobHistory } from 'zanith';
2 
3// Nightly cleanup at 02:30 UTC.
4await adapter.execute(cronSchedule(
5  'cleanup-expired-sessions',
6  '30 2 * * *',
7  "DELETE FROM sessions WHERE expires_at < now()",
8));
9 
10// Inspect the last 50 runs:
11const { rows } = await adapter.execute(cronJobHistory('cleanup-expired-sessions'));

#Composability with the search system

search() already composes vector and trigram inside one ranked query. Reach for the standalone helpers when you need something search()can't express — distance in the projection, an extra rank tweak in orderBy, or a fuzzy join condition.