A Locked Window for My Scattered Databases

On my server, "my own databases" are scattered all over the place: the blog, n8n, Uptime Kuma, 3x-ui, Jarvis and the NAS each have a SQLite file, and another project has a MongoDB on top of that. Every time I want to look at some data, I either ssh into a container and type sqlite3, spin up a throwaway web UI, or install Compass on my laptop one more time.

The real pain isn't that SQLite is bad — it's that it's just a file buried inside the server, hard to peek at live and even harder to edit on a whim.

The goal was simple: one web console I can open from anywhere to read and edit everything, with 2FA in front. But that "simple" wish pulled out a whole chain of Docker, SQLite WAL, libSQL, firewall and Service Worker details. This post writes down every pothole and how I got out of it — including the detours I took while choosing the tools.

Don't rush to swap SQLite for Postgres#

The first fork wasn't a tool, it was whether to ditch SQLite entirely. I did wonder "should I move to Postgres?" and even checked what Innei uses — turns out it's MongoDB + Redis, but that's because his backend is a document-style CMS installed by lots of people, a completely different world from my single-machine, single-backend, read-heavy personal site. Not a reason to copy him.

After thinking it through: for me, SQLite is the right answer, not a compromise. The only reason worth switching to Postgres would be pgvector for AI semantic search someday — and a change at that scale should be bundled with "rewriting the whole backend in Rust," not done now just to switch for switching's sake.

So I redefined the problem: the pain isn't SQLite, it's "a database hidden inside a file is hard to see and edit live." That's solved with a tool, not by swapping databases.

Finding a tool that's good-looking AND can open a remote file#

Two requirements: the interface has to be good-looking (I have no patience for ugly tools), and it has to be able to open a local file on the server. Those two together turned out to be surprisingly hard:

• The VS Code SQLite extension: what I use now, but it's view-only and can't edit conveniently — that's the origin of the pain.
• sqlite-web / Adminer: run in a single container, eat anything, but that bare Flask / PHP tool-feel makes me close the tab on sight.
• Beekeeper Studio / TablePlus: the desktop crowd have pretty interfaces, but my SQLite is a remote file, and a desktop tool has to sftp or sync it down first — clunky.
• CloudBeaver (the web version of DBeaver): server-side Java, a built-in JDBC SQLite driver, can open a file on the server directly, multi-process WAL-safe too — technically the most solid, and I almost went with it.

In the end I still wanted Outerbase Studio, because it's the most modern, most easy-on-the-eyes of the bunch (a bit of a Notion feel). But after pulling the official Docker image, it couldn't read the .sqlite files on my server. Its dependencies explain why:

$ docker run --rm --entrypoint cat outerbase/studio /app/package.json | grep -iE "sqlite|libsql"
"@libsql/client": "^0.5.3"

Only @libsql/client — nothing like better-sqlite3 that could open a local file on the server side. That image is browser-first: by design it connects to network databases like Turso / libSQL / D1, and "local file" means a file on your own machine, opened through the browser.

I nearly concluded "Outerbase can't do it, switch to CloudBeaver" — until I dug into its npm. It also ships a CLI, @outerbase/studio; studio <path> starts a server on the host and opens the file directly on the server, with built-in basic auth to boot. So I packaged a tiny container that runs just that CLI:

FROM node:20-alpine
RUN npm install -g @outerbase/studio@0.2.7   # pin the version, don't let runtime grab latest
ENTRYPOINT ["studio"]

One database, one instance#

SQLite has ATTACH DATABASE, which in theory lets me hang all six DBs off a single connection and browse them at once. I actually wired it up — but Outerbase's table browser only knows the main database's schema. The five attached DBs are queryable (SELECT * FROM alias.table), but the sidebar shows none of their tables.

So I switched to one instance per DB, each with its own --base-path, and let nginx fold them into a single domain by path:

# docker-compose.yml (excerpt)
ob-web:   { command: ["/data/db.sqlite",     "--port","4000","--base-path","/web"] }
ob-n8n:   { command: ["/data/database.sqlite","--port","4002","--base-path","/n8n"] }
ob-kuma:  { command: ["/data/kuma.db",       "--port","4003","--base-path","/kuma"] }
# …xui / jarvis / nas the same way

