How to open a SQLite database file
Open a .db or .sqlite file with the sqlite3 shell, Python, Node.js or a SQLite viewer, safely read-only, and avoid the empty-file and WAL traps.
Quick Answer
Pass the file to the sqlite3 shell, then list what's inside:
sqlite3 app.db
sqlite> .tablesTo make sure you can't change anything, open it read-only:
sqlite3 -readonly app.dbIf you'd rather click than type, open the file in a SQLite viewer instead.
Is it really a SQLite file?
SQLite files often end in .db, .sqlite, .sqlite3 or .db3, but the extension means nothing. Many apps use other names, such as .data, or no extension at all. The reliable check is the first 16 bytes, which are always SQLite format 3 followed by a zero byte:
$ head -c 16 app.db | xxd
00000000: 5351 4c69 7465 2066 6f72 6d61 7420 3300 SQLite format 3.
$ file app.db
app.db: SQLite 3.x database, last written using SQLite version 3043002, ...If the header is something else, the file isn't SQLite, or it's encrypted (SQLCipher, for example), and sqlite3 will say file is not a database.
In VisuaLeaf
Open it in a SQLite viewer
Double-clicking a .db file usually does nothing, because no operating system ships an app for it. A SQLite viewer opens the file and shows its tables, rows and schema without any commands.
VisuaLeaf works as a SQLite viewer on Mac, Windows and Linux: pick a local .db or .sqlite file, with no server or connection string. You get the tables and rows, plus Table Management for each table: column types, primary and foreign keys, and the full CREATE TABLE statement. The SQL editor runs PRAGMA statements too, so PRAGMA journal_mode; tells you whether the file is in WAL mode before you copy it. Other options include DB Browser for SQLite and the SQLite extensions for VS Code.
Work on a copy when the file belongs to a running app, like a browser profile or a mobile backup. Copy the -wal and -shm files with it, as shown below.
Open it in the sqlite3 shell
The sqlite3 command ships with macOS and most Linux distributions. On Windows, download the "command-line tools" bundle from sqlite.org.
sqlite3 app.db # open (creates the file if it doesn't exist!)
sqlite3 -readonly app.db # refuse all writes
sqlite3 'file:app.db?mode=ro' # read-only, and never create the fileInside the shell, these are the first commands worth running:
| Command | What it does |
|---|---|
.tables | List tables and views |
.schema tablename | Show the CREATE statement for a table |
.headers on + .mode box | Readable, boxed query results |
.databases | Show which file is open (catches typos) |
.open other.db | Switch to another file without leaving the shell |
.quit | Exit |
What we found testing this
- A typo in the path gives you an empty database, not an error.
sqlite3 typo.dbcreated a 0-bytetypo.db,.tablesprinted nothing, andSELECT count(*) FROM sqlite_schemareturned 0. Python'ssqlite3.connect('typo.db')and better-sqlite3'snew Database('typo.db')also created empty files. To fail instead, use?mode=ro(error:unable to open database file) or better-sqlite3'sfileMustExist: true(error codeSQLITE_CANTOPEN). - Copying only the .db file of a WAL database can lose whole tables. We put a database in WAL mode, created a table, inserted 1,000 rows, and copied just
app.dbwhile the app still had it open. The copy saidno such table: t: the table and every row were still inapp.db-wal. Copying the-walfile alongside it brought all 1,000 rows back. Close the app first, or copy all files with the same name. - "Not a database" errors can show up late. On a plain text file,
SELECT 1succeeded (exit code 0) because it never reads the file, while.tablesfailed withError: file is not a database. A connection that opens fine proves nothing until the first real query. -readonlystops writes with SQLite's own error. An INSERT failed withattempt to write a readonly database (8), and sqlite3 exited with code 8.
$ sqlite3 copy/app.db "SELECT count(*) FROM t"
Error: in prepare, no such table: t
$ cp app.db-wal copy/ && sqlite3 copy/app.db "SELECT count(*) FROM t"
1000Open it from Python or Node.js
Python's sqlite3 module is built in. Use a URI to open read-only and fail on a missing file:
import sqlite3
con = sqlite3.connect("file:app.db?mode=ro", uri=True)
print(con.execute("SELECT name FROM sqlite_schema WHERE type = 'table'").fetchall())In Node.js, better-sqlite3 is the common choice:
import Database from 'better-sqlite3'
const db = new Database('app.db', { readonly: true, fileMustExist: true })
console.log(db.prepare("SELECT name FROM sqlite_schema WHERE type = 'table'").pluck().all())Frequently Asked Questions
What program opens a .db file?
If it is a SQLite file (the first bytes read "SQLite format 3"), the sqlite3 command-line shell or any SQLite viewer opens it. Other databases also use .db, so check the header first.
Why does my SQLite database have no tables?
Most often the path was wrong and sqlite3 created a new empty file. Run .databases to see which file is open. The other common cause is a WAL-mode database copied without its -wal file.
How do I open a SQLite file without changing it?
Use sqlite3 -readonly file.db, or a URI with mode=ro: sqlite3 'file:file.db?mode=ro'. The URI form also refuses to create a missing file.
What are the -wal and -shm files next to my database?
They belong to WAL (write-ahead log) mode. The -wal file holds recent changes that are not yet copied into the main file, so always keep it with the .db file when copying.