SQLite Viewer

What it's for

Look inside an SQLite database and query it with SQL, or query CSV files with SQL as if they were a database. SQLite is the small database built into phones, browsers and many apps: their .db, .sqlite and .sqlite3 files are SQLite databases.

• When you’re debugging an app and want to see what it actually stored in its database.
• When you have a CSV export too big or awkward to sort out in a spreadsheet, and a GROUP BY or a JOIN with another CSV would answer the question in one line.

How to use it

Choose a database file or one or more CSV files under Open, or drop them on the page. A database opens in place of whatever was open; each CSV (or TSV) becomes a table beside what’s there, named after its file, with its first row as the column names. A CSV column made only of whole numbers becomes INTEGER, of numbers REAL, and anything else TEXT; blank cells become NULL.

Table lists the tables with their row counts, and the views. Choosing one shows its first 1,000 rows and puts that query in the SQL box to change. Type any SQL there, several statements if you like, and press Run (or ⌘ Enter / Ctrl+Enter): the last statement that returns rows is shown as a table. Schema has the CREATE statements for everything in the database.

Save result as CSV and Copy result as CSV take the rows of the last result. Save database saves the database as it is now, with your changes, as a new file. With nothing open, you can start from an empty database by writing CREATE TABLE yourself.

Example

With nothing open, paste this into the SQL box and press Run:

CREATE TABLE fruit (name TEXT, colour TEXT, price REAL);
INSERT INTO fruit VALUES ('apple', 'green', 0.4), ('banana', 'yellow', 0.25), ('cherry', 'red', 3.5), ('lime', 'green', 0.3);
SELECT colour, COUNT(*) AS kinds, AVG(price) AS average FROM fruit GROUP BY colour ORDER BY kinds DESC;

The tool reports 3 rows and shows green 2 0.35, yellow 1 0.25 and red 1 3.5. Table now lists fruit (4 rows).

Good to know

Your file is never changed: the database is copied into the page, and changes stay in that copy until you save it. Large databases take as much memory as their size, and a query’s whole result is held in memory, so add LIMIT when exploring a big table; only the first 2,000 rows of a result are shown, but CSV gets them all.

A database an app is using may be in write-ahead log (WAL) mode, keeping its latest changes in a separate -wal file that isn’t read. The tool warns when it sees this: quit the app first, so it moves those changes into the main file. Encrypted databases (SQLCipher) and tables that need an extension SQLite doesn’t have built in can’t be read. Blobs show as their size, and as hexadecimal in CSV.

SQLite’s JSON functions and full-text search (FTS3 and FTS4) are built in; FTS5 and R*Tree tables aren’t, so queries on those fail with no such module. SQLite is downloaded from this site the first time you use the tool (about 700 KB).