SQLite Dialect

$ npm install ts-prorm-orm better-sqlite3

$ pnpm add ts-prorm-orm better-sqlite3

$ yarn add ts-prorm-orm better-sqlite3

$ bun add ts-prorm-orm better-sqlite3

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

export const db = new Prorm({
  dialect: 'sqlite',
  storage: './data/app.db', // or ':memory:'
});

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

export const db = new Prorm({
  dialect: 'sqlite',
  url: 'file:./data/app.db',
});

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

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

// File-backed database
const db = new Prorm({
  dialect: 'sqlite',
  storage: './data/app.db',
});

// In-memory database (the default when storage is omitted)
const mem = new Prorm({
  dialect: 'sqlite',
  storage: ':memory:',
});

// With extra better-sqlite3 driver options
const ro = new Prorm({
  dialect: 'sqlite',
  storage: './data/app.db',
  dialectOptions: {
    readonly: true,       // open the file read-only
    fileMustExist: true,  // fail instead of creating a new file
    timeout: 5000,        // busy-handler timeout in ms (default 5000)
    // verbose: console.log, // log every executed statement
  },
});

dialect.getDataTypeSql({ key: 'ENUM', values: ['draft', 'live'] } as any, 'status');

await dialect.createTable(
  'users',
  {
    id: { type: { key: 'INTEGER' } as any, primaryKey: true, autoIncrement: true },
    email: { type: { key: 'STRING' } as any, allowNull: false, unique: true },
    role: { type: { key: 'ENUM', values: ['admin', 'user'] } as any },
  },
  { strict: true },
);

dialect.buildInsertQuery('kv', { k: 'a', v: 1 });
// { sql: 'INSERT INTO "kv" ("k", "v") VALUES (?, ?)', values: ['a', 1] }

dialect.buildInsertQuery(
  'kv',
  { k: 'a', v: 1 },
  { upsert: true, conflictFields: ['k'] },
);

dialect.buildInsertQuery('kv', { k: 'a', v: 1 }, { upsert: true, doNothing: true });
// ... VALUES (?, ?) ON CONFLICT DO NOTHING

dialect.buildInsertQuery(
  'accounts',
  { id: 1, email: 'a@x.io', handle: 'ada' },
  {
    upsert: true,
    conflictTargets: [
      { fields: ['id'], action: 'DO NOTHING' },
      { fields: ['email'], action: 'DO UPDATE', updateColumns: ['handle'] },
    ],
  },
);

dialect.buildWhereClause({ id: [1, 2, 3] });
// { sql: '"id" IN (?,?,?)', values: [1, 2, 3] }

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

// The dialect class itself is exported, so its SQLite-only options (`strict`)
// and helpers (`buildJsonExtract`) are typed. `new Prorm({ dialect: 'sqlite' })`
// reaches the same class through `getDialectInstance()`.
const dialect = new SQLiteDialect({ storage: ':memory:' });
await dialect.connect(); // opens the DB and runs PRAGMA foreign_keys = ON

// Create a STRICT table with an emulated ENUM
await dialect.createTable(
  'todos',
  {
    id: { type: { key: 'INTEGER' } as any, primaryKey: true, autoIncrement: true },
    title: { type: { key: 'STRING', length: 200 } as any, allowNull: false },
    status: { type: { key: 'ENUM', values: ['open', 'done'] } as any, defaultValue: 'open' },
    meta: { type: { key: 'JSON' } as any },
  },
  { strict: true },
);

// Insert (booleans/objects are coerced automatically on bind)
const insert = dialect.buildInsertQuery('todos', {
  title: 'Write docs',
  meta: { priority: 3 },
});
await dialect.query(insert.sql, { replacements: insert.values as any });

// Upsert on the primary key
const up = dialect.buildUpsertQuery(
  'todos',
  { id: 1, title: 'Write docs (v2)', status: 'done' },
  { conflictFields: ['id'], updateOnDuplicate: ['title', 'status'], returning: true },
);
await dialect.query(up.sql, { replacements: up.values as any });

// Query with operators + JSON extraction
const where = dialect.buildWhereClause({ status: 'done', id: { $gte: 1 } });
const jsonPri = dialect.buildJsonExtract('meta', 'priority');
const rows = await dialect.query(
  `SELECT id, title, ${jsonPri} AS priority FROM "todos" WHERE ${where.sql}`,
  { replacements: where.values as any },
);

await dialect.disconnect();