SQLite Database Inspector

How to use

1. Drop the .sqlite, .db or .sqlite3 file onto the box, or click to choose one. It stays in the browser.
2. Read the header first: the page size and page count multiply out to the file size, and free pages are what a VACUUM would give back.
3. Check the journal mode. If it says WAL, any commits still in the -wal sidecar are not in this file, so treat the row counts as a floor.
4. Go through the table list for the row counts, the columns with their declared types, and the indexes attached to each table.
5. Open the CREATE statement under a table when the parsed column list looks wrong — the original DDL is the authority.

Frequently asked questions

Why does the row count here differ from what my application reports?

Almost always the write-ahead log. In WAL mode SQLite appends new and changed pages to a separate -wal file and only folds them into the main database at a checkpoint, so a database copied without its sidecar is a snapshot of the last checkpoint. This tool reads only the file you give it, so recent inserts can be missing. Run PRAGMA wal_checkpoint(TRUNCATE) before copying, or copy the -wal and -shm files too and let SQLite recover them. The other cause is a WITHOUT ROWID table, whose count is shown as unknown rather than wrong.

Can it open an encrypted database, like one made with SQLCipher?

No, and it will say so rather than showing you nonsense. SQLCipher encrypts the header along with the pages, so an encrypted file does not begin with the bytes "SQLite format 3" and there is nothing to parse without the key. That check is also what tells you a file is encrypted rather than corrupt: a truncated but unencrypted database still has a readable header, and this tool will read what it can and warn that the page count does not match the file's length.

What are free pages, and should I run VACUUM?

Deleting rows does not shrink a SQLite file; the pages that held them go on the freelist to be reused by future inserts. Free pages are therefore space the file already owns and will reuse. VACUUM rebuilds the database without them and gives the space back to the filesystem, which is worth doing after a large deletion or if you are shipping the database somewhere, but it rewrites the entire file and invalidates any open connection, so it is not something to run casually on a live database.

What do user_version and application_id mean?

They are two 32-bit slots in the header that SQLite never touches itself, for applications to use as they like. Most projects use user_version as a schema migration counter — the app compares it against the version its code expects and runs the migrations in between — and application_id as a magic number identifying the file's format, so that file(1) and similar tools can tell one kind of SQLite database from another. Both read zero when nothing has set them.

Why is the schema version different from the schema format?

The schema version, which SQLite calls the schema cookie, is a counter bumped every time the schema changes; prepared statements compare it to notice that they need recompiling. The schema format is the layout generation of the file, between 1 and 4, and tells you which features the file may use — 4 means descending indexes and boolean literals are allowed, and it is what any SQLite from the last two decades writes.

Does the file get uploaded anywhere?

No. The database is read with the browser's own File API and parsed by JavaScript on the page; nothing is sent to a server, and there is no server-side component to send it to. You can confirm this by opening the page, disconnecting from the network, and inspecting a file anyway — everything still works, because the whole tool is already in the page.

Related tools