Local testing

The SDK ships a local mode that swaps the HTTP transport for an in-process better-sqlite3 connection. Your data layer needs no changes — same db.query, same db.batch, same db.driver() — but tests stay isolated, fast, and offline.

Setup

npm i -D better-sqlite3

better-sqlite3 is an optional peer dependency. It’s loaded lazily the first time you use local mode, so customers who only use the live SDK never pull the native module.

Usage

import { PerSQL } from "@persql/sdk";

const persql = new PerSQL({ local: ":memory:" });
const db = persql.database("test/db");

await db.query("CREATE TABLE customers (id INTEGER, email TEXT)");
await db.query("INSERT INTO customers VALUES (?, ?)", [1, "a@b.co"]);
const { data } = await db.query("SELECT id, email FROM customers");
//      ^? { id: number; email: string }[]

Pass a file path instead of ":memory:" to persist between runs:

const persql = new PerSQL({ local: "./test.db" });

Call persql.close() to release the file handle when your test suite finishes.

With Drizzle

The same db.driver() callback works in local mode, so a Drizzle data layer needs zero changes between tests and prod:

import { drizzle } from "drizzle-orm/sqlite-proxy";
import { PerSQL } from "@persql/sdk";
import * as schema from "./db/schema";

function makeDb() {
  const persql =
    process.env.NODE_ENV === "test"
      ? new PerSQL({ local: ":memory:" })
      : new PerSQL({ token: process.env.PERSQL_TOKEN! });
  return drizzle(persql.database("acme/orders").driver(), { schema });
}

What works in local mode

Feature	Local mode
db.query	yes — runs against better-sqlite3
db.batch	yes
db.transaction	yes — wrapped in a SQLite transaction
db.tables	yes
db.explain	yes — EXPLAIN QUERY PLAN
db.schema	yes — introspection
db.driver() (Drizzle)	yes
db.subscribe	throws — DO change-feed has no local equivalent

For the “throws” rows — mock them at the test boundary, or factor your code so the part you’re testing doesn’t depend on them.

Caveats

• Single connection per PerSQL instance. The namespace/slug passed to persql.database(…) is ignored — every database call goes to the same SQLite file. Instantiate multiple PerSQL clients if you need multi-DB isolation.
• No edge runtimes. better-sqlite3 is a native Node module — it won’t load in Cloudflare Workers, Vercel Edge, Deno Deploy, etc. This is fine: local mode is for tests; production code uses the HTTP path which works everywhere.
• Idempotency keys are silently ignored. They’re a server-side feature for retry safety; local mode doesn’t need them.