I'm building a small Node.js/Express web app backed by a local SQLite database on the same Debian VPS as the application. I've deployed it manually over SSH by cloning the code repository, but I'm unsure how to manage the .db file safely in production.
The app is currently a small event manager with only a few tables and an expected single user, so SQLite seems appropriate for now. I'm considering storing the database outside the project directory so pulling new application code won't overwrite it. However, I still need a sensible process for backups, restoring data, and applying schema migrations as the app evolves.
What deployment and maintenance approach would you recommend for this kind of stateful SQLite application? Should I continue with SQLite, or move to a server database such as MySQL?
3 Answers
For a single-user prototype with only a few tables, moving immediately to MySQL or another server database may add operational work without solving a problem you currently have. SQLite is a reasonable choice as long as you understand its write-concurrency and availability limits.
The database file should live in persistent application data storage, not inside the deploy directory. Your deployment can replace the code while leaving that data path untouched. Automate regular backups, retain several historical versions, and periodically verify that a backup can actually be restored. Also consider what happens if the VPS itself fails; a backup stored only on that machine won’t help much.
SQLite can be perfectly reasonable for a small, low-concurrency prototype. The main concern isn’t necessarily performance; it’s making backups and deployments safe. Keep the database outside the repository, configure its path through an environment variable, and make sure the application user has appropriate permissions.
For backups, don’t simply copy a live database file while it may be receiving writes. Use SQLite’s backup command, such as `sqlite3 app.db ".backup 'backup.db'"`, and store copies somewhere separate from the VPS if possible. WAL mode can also improve behavior when reads and writes overlap. If you later need continuous off-machine replication, a tool such as Litestream is worth investigating.
Treat migrations as a versioned deployment step rather than editing the production database manually over SSH. Each migration should make one known schema change, record that it ran, and be applied in order before the new application code starts serving requests.
Create numbered migration scripts, such as `001-create-events.sql` and `002-add-photo-status.sql`, and keep them in the codebase. A migration runner records the latest applied number in a metadata table, runs any newer scripts inside a transaction where possible, and then starts or reloads the application. Test the migration against a copy of production first, and keep a recent backup before changing the schema.
Database schema changes should be handled as a sequence of migrations from version N to version N+1. The database version does not need to match the application version. A migration tool such as Flyway can manage this for SQLite and may make a later move to another relational database easier, although a small project can also use a simple custom migration runner.
Keep migrations in source control, run them automatically during deployment, and avoid destructive changes until you have confirmed that the backup and restore process works.
What does applying migrations as a versioned deployment step look like in practice?