DuckDB 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: 'duckdb',
  storage: './analytics.duckdb', // or ':memory:'
});

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

export const db = new Prorm({
  dialect: 'duckdb',
  url: 'duckdb:./analytics.duckdb',
});

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';

// In-memory (default): fast, ephemeral, gone when the process exits.
const mem = new Prorm({
  dialect: 'duckdb',
  storage: ':memory:',
});

// File-backed: a single on-disk DuckDB database file.
const db = new Prorm({
  dialect: 'duckdb',
  storage: './analytics.duckdb',
});

const ro = new Prorm({
  dialect: 'duckdb',
  storage: './analytics.duckdb',
  dialectOptions: { readonly: true, config: { max_memory: '4GB' } },
});

await db.dialect.createSequence({ name: 'order_ids', startWith: 1000, incrementBy: 1 });
const next = await db.dialect.nextSequenceValue('order_ids');
const seqs = await db.dialect.listSequences();

db.dialect.buildUpsertQuery(
  'kv',
  { k: 'a', v: 1 },
  { conflictFields: ['k'] },
);

DuckDB upsert requires `conflictFields` naming the PRIMARY KEY or UNIQUE columns
to resolve conflicts against (e.g. { conflictFields: ["id"] }), unless
`doNothing` is set.

const tx = await db.dialect.startTransaction();
try {
  await db.dialect.query('INSERT INTO t (id) VALUES (1)');
  await tx.commit();
} catch (err) {
  await tx.rollback();
  throw err;
}

// SELECT ... FROM read_parquet('data/*.parquet', hive_partitioning=true)
const src = db.dialect.readParquet('data/*.parquet', { hivePartitioning: true });
const rows = await db.dialect.query(`SELECT region, SUM(amount) AS total FROM ${src} GROUP BY region`);

// CSV and JSON too:
db.dialect.readCsv('sales.csv', { header: true });          // read_csv_auto('sales.csv', header=true)
db.dialect.readJson('events.ndjson', { format: 'newline_delimited' });

await db.dialect.copyTo(
  'SELECT * FROM sales',
  'out/sales',
  'PARQUET',
  { partitionBy: ['region'], overwrite: true, compression: 'zstd' },
);

await db.dialect.createExtension('httpfs');

// Global SET variables (simplest, one credential set at a time):
await db.dialect.configureS3Credentials({
  region: 'us-east-1',
  accessKeyId: '...',
  secretAccessKey: '...',
});

// Or the newer, recommended named-secret mechanism (scoped, can coexist):
await db.dialect.createSecret('my_bucket', {
  keyId: '...',
  secret: '...',
  region: 'us-east-1',
  scope: 's3://my-bucket',
});

await db.dialect.createExtension('parquet');   // INSTALL parquet; LOAD parquet;
const has = await db.dialect.hasExtension('parquet');
const all = await db.dialect.getExtensions();

await db.dialect.attachDatabase('./other.duckdb', 'other');
await db.dialect.query('SELECT * FROM other.users');
await db.dialect.detachDatabase('other');