SQLite (better-sqlite3)

// npm i better-sqlite3
import Database from 'better-sqlite3';

const db = new Database('shop.db');

// 1) Modern defaults — turn them on once at startup
db.pragma('journal_mode = WAL');             // concurrent reads + writes
db.pragma('synchronous = NORMAL');           // safe + fast on commits
db.pragma('foreign_keys = ON');              // enforce FK constraints
db.pragma('busy_timeout = 5000');            // retry on contention

// 2) Schema + migrations — keep them in source, run at boot
db.exec(\`
  CREATE TABLE IF NOT EXISTS customers (
    id          INTEGER PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_cents INTEGER NOT NULL,
    status      TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
  );

  CREATE INDEX IF NOT EXISTS ix_orders_customer_status ON orders (customer_id, status);
\`);

// 3) Prepared statements — compile ONCE, run many
const insertCustomer = db.prepare(
  'INSERT INTO customers (email, name) VALUES (?, ?) RETURNING id'
);
const insertOrder = db.prepare(
  'INSERT INTO orders (customer_id, total_cents, status) VALUES (?, ?, ?) RETURNING id'
);
const listOrders = db.prepare(
  'SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT ?'
);
const getOrder = db.prepare('SELECT * FROM orders WHERE id = ?');

// 4) Use them
const { id: customerId } = insertCustomer.get('alice@example.com', 'Alice') as { id: number };
const { id: orderId    } = insertOrder.get(customerId, 4995, 'new')         as { id: number };

console.log(getOrder.get(orderId));
console.log(listOrders.all(customerId, 20));

// 5) Transactions — much faster than autocommit + atomic
const placeOrder = db.transaction((email: string, name: string, totalCents: number) => {
  const c = insertCustomer.get(email, name) as { id: number };
  const o = insertOrder.get(c.id, totalCents, 'new')      as { id: number };
  return o.id;
});

const id = placeOrder('bob@example.com', 'Bob', 9900);

// 6) Bulk insert — orders of magnitude faster than per-row commits
const insertOrders = db.transaction((rows: any[]) => {
  for (const r of rows) insertOrder.run(r.customerId, r.totalCents, r.status);
});
insertOrders([
  { customerId, totalCents: 1500, status: 'new' },
  { customerId, totalCents: 2500, status: 'new' },
]);

// 7) JSON columns (SQLite has json_extract, json_set, etc.)
db.exec(\`
  CREATE TABLE IF NOT EXISTS events (
    id          INTEGER PRIMARY KEY,
    type        TEXT NOT NULL,
    payload     TEXT NOT NULL,        -- JSON stored as TEXT
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
  );
\`);
db.prepare('INSERT INTO events (type, payload) VALUES (?, json(?))')
  .run('order.paid', JSON.stringify({ orderId, amount: 4995 }));

const recent = db.prepare(\`
  SELECT id, type, json_extract(payload, '$.orderId') AS order_id
  FROM events
  WHERE type = ? AND created_at > datetime('now', '-1 day')
\`).all('order.paid');

// 8) Backups while the app runs
db.backup('shop-' + new Date().toISOString().slice(0, 10) + '.db')
  .then(() => console.log('backup ok'));

// 9) The built-in node:sqlite (Node 22+)
// import { DatabaseSync } from 'node:sqlite';
// const db2 = new DatabaseSync('shop.db');
// const rows = db2.prepare('SELECT * FROM orders').all();
// Same parameterised-SQL discipline; smaller, no native build step.

// 10) When SQLite is the WRONG choice
// - Many concurrent writers from multiple processes / hosts
// - Need a network DB (microservices reaching it from many nodes)
// - Dataset > ~1 TB or hot-set > available RAM
// - Need PostgreSQL features (CTE, window functions, partial indexes are fine in SQLite though)

// 11) Pitfalls
// - String concat in SQL (use parameterised statements ALWAYS)
// - Forgetting WAL -> reads block during writes
// - Forgetting busy_timeout -> SQLITE_BUSY errors on contention
// - Long-running transactions -> WAL grows unbounded
// - Storing 1MB blobs without VACUUM -> file grows; pragma auto_vacuum

import Database from 'better-sqlite3';
const db = new Database('app.db');
const rows = db.prepare('SELECT * FROM users WHERE active = ?').all(1);