MonetDB Dialect

Reference for the monetdb SQL dialect.

Source: monetdb
Tests: monetdb

Overview

MonetDB is its own independent open-source
column-store analytical database — not a fork of PostgreSQL or MySQL, and not
related to them at the wire-protocol level. Its SQL grammar follows the SQL
standard closely enough, though (double-quoted identifiers, LIMIT n OFFSET m, ILIKE, CREATE SCHEMA, CREATE TABLE IF NOT EXISTS, sequences,
triggers, savepoints and a MERGE statement) that the PostgreSQL builders'
WHERE / ORDER BY / GROUP BY / JOIN / CTE output is also valid MonetDB
SQL. So MonetDBDialect extends PostgresDialect and keeps those
builders — every value still leaves them as a $n placeholder with the value
beside it — while replacing the entire connection layer, since MonetDB speaks
its own MAPI protocol, not PostgreSQL's.

MonetDBDialect is registered with Prorm, so new Prorm({ dialect: 'monetdb', ... }) works. It can also be constructed directly:

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

const d = new Prorm({
  dialect: 'monetdb',
  host: 'localhost',
  // port defaults to MONETDB_DEFAULT_PORT (50000)
  database: 'demo',           // defaults to 'demo'
  username: 'monetdb',
  password: process.env.MONETDB_PASSWORD,
  dialectOptions: {
    connectOptions: { language: 'sql' }, // passed straight through to `new Connection(...)`
  },
});
await d.connect();

d.name === 'monetdb'; d.library === 'monetdb'.

Quick start

Install

$ npm install ts-prorm-orm monetdb

$ pnpm add ts-prorm-orm monetdb

$ yarn add ts-prorm-orm monetdb

$ bun add ts-prorm-orm monetdb

Connect

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

