Why SQLite data disappears after scaling to two app instances

A SQLite app can save a record successfully and return an empty list on the next request after scaling to two instances. Each instance can open /data/notes.sqlite on a different volume. A load balancer sends the save to one copy of the app and the next read to the other.

How a successful write disappears on refresh

In a notes app with two instances, both copies run the same code and create the same notes table. On a platform such as Fly.io, each machine has its own volume and the platform leaves the volumes independent. The same mount path can therefore refer to two separate SQLite databases. Fly Volumes overview

After both instances accept writes, each file may contain notes missing from the other. Before removing an instance, preserve both databases and check which records need to be carried over.

Reproduce it with two database files

Run this in a shell with the sqlite3 command installed. The directories stand in for the two machines’ mounts. Each command opens a fresh database connection.

(
  set -eu
  workdir=$(mktemp -d)
  trap 'rm -r -- "$workdir"' EXIT
  mkdir "$workdir/a" "$workdir/b"
  db_a="$workdir/a/notes.sqlite"
  db_b="$workdir/b/notes.sqlite"

  schema='CREATE TABLE notes (body TEXT NOT NULL)'
  sqlite3 "$db_a" "$schema"
  sqlite3 "$db_b" "$schema"
  sqlite3 "$db_a" "INSERT INTO notes VALUES ('Pick up keys')"

  sqlite3 "$db_a" 'SELECT count(*) FROM notes'  # 1
  sqlite3 "$db_b" 'SELECT count(*) FROM notes'  # 0

  # Let the second connection open the same local file.
  db_b="$db_a"
  sqlite3 "$db_b" 'SELECT count(*) FROM notes'  # 1
)

SQLite CLI 3.51.0 returned 1, 0, and 1. The last query sees the note because it opens the same file where the insert committed. SQLite documentation on isolation between connections

One local database or a shared database server

A single app instance with SQLite on a persistent volume can work when one machine can handle the traffic and its downtime is acceptable. Backups cover recovery. Multiple workers on the same machine can open the same file, and SQLite coordinates those connections with one writer at a time. Appropriate uses for SQLite, SQLite isolation

For app instances on separate machines that must see the same committed notes, a shared database server is one option. Both instances can send queries to the same PostgreSQL primary, which manages the database files. The deployment diff should show where the new instance connects. PostgreSQL architecture

A staging check across both instances

In staging, save a uniquely named note through A and read it through B as soon as the save succeeds. Repeat in the other direction. Send requests to specific instances where the host supports it, or match each request to an instance ID in the logs. Both responses can be 200 OK while one is missing the note, so assert on the returned records.

For how the load balancer fits into the request path, see 5 Essential System Design Patterns for Building Scalable Applications.