@possumtech/sqlrite

v2.2.0

Published

5 days ago

SQL Done Right

0High
0Medium
0Low

possumtechcom

node sqlite sql orm database

🪨 SqlRite

SQL Done Right. A high-performance, opinionated, and LLM-ready wrapper for Node.js native SQLite.

📖 About

SqlRite is a thin, zero-dependency wrapper around the native Node.js sqlite module. It enforces a clean separation of concerns by treating SQL as a first-class citizen, enabling a development workflow that is faster, more secure, and optimized for modern AI coding assistants.

Why SqlRite?

⚡ Zero-Config Prepared Statements: Define SQL in .sql files; call them as native JS methods.
🧵 True Non-Blocking I/O: The default async model offloads all DB operations to a dedicated Worker Thread.
📦 LLM-Ready Architecture: By isolating SQL from JS boilerplate, you provide AI agents with a clean, high-signal "Source of Truth" for your data layer.
🧩 Locality of Behavior: Keep your SQL files right next to the JS logic that uses them.
🚀 Modern Standards: Built for Node 25+, ESM-native, and uses the latest node:sqlite primitives.
🛡️ Production-Ready Defaults: Automatically enables WAL mode, Foreign Key enforcement, and DML Strictness.

🛠 Installation

npm install @possumtech/sqlrite

🚀 Quick Start

1. Define your SQL (`src/users.sql`)

SqlRite uses simple metadata headers to turn SQL chunks into JS methods. We recommend using STRICT tables for maximum type safety.

-- INIT: createUsers
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  meta TEXT
) STRICT;

-- PREP: addUser
INSERT INTO users (name, meta) VALUES ($name, $meta);

-- PREP: getUserByName
SELECT * FROM users WHERE name = $name;

2. Use it in Javascript

Asynchronous (Default - Recommended)

Uses Worker Threads to keep your main event loop free.

import SqlRite from "@possumtech/sqlrite";

const sql = await SqlRite.open({ 
  path: "data.db", 
  dir: "src" 
});

// PREP chunks expose .all(), .get(), and .run()
// Named parameters are prefix-agnostic in JS (e.g., $name -> name)
// Objects/Arrays are automatically stringified for you!
await sql.addUser.run({ 
  name: "Alice", 
  meta: { theme: "dark", preferences: [1, 2, 3] } 
});

const user = await sql.getUserByName.get({ name: "Alice" });
console.log(JSON.parse(user.meta).theme); // Manual parse required for output

await sql.close();

Synchronous

Ideal for CLI tools, migrations, or scripts.

import { SqlRiteSync } from "@possumtech/sqlrite";

const sql = new SqlRiteSync({ dir: ["src", "migrations"] });
const users = sql.getUserByName.all({ name: "Alice" });
sql.close();

🏗️ Migrations & Schema Management

SqlRite provides a production-grade migration workflow built directly into the initialization process. It eliminates the need for external migration tools by leveraging deterministic file sorting and idempotent SQL.

1. Idempotent Schema (`-- INIT`)

Blocks tagged with -- INIT are executed automatically when the database is opened. Use IF NOT EXISTS to ensure these operations are safe to run repeatedly.

2. Deterministic Execution Order

SqlRite recursively scans your directories and sorts all .sql files numerically by their basename prefix (e.g., 001-setup.sql runs before 002-feature.sql). This ensures your schema is built in the exact order you intended, across all provided directories.

3. Multi-Directory "Overlay"

You can keep your core migrations in one folder and feature-specific SQL right next to your application logic:

const sql = await SqlRite.open({
  dir: [
    "migrations",      // Global schema (001-base.sql, 002-auth.sql)
    "src/users",       // Local logic (003-users-view.sql, users.sql)
    "src/billing"      // Local logic (billing.sql)
  ]
});

SqlRite merges these folders into a single, sorted execution plan, allowing for both centralized management and Locality of Behavior.

⚡ Features & Syntax

Prefix-Agnostic Interface

While SQL requires prefixes ($, :, or @) to identify parameters, SqlRite abstracts this away for the JavaScript consumer. You can pass clean object keys, and the library handles the mapping automatically.

🛡️ Type Generation (Codegen)

SqlRite includes a built-in codegen tool to provide precise TypeScript definitions for your dynamically generated methods.

# Generate SqlRite.d.ts from your SQL files
npm run build:types

This enables full LSP support (autocomplete and parameter hints) in your IDE, even for runtime-generated objects.

🤖 LLM-Ready Architecture

In the era of AI-assisted engineering, Context is King.

SqlRite's "SQL-First" approach is specifically designed to maximize the effectiveness of LLMs:

High Signal-to-Noise: When you feed a .sql file to an LLM, it sees 100% schema and logic, 0% Javascript boilerplate.
LLM Reference: See LLMS.md for a high-signal "contract" that AI agents can use to understand and implement your data layer.
Schema Awareness: Agents can instantly "understand" your entire database contract by reading isolated SQL files.

💎 Features & Syntax

Modern Defaults

SqlRite automatically executes these PRAGMAs on every connection to ensure high performance and data integrity:

WAL Mode: PRAGMA journal_mode = WAL enables concurrent readers and writers.
Foreign Keys: PRAGMA foreign_keys = ON enforces relational constraints.
DML Strict Mode: PRAGMA dml_strict = ON catches common SQL errors (like using double quotes for strings).

Metadata Headers

Locality & Multi-Directory Support

You don't have to put all your SQL in one folder. SqlRite encourages placing SQL files exactly where they are needed:

const sql = new SqlRite({
  dir: ["src/auth", "src/billing", "src/shared/sql"]
});

Files are sorted numerically by filename prefix across all directories (e.g., 001-setup.sql will always run before 002-seed.sql), ensuring deterministic migrations.

⚙️ Configuration