---
name: shipholm-sqlite-app
description: "Build and deploy an app that keeps its own data in SQLite on a persistent Shipholm disk (storage=true). Use for catalogues, sign-up lists, scores, submissions or a small CMS – one server process and no database server."
---

# App with its own data (SQLite on a disk)
A small server plus SQLite on a persistent disk – the light way to keep data: nothing runs beside the app.

## When this fits
Catalogues, sign-up lists, scores, submissions, small CMSs – one server process writes, up to
a few hundred requests a second. For many concurrent writers or several replicas, use
`shipholm-postgres-app` instead.

## Steps
1. Create the app with a disk:
   ```
   create_app(app="katalog", source_repo="https://github.com/<owner>/katalog",
              storage=true, health_path="/healthz")
   ```
   The app gets a persistent directory in `DATA_DIR` (`/data`). Everything else is read-only,
   and `/tmp` is emptied on every restart – keep the database and uploaded files in `DATA_DIR`.
   With `previews=true`, set `storage=true` and the secrets on the app before the first pull
   request is built: a preview copies them from the app when it is built, and gets its own
   empty disk.
   Moving an existing app to a server that answers `/healthz`? Keep the old `health_path` until
   the new server runs, then switch.
2. Write the server so that it
   - listens on `$PORT` and answers `GET /healthz` with 200 after a query (`SELECT 1`), so a
     broken database shows up as an unhealthy pod,
   - opens `$DATA_DIR/app.db` and creates or migrates its schema at start
     (keep a `schema_version` table),
   - turns on WAL (`PRAGMA journal_mode=WAL`) and a busy timeout,
   - serves the built frontend and the JSON API from the same process,
   - still starts when `DATA_DIR` is not writable: fall back to a directory under `/tmp` and log
     a warning. A forgotten disk then shows in `app_logs` instead of a pod that never gets ready.
3. Node has SQLite built in (`node:sqlite`, without a flag since 22.13) – no dependencies:
   ```js
   import { DatabaseSync } from "node:sqlite";
   const db = new DatabaseSync(`${process.env.DATA_DIR ?? "./data"}/app.db`);
   db.exec("PRAGMA journal_mode=WAL; PRAGMA busy_timeout=5000;");
   db.exec("CREATE TABLE IF NOT EXISTS items (id TEXT PRIMARY KEY, title TEXT NOT NULL, created_at TEXT NOT NULL)");
   const add = db.prepare("INSERT INTO items VALUES (?, ?, ?)");
   add.run(crypto.randomUUID(), "Hej", new Date().toISOString());
   ```
   Python has `sqlite3` in the standard library; Go has `modernc.org/sqlite` (no cgo).
4. A Dockerfile keeps the image small:
   ```dockerfile
   FROM node:24-alpine AS build
   WORKDIR /app
   COPY package*.json ./
   RUN npm ci
   COPY . .
   RUN npm run build
   FROM node:24-alpine
   WORKDIR /app
   COPY --from=build /app/dist ./dist
   COPY server ./server
   ENV NODE_ENV=production PORT=8080
   CMD ["node", "server/index.mjs"]
   ```
5. Secrets (an admin key, an API key) with `set_env(name=..., value=..., app=..., secret=true)`.

## What to know
- One replica: a deploy stops the old pod before the new one starts – a few seconds of
  downtime. Scale to zero still works.
- Previews get their own empty disk; seed them at start if the app needs data.
- The disk lives on one node. Backups are not automatic yet – export important data (an admin
  endpoint that returns JSON is enough) until they are.
- Import data through an admin endpoint protected by a secret key, not by committing it to git:
  data with emails or names must never go into a repository. Make it safe to run again:
  upsert everything in one transaction, cap the request size, and answer with counts per table.
- Files from elsewhere (images in the data): the import downloads them into `DATA_DIR/images`
  under a content-hash name and rewrites the URLs to `/images/<hash>.<ext>`, which the server
  serves with a one-year immutable cache. Check the type of each download – an SPA answers a
  missing file with HTML and status 200. Skip files you already have, so a second run fetches
  only the ones that are missing.
- Anything anonymous visitors can write (votes, submissions) needs a rate limit per IP. Keep
  personal data and tokens out of the logs.
- A consistent copy of a live database: `VACUUM INTO '/data/backup-<date>.db'`. Copying the
  file can catch it mid-write in WAL mode.
- Tests: start the server on port 0 with a temporary `DATA_DIR` (`node --test` needs no
  dependencies) and test the import, the API and the health check against it.
- `storage` can be added later (`update_app(storage=true)`) and grown (`storage_size="5Gi"`),
  never removed – that would delete the data.
