1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
# Migrate the server from SQLite to PostgreSQL - DONE
Done on 2026-08-30. The server runs on Postgres 18: sqlx switched to the
`postgres` feature, 95 placeholders renumbered to `$N`, `REAL` columns
widened to `DOUBLE PRECISION` (Postgres REAL is float4 and will not decode
into f64), the `flagged` and `is_bot` flags made real BOOLEANs, the
leaderboard's derived table given the alias Postgres requires, and
`INSERT OR IGNORE` rewritten as `ON CONFLICT DO NOTHING`. Integration tests
run against a real database, each in its own throwaway schema, since
Postgres has no in-memory mode.
## Still open
- **Timestamps are still TEXT.** They are RFC3339 and sort correctly as
text, so this is not a correctness problem, but `TIMESTAMPTZ` would let
the database do date arithmetic instead of the application.
- **LISTEN/NOTIFY is unused.** Multiplayer rooms are still in-process, so
the server cannot yet run as more than one instance.
---
## Original note
## Why
The server is a multi-user network service. It handles auth, sessions,
friendships, leaderboards, live multiplayer races, and rate limiting. SQLite
allows only one writer at a time. Races, leaderboard writes, and rate-limit
counters all write at the same time, so the single writer is the wrong shape
for this workload.
PostgreSQL gives three things the server needs:
- MVCC. Concurrent writers do not block each other.
- Real timestamp types. `created_at` and `expires_at` are `TEXT` today.
- `LISTEN`/`NOTIFY`. This maps onto multiplayer room pub/sub, and lets the
server run as more than one instance.
Keep `mitmux` on SQLite. It is a single-user local tool, and its pure-Go
SQLite driver is what keeps cross-compilation free of CGO.
## Scope
Server only (`crates/server`). The TUI, WASM, and Steam builds do not touch
the database.
## What makes this cheap
- The migrations use no SQLite-only syntax. There is no `AUTOINCREMENT`, no
`PRAGMA`, no `strftime`, and no `WITHOUT ROWID`.
- Primary keys are `TEXT` UUIDs, not integer rowids.
- All 33 call sites use the runtime `sqlx::query` function, not the
`sqlx::query!` macro. There is no compile-time schema metadata, so no
`cargo sqlx prepare` step and no offline query cache to regenerate.
## Steps
1. Change the sqlx feature in `crates/server/Cargo.toml` from `sqlite` to
`postgres`. Keep `runtime-tokio-rustls`, `time`, and `uuid`.
2. Change the pool type from `SqlitePool` to `PgPool` in `state.rs`, and
change the connection string handling in `main.rs`.
3. Convert every placeholder from `?` to `$1`, `$2`, and so on. Postgres
numbers its placeholders. This is the largest mechanical change: 33
queries across `auth.rs`, `stats.rs`, `friends.rs`, `multiplayer.rs`,
`cosmetics.rs`, `anticheat.rs`, `rate_limit.rs`, and `spotify.rs`.
4. Change `created_at TEXT` and `expires_at TEXT` to `TIMESTAMPTZ` in the
migrations. Update the Rust structs to `time::OffsetDateTime`.
5. Replace any `INSERT OR REPLACE` or `INSERT OR IGNORE` with
`INSERT ... ON CONFLICT`. Check `cosmetics.rs` and `rate_limit.rs` first.
6. Add a `docker-compose.yml` with a `postgres` service, so a contributor can
start a database with one command.
7. Run the full test suite against the new backend.
## Verify
- `cargo test --workspace` passes.
- Two clients can finish a multiplayer race at the same time, and both
results are written.
- A session expires at the correct time. This confirms the `TIMESTAMPTZ`
conversion.
- Rate limiting still rejects a burst from one user.
## Follow-up
Move multiplayer room pub/sub from in-process state to `LISTEN`/`NOTIFY`.
Only after that can the server run more than one instance.
|