SQLite

Atoll supports PostgreSQL (the default) and SQLite for a single-node PDS. The Ecto adapter is selected at build time. Keep ATOLL_DATABASE=sqlite set for every Mix command, including release builds. Switching adapters requires recompilation and does not convert an existing database. SQLite uses its own _build/sqlite directory so adapter-specific compiled artifacts stay separate.

Development and tests#

export ATOLL_DATABASE=sqlite
mix setup
mix phx.server

Development uses atoll_dev.sqlite3 in the project directory. Tests use atoll_test.sqlite3 (with MIX_TEST_PARTITION appended to the filename when set). Override the filename with DATABASE_PATH; use a different file for each test partition if overriding it. Database files and journals are ignored by Git.

ATOLL_DATABASE=sqlite mix precommit
ATOLL_DATABASE=postgres mix precommit

SQLite sandbox cases run serially. Concurrency tests still run concurrent workers, which queue for SQLite’s single pooled connection. PostgreSQL-only replica, row-lock, and aborted transaction tests are excluded on SQLite.

Production#

Build with ATOLL_DATABASE=sqlite MIX_ENV=prod mix release. Set DATABASE_PATH=/absolute/path/to/atoll.sqlite3 at runtime; its parent directory must exist and be writable by the service. Run bin/atoll eval 'Atoll.Release.migrate()' before starting the release. All the identity, session, custody-key, and HTTPS settings in deploy.md still apply.

Use a persistent local filesystem, with one Atoll node owning the database. SQLite uses WAL, foreign keys, full synchronous durability, a single-connection pool, and a five-second busy timeout. Transactions begin with BEGIN IMMEDIATE to acquire the writer lock before reading authorization or mutation state. The pool queues contenders before they enter the native driver. SQLite has no equivalent of PostgreSQL's per-statement timeout. Long transactions serialize writers across the database, so PostgreSQL remains the choice for multiple nodes, replicas, or higher write concurrency. READ_DATABASE_URL is rejected with SQLite.

The existing ATOLL_BLOB_STORAGE=postgres value means database-backed blobs; with this adapter those blobs live in SQLite. S3 remains supported. Likewise, the existing postgres rate-limit backend value selects the primary database. These names are retained for configuration and stored-data compatibility.

Schema and backups#

SQLite migrations live in priv/sqlite_repo/migrations; PostgreSQL migrations remain in priv/repo/migrations. The SQLite baseline includes the schema through PostgreSQL migration 20260927072902, including check constraints, foreign keys, indexes, and transactional revision-reference triggers. Add migrations for both adapters when changing the schema. Generate SQLite migrations with ATOLL_DATABASE=sqlite mix ecto.gen.migration migration_name.

The PostgreSQL backup/recovery scripts in ops/backup do not back up SQLite. For a consistent live SQLite backup, use SQLite's backup command:

sqlite3 /absolute/path/to/atoll.sqlite3 ".backup '/backup/atoll.sqlite3'"

Alternatively, stop Atoll cleanly before copying its database files. Do not copy only the main database file while WAL writes are active. Back up encryption and session keys and any S3 objects with the database, as described in the deployment guide. SQLite and PostgreSQL databases are not interchangeable restore formats.