SQLite: CLI Inspection, WAL Mode, Backup, and Migration

SQLite is an embedded relational database engine. The application reads and writes a database file directly, without connecting to a separate database server.

Think of SQLite as a field notebook with a built-in index. The notebook travels with the application, yet its pages still follow rules: tables define the layout, transactions protect complete edits, and indexes help find entries. Portability does not remove the need to protect the notebook.

How SQLite Began

D. Richard Hipp originally developed and released SQLite in 2000. The official release chronology starts with SQLite 1.0 on August 17, 2000. SQLite 3.0 arrived in 2004 with the modern file format and support for binary data plus Unicode.

The project publishes its source and documentation in the public domain. Its self-contained library and stable file format helped it spread through phones, desktop software, browsers, devices, command-line tools, and test environments.

That history explains why SQLite appears inside applications instead of beside them as another service. It does not make every workload local-file shaped. Concurrency, backup, permissions, and deployment topology still decide whether the fit is sound.

Where SQLite Fits

SQLite fits data that belongs close to one application or device. It can replace a custom JSON, XML, or binary project file while adding SQL queries, constraints, transactions, and indexes.

SQLite is a strong fit for these uses:

• Mobile and desktop application state.
• Command-line history, caches, and searchable local data.
• Test databases that need no server provisioning.
• Embedded devices and application file formats.
• Small websites on one host with short, modest writes.

A client/server database fits better when data lives across a network, several hosts need to write concurrently, or database roles and central operations are part of the requirement. Compare PostgreSQL and MySQL before adding workarounds for a deployment that has outgrown one file.

How to Open and Inspect a Database

The examples below target SQLite 3.53.4, released on July 24, 2026 and current on July 29, 2026. Check the installed library because operating systems and language runtimes can bundle a different SQLite version.

sqlite3 --version
sqlite3 app.db

Inside the shell, inspect the file before changing it:

.databases
.tables
.schema
.headers on
.mode column
SELECT sqlite_version();
.quit

The dot-prefixed lines are commands understood by the sqlite3 shell, while SELECT sqlite_version(); is the SQL statement sent to the database.

Create a small notes table with required fields and a stable sort key:

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX notes_created_idx
ON notes (created_at DESC, id DESC);

INSERT INTO notes (title, body)
VALUES ('Backup check', 'Restore the notebook before trusting the copy.');

SELECT id, title, created_at
FROM notes
ORDER BY created_at DESC, id DESC;

INTEGER PRIMARY KEY identifies the row using SQLite's integer row identifier. The composite index matches the sort order and uses id to break timestamp ties. The same CLI can inspect a backup or reproduce a migration without first writing application code.

How WAL Mode Changes Concurrency

SQLite uses a rollback journal by default. Write-ahead logging, or WAL, appends changes to a separate log before checkpointing them into the main database file.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode;
PRAGMA wal_checkpoint;

WAL lets readers continue while a writer is active in common cases, which can improve a read-heavy local application. It does not permit several simultaneous writers. SQLite still serializes writes, so long transactions make other writers wait.

The notebook now has an attached change log. The main database file and the WAL state belong together until a checkpoint integrates the changes. All processes using a WAL database must run on the same host because the wal-index relies on shared memory. Do not place the database on a network filesystem to let several application servers write it.

Automatic checkpoints handle many workloads, but a production review should still observe WAL growth, busy errors, checkpoint timing, shutdown behavior, and backup consistency.

How to Back Up and Migrate SQLite

Use SQLite-aware backup methods while the database is live. The command-line shell's .backup command uses the backup API rather than treating the active database as an ordinary file.

sqlite3 app.db ".backup 'app-backup.db'"
sqlite3 app-backup.db "PRAGMA integrity_check;"

An integrity check is useful, but a restore test must also open the application schema, run representative queries, and confirm that the backup contains the expected rows. Keep a protected copy away from the device or host it protects.

Track schema changes in migrations and apply them inside transactions where SQLite supports the complete operation:

BEGIN IMMEDIATE;

ALTER TABLE notes
ADD COLUMN archived_at TEXT;

CREATE TABLE schema_migrations (
  version TEXT PRIMARY KEY,
  applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO schema_migrations (version)
VALUES ('2026-07-29-add-archived-at');

COMMIT;

BEGIN IMMEDIATE acquires the write transaction before migration work begins, so a lock conflict appears before partial work. Rehearse table-rebuild migrations on a realistic backup because some schema changes require a new table, copied rows, and a controlled rename.

Common Pitfalls

Sharing the File over a Network Filesystem

Symptom: WAL mode fails or database access becomes unreliable across hosts. Cause: several machines are treating one file as a network database. Fix: keep SQLite and every accessing process on one host, or move shared network data to a client/server database.

Holding Write Transactions Open

Symptom: other writes return busy errors or wait for noticeable periods. Cause: application code performs slow work while a write transaction remains open. Fix: prepare external work first, keep database writes short, commit promptly, and set a measured busy timeout.

Copying Only the Main File During a Write

Symptom: a copied database is missing recent changes or fails verification. Cause: a file copy ignored active journal or WAL state. Fix: use the online backup API, the shell's .backup command, or another documented SQLite-aware method, then test the copy.

Treating One File as No Operations

Symptom: an application update loses data or cannot restore an older schema. Cause: the single-file model was mistaken for a system without backups or migrations. Fix: document the file location, migration ledger, backup schedule, restore command, retention, and recovery owner.

Frequently Asked Questions

Is SQLite a relational database?

SQLite is a relational database with transactional SQL support. It implements tables, indexes, triggers, views, constraints, and joins. The engine is embedded in the application process instead of running as a separate server.

Can SQLite run a website?

SQLite can run a website when one application host, modest write concurrency, and a local database file match the workload. A website that needs several writing hosts, database-level user administration, replicas, or heavy concurrent writes usually fits a client/server database better.

Does WAL mode allow multiple writers?

WAL mode still serializes every database write. It lets readers continue while a writer appends changes. Keep write transactions short, set a suitable busy timeout, and measure lock waits. WAL also requires all processes using the database to remain on the same host.

When should an application move from SQLite?

Move when the deployment no longer fits one local database file: several application servers need writes, lock waits affect users, centralized roles or replicas are required, or managed recovery becomes necessary. The trigger is a changed workload or operating model, not SQLite's age.

What to Read Next

Use SQL schema design basics to strengthen keys and constraints, then compare MySQL vs PostgreSQL vs SQLite before choosing a server database.

Return to the SQL databases hub for all four database guides.