MotherDuck Dialect

$ npm install ts-prorm-orm duckdb

$ pnpm add ts-prorm-orm duckdb

$ yarn add ts-prorm-orm duckdb

$ bun add ts-prorm-orm duckdb

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

export const db = new Prorm({
  dialect: 'motherduck',
  database: 'analytics', // opens md:analytics
  dialectOptions: {
    token: process.env.MOTHERDUCK_TOKEN,
  },
});

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

export const db = new Prorm({
  dialect: 'motherduck',
  url: 'md:analytics',
  dialectOptions: {
    token: process.env.MOTHERDUCK_TOKEN,
  },
});

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

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

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

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

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

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

const latest = db.getDialectInstance()!.buildSelectQuery({
  tableName: 'users',
  distinctOn: ['name'],
  order: [['name', 'ASC'], ['id', 'DESC']],
});
await db.query(latest.sql, { replacements: latest.values });

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

const db = new Prorm({
  dialect: 'motherduck',
  database: 'analytics', // opens md:analytics
  dialectOptions: {
    token: process.env.MOTHERDUCK_SERVICE_TOKEN,
  },
});

const md = new MotherDuckDialect({ database: 'analytics', storage: './scratch.duckdb' });
await md.connect();
// md:analytics is current; ./scratch.duckdb is attached as "local"

md.readParquet('s3://bucket/events/*.parquet', { mdRun: 'REMOTE' });
// read_parquet('s3://bucket/events/*.parquet', MD_RUN = REMOTE)
md.readCsv('./local.csv', { header: true, mdRun: 'LOCAL' });
// read_csv_auto('./local.csv', header=true, MD_RUN = LOCAL)

md.createDatabaseSQL({ name: 'ducks' }); // CREATE DATABASE "ducks"
md.buildCreateDatabaseSql({ name: 'dev', from: { database: 'prod' } });
// CREATE DATABASE "dev" FROM "prod"              — zero-copy clone
md.buildCreateDatabaseSql({ name: 'c', from: { share: 'md:_share/ducks/0a9a…' } });
md.buildCreateDatabaseSql({ name: 'c', from: { path: 's3://bucket/ducks.db' } }); // physical copy
md.dropDatabaseSQL('ducks'); // DROP DATABASE "ducks"
md.buildAttachMotherDuckSql('prod', 'p'); // ATTACH 'md:prod' AS "p"

const url = await md.createShare({
  name: 'birds',
  from: 'ducks',
  access: 'RESTRICTED',
  visibility: 'HIDDEN',
  update: 'MANUAL',
  includePatterns: ['main.*'],
});
// CREATE SHARE "birds" FROM "ducks" (ACCESS RESTRICTED, VISIBILITY HIDDEN, UPDATE MANUAL, INCLUDE_PATTERN 'main.*')

await md.updateShare('birds'); // UPDATE SHARE "birds" — publish a new snapshot
await md.attachShare(url!, 'birds'); // ATTACH 'md:_share/…' AS "birds"
await md.dropShare('birds', { ifExists: true }); // DROP SHARE IF EXISTS "birds"
await md.listShares(); // LIST SHARES