| 1 |
# OxygenHub on PostgreSQL |
| 2 |
|
| 3 |
A hub keeps its records in SQLite (`<data>/hub.db`) unless it is built with |
| 4 |
the `postgres` feature, when it keeps them in PostgreSQL 16 or later |
| 5 |
(oxygenhub#211). The engine is chosen when the hub is built, not when it |
| 6 |
starts: one binary speaks one engine. |
| 7 |
|
| 8 |
## Build and configure |
| 9 |
|
| 10 |
```sh |
| 11 |
cargo build --release -p oxygenhub --features postgres |
| 12 |
``` |
| 13 |
|
| 14 |
```toml |
| 15 |
# <data>/hub.toml |
| 16 |
[database] |
| 17 |
url = "postgres://[email protected]/hub" # the password from PGPASSWORD or the URL |
| 18 |
schema = "" # empty: public |
| 19 |
``` |
| 20 |
|
| 21 |
`OXYHUB_DATABASE_URL` and `OXYHUB_DATABASE_SCHEMA` override the file. The |
| 22 |
hub makes the `citext` extension (in `public`) and its schema if they are |
| 23 |
missing, then applies `crates/hub/migrations-pg`. Repositories, keys and |
| 24 |
uploads stay in the data directory as before. |
| 25 |
|
| 26 |
`schema = "*"` gives each data directory a schema of its own (`hub_<dir>`), |
| 27 |
so many hubs can share one database. The tests use it. |
| 28 |
|
| 29 |
## Two schemas, one set of queries |
| 30 |
|
| 31 |
The queries are the same on both engines: `$n` placeholders, `ON CONFLICT`, |
| 32 |
`TRUE`/`FALSE`, `RETURNING`, `CASE` for counting flags, window functions, |
| 33 |
`lower()` for comparing names. Postgres is stricter than SQLite, so: |
| 34 |
|
| 35 |
- A flag column is `BOOLEAN` on Postgres and `INTEGER` on SQLite. Bind and |
| 36 |
read it as `bool`, and compare it with `TRUE`/`FALSE`, never `1`/`0`. |
| 37 |
- `SUM` of a `BIGINT` is `NUMERIC` on Postgres: `CAST(SUM(…) AS BIGINT)`. |
| 38 |
- Every column a `GROUP BY` query returns is grouped or aggregated. |
| 39 |
- `HAVING` and `WHERE` cannot name a `SELECT` alias: repeat the expression or |
| 40 |
select from a subquery. |
| 41 |
|
| 42 |
The few places that differ choose by `cfg(feature = "postgres")`: |
| 43 |
connecting and migrating (`db::open`), the write lock (`db::begin_write`: |
| 44 |
`BEGIN IMMEDIATE`, or an advisory lock), full-text search (FTS5, or a |
| 45 |
`tsvector` with `ts_headline`), JSON (`db::json_text`, `json_each`), and the |
| 46 |
catalog backups read (`backups::catalog`). A backup taken on either engine |
| 47 |
restores into either: flags are written as 0 or 1, blobs as hex. |
| 48 |
|
| 49 |
## Changing the schema |
| 50 |
|
| 51 |
`migrations-pg/0001_schema.sql` is SQLite's 0001–0072 as one. Each later |
| 52 |
change is two files with the same number, one in `migrations` (SQLite) and |
| 53 |
one in `migrations-pg`. `tests/schema.rs` fails when the two disagree about a |
| 54 |
table or column. |
| 55 |
|
| 56 |
## Testing on Postgres |
| 57 |
|
| 58 |
```sh |
| 59 |
docker run -d --name oxyhub-pg-test -e POSTGRES_USER=hub -e POSTGRES_PASSWORD=test \ |
| 60 |
-p 127.0.0.1:55432:5432 postgres:16-alpine |
| 61 |
OXYHUB_DATABASE_URL=postgres://hub:[email protected]:55432/hub OXYHUB_DATABASE_SCHEMA='*' \ |
| 62 |
cargo test -p oxygenhub --features postgres |
| 63 |
``` |
| 64 |
|
| 65 |
Each test makes its own schema; drop the `hub_%` schemas between runs. |