Data Layer

Data layer

x provides built-in SQLite and PostgreSQL integrations via @thexjs/core/data. Connect to a database, run file-based migrations, and query data directly from loaders and server functions.

SQLite

Use connectSQLite to connect to a local SQLite database file. It wraps bun:sqlite and turns on WAL mode and foreign_keys by default. SQLite requires zero configuration and is perfect for development and single-server deployments.

import { Database } from "bun:sqlite";import { connectSQLite, runSQLiteMigrations } from "@thexjs/core/data";const db = connectSQLite({ path: "data/app.db" });await runSQLiteMigrations(db, "data/migrations");export { db };export type DB = typeof db;

Querying SQLite

The returned object is the standard bun:sqlite Database, so db.query(...).all() and db.run(...) work as expected.

import type { RouteProps, LoaderArgs } from "@thexjs/core";import { db } from "../lib/db";export async function loader({}: LoaderArgs) {  const users = db.query(    "SELECT id, name, email FROM users ORDER BY created_at DESC",  ).all();  return { users };}export default function Users({ loaderData }: RouteProps) {  const { users } = loaderData as {    users: Array<{ id: string; name: string; email: string }>;  };  return (    <div>      <h1 className="text-3xl font-bold">Users</h1>      <ul className="mt-6 space-y-3">        {users.map((u) => (          <li key={u.id} className="rounded-xl border border-border bg-card p-4">            <p className="font-semibold">{u.name}</p>            <p className="text-sm text-muted-foreground">{u.email}</p>          </li>        ))}      </ul>    </div>  );}

PostgreSQL

For production deployments, use connectPostgres with a url (or the DATABASE_URL env var). It wraps Bun.sql with a connection pool, TLS enforcement (defaults to require in production), and retry with backoff so the app tolerates a database that is still coming up.

import { connectPostgres, runPostgresMigrations } from "@thexjs/core/data";const db = connectPostgres({  url: process.env.DATABASE_URL,  max: 20,        // connection pool size  ssl: "verify-full",  ca: process.env.POSTGRES_CA,   // PEM CA, for verify-ca/verify-full  retryAttempts: 5,              // reconnect with backoff before failing});await runPostgresMigrations(db, "data/migrations");export { db };

connectPostgres requires Bun and will throw on non-Bun runtimes (such as Vercel's Node functions) — for those, connect with your own Postgres client and pass it around instead.

Migrations

Both runSQLiteMigrations and runPostgresMigrations take a directory of .sql files, applied in filename order. Applied migrations are tracked in a _x_migrations table and never re-run.

data/migrations/
001_create_users.sql
002_create_posts.sql
003_add_posts_index.sql

CREATE TABLE IF NOT EXISTS users (  id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT NOT NULL,  email TEXT UNIQUE NOT NULL,  created_at TEXT DEFAULT (datetime('now')));

The result is a { applied, skipped } list of filenames, so you can log or test which migrations ran.

Backup & disaster recovery

Back up the same data the app actually writes. That is the database plus — if you use @thexjs/auth — the x_sessions table. Migrations are not data: the _x_migrations table records history, so restore the database and let the migration runner verify it's in the state your code expects.

Two numbers to write down for any plan: RPO (how much data you can lose — dictates backup frequency) and RTO (how fast you must be back — dictates restore procedure). The recipes below give you the mechanics; pick schedule and retention to meet your own RPO/RTO.

SQLite (WAL mode)

connectSQLite enables WAL mode, which is exactly what you want for backups: it allows a safe, consistent file snapshot while the app keeps writing. Never copy the .db file with a plain cp while the app is running — you can catch the file mid-write. Three safe options, in order of preference:

import { Database } from "bun:sqlite";const db = new Database("data/app.db");// Online backup API: consistent snapshot while the app keeps writing.await db.backup("backups/app-$(date -u +%FT%TZ).db");db.close();

Prefer db.backup() — it is the only option that is safe with zero coordination. Alternatively, from a separate shell you can use the SQLite .backup command:

# Safe: consistent snapshot via SQLite itself
sqlite3 data/app.db ".backup 'backups/app-$(date -u +%FT%TZ).db'"
# Safe too: checkpoint WAL first, then copy all three files together
sqlite3 data/app.db "PRAGMA wal_checkpoint(FULL);"
cp data/app.db data/app.db-wal data/app.db-shm backups/
# NOT safe while running: a bare cp of just app.db
# cp data/app.db backups/   # <- corrupt snapshot risk

Restore is the reverse: stop the app, copy the snapshot back (removing any stale -wal/-shm files first), then start the app. Because migrations are tracked in _x_migrations, you can restore to an older snapshot than your current code and the runner will simply apply the missing migrations — but only forward. Restoring an older snapshot after newer migrations already ran requires either re-applying them or restoring a snapshot taken after they applied.

PostgreSQL

Use the platform's built-in backups (RDS automated snapshots, Neon/Cloudflare D1-style point-in-time recovery, Supabase backups) as the primary mechanism, plus logical pg_dump for portable, schema-safe snapshots and cross-provider restore.

# Logical backup (portable, survives provider migration)
pg_dump "$DATABASE_URL" -Fc -f backup.dump
# Restore onto a fresh database
createdb "$DATABASE_URL"   # if restoring into an empty DB
pg_restore "$DATABASE_URL" -d "$DATABASE_URL" --clean --if-exists backup.dump

If you run one-off scripts from inside the app (a cron worker or a daily task), prefer the app's own connectPostgres connection so the connection-pool, TLS, and retry settings you configured are the ones doing the work.

Runbook checklist

• Test the restore, not just the backup. A backup that has never been restored is a guess. Restore into a scratch database as part of CI or a monthly drill and verify row counts and a couple of hand-picked rows.
• Store snapshots off the same machine. Object storage (S3/R2) or a separate volume; a backup on the same disk dies with the app.
• Back up sessions too. If @thexjs/auth backs sessions with x_sessions, it is part of the database backup automatically. If you ever point auth at a separate session store, add it to the backup set. Restoring an older snapshot will log everyone out (sessions created after it don't exist) — plan for a re-auth wave.
• Multi-instance deploys: SQLite is single-node. For two or more app instances behind a load balancer, use Postgres (or a dedicated SQLite host) so every instance reads the same data. Back up from one instance's maintenance window, not from a live replica mid-write.
• Write down the runbook. RTO is set by how long restore takes when you are panicking, so document the exact commands above somewhere your on-call can reach.

Sessions, not hand-rolled

If you need sessions on top of this, @thexjs/auth provides prebuilt credentials and OAuth2 (GitHub) sign-in backed by an x_sessions table in SQLite or Postgres — HMAC'd, revocable session tokens, Argon2 password hashing, and automatic CSRF on auth endpoints — instead of hand-rolling your own session store.