export const db = new Prorm({
  dialect: 'monetdb',
  host: 'localhost',
  database: 'demo',
  username: 'monetdb',
  password: process.env.MONETDB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'monetdb',
  url: 'monetdb://monetdb:monetdb@localhost:50000/demo',
});

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

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

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 IF NOT EXISTS "users" ("id" INTEGER AUTO_INCREMENT NOT NULL, "email" VARCHAR(255) NOT NULL UNIQUE, "name" VARCHAR(100), "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("id"));

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

-- insert
INSERT INTO "users" ("email", "name") VALUES ('ada@example.com', 'Ada');

-- update
UPDATE "users" SET "name" = 'Grace', "updatedAt" = TIMESTAMP '2026-09-27 05:01:42.307' WHERE "email" = 'ada@example.com';

-- delete
DELETE FROM "users" WHERE "id" = 1;

Upsert

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

MERGE INTO "users" AS tgt USING (SELECT 1 AS "id", 'ada@example.com' AS "email", 'Ada Lovelace' AS "name") AS src ON tgt."id" = src."id" WHEN MATCHED THEN UPDATE SET "email" = src."email", "name" = src."name" WHEN NOT MATCHED THEN INSERT ("id", "email", "name") VALUES (src."id", src."email", src."name");

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

SELECT * FROM "users" WHERE "name" LIKE 'A%' ORDER BY "id" ASC LIMIT 20 OFFSET 40;

JSON

[!NOTE] No JSON operator in the WHERE builder
DataTypes.JSON() maps to JSON, but this dialect's WHERE builder refuses Op.contains on it: MonetDB does not support the @> / <@ containment operators. The condition used a PostgreSQL-only operator; MonetDB reads JSON with json.filter / json.text.

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

START TRANSACTION;
INSERT INTO "users" ("email", "name") VALUES ('grace@example.com', 'Grace');
SAVEPOINT "prorm_sp_1";
DELETE FROM "users" WHERE "id" = 1;
ROLLBACK TO SAVEPOINT "prorm_sp_1";
RELEASE SAVEPOINT "prorm_sp_1";
COMMIT;

Table sampling

sample on the select builder reads a random fraction of the table, for estimates over large tables:

const sample = db.getDialectInstance()!.buildSelectQuery({
  tableName: 'users',
  sample: { method: 'BERNOULLI', percent: 10 },
});
await db.query(sample.sql);

SELECT * FROM "users" SAMPLE 0.1;

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

Connection

connect() calls new Connection(buildConnectOptions()) then await conn.connect(); disconnect() closes it. getConnection() returns the raw
driver connection; isConnected() reflects whether one is open.

Query execution

Values are bound by the server, not inlined: query() rewrites $n (array
replacements) or :name (object replacements) placeholders in the SQL to
MonetDB's ? markers via toWire(), then:

• no params → await conn.execute(sql);
• params → await conn.prepare(sql), await stmt.execute(...params), await stmt.release().

dialect.buildSelectQuery({ tableName: 'users', where: { name: "O'Brien" }, limit: 5 });
// sql:    SELECT * FROM "users" WHERE "name" = $1 LIMIT 5
// after toWire + prepare: 'SELECT * FROM "users" WHERE "name" = ? LIMIT 5', args: ["O'Brien"]

toDriverValue() converts bind values for the wire: Date → 'YYYY-MM-DD HH:MI:SS.fff' (UTC), bigint → string, Buffer → uppercase hex, a plain
object → JSON.stringify, and an array throws (MonetDB has no array type;
bind the elements individually). A result with no columns reports
rowCount from affectedRows; otherwise each row is zipped into an object
keyed by column name.

getDatabaseVersion() reads sys.environment where name = 'monet_version'.

Transactions

startTransaction() opens a dedicated connection, runs START TRANSACTION on it, and returns a MonetDBTransaction; commit() /
rollback() run COMMIT / ROLLBACK on that connection and close it.
Savepoints are inherited unchanged from PostgresDialect
(createSavepointSQL('sp1') → 'SAVEPOINT sp1') — MonetDB has them.

MonetDB uses optimistic concurrency control: there is no per-transaction
isolation level to pick, so passing isolationLevel throws.

What differs from Postgres

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

Escaping

escape() doubles backslashes as well as apostrophes — MonetDB reads C-style
backslash escapes inside ordinary '...' literals, so a value like
C:\temp would otherwise arrive with a tab in it:

d().escape("C:\\temp\\it's"); // 'C:\\temp\\it''s'

Dates become typed TIMESTAMP '...' literals in UTC, and Buffers a BLOB '<hex>' literal:

d().escape(new Date(Date.UTC(2024, 4, 6, 7, 8, 9, 10))); // TIMESTAMP '2024-05-06 07:08:09.010'
d().escape(Buffer.from([0xde, 0xad]));                   // BLOB 'DEAD'

quoteTable(table, schema) never elides the schema — MonetDB's default
schema is sys, not public — so quoteTable('t', 'sys') is "sys"."t".

Type mapping

ARRAY, HSTORE, VECTOR, RANGE, CIDR, MACADDR, GEOGRAPHY and any other unknown
key throw ("MonetDB does not support the column type '...'").

CREATE TABLE kinds

buildCreateTableSql supports MonetDB's table kinds through kind on
MonetDBTableOptions: MERGE, REPLICA, REMOTE, UNLOGGED, LOCAL TEMPORARY, GLOBAL TEMPORARY (default: an ordinary table).

d().buildCreateTableSql('ev', cols, { kind: 'MERGE', mergePartitionBy: { type: 'RANGE', column: 'd' } });
// CREATE MERGE TABLE "ev" ("id" INTEGER, "d" DATE) PARTITION BY RANGE ON ("d")

d().buildCreateTableSql('r', { id: { type: INT } }, {
  kind: 'REMOTE',
  remote: { host: 'node2.local', database: 'db', schema: 'sys', table: 'orders', user: 'u', password: "p'w" },
});
// CREATE REMOTE TABLE "r" ("id" INTEGER) ON 'mapi:monetdb://node2.local:50000/db/sys/orders' WITH USER 'u' PASSWORD 'p''w'

d().buildCreateTableSql('tmp', { id: { type: INT } }, { kind: 'LOCAL TEMPORARY' });
// CREATE LOCAL TEMPORARY TABLE "tmp" ("id" INTEGER) ON COMMIT PRESERVE ROWS

buildMapiUri() validates every host/database/schema/table token against
^[A-Za-z0-9._-]+$ before building the mapi:monetdb://host:port/db[/schema/table]
URI. A raw partitionBy throws, pointing at kind: 'MERGE' with
mergePartitionBy instead; mergePartitionBy without kind: 'MERGE',
remote without kind: 'REMOTE', and onCommit on a non-TEMPORARY kind
all throw.

Merge-table partitions

d().buildAddPartitionSql('m', 'p1', { range: { from: 1, to: 100 }, withNullValues: true });
// ALTER TABLE "m" ADD TABLE "p1" AS PARTITION FROM 1 TO 100 WITH NULL VALUES
d().buildAddPartitionSql('m', 'p2', { range: { from: null, to: '2024-01-01' } });
// ALTER TABLE "m" ADD TABLE "p2" AS PARTITION FROM RANGE MINVALUE TO '2024-01-01'
d().buildAddPartitionSql('m', 'p3', { values: ["x'y", 'z'] });
// ALTER TABLE "m" ADD TABLE "p3" AS PARTITION IN ('x''y', 'z')
d().buildDropPartitionSql('m', 'p1');
// ALTER TABLE "m" DROP TABLE "p1"

addMergePartition / dropMergePartition run these. Exactly one of range,
values or forNullValues may be given.

Indexes

ORDERED and IMPRINTS are MonetDB's column-store index kinds and cover
exactly one column; every index (including these) is treated by MonetDB as
an optimizer hint, not a constraint:

d().buildCreateIndexSql('t', { name: 'ix', fields: ['a'], kind: 'IMPRINTS' });
// CREATE IMPRINTS INDEX "ix" ON "t" ("a")

removeIndex / dropIndex emit DROP INDEX "n" with no ON table — index
names are schema-scoped in MonetDB. addIndex/createIndex refuse using
(access methods — use kind), where (partial indexes), include and
concurrently.

ALTER TABLE

changeColumn retypes with ALTER TABLE t ALTER COLUMN c <type> — no
PostgreSQL TYPE keyword — then SET [NOT] NULL and SET DEFAULT as
separate statements (releases before Dec2023 cannot retype a column at all).

SELECT and WHERE

buildLimitOffset validates limit/offset as non-negative integers
(toCount), rejecting e.g. '5 OR 1=1'.

buildSelectQuery adds a trailing SAMPLE, MonetDB's row-level sampling
clause:

d().buildSelectQuery({ tableName: 't', monetdbSample: { rows: 100, seed: 7 } });
// SELECT * FROM "t" SAMPLE 100 SEED 7
d().buildSelectQuery({ tableName: 't', sample: { method: 'BERNOULLI', percent: 25, seed: 3 } });
// SELECT * FROM "t" SAMPLE 0.25 SEED 3   (PostgreSQL TABLESAMPLE is translated: percent/100 -> fraction)

A PostgreSQL sample.method other than 'BERNOULLI' throws (MonetDB
SAMPLE is row-level, not block-level); lock: true (FOR UPDATE/FOR SHARE) throws, since MonetDB uses optimistic concurrency instead of row
locks; sample and monetdbSample together throw; a SAMPLE clause on a
dialect-built UNION throws.

buildWhereClause keeps ILIKE (MonetDB has it) but scans the compiled SQL
(outside string/identifier literals) and throws on PostgreSQL-only operators:
->/->>/#>/#>>, @>/<@, &&, ::, and ~/!~/~*/!~*.

Bulk load and unload: COPY INTO / COPY ... INTO

MonetDB's wire does not speak PostgreSQL's COPY FROM STDIN stream, so
copyFrom()/copyTo() both refuse and point at the structured builders
below.

d().buildCopyIntoSql('t', {
  from: ['/data/a.csv', "/data/o'b.csv"],
  onClient: true,
  columns: ['a', 'b'],
  records: 1000,
  offset: 2,
  delimiters: { field: ',', record: '\n', quote: '"' },
  nullAs: '',
  bestEffort: true,
});
// COPY 1000 RECORDS OFFSET 2 INTO "t" ("a", "b") FROM '/data/a.csv', '/data/o''b.csv' ON CLIENT
//   USING DELIMITERS ',', '\n', '"' NULL AS '' BEST EFFORT

d().buildCopyToSql({ select: { tableName: 't', where: { a: "x'y" } }, to: '/tmp/out.csv', delimiters: { field: '|' } });
// COPY SELECT * FROM "t" WHERE "a" = 'x''y' INTO '/tmp/out.csv' ON SERVER USING DELIMITERS '|'

copyInto(table, options) runs buildCopyIntoSql through query().
buildCopyToSql inlines its SELECT's bound values as escaped literals,
because COPY is not a preparable statement. records/offset are
validated as non-negative integers; 'STDIN'/'STDOUT' cannot be combined
with onClient.

Sequences

MonetDB spells sequences differently from PostgreSQL: NEXT VALUE FOR "s",
not nextval('s').

d().buildCreateSequenceSql({ name: 's', startWith: 10, incrementBy: 5, cycle: true });
// CREATE SEQUENCE "s" START WITH 10 INCREMENT BY 5 CYCLE

OR REPLACE / IF NOT EXISTS / TEMPORARY / ownedBy all throw.
nextSequenceValue() runs SELECT NEXT VALUE FOR "s" AS value;
currSequenceValue() calls get_value_for(CURRENT_SCHEMA, $1);
hasSequence() and listSequences() read sys.sequences.

Triggers

MonetDB trigger bodies are inline — there is no companion trigger function as
on PostgreSQL — and a trigger fires on exactly one event:

d().buildCreateTriggerSql({
  name: 'audit', tableName: 't', timing: 'AFTER', events: ['INSERT'], level: 'ROW',
  referencing: { newRow: 'n' }, body: 'INSERT INTO log VALUES (n.id);',
});
// CREATE TRIGGER "audit" AFTER INSERT ON "t" REFERENCING NEW ROW AS "n"
//   FOR EACH ROW BEGIN ATOMIC INSERT INTO log VALUES (n.id); END

INSTEAD OF timing, more than one event, and constraint triggers all throw.
dropTrigger / hasTrigger are schema-scoped like indexes and sequences.

Writes: MERGE instead of ON CONFLICT / upsert, RETURNING, LIMIT

MonetDB has no ON CONFLICT and no RETURNING. Single-row upserts compile
to a MERGE statement:

d().buildUpsertQuery('t', { id: 1, v: 'x' }, { conflictFields: ['id'] });

MERGE INTO "t" AS tgt USING (SELECT $1 AS "id", $2 AS "v") AS src ON tgt."id" = src."id"
WHEN MATCHED THEN UPDATE SET "v" = src."v"
WHEN NOT MATCHED THEN INSERT ("id", "v") VALUES (src."id", src."v")
-- values: [1, 'x']

buildInsertQuery routes to the same buildMergeSql when options.upsert
is set; conflictFields is required. buildUpdateQuery/buildDeleteQuery
throw on limit (there is no UPDATE/DELETE ... LIMIT; narrow the
WHERE instead) and on returning. buildDeleteQuery({ truncate: true, restartIdentity: true }) emits TRUNCATE TABLE "t" RESTART IDENTITY.

Refused, by name

Caveats / not-yet-verified

• The driver (monetdb / monetdb-nodejs) is mocked in the test suite as a
  virtual module (tests/dialects/monetdb.test.ts); it has not been run
  against a live monetdbd in this repo.
• getExtensions()/hasExtension()/hasPolicy() return []/false rather
  than throwing, since "list what doesn't exist" has a sensible empty answer.
• queryStream() fetches the whole result first, then emits it row by row —
  it is not a true server-side cursor.
• DECIMAL precision above 38 throws; the comment on getDataTypeSql notes
  38 needs a 128-bit build (18 otherwise), but the dialect does not detect
  the server's build and always allows up to 38.
• describeTable() infers autoIncrement by testing the column default for
  /next value for/i — a column whose default happens to be a literal
  NEXT VALUE FOR expression from a different sequence would also read as
  auto-increment.

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.

Related reading

• All dialects
• monetdb npm package (MAPI protocol)
• MonetDB SQL manual