r/sqlite • • 4d ago

One SQLite file per generated app: counters, imports and what a restore breaks

My open-source builder gives every generated app its own SQLite file. Notes that may help others:

- Auto-numbers are assigned inside the insert transaction as the largest of the start value, the last number plus one, and the highest stored value plus one. That keeps numbering correct after imports.

- 200 parallel inserts across two handles produced no duplicates in tests.

- CSV import runs in batches of 500, one transaction per batch, so a failure stops cleanly at a known row.

- The unsolved case: restoring an older backup hands out numbers already issued after that backup. I documented it instead of pretending to fix it.

How do you handle counters across restores?

Repo (my project): https://github.com/ChristianMunoz31/local-app-studio

0 Upvotes

1 comment sorted by

1

u/DickBrainTime 2d ago

That restore case is fundamentally different from parallel inserts: after loading an older SQLite file, the database has **no evidence** of numbers handed out after that backup. `AUTOINCREMENT`/`sqlite_sequence` doesn't solve it either, because that metadata is part of the restored database state.

I'd separate two requirements: (1) a stable **record identity** and (2) a human-readable increasing **display number**. A UUID/ULID for the primary key survives offline edits, imports and replica merges without needing a shared counter; a local `1234` can remain a convenient label, but it must not be treated as a globally never-reused identifier after rollback. If you truly need invoice-like numbers that must never be reissued, the high-water mark needs to survive *outside* the file being restored: e.g. a durable central sequence service or preallocated number ranges recorded by an independent authority. Offline apps then trade gaps or exhausted leases for that guarantee.

Within a single live file, keep the allocation and insert in one write transaction with a UNIQUE constraint. Across a restore, decide explicitly whether reuse is allowed, a new numbering namespace/epoch is created, or an external authority is mandatory. Which guarantee matters for your generated apps: no duplicates among *current rows*, or never reusing a number that anyone might have seen before the restore?