SQLite Viewer

Common questions

How do I open a SQLite file here?

Press Open file and pick it, or drop the file anywhere on the dark stage. The reading happens in this tab: nothing is uploaded, there is no account, and no size is refused. The page opens on a sample database so the job is visible before you open anything of your own: 6,144 bytes, 12 pages of 512 bytes, 4 tables and 64 rows between them, with NULLs, two BLOBs and one note of 613 characters, which is more than a 512-byte page can hold and so is stored on an overflow page the reader has to follow. Your own file is read the same way and is never written to.

What SQL can I run against the file?

One SELECT over one table: a column list or *, FROM one table, WHERE with terms joined by AND, ORDER BY one column with ASC or DESC, and LIMIT with OFFSET. A WHERE term compares with =, !=, <, <=, >, >=, LIKE, NOT LIKE, IS NULL or IS NOT NULL, and names may be quoted with double quotes, square brackets or backticks. Everything else is refused by name rather than quietly ignored: JOIN, a second table in the FROM list, OR, IN, BETWEEN, GROUP BY, HAVING, DISTINCT, UNION, a subquery, a function call, a column alias and a second statement after a semicolon each get their own message naming what was refused. Leave the box as SELECT * FROM a table and you are simply browsing it.

Can this change or damage my database?

No. There is no code here that writes: the file's bytes are read through views onto the array in memory and are never assigned into, and the file on your disk is only ever read. UPDATE, INSERT, DELETE, DROP, ALTER, CREATE, REPLACE, VACUUM, BEGIN, ATTACH, DETACH and PRAGMA are each refused by name, and load_extension is refused as well, because no SQLite extension is ever loaded and no function at all is run. The Mode cell under the stage says Read-only because read-only is the only mode this page has.

Why does one of my tables say it is not readable?

Because its rows are not kept in the shape this reader decodes, and saying so beats showing you a table that is half right. There are three kinds. A WITHOUT ROWID table keeps its rows in an index b-tree, whose records are laid out in a different order. A virtual table, an FTS or R-tree table for example, has no b-tree of its own at all: a module inside SQLite produces its rows when a query runs. And a table with a VIRTUAL generated column stores fewer values in each row than it declares columns, so the values and the column names would not line up. The table is still listed by name, and selecting it shows the reason. Views are listed separately as views: a view is a stored query rather than stored rows.

How are NULL and BLOB values shown?

NULL is shown as the word NULL in a dimmer colour, and never as an empty cell, because an empty string is a different value and has to look different. A BLOB is shown as its byte length, for example BLOB 16 bytes, and the cell's tooltip carries the first bytes as hex so you can recognise a PNG or a JPEG header. The CSV that Copy and Download produce writes a NULL as an empty field and a BLOB as blob:16 bytes: never part of the bytes, because half a blob in a spreadsheet is worse than none. Everything else is written by the RFC 4180 rules, so a value holding a comma, a quote or a line break comes back quoted.

The row count here is lower than the count my app shows. Why?

Most likely the database is in WAL mode and the newest rows are still in the -wal file sitting beside it. This page reads the one file you opened and never the sidecar, so when the header says WAL mode it says so in a note under the stage rather than letting you believe you are looking at everything. Close the program that owns the database, or run a checkpoint in it, then open the file again and the rows will be there.

How large a database can it open, and how does it stay quick?

No limit is imposed here. The file is held in the tab and its pages are read as the query needs them, so the ceiling is the memory of the machine you are sitting at rather than a number this page invented. One scan looks at up to 200,000 rows, and an ORDER BY holds up to 50,000 matching rows while it sorts them. When a scan reaches one of those it stops and says so under the stage, and the count on screen is marked as at least that many rather than presented as exact. Narrow the query with WHERE or LIMIT and the count is exact again.

Why does a number show as 12.0 rather than 12?

Because the column is REAL and the value is stored as a float, which is worth being able to see. SQLite saves room by writing a REAL column's whole number as an integer and converting it back to a float when it reads it, and this reader follows the same rule, so a REAL column prints 12.0 where an INTEGER column prints 12. It is what the sqlite3 shell prints too. Integers are printed in full, including 64-bit values past the range a JavaScript number can hold exactly.

What happens if the file is encrypted or damaged?

It is refused, with a message naming what went wrong. An encrypted file does not start with the 16 bytes SQLite writes at the front of every database, so the page says it is not a SQLite database instead of showing nonsense; nothing here decrypts anything. A damaged file is caught where the damage is: a page pointer that leaves the file, a cell pointer outside its page, a payload longer than the file, a b-tree that points back at itself and an overflow chain that does not end are each named, and the page tells you which page number is at fault rather than hanging or showing a wrong value.

Is my file or my query remembered?

The query and the rows-per-page setting are kept in this browser alone, so the box is as you left it when you come back, and the Start over button above the tool forgets them. The database itself is not remembered: it stays on your disk, and this page holds it only while the tab is open. If your browser blocks storage entirely, everything on the page still works and nothing is kept between visits.