How to open a SQLite file without installing anything

A .sqlite, .sqlite3 or .db file is a whole database in one file: tables, rows, indexes, the lot. Browsers keep their history in one, apps keep their data in one, and developers send them around as test data. Double-clicking does nothing useful, because there is no app for "a database" until you install one. On a Mac you already have one.

sqlite3 -readonly shop.sqlite

.tables
.mode box
SELECT id, name, city, plan FROM customers LIMIT 5;

customer_totals  customers        orders
┌────┬───────────────────┬────────┬──────┐
│ id │       name        │  city  │ plan │
├────┼───────────────────┼────────┼──────┤
│ 1  │ Mallory Quinn     │ Vienna │ team │
│ 2  │ Katya Okoye       │ Oslo   │ pro  │
│ 3  │ Pavel Vasquez     │ Berlin │ free │
│ 4  │ Viktor Eriksen    │ Lyon   │ team │
│ 5  │ Beatrice Lindgren │ Gdansk │ pro  │
└────┴───────────────────┴────────┴──────┘

Look around with sqlite3

Three dot-commands do most of the work. .tables lists tables and views. .schema shows how a table is built:

sqlite> .schema orders
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  total       REAL NOT NULL,
  placed      TEXT NOT NULL,
  status      TEXT NOT NULL
);

And .mode box draws results as a table instead of the default pipe-separated lines. After that it is ordinary SQL:

sqlite> SELECT status, count(*) AS n FROM orders GROUP BY status;
┌───────────┬────┐
│  status   │ n  │
├───────────┼────┤
│ delivered │ 65 │
│ paid      │ 65 │
│ pending   │ 65 │
│ shipped   │ 65 │
└───────────┴────┘

With -readonly, anything that writes is refused and the file is left as it was. We ran DELETE FROM orders; against it to check:

Error: stepping, attempt to write a readonly database (8)

The SHA-1 of shop.sqlite was the same before and after every command in this section.

Save a table as CSV

.once sends the next result to a file instead of the screen:

sqlite> .headers on
sqlite> .mode csv
sqlite> .once customers.csv
sqlite> SELECT id, name, email, city FROM customers;

That wrote customers.csv with a header row and all 120 customers:

id,name,email,city
1,"Mallory Quinn",[email protected],Vienna
2,"Katya Okoye",[email protected],Oslo

.once works with -readonly, because the CSV is a new file, not the database.

An app's database: copy it first

Opening a file that a running program also has open is where things go wrong. Firefox keeps your history in places.sqlite, Chrome in a file called History, and many apps use SQLite for everything. Two things can happen.

The file is locked. Some programs hold the database exclusively while they run. Then every command fails with:

Error: in prepare, database is locked (5)

Quit the program and try again.

The newest changes are in another file. Most apps run SQLite in WAL mode: new changes go into a -wal file next to the database (with a -shm index beside it) and are folded into the main file later. We wrote 5 rows to a WAL database, left 2 of them in the -wal, and copied the files the way you would in Finder:

So copy the -wal with the database, or better, let SQLite make one self-contained copy. VACUUM INTO reads everything, including what is still in the -wal, and writes a new ordinary database:

sqlite3 -readonly notes.db "VACUUM INTO 'copy.db'"
sqlite3 -readonly copy.db

We ran that while the program still had the database open. copy.db held all 5 rows, it is a single file with no -wal, and the original's checksum and modification time did not change.

Open it in the browser

What to know before you rely on it:

• Your file is never written to. The viewer edits a copy in the browser. Until you download, the sidebar marks the database as changed.
• It opens only the main file. Drop the -wal with it and you get a warning that changes still in the -wal are missing. To include them, make a copy with VACUUM INTO as above and open that.
• The whole file is held in memory. You get a warning from 300 MB, and the limit is about 1 GB; your device may stop sooner.
• No CSV export. It downloads the database and copies a single row as JSON. For CSV, use sqlite3 as above.
• Encrypted databases (SQLCipher) don't open, and tables that need the FTS5 or R*Tree extensions open read-only, because the bundled SQLite (3.49.1) doesn't include them.

On Windows: DB Browser for SQLite

Windows has no sqlite3 command. If you want a desktop app, DB Browser for SQLite (3.13.1) is free and open source. Next to the installer, its download page offers a .zip (no installer) and a PortableApp, which are the ones to try on a laptop where you can't install software. There is no portable build for ARM64 Windows. On a Mac it is brew install db-browser-for-sqlite. We did not test it on Windows. Use its read-only open option on an app's database, for the same reasons as above.

If you'd rather have the command, sqlite.org offers it as Command-line tools for Windows x64, a zip from its download page; it runs without installing.

Which to use

• On a Mac or Linux: sqlite3 -readonly file.db. It is already there, and .once gets you a CSV.
• An app's live database: VACUUM INTO a copy first, then open the copy with anything.
• Windows, a managed laptop, a phone, or one quick look: the SQLite Database Viewer. Nothing to install, and nothing leaves the device.
• Editing and handing it back: the viewer, then Download, and send the new file. The original stays as it was.