Materialize Dialect

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

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

export const db = new Prorm({
  dialect: 'materialize',
  host: 'abc123.us-east-1.aws.materialize.cloud',
  database: 'materialize',
  username: 'you@example.com',
  password: process.env.MZ_APP_PASSWORD,
  dialectOptions: {
    ssl: { rejectUnauthorized: true },
  },
});

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

export const db = new Prorm({
  dialect: 'materialize',
  url: 'materialize://you%40example.com:secret@abc123.us-east-1.aws.materialize.cloud:6875/materialize',
  dialectOptions: {
    ssl: { rejectUnauthorized: true },
  },
});

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

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

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

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

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

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

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

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

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

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

await db.transaction(async (t) => {
  await User.create({ id: 2, email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

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

const db = new Prorm({
  dialect: 'materialize',
  host: 'xxx.materialize.cloud',
  port: 6875, // the default; no override needed
  username: 'you@example.com',
  password: process.env.MZ_APP_PASSWORD,
  database: 'materialize',
  dialectOptions: { ssl: { rejectUnauthorized: true } }, // Materialize Cloud requires TLS
});

Materialize has no REFRESH MATERIALIZED VIEW ('mv'). A Materialize materialized
view is recomputed incrementally as its inputs change and is always current —
there is no stale state to refresh away. If the goal is to read it at a
specific logical time, use `AS OF` (or SUBSCRIBE) instead.

await db.dialect.createTable('t', {
  id: { type: DataTypes.BIGINT, allowNull: false },
  doc: { type: DataTypes.JSON },
});
// CREATE TABLE "t" ("id" BIGINT NOT NULL, "doc" JSONB)

Materialize tables have no PRIMARY KEY (column 'id'). Tables are unconstrained
inputs to a dataflow; deduplicate in the view that reads them, or let the
source apply ENVELOPE UPSERT on the way in.

Materialize has no ON CONFLICT clause — a table has no unique constraint for a
conflict to be detected against. Upsert belongs on the way *in*: a source
declared with ENVELOPE UPSERT keys the stream and keeps the latest value per
key. For a table written by hand, DELETE the key and INSERT inside one
transaction.

db.dialect.buildSubscribeQuery({
  tableName: 'orders',
  where: { status: 'open' },
  progress: true,
  envelopeUpsertKey: ['id'],
});

db.dialect.buildCreateSourceSql({
  name: 'kafka_src',
  cluster: 'ingest',
  from: "FROM KAFKA CONNECTION kc (TOPIC 'events') FORMAT JSON",
});
// CREATE SOURCE "kafka_src" IN CLUSTER "ingest" FROM KAFKA CONNECTION kc (TOPIC 'events') FORMAT JSON

db.dialect.buildCreateIndexSql('orders_view', {
  name: 'idx',
  cluster: 'serving',
  fields: ['id'],
});
// CREATE INDEX "idx" IN CLUSTER "serving" ON "orders_view" ("id")

db.dialect.buildCreateIndexSql('orders_view');
// CREATE DEFAULT INDEX ON "orders_view"

db.dialect.buildCreateMaterializedViewSql({
  name: 'mv',
  query: 'SELECT a, b FROM t',
  columns: ['a', 'b'],
  cluster: 'compute',
  assertNotNull: ['a'],
  retainHistoryFor: '1 hour',
});