LumenJS supports SQLite and PostgreSQL through a single useDb() API. SQLite works out of the box with zero config. Set a DATABASE_URL environment variable to switch to PostgreSQL. Your code stays the same.
import { useDb } from '@nuraly/lumenjs/db'; export async function loader() { const db = useDb(); const posts = await db.all('SELECT * FROM posts ORDER BY created_at DESC'); return { posts }; }
That's it. No config file needed for SQLite. The database file is created at data/db.sqlite on the first call.
| SQLite | PostgreSQL | |
|---|---|---|
| Config | Zero config. Works immediately | Set DATABASE_URL env var |
| Best for | Content sites, dashboards, prototypes | Production apps, multi-server, concurrent writes |
| Migrations | data/migrations/*.sql | data/migrations/postgres/*.sql |
| Dependencies | better-sqlite3 (built-in) | pg (built-in) |
The API is identical for both. Write your queries once and switch backends by setting an environment variable:
# SQLite (default, no env var needed) # PostgreSQL DATABASE_URL=postgresql://user:pass@localhost:5432/mydb
DATABASE_URL. If the URL starts with postgres:// or postgresql://, it uses PostgreSQL. Otherwise it defaults to SQLite.
useDb() returns an async wrapper with these methods:
| Method | Returns | Purpose |
|---|---|---|
await db.all(sql, ...params) | any[] | SELECT multiple rows |
await db.get(sql, ...params) | any | undefined | SELECT single row |
await db.run(sql, ...params) | { changes, lastInsertRowid } | INSERT / UPDATE / DELETE |
await db.exec(sql) | void | Multi-statement DDL |
await db.withTransaction(fn) | T | Run queries in a transaction |
db.isPg | boolean | Check which backend is active |
In SQLite mode, access the underlying better-sqlite3 instance via db.raw.
Always use ? placeholders for user input. Parameters are bound safely. No SQL injection risk:
// Safe - params are bound, never interpolated const post = await db.get('SELECT * FROM posts WHERE slug = ?', params.slug); await db.run( 'INSERT INTO posts (title, slug, content) VALUES (?, ?, ?)', title, slug, content );
? placeholders. When running on PostgreSQL, LumenJS automatically converts them to $1, $2, etc. It also converts datetime('now') to NOW() and INSERT OR IGNORE to ON CONFLICT DO NOTHING.
const db = useDb(); await db.withTransaction(async () => { await db.run('UPDATE accounts SET balance = balance - ? WHERE id = ?', amount, fromId); await db.run('UPDATE accounts SET balance = balance + ? WHERE id = ?', amount, toId); });
On SQLite, transactions use BEGIN/COMMIT/ROLLBACK. On PostgreSQL, a dedicated client is acquired from the pool and all queries inside the callback use that client via AsyncLocalStorage.
// api/posts.ts import { useDb } from '@nuraly/lumenjs/db'; export async function GET() { const db = useDb(); return { posts: await db.all('SELECT * FROM posts') }; } export async function POST(req) { const { title, slug, content } = req.body; const db = useDb(); const result = await db.run( 'INSERT INTO posts (title, slug, content) VALUES (?, ?, ?)', title, slug, content ); return { id: result.lastInsertRowid }; }
Place .sql files in the migrations directory. They run automatically on the first useDb() call.
SQLite migrations go in data/migrations/:
data/migrations/ 001_init.sql 002_add_tags.sql
PostgreSQL migrations go in data/migrations/postgres/:
data/migrations/postgres/ 001_init.sql 002_add_tags.sql
Example SQLite migration:
-- data/migrations/001_init.sql CREATE TABLE IF NOT EXISTS posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, slug TEXT NOT NULL UNIQUE, content TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now')) );
Example PostgreSQL migration:
-- data/migrations/postgres/001_init.sql CREATE TABLE IF NOT EXISTS posts ( id BIGSERIAL PRIMARY KEY, title TEXT NOT NULL, slug TEXT NOT NULL UNIQUE, content TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() );
_lumen_migrations table. Migrations that have already been applied are skipped. Files are sorted alphabetically and executed in order.
Define tables in code with defineTable() for a single source of truth. In dev mode, LumenJS auto-generates SQLite migration files when the schema changes:
// db/schema.ts import { defineTable } from '@nuraly/lumenjs/db'; export const posts = defineTable('posts', { id: 'INTEGER PRIMARY KEY AUTOINCREMENT', title: 'TEXT NOT NULL', slug: 'TEXT NOT NULL UNIQUE', content: 'TEXT NOT NULL', created_at: 'TEXT NOT NULL DEFAULT (datetime(\'now\'))', });
Import the schema file in a loader or API route so definitions are registered before useDb():
import '../db/schema.js'; import { useDb } from '@nuraly/lumenjs/db'; export async function loader() { const db = useDb(); return { posts: await db.all('SELECT * FROM posts') }; }
defineTable() only run in dev mode (NODE_ENV !== 'production') and only for SQLite. For PostgreSQL, write your migrations manually in data/migrations/postgres/.
SQLite: the default path is data/db.sqlite. Override it in your config:
// lumenjs.config.ts export default { title: 'My App', db: { path: 'data/db.sqlite' }, };
WAL mode and foreign keys are enabled automatically.
PostgreSQL: set the DATABASE_URL environment variable. LumenJS creates a connection pool via pg.Pool:
DATABASE_URL=postgresql://user:pass@localhost:5432/mydb
SQLite is embedded. No server process, no connection strings. The entire database is a single file. Perfect for content sites, dashboards, internal tools, and prototypes. better-sqlite3 gives you a fast synchronous API under the async wrapper.
PostgreSQL is the choice for production apps that need concurrent writes, multiple servers, or advanced features like JSONB, full-text search, and row-level security. LumenJS connects via a pool and handles connection management automatically.
Start with SQLite. When you outgrow it, set DATABASE_URL and add your PostgreSQL migrations. Your queries stay the same.