OxygenHub on PostgreSQL

A hub keeps its records in SQLite (<data>/hub.db) unless it is built with the postgres feature, when it keeps them in PostgreSQL 16 or later (oxygenhub#211). The engine is chosen when the hub is built, not when it starts: one binary speaks one engine.

Build and configure

cargo build --release -p oxygenhub --features postgres

# <data>/hub.toml
[database]
url = "postgres://[email protected]/hub"   # the password from PGPASSWORD or the URL
schema = ""                             # empty: public

OXYHUB_DATABASE_URL and OXYHUB_DATABASE_SCHEMA override the file. The hub makes the citext extension (in public) and its schema if they are missing, then applies crates/hub/migrations-pg. Repositories, keys and uploads stay in the data directory as before.

schema = "*" gives each data directory a schema of its own (hub_<dir>), so many hubs can share one database. The tests use it.

Two schemas, one set of queries

The queries are the same on both engines: $n placeholders, ON CONFLICT, TRUE/FALSE, RETURNING, CASE for counting flags, window functions, lower() for comparing names. Postgres is stricter than SQLite, so:

• A flag column is BOOLEAN on Postgres and INTEGER on SQLite. Bind and read it as bool, and compare it with TRUE/FALSE, never 1/0.
• SUM of a BIGINT is NUMERIC on Postgres: CAST(SUM(…) AS BIGINT).
• Every column a GROUP BY query returns is grouped or aggregated.
• HAVING and WHERE cannot name a SELECT alias: repeat the expression or select from a subquery.

The few places that differ choose by cfg(feature = "postgres"): connecting and migrating (db::open), the write lock (db::begin_write: BEGIN IMMEDIATE, or an advisory lock), full-text search (FTS5, or a tsvector with ts_headline), JSON (db::json_text, json_each), and the catalog backups read (backups::catalog). A backup taken on either engine restores into either: flags are written as 0 or 1, blobs as hex.

Changing the schema

migrations-pg/0001_schema.sql is SQLite's 0001–0072 as one. Each later change is two files with the same number, one in migrations (SQLite) and one in migrations-pg. tests/schema.rs fails when the two disagree about a table or column.

Testing on Postgres

docker run -d --name oxyhub-pg-test -e POSTGRES_USER=hub -e POSTGRES_PASSWORD=test \
  -p 127.0.0.1:55432:5432 postgres:16-alpine
OXYHUB_DATABASE_URL=postgres://hub:[email protected]:55432/hub OXYHUB_DATABASE_SCHEMA='*' \
  cargo test -p oxygenhub --features postgres

Each test makes its own schema; drop the hub_% schemas between runs.