Database backends

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.

Choosing a backend

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.

Running on Postgres

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.

Migrating an existing SQLite library to Postgres

Stop Questarr first — the script reads the SQLite file directly and expects it not to be changing underneath it.

# 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.

How the two backends stay in step

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.

Type mapping, and why timestamps look like numbers

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.

Deployment

Reference

Security

Project

Community

DATABASE

Database backends

Choosing a backend

Running on Postgres

Migrating an existing SQLite library to Postgres

How the two backends stay in step

Type mapping, and why timestamps look like numbers

​Database backends

​Choosing a backend

​Running on Postgres

​Migrating an existing SQLite library to Postgres

​How the two backends stay in step

​Type mapping, and why timestamps look like numbers

Database backends

Choosing a backend

Running on Postgres

Migrating an existing SQLite library to Postgres

How the two backends stay in step

Type mapping, and why timestamps look like numbers