Moving a desktop app from PostgreSQL to SQLite without losing a cent - DEV Community

DEV Community

Ibrahim Oulkhir
Ibrahim Oulkhir

Posted on AI-assisted

Moving a desktop app from PostgreSQL to SQLite without losing a cent

#python #sqlite #postgres #windows

Why PostgreSQL had to go

Personal Finance Intelligence (PFI) is a local-first budgeting app for Windows: a WinUI 3 shell, a FastAPI backend and Next.js screens, with no account and no cloud. Up to version 0.3 it ran its own private PostgreSQL server inside the user's data folder.

That worked, but it was the wrong tool for a single-user desktop app. The Microsoft Store build tripped over PostgreSQL's files, PostgreSQL could not run from the Store's read-only install folder without copying its binaries elsewhere first, and the app carried a whole database server for one user. Version 0.4 moved everyone to a single SQLite file; 0.5 removed PostgreSQL, which made the app about a third smaller to download and 155 MB smaller on disk.

The hard part was not SQLite. It was moving real people's money records from one database to another on their own PCs, with nobody there to fix it if it went wrong.

The rules

Three rules decided every design choice:

  1. Never lose a cent. Every amount must arrive exactly, and the app must prove it, not assume it.
  2. Never touch the original until the copy is proven. If anything fails, the user keeps running on their old data as if nothing happened.
  3. Never delete on the user's behalf. The old database is kept after the move and only removed when the user asks, one release later.

Exact money in SQLite

PostgreSQL has a real NUMERIC type; SQLite does not, and storing amounts as floats would make 0.10 + 0.20 drift. PFI stores every amount as an integer scaled by 10², so CHF 12.34 is 1234. A small SQLAlchemy TypeDecorator does the conversion in both directions:

class ExactDecimal(TypeDecorator):
    """A decimal stored as an integer scaled by 10 ** scale."""
    impl = BigInteger
    cache_ok = True

    def __init__(self, scale: int) -> None:
        super().__init__()
        self.scale = scale
        self._factor = Decimal(10) ** scale
        self._quantum = Decimal(1).scaleb(-scale)

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        return int((Decimal(str(value)) * self._factor)
                   .to_integral_value(ROUND_HALF_EVEN))

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        return (Decimal(int(value)) / self._factor).quantize(self._quantum)

Money = ExactDecimal(2)
Enter fullscreen mode Exit fullscreen mode

Because the database only ever sees integers, SUM() and comparisons are exact, and they run inside SQLite rather than in Python. Rounding is half-to-even, as a NUMERIC(p, 2) column would do. A test adds ten amounts of 0.10 and one of −1,234,567.89 and checks the total is exactly −1,234,566.89.

The copy: prove it, then switch

On the first start of 0.4, if the data folder still held a PostgreSQL cluster, the app moved it before opening anything else:

  1. Start the old PostgreSQL one last time and bring its schema up to the newest 0.3 version.
  2. Create the new database as pfi.sqlite.part, never as the real file name, and run the SQLite schema migrations on it.
  3. Copy every table in foreign-key order, 2,000 rows at a time, with foreign keys switched off during the copy. Timestamps with a time zone are converted to UTC, because SQLite keeps no time zones.
  4. Check the copy: the same row count in every table, the same SUM(amount) over all transactions (exact, thanks to the integer cents), and PRAGMA foreign_key_check returning nothing.
  5. Only then rename pfi.sqlite.part to pfi.sqlite with os.replace, which is atomic on the same disk.
  6. Rename the old pgdata folder to pgdata-0.3. Nothing is deleted.

If any check fails, the .part file is removed and the user sees a plain message: nothing was changed, the old data is still in use, and the log file to send. The core of the check:

src_sum = sconn.scalar(select(func.coalesce(
    func.sum(source.tables["transactions"].c.amount), 0)))
dest_sum = dconn.scalar(select(func.sum(Transaction.amount)))
if Decimal(str(src_sum)) != (dest_sum or Decimal("0.00")):
    raise LegacyError("The transaction total differs after copying ...")
broken = dconn.execute(text("PRAGMA foreign_key_check")).fetchall()
if broken:
    raise LegacyError(f"{len(broken)} broken references after copying ...")
Enter fullscreen mode Exit fullscreen mode

While 0.4 was current, the build tested this path on every build: its smoke test created a 0.3-style PostgreSQL cluster with sample data, started the new app on it and compared the totals.

What if it stops halfway?

A reader asked the right question: what happens if the PC loses power in the middle of the copy?

Nothing breaks, by construction. Until step 5 the real pfi.sqlite does not exist and pgdata has not been renamed, so on the next start the app sees exactly what it saw the first time: an old cluster and no SQLite file. It deletes any leftover pfi.sqlite.part (and its -wal and -shm files) and starts the copy again from scratch. There is no “resume” to get wrong, because the copy is all or nothing.

Old backups needed the same care. Version 0.3 wrote backups with pg_dump, and a user might restore one months later. Version 0.4 could still read them: it restored the .dump into a throw-away PostgreSQL cluster in a temporary folder, then ran the same checked copy into SQLite. Since 0.5, which no longer ships PostgreSQL, the app explains that a .dump file needs 0.4.0 and links to that installer.

Retiring PostgreSQL, one release later

Version 0.4 still shipped PostgreSQL, but only to perform the move once. Version 0.5 removed it completely:

  • Settings shows an “Old data from PFI 0.3” row with the size of what is left (pgdata-0.3, the copied binaries, old server logs) and deletes it only when the user clicks. Old .dump backups are deleted only if the user ticks a separate box.
  • If someone jumps straight from 0.3 to 0.5, the app does not guess. It refuses to open the old data and shows a page explaining that 0.4.0 must run once first, with a link to that installer.

The result: one SQLite file instead of a database server, no background service, backups that are a single zip of that file, and an app about a third smaller to download. My own data went from 0.3 to 0.4 to 0.5 this way, and I checked each step in the logs.

What I'd do again

  • Write to a temporary name, verify, then rename. It turns “what if it crashes halfway” into a non-question.
  • Check totals, not just row counts. A row count can match while an amount was rounded; an exact sum cannot.
  • Keep the old data for a release. Disk space is cheap; a user's ten years of transactions are not.
  • Make the error messages say what to do. Every failure above tells the user that nothing changed and what to try next.

The same idea now runs the other way: version 0.5.1 adds a “Test this backup” button that opens a backup in a temporary folder and compares balances, counts and a random sample of transactions with the live data, without restoring anything.

PFI is free and open source (MIT). I built it with Claude as a coding assistant; the design decisions, testing on my own data and the releases are mine.

Top comments (0)