file is not a database

What it means

Every SQLite database begins with the same 16 bytes: the text SQLite format 3 followed by a zero byte. When SQLite reads a file that doesn’t begin that way, it refuses to go further and returns SQLITE_NOTADB (code 26), “file is not a database”.

Opening the file doesn’t read it, so the error appears on the first statement, even PRAGMA user_version. A zero-byte file is different: SQLite treats it as a new, empty database, so a wrong path gives no such table instead.

Common causes

1. It isn’t an SQLite file. A CSV, a .sql dump (SQL text), JSON, a spreadsheet, or an HTML error page saved by a download script, named .db or .sqlite.
2. It’s a compressed backup: a .gz or .zip opened as if it were the database.
3. It’s a Git LFS pointer. The repository was cloned without Git LFS, so the file is a short text file pointing at the real one.
4. It’s encrypted with SQLCipher or the SQLite Encryption Extension. Without the right library and key, the whole file looks like random bytes. SQLCipher gives this same error for a wrong key.
5. The header is damaged: an interrupted copy, or something wrote over the first bytes.
6. You opened the -wal or -shm file instead of the database next to it.

How to fix it

Look at the first bytes

head -c 16 app.db | xxd
file app.db

A real database:

00000000: 5351 4c69 7465 2066 6f72 6d61 7420 3300  SQLite format 3.
uniq.db:   SQLite 3.x database, last written using SQLite version 3051000, …

Anything else tells you what the file really is: readable text (CSV, SQL, an LFS pointer, HTML), gzip compressed data or Zip archive data. Bytes with no pattern mean an encrypted or damaged file; file then prints data or a wrong guess (it called 4 KB of random bytes an “OpenPGP Secret Key”).

Load text formats into a new database

A SQL dump is a script; run it into a new file:

sqlite3 new.db < dump.sql

A CSV is imported into a table. With --csv, the sqlite3 shell creates the table from the header row if it doesn’t exist:

sqlite3 new.db ".import --csv people.csv people"

Unpack compressed backups

gunzip -c app.db.gz > app.db

For a .zip, use unzip. Then open the unpacked file.

Fetch the real file from Git LFS

If the file starts with version https://git-lfs.github.com/spec/v1, install Git LFS and run git lfs pull in the repository to replace pointers with the real files.

Open encrypted databases with the right library and key

An SQLCipher database needs SQLCipher (its own shell or library) and the key, given with PRAGMA key before the first query. The plain sqlite3 shell can’t read it. SQLCipher’s maintainers list a wrong or missing key, a key given to an unencrypted database, and a different major version of SQLCipher as causes of this error.

Salvage a file with a damaged header

If the rest of the file is intact, .recover can read the pages without the header. Work on a copy:

sqlite3 damaged.db .recover > recovered.sql
sqlite3 recovered.db < recovered.sql

See database disk image is malformed for checking the result.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0. A CSV file:

printf 'id,name\n1,Ada\n' > notdb.csv
sqlite3 notdb.csv "SELECT 1 FROM sqlite_schema;"

Error: in prepare, file is not a database (26)

The shell adds “Error: in prepare,” and the result code; SQLite’s message is file is not a database. .tables on the same file printed Error: file is not a database.

The same error came from a .sql dump, a gzipped copy of a working database, a Git LFS pointer file, 4 KB of random bytes, the -shm file of a WAL database, and a working database whose first 16 bytes were overwritten. gunzip -c restored the gzipped one. .recover on the overwritten one recovered every table and row (4 of 4 rows in users). A zero-byte file returned 0 for SELECT count(*) FROM sqlite_schema instead of an error.

In Inlet

Inlet opens SQLite database files directly and can create a new one. To use a CSV, create a new database, create a table and use CSV import; a .sql dump is statements to run, which the query editor can run all at once (⇧⌘↩).