SQLite
SQLite keeps the whole database in a file on the server's disk. It is the default: a Node or Docker site that sets nothing uses it, and there is no database server to install. This page is for whoever runs such a site.
Settings
| Variable | What it does | Default |
|---|---|---|
DB_PROVIDER | sqlite chooses SQLite. | sqlite on Node |
SQLITE_PATH | Where the database file is. | ./data/choir.sqlite |
You need neither to get started.
The site uses the SQLite built into Node (node:sqlite), which is why it needs Node 22.13 or newer and why nothing has to be compiled or installed. Node 22 prints this warning when the database is opened. It is harmless:
ExperimentalWarning: SQLite is an experimental feature and might change at any time
Where the file is
A relative SQLITE_PATH is taken from the folder the server is started in, not from where the code is. Started from /opt/choirmaster, the default is /opt/choirmaster/data/choir.sqlite. Started from somewhere else, it is somewhere else, and the site comes up empty. To be safe, set an absolute path, or make sure whatever starts the site sets the working directory (systemd's WorkingDirectory=).
The folder is created if it does not exist. The file is created the first time the site starts, and its tables with it, unless you have set DB_AUTO_MIGRATE=false (How the database schema is kept up to date).
In the Docker image the data folder is /app/data, and docker-compose.sqlite.yml sets SQLITE_PATH=/app/data/choir.sqlite on a named volume. Without a volume there, the database is lost when the container is replaced: Docker: HTTPS, ports and volumes.
It is three files, not one
The site opens the database in write-ahead mode, with foreign keys enforced and a five-second wait when the file is busy. In that mode a running site has three files side by side:
data/choir.sqlite
data/choir.sqlite-wal
data/choir.sqlite-shm
Recent changes are in the -wal file until SQLite folds them into the main one. When the site stops cleanly, the two extra files are removed and everything is in choir.sqlite.
A plain copy of choir.sqlite taken while the site is running can miss recent changes or be unusable. Either stop the site first and copy the file, or use a method that takes a consistent copy. Back up a single-server site has both.
Before a one-click update the site takes such a copy itself, into backups in the data folder, and keeps the last three. That is a safety net for the update, not a backup routine.
One process only
Run one copy of the site against a SQLite file. Two servers, two containers or two replicas sharing the file, or a file on a network share, are not supported: the database is meant to be opened by one process on a local disk. If you need more than one copy of the site, use PostgreSQL or MySQL.
The helper scripts (admin:add, db:migrate) may be run while the site is up. They open the same file for a moment, and the five-second wait covers the overlap.
Permissions
The account the site runs as must be able to write to the folder, not only to the file, because SQLite creates the two extra files beside it.
sudo chown -R choir:choir /opt/choirmaster/data
sudo chmod 700 /opt/choirmaster/data
Here choir stands for your service account. The database holds members' names and email addresses and donors' details, so nobody else on the server should be able to read it.
If you run a helper script as a different user, such as root, it may create the extra files under that user and leave the site unable to write. Run scripts as the service account:
sudo -u choir node --env-file=.env scripts/add-admin.js conductor@example.org
Check it
After the first start, the log has this line:
[db] schema is up to date (sqlite)
and the file exists where you expect it:
ls -l /opt/choirmaster/data
If something goes wrong
| What you see | Cause |
|---|---|
| The site starts empty after a restart or a move | A relative SQLITE_PATH and a different working directory, or in Docker no volume on /app/data. Look for a second data folder. |
attempt to write a readonly database, or unable to open database file | The service account cannot write to the file or to its folder. |
database is locked | Something else held the file for more than five seconds: a second copy of the site, or a backup tool that locks it. |