Questarr runs on SQLite by default and needs no configuration for it. Postgres is available as an opt-in alternative, from v1.5.0 onwards.
Most self-hosters should stay on SQLite. Questarr actually ran on Postgres before
v1.1 and moved to SQLite deliberately, to simplify deployment and cut resource
usage (see MIGRATION.md). Postgres is worth choosing if you
already run one and would rather back up a single database server, or if you want
your library in something you can query directly with psql.
Switching the backend does not move your data. If you already run Questarr on SQLite and set
DB_DIALECT=postgres, Questarr starts against an empty Postgres database and your library stays in the SQLite file, untouched. To bring it across, run the migration below.
| Variable | Default | Meaning |
|---|---|---|
DB_DIALECT |
sqlite |
sqlite or postgres |
SQLITE_DB_PATH |
sqlite.db |
SQLite file path. Ignored when DB_DIALECT=postgres. |
DATABASE_URL |
— | Postgres connection string. Required when DB_DIALECT=postgres. |
DATABASE_POOL_MAX |
10 |
Postgres connection pool size. Minimum 2 — migrations hold one connection for the advisory lock and need a second to apply. |
The backend is chosen by DB_DIALECT only. Setting DATABASE_URL on its own
does not switch Questarr to Postgres — it logs a warning and stays on SQLite.
That is deliberate: installs predating v1.1 may still carry a stale
DATABASE_URL in their compose file, and inferring the backend from it would
silently swap a live SQLite library for an empty or four-versions-stale database
on upgrade.
With Docker Compose:
POSTGRES_PASSWORD=$(openssl rand -hex 32) docker compose -f docker-compose.postgres.yml up -d
Keep whatever password you pick — the same value must be supplied on every
restart. It’s interpolated directly into a connection URL with no encoding, so
it must be safe to embed in one; a hex string from openssl rand -hex 32
always is.
That file adds a postgres:17-alpine service with a healthcheck the app waits on,
so the first migration cannot run against a database that is still starting. The
questarr database itself is created automatically on that service’s first start,
via its POSTGRES_DB environment variable – nothing to do on a fresh install.
Manually, against a Postgres you already run:
createdb questarr # the database must exist; Questarr will not create it
export DB_DIALECT=postgres
export DATABASE_URL=postgres://questarr:password@localhost:5432/questarr
npm run db:migrate # applies migrations-pg/
npm start
If your password contains characters with reserved meaning in a URL (@ / :
% # ? &), percent-encode them in DATABASE_URL – pass@word becomes
pass%40word.
Questarr applies migrations at startup, so db:migrate is only needed if you want
to run them ahead of time.
Stop Questarr first — the script reads the SQLite file directly and expects it not to be changing underneath it.
Run this version of Questarr on SQLite at least once before copying, so the SQLite file carries every migration (including the 1.5.0 plain-HTTP backfill). The script refuses a source with pending migrations.
# 1. Create and migrate the target database
createdb questarr
DB_DIALECT=postgres DATABASE_URL=postgres://questarr:password@localhost:5432/questarr \
npm run db:migrate
# 2. Copy the data across
SQLITE_DB_PATH=./data/sqlite.db \
DATABASE_URL=postgres://questarr:password@localhost:5432/questarr \
npx tsx scripts/sqlite-to-pg.ts
# 3. Start Questarr on Postgres
DB_DIALECT=postgres DATABASE_URL=... npm start
The script copies every table in foreign-key-safe order, then reconciles by
counting rows in Postgres and exits non-zero if any table does not match, so
a partial copy cannot be mistaken for a successful one. It refuses to run against
a database that already holds rows unless you pass --force; --batch-size=N
tunes the insert batching (default 500).
Your SQLite file is opened read-only and is never modified, so it remains a
working fallback: if anything looks wrong, unset DB_DIALECT and you are back
where you started.
Each dialect has its own schema module and migration history:
| SQLite | Postgres | |
|---|---|---|
| Schema | shared/schema.ts |
shared/schema.pg.ts |
| Migrations | migrations/ |
migrations-pg/ |
| Generate | npm run db:generate |
npm run db:generate:pg |
shared/schema.ts is the single source of TypeScript types for the whole
codebase; shared/schema.pg.ts is a runtime-only mirror. They are kept
interchangeable by two guards, both of which run in CI:
shared/schema-parity.ts — compile-time assertions that all 20 tables infer
identical row and insert shapes.shared/__tests__/schema-parity.test.ts — runtime assertions on SQL column
names, nullability, defaults and primary keys.server/__tests__/dialect-parity.test.ts then runs the same behavioural
assertions against both backends, using an in-process Postgres (PGlite) so no
container is needed.
If you are changing anything database-related, read
.claude/skills/db-dialect-parity/SKILL.md first.
Postgres columns use the most native type that preserves the exact TypeScript
type, so application code is identical on both backends: boolean for booleans,
jsonb for JSON columns, double precision for ratings, and bigint for byte
counts (Postgres integer is 32-bit and would overflow on any file or disk over
2.1 GB).
Timestamps are the exception. They are stored as epoch milliseconds in a
bigint rather than timestamptz, so in psql they look like 1789595556783
rather than a date. This is deliberate: several queries compare these columns
against Date.now(), and a timestamptz column rejects an integer bind, which
would force per-dialect branches through the storage layer. Questarr does no
calendar arithmetic in SQL — all date handling is in JavaScript — so timestamptz
would buy nothing today.
Converting later is cheap and self-contained, because the TypeScript type is
Date either way and no application code would change:
ALTER TABLE games ALTER COLUMN added_at TYPE timestamptz USING to_timestamp(added_at / 1000.0);
plus a new body for the timestampMs custom type in shared/schema.pg.ts.