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. There, 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: the database, plus the @thexjs/auth x_sessions table if you use auth. If sessions live in their own SQLite file (the default is data/auth.db), that file is part of the backup set too. 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; it 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 because 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(), 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. It only does so 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), so 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 instead of hand-rolling your own session store. Tokens are HMAC'd and revocable, passwords get Argon2 hashing, and auth endpoints get automatic CSRF protection.

Per-request state contract

X serves every request in one persistent process. A loader or action must not stash anything in module-level (file-level) variables, because a cache, a counter, or a "current user" singleton would be visible to the next concurrent request. Loaders receive their request context through LoaderArgs and returned loaderData; actions receive their arguments; that is the whole contract.

• If you need request-scoped context that crosses writes (e.g. a tracing span, a tenant id, a user id), key it by the values already in scope or use AsyncLocalStorage, which Bun supports natively. The framework itself keeps no shared mutable module state.
• The internal registries that X does keep (island ids, server-function routes) are scoped per request/rebuild, and the framework ships a concurrency test that hammers N parallel requests with distinct identities to prove nothing leaks across requests.