location /web/  { proxy_pass http://127.0.0.1:4000; }
location /n8n/  { proxy_pass http://127.0.0.1:4002; }
location /kuma/ { proxy_pass http://127.0.0.1:4003; }

The six instances all live under one db.koimsurai.com (the root path auto-redirects to /web). I didn't want to memorize which path maps to which DB every time, so I wrote a little dark-themed card menu as the landing page, six cards each going into one DB. To make sure this SPA wouldn't break under a base path (asset paths going wrong), I ran it through a headless browser and confirmed it rendered fully before calling it done.

Read-only: two lines, after a big detour#

For third-party services like n8n, Kuma and 3x-ui, I don't understand the table layout and only want to look — never fat-finger something and break it. I walked two dead ends on this one.

The first was snapshots: a sidecar that runs sqlite3 .backup every five minutes to produce a copy, and Outerbase reads the copy. Problem: the copy from .backup inherits the source's WAL mode, so the copy is WAL too and still won't open read-only — I had to add PRAGMA journal_mode=DELETE to turn it non-WAL. But the more fundamental issue is that what you're now seeing is a copy, not live data, which flatly contradicts my original "must see it live" requirement. The whole snapshot approach got scrapped.

The second is the real path: mount the file read-only directly. The obvious docker :ro blows up immediately:

The reason is WAL. In WAL mode even reading needs a shared-memory index -shm; make the whole filesystem read-only and -shm can't be created, so it won't open. Next I tried passing SQLite's read-only params in the connection string, and @libsql/client flat-out refuses them:

Neither ?mode=ro nor ?immutable=1 is accepted. Stuck here, I boiled the problem down to its simplest shape as an experiment: what if only the main file is read-only, but its directory stays writable?

// A background process keeps writing (simulating the app, keeping -shm alive); the main file is chmod 444
const db = createClient({ url: "file:/tmp/w.db" });
await db.execute("SELECT count(*) FROM t");   // → reads 56 rows (incl. what the background just wrote)
await db.execute("INSERT INTO t(v) VALUES('x')");
// → SQLITE_READONLY: attempt to write a readonly database

It worked. The principle: SQLite decides whether the whole connection is read-only by whether the main database file is writable. Main file read-only → connection read-only, all writes rejected; meanwhile -shm / -wal live in the writable directory, so it still reads the live WAL data. In Docker that's two lines — directory rw, main file with an extra :ro layer:

volumes:
  - /path/service-data:/data                            # dir rw: for -shm
  - /path/service-data/db.sqlite:/data/db.sqlite:ro     # main file read-only: blocks writes

Inside the container, not even root can write it — blocked at both the filesystem layer (EROFS) and the SQLite layer (SQLITE_READONLY):

$ docker exec ob-n8n sh -c 'dd if=/dev/zero of=/data/database.sqlite bs=1 count=1'
dd: can't open '/data/database.sqlite': Read-only file system

Will the writable DBs get corrupted?#

web, Jarvis and the NAS are mine, so I mount them writable. But the libSQL engine and the standard SQLite the app uses are now writing the same WAL file at the same time — will they fight and corrupt it? Rather than guess, I had two processes (Python's built-in sqlite3 as the "standard engine," @libsql/client as the Outerbase engine) hammer the same file for five seconds:

# Round 1 (libSQL with no busy_timeout)
standard sqlite3 inserts: 24803
libSQL: 0 — SQLITE_BUSY: database is locked
integrity_check: ok

# Round 2 (libSQL with busy_timeout=8000)
both sides inserted
integrity_check: ok

The key is the SQLITE_BUSY in round 1: it means libSQL sees and respects the lock held by standard SQLite — it queues, it doesn't barge past and write independently (libSQL has a "Virtual WAL" custom interface, which was what worried me at first); and integrity_check was ok both times. Worst case, a write waits for a lock; it doesn't corrupt the file.

Mongo: two walls stacked together#

Another project has a MongoDB, and I wanted Mongoku (a modern web version of Compass). There are two walls stacked here:

1. mongod only binds 127.0.0.1.
2. This host's firewall drops every new "container → host" connection.

I diagnosed the second one like this — connecting from the host itself works, but any container (including Docker's own host.docker.internal) times out:

# from the host: works
$ mongosh "mongodb://USER:PASS@127.0.0.1:27017/?authSource=admin" --eval "db.adminCommand('listDatabases')"
→ lists the databases fine

# from a container, same address: times out (dropped by the firewall)
$ docker run --rm --network db-admin_default mongo:7 mongosh "mongodb://…@172.17.0.1:27017/…"
→ MongoServerSelectionError: connect timed out

I even tried standing up a socat relay, and that didn't work either — because Mongoku is on the db-admin_default network while socat was bound to the default bridge, and the two are isolated across Docker networks. On top of that, Mongoku's image hard-codes the listen port to 3100, and on my box 3100 was already taken by another next-server, and setting PORT did nothing.

Two breakthroughs. First, a host-network container goes through the host's own loopback and doesn't pass through the firewall that blocks containers — so I let Mongoku run with network_mode: host and connect straight to 127.0.0.1:27017. Second, PORT did nothing because it's built with SvelteKit, whose env prefix has to be MONGOKU_SERVER_:

mongoku:
  image: huggingface/mongoku:latest
  network_mode: host
  environment:
    - MONGOKU_SERVER_HOST=127.0.0.1   # bind loopback only, not exposed
    - MONGOKU_SERVER_PORT=4001        # avoid the taken 3100
    - MONGOKU_SERVER_ORIGIN=http://127.0.0.1:4001   # only needed for write access
    - MONGOKU_DEFAULT_HOST=mongodb://USER:PASS@127.0.0.1:27017/?authSource=admin

Opened it, and it dutifully listed that Mongo's databases.

The subdomain hijacked by a Service Worker#

After wiring it up, a maddening thing happened: opening db.koimsurai.com sometimes showed my blog instead. I first misdiagnosed it as port 80 being grabbed by the blog's wildcard, and chased that for a while — wrong.

The culprit was a Service Worker. Before this subdomain became the database console, I had once used it to visit the blog; and the blog is a PWA, so its service worker was registered on the db.koimsurai.com origin and had cached the whole app shell — so the moment I navigated there, it "answered" with the cached blog. Ctrl + Shift + R bypasses the service worker and it's fine. To fix it for good, clear the site's data, or drop a self-unregistering sw.js at the old path. This pothole had nothing to do with databases at all — it was purely the browser keeping a memory for me for far too long.

One 2FA gate over everything#

Last is the lock on the outside. My conditions were clear: a URL I can reach, but a username and password alone isn't safe enough — I want 2FA, and I didn't want to set up WireGuard-style private networking.

The first thing recommended was Cloudflare Tunnel + Access: zero inbound ports exposed, a passkey right at the edge, free. Technically beautiful, but I got hung up on a gut feeling — all the traffic detours through Cloudflare, won't that be laggy? Plus I wanted the control of self-hosting, so I went with self-hosted Authelia (I looked at Tailscale and Authentik too; the former needs a client and I'm not used to VPNs). My mindset: "Opening a port is fine — even if it's scanned, they can't get in; and if I misconfigure it and lock myself out someday, that's my own problem."

Authelia wraps the whole set with forward-auth; before nginx reaches any DB console, it goes through Authelia's login plus a passkey:

auth_request /internal/authelia/authz;
auth_request_set $redirect $scheme://$http_host$request_uri;
error_page 401 =302 https://auth.koimsurai.com/?rd=$redirect;

Two small things worth noting during setup. One: Authelia by default wants you to configure SMTP to send confirmation emails — but running an SMTP server when everything else is self-hosted is odd, so I used notifier.filesystem instead, which writes the confirmation straight to a local file; on the first 2FA registration you just docker exec authelia cat /config/notification.txt and dig out the link. Two: its TOTP is stored in its own SQLite, keyed by username, so when I later renamed my account from timo to timo9378, I had to rename it in that table too, or the OTP wouldn't match and I'd have to re-register.

I deliberately split Authelia into its own package (its own repo), so any future service that wants 2FA just points one nginx block at it — completely pluggable. The A records for the three subdomains auth. / db. / mongo. are all DNS-only (not through Cloudflare's orange-cloud proxy), and the cert simply reuses the existing wildcard *.koimsurai.com — the subdomains don't need separate certs.

Verify first, then build#

The end state: six SQLite databases (mine editable, the third-party ones read-only, all live) plus one Mongo, all behind a single domain and a single 2FA gate.

Looking back, none of these potholes were truly hard. Outerbase's CLI, the ATTACH limitation, WAL read-only, a container that can't reach the host, a subdomain hijacked by a service worker — every answer was just one step away: "go figure out how the thing actually behaves first." I took the long way around almost entirely because I treated "the container is up and returns 200" as "done" too early. Verify first, then build.