SQLite in depth

SQLite is the database you already reach for without thinking: better-sqlite3 in a bot, links.db next to a URL shortener, a notes table in a small web app. It's a real relational database — proper SQL, indexes, transactions — that happens to live in a single file with no server to run. This guide is the depth behind that: how to make it fast and safe with WAL mode, how to back it up without corrupting it, where its limits actually are, and the honest rule for when to stop using it and move to a managed database.

The one-file model

On the Node.js application, better-sqlite3 is the driver to use — it ships prebuilt binaries, so it installs from the Packages page with no compiler. new Database('app.db') opens (or creates) a file in /home/container, right next to your code:

const Database = require('better-sqlite3');
const db = new Database('app.db');

db.exec(`CREATE TABLE IF NOT EXISTS notes (
  id   INTEGER PRIMARY KEY,
  body TEXT NOT NULL
)`);

That file is your database. It survives restarts. It does not survive a reinstall or an application switch — those wipe /home/container. Hold that thought; it decides half of this guide.

🎯 Good to know: In Python the equivalent is the built-in sqlite3 module (import sqlite3; con = sqlite3.connect("app.db")) — nothing to install at all. The pragmas and backup ideas below apply to both.

Turn on WAL mode

By default SQLite uses a rollback journal: while one connection writes, readers are blocked, and vice-versa. For a bot or web app handling several things at once, that's the wrong default. WAL (Write-Ahead Logging) fixes it — writers append to a separate log file, so readers never block the writer and the writer never blocks readers. Set it once, right after opening the database:

const db = new Database('app.db');
db.pragma('journal_mode = WAL');      // readers and a writer can work at once
db.pragma('synchronous = NORMAL');    // safe with WAL, and much faster
db.pragma('foreign_keys = ON');       // enforce your foreign keys (off by default)
db.pragma('busy_timeout = 5000');     // wait up to 5s for a lock instead of erroring

Reading journal_mode back returns wal, which is how you confirm it stuck. WAL is a property of the database file, not the connection, so you only need to set it once — but setting it every startup is harmless and the honest habit.

Once WAL is on you'll see two companion files appear next to app.db:

Those two extra files are normal. They're part of the database — never delete them while the app is running, and include them when you copy the database by hand (more on that next).

💡 Tip: busy_timeout is the single most useful pragma for a busy app. Without it, a write that hits a momentary lock throws SQLITE_BUSY immediately; with it, SQLite quietly waits and retries for you.

Backing up SQLite the right way

You cannot back up a live SQLite database by copying app.db alone while writes are happening — you can catch it mid-write and copy the -wal file inconsistently. There are two correct ways.

1. Online backup (safe while the app runs). better-sqlite3 has a built-in backup that snapshots a consistent copy even under active writes:

// Writes a complete, consistent copy — no need to stop anything
await db.backup(`backups/app-${Date.now()}.db`);

This is the one to schedule from your code — for example on a timer, or before a risky migration. The copy it produces is a normal database file you can open read-only.

2. Checkpoint, then copy the file. If you want a plain file copy (to hand to a server backup, or to download over SFTP), first fold the WAL back into the main file so the single file is complete:

db.pragma('wal_checkpoint(TRUNCATE)');   // merge the -wal into app.db
// now app.db on its own is a full, consistent snapshot

After a TRUNCATE checkpoint, copying app.db by itself is safe. Either way, remember the file lives in /home/container like any other server file, so a server backup captures it — that's the zero-code option: take a backup from the Backups page before anything risky and you've got a rollback. (A managed database, by contrast, is backed up on its own Databases page, not by server file backups.)

⚠️ Heads up: Add app.db, app.db-wal, and app.db-shm to your .gitignore. A database file does not belong in a repository, and a Git deploy that carried an old app.db would overwrite your live data. See Keep secrets out of Git.

Size and limits — honestly

SQLite is far more capable than its reputation. The realities that actually matter on Falix:

When SQLite is enough — and when it isn't

This is the decision the whole guide is for.

SQLite is the right call when:

• One process owns the data — a single bot, a single web app, on one server.
• The data is happy living on that server, and a server backup is enough safety.
• You want zero extra services and the lowest latency.

Move to a managed database (MySQL/PostgreSQL/MongoDB) when:

• The data must survive reinstalls and application switches without you thinking about it.
• Two or more servers need to read or write the same data.
• You've genuinely outgrown one writer — many concurrent writers, or you want the database to stay up even while the app server sleeps on a free timer.

The migration path is painless: your SQL barely changes. See Connect your app to a database for the driver swap, and Choosing between Redis, SQLite, MySQL and Mongo if you're still deciding shapes.

Verify it works

Start the server and watch the Console. After opening the database, print the journal mode to prove WAL is on and run a round-trip write:

console.log('journal mode:', db.pragma('journal_mode', { simple: true })); // "wal"
db.prepare('INSERT INTO notes (body) VALUES (?)').run('hello');
console.log('rows:', db.prepare('SELECT count(*) AS c FROM notes').get().c);

Seeing wal and a growing row count means the database is live, in WAL mode, and persisting between restarts. Open the File Manager and you'll see app.db (plus -wal and -shm) sitting in /home/container.

Troubleshooting

• SQLITE_BUSY / "database is locked" — two writes collided. Set db.pragma('busy_timeout = 5000') so SQLite waits for the lock, and make sure you're not opening the same file from a second process. See Database connection errors.
• My data vanished after a reinstall — SQLite lives in /home/container, which a reinstall or application switch wipes. Restore a server backup, and for data that must never disappear, use a managed database.
• The -wal file is huge — a long-running writer with no checkpoint. Run db.pragma('wal_checkpoint(TRUNCATE)') periodically (or on a schedule) to fold it back in.
• SqliteError: no such table — your CREATE TABLE IF NOT EXISTS didn't run before the query, or you're pointing at a different file path than you think. Log the path you passed to new Database(...).
• Corruption after a crash while copying — you copied app.db mid-write without checkpointing. Use db.backup(...) or checkpoint first (above).

Everything past the Falix layer is standard SQLite — the official documentation at sqlite.org owns the SQL dialect, pragmas, and file format in full.

Next steps

• Storing data for your bot
• Connect your app to a database
• Automate and manage backups
• Choosing between Redis, SQLite, MySQL and Mongo