ParadeDB Dialect

Overview

ParadeDB is PostgreSQL with the pg_search
extension: Elasticsearch-quality full-text search (BM25 scoring, tokenizers,
fuzzy matching, highlighting) inside Postgres. It is the same server, protocol,
port and pg driver, so ParadeDBDialect extends PostgresDialect and
refuses nothing of PostgreSQL's — it only adds structured builders for what
pg_search brings.

Property	Value
Dialect name	`paradedb`
Extends	`PostgresDialect`
Driver	`pg`
Default port	5432
Extension	`pg_search`
Cloud SDK	none
Local runtime	self-hosted; no image in this repo

This dialect emits ParadeDB's v2 API (the default since 0.20.0): the
|||, &&&, ===, ### and @@@ operators with pdb.* functions and cast
modifiers. The legacy paradedb.* function API is slated for removal upstream
and is not emitted.

Quick start

Install

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

pg is the same driver the postgres dialect uses. pg-copy-streams is optional: only the features that need it load it.

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'paradedb',
  host: 'localhost',
  database: 'app',
  username: 'postgres',
  password: process.env.DB_PASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'paradedb',
  url: 'paradedb://postgres:secret@localhost:5432/app',
});

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
  meta: { type: DataTypes.JSONB() },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

meta is JSONB, which the containment query below needs.

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

JSON

Containment on the meta column:

import { Op } from 'ts-prorm-orm';

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

Transaction with a savepoint

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

Skip locked rows (`SKIP LOCKED`)

A worker that claims rows from a queue table should pass over rows another transaction holds rather than wait for them. lock with skipLocked inside a transaction:

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

Type mapping for the portable descriptors: STRING → VARCHAR(255), auto-increment INTEGER → SERIAL, BOOLEAN → BOOLEAN, DATE → TIMESTAMP, UUID → UUID, JSON → JSON. The type comparison lines the major engines up side by side.

Connection

import { ParadeDBDialect } from 'prorm/dialects/paradedb';

const dialect = new ParadeDBDialect({
  host: 'localhost',
  username: 'postgres',
  password: process.env.PG_PASSWORD,
  database: 'app',
});
await dialect.query(dialect.createExtensionSQL()); // CREATE EXTENSION IF NOT EXISTS pg_search

What differs from Postgres

ParadeDBDialect extends PostgresDialect (postgres) and
overrides 16 of its methods (Object.getOwnPropertyNames(ParadeDBDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
DML builders	`buildSearchCondition`, `buildSearchQuery`, `buildWhereClause`
DDL and schema	`addIndex`, `buildCreateSearchIndexSql`, `createExtensionSQL`, `createSearchIndex`
Other	`buildScoreExpression`, `buildSnippetExpression`, `buildTokenizerSql`, `compileSearch`, `fuzzySql`, `numberSql`, `operandSql`, `partialPredicate`, `slopSql`

The ParadeDB index

dialect.buildCreateSearchIndexSql('mock_items', {
  name: 'search_idx',
  keyField: 'id',
  fields: [
    { column: 'description', tokenizer: { name: 'simple', options: { stemmer: 'english' } } },
    'category',
    'rating',
  ],
});

keyField is required, emitted first, and must be untokenized (a
tokenizer on it throws). It must be unique — usually the primary key.

One ParadeDB index per table; index every column you search, filter, sort or
aggregate on.

accessMethod: 'paradedb' emits USING paradedb (0.25.0+); the default
bm25 is a supported alias on every v2 release.

concurrently: true (ParadeDB's recommended rebuild path), ifNotExists,
and a partial where object are supported.

Tokenizers

{ name, min, max, pattern, language, options } renders as a pdb.* type:

Spec	SQL
`{ name: 'literal' }`	`pdb.literal`
`{ name: 'ngram', min: 3, max: 3 }`	`pdb.ngram(3, 3)`
`{ name: 'edge_ngram', min: 2, max: 5 }`	`pdb.edge_ngram(2, 5)`
`{ name: 'lindera', language: 'japanese' }`	`pdb.lindera(japanese)`
`{ name: 'regex_pattern', pattern: '\\w+' }`	`pdb.regex_pattern('\w+')`
`{ name: 'simple', options: { k1: 0.9, b: 0.3 } }`	`pdb.simple('k1=0.9', 'b=0.3')`

Known names: unicode_words (the default), simple, whitespace, icu,
literal, literal_normalized, source_code, chinese_compatible, jieba,
lindera, ngram, edge_ngram, regex_pattern. Option values are escaped;
option names are validated.

Searching

Searches are data, built with paradedbSearch and compiled by the dialect —
every query string is a bound $n parameter:

Builder	SQL
`match('description', 'running shoes')`	`"description" \|\|\| $1::text`
`match(..., { mode: 'all' })`	`"description" &&& $1::text`
`match(..., ['running', 'shoes'])`	`... \|\|\| $1::text[]` (exact tokens)
`match(..., { fuzzy: 2 })`	`... \|\|\| $1::pdb.fuzzy(2)`
`match(..., { fuzzy: 2, boost: 2 })`	`... $1::pdb.fuzzy(2)::pdb.boost(2)`
`match(..., { constScore: 1 })`	`... $1::pdb.const(1)`
`match(..., { tokenizer: { name: 'whitespace' } })`	`... $1::pdb.whitespace`
`term('category', 'footwear')`	`"category" === $1::text`
`term(..., 'rann', { fuzzy: { distance: 1, prefix: true } })`	`=== $1::pdb.fuzzy(1, t)`
`phrase('description', 'shoes running', { slop: 2 })`	`### $1::pdb.slop(2)`
`regex('description', 'key.*')`	`@@@ pdb.regex($1::text)`
`parse('id', 'description:(sleek shoes) AND rating:>3', { lenient: true })`	`"id" @@@ pdb.parse($1::text, lenient => true)`
`all('id')`	`"id" @@@ pdb.all()`
`and(...)`, `or(...)`, `not(...)`	parenthesised combinations

Fuzzy distance is 0–2; transpositionCostOne renders pdb.fuzzy(d, f, t).

Only documented modifier combinations compile

ParadeDB documents specific cast chains (fuzzy + boost, slop on arrays, ...).
Anything else — tokenizer + fuzzy, boost + const, slop + boost, fuzzy on an
array query — throws, rather than emitting a chain whose meaning is
unspecified.

In a where-object

Put a search (or an array of them) under the PARADEDB_SEARCH symbol and every
where-consuming builder — findAll's SELECT, UPDATE and DELETE — compiles it
after the ordinary conditions, numbering its parameters after theirs:

import { paradedbSearch as s, PARADEDB_SEARCH } from 'prorm/dialects/paradedb';

dialect.buildSelectQuery({
  tableName: 'mock_items',
  where: {
    in_stock: true,
    rating: { [Op.gte]: 4 },
    [PARADEDB_SEARCH]: s.match('description', 'running shoes', { fuzzy: 1 }),
  },
  limit: 5,
});

The key is recognised at the top level of the where-object only — combine
searches with s.or(...)/s.and(...), not inside $or.
buildSearchCondition(search, startAt) returns the bare { sql, values }.

Ranked results: score and snippets

dialect.buildSearchQuery({
  tableName: 'mock_items',
  attributes: ['id', 'description'],
  search: s.match('description', 'shoes'),
  score: { keyField: 'id' },
  snippets: [{ column: 'description', startTag: '<i>', endTag: '</i>' }],
  orderByScore: true,
  order: [['id', 'ASC']], // tie-breaker: equal scores are otherwise unordered
  limit: 5,
});

buildScoreExpression(key) and buildSnippetExpression(col, opts) return the
bare expressions. Both functions are only valid in a query that has a ParadeDB
predicate.

Other indexes

addIndex / createIndex behave as in PostgreSQL (partial indexes included).
Asking addIndex for using: 'bm25' throws and points at
createSearchIndex, because that index cannot exist without a key_field.

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Capability	`paradedb`
Transactions atomic	yes
Savepoints	yes
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only yes; deferrable yes; constraint deferral yes
RETURNING	`RETURNING` on INSERT
Upsert	`INSERT … ON CONFLICT (…) DO UPDATE`
JSON column	`JSON` (`JSONB` → `JSONB`)
Vector column	`VECTOR(3)`
Array column	`INTEGER[]`
WHERE builder	35 operator keys; `$and`/`$or`/`$not`; `$exists`: no; `$in` subquery: no; expression operands: no
SQL objects	family `postgres`: triggers yes; procedures yes; functions `multiStatement`; materialized views native; sequences yes; enums `named`; RLS `policy`
Streaming	`queryStream()` present; `LISTEN` present; `createExtension()` present

Testing

tests/dialects/paradedb.test.ts exercises the dialect with a mocked driver, so nothing touches a server.

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

JSON#

Transaction with a savepoint#

Skip locked rows (SKIP LOCKED)#

Connection#

What differs from Postgres#

The ParadeDB index#

Tokenizers#

Searching#

Only documented modifier combinations compile#

In a where-object#

Ranked results: score and snippets#

Other indexes#

Capabilities#

Testing#

See also#