@plinthjs/database

The database layer for Mason: connections, a fluent query builder, the SQL grammar, and a driver seam. Depends on @plinthjs/support.

Built from scratch, not on TypeORM — see the amendment in ADR-0002. The builder + grammar mirror Laravel's own architecture (Connection + Grammar + fluent builder); execution goes through a Driver. The first driver is Node's built-in node:sqlite (zero native dependencies). Postgres/MySQL drivers slot in behind the same seam later.

Architecture

Connection ── table() ──▶ QueryBuilder ──compile──▶ Grammar ──SQL+bindings──▶ Driver ──▶ database
   │                                                                            │
   └─ select / statement / transaction                          SqliteDriver (node:sqlite)

• Driver — runs compiled SQL + bindings; returns rows or { changes, lastInsertId }. Async contract so async drivers (pg/mysql) slot in later.
• Grammar — pure QuerySpec → { sql, bindings } compiler (SQLite dialect; double-quoted identifiers, ? placeholders). Subclass for other dialects.
• QueryBuilder — the fluent surface.
• Connection — wires a driver to a grammar; db.table('users'), raw exec, transactions.

Usage

import { sqliteConnection, raw } from '@plinthjs/database'

const db = sqliteConnection() // in-memory; pass { filename } for a file

await db.statement('create table users (id integer primary key, name text, age integer)')
await db.table('users').insert([
  { name: 'Ada', age: 36 },
  { name: 'Linus', age: 54 },
])

await db.table('users').where('age', '>', 40).orderBy('name').get()
await db.table('users').where('name', 'Ada').value('age') // 36
await db.table('users').count() // 2
await db.table('users').where('id', 1).update({ age: 37 }) // affected rows
const id = await db.table('users').insertGetId({ name: 'Grace' }) // new id

await db.transaction(async (tx) => {
  await tx.table('users').insert({ name: 'Dennis' })
})

Builder surface

select · distinct · where / orWhere (operator + closure-nested groups) · whereIn / whereNotIn · whereNull / whereNotNull · whereBetween · whereColumn · whereRaw · join / leftJoin / rightJoin · groupBy · having · orderBy / orderByDesc / orderByRaw · limit / offset / forPage · get / first / find / value / pluck / exists · count / sum / avg / min / max · insert / insertGetId / update / delete · toSql (inspect) · raw() escape hatch.

Next (Tier 2): the schema builder + migrations, a connection manager (multiple/read-write connections), pagination, and reimplementing @plinthjs/orm's DataSource on top of Connection so the relationship layer runs against real SQL.