Query SQLite Databases in Your Browser

Query SQLite Databases in Your Browser

When an app needs a self-contained database, it often chooses SQLite. The SQL Workbench opens any .sqlite or .db file through sql.js, a WebAssembly build of SQLite, so every table is available to query immediately without a server, install, or upload.¹

Where SQLite files come from

SQLite files can come from many local systems. Mobile apps, browser history exports, Datasette databases, and local development tools often ship data as a single .sqlite or .db file that you can copy to any machine and open immediately. sql.js can import an existing SQLite file into browser memory, then expose it as a relational database you can query directly.¹ When you export from one of these systems, you get a file that contains one or more tables. The workbench opens any of them, whatever extension they use.

That matters when you inherit a database without documentation, because guessing table names from application code is slow and error-prone. Instead of hunting through source files, you can inspect sqlite_master, read PRAGMA metadata, and run small discovery queries before deciding which relationships matter. This discovery workflow is especially valuable when the database was created by a framework that generates schema migrations automatically.

How the workbench loads SQLite files

SQLite files open through sql.js, a WebAssembly port of the SQLite C library, rather than through DuckDB. That distinction matters: sql.js speaks standard SQLite SQL, not DuckDB's extended dialect. Window functions, CTEs, and standard aggregations work the same way. However, DuckDB-specific syntax like QUALIFY, the :: cast operator, PIVOT, UNPIVOT, and UNNEST are not available in SQLite sessions, so you need to rewrite those constructs using standard SQLite equivalents before a query will run.

All tables load together

Every table in the database becomes available the moment loading completes. A database with tables named users, orders, and products exposes all three at once. You can JOIN across tables freely because they all live in the same file.² This is a key advantage over CSV or JSON workflows, where each file produces only one table and joining across files requires a preprocessing step before the workbench can query anything.

Because the whole file loads into a single in-memory database, foreign key relationships defined in the schema stay intact and queryable, unlike a CSV workflow where each table lives in a separate file with no declared link. After the load completes, you can run discovery queries against sqlite_master to map those relationships before writing analysis queries that depend on them.

Practical patterns for SQLite databases

Start with the SQLite equivalent of SHOW TABLES: SELECT name FROM sqlite_master WHERE type = 'table'. This lists every table in the database because sqlite_master is a historical name for SQLite's schema table, which stores schema rows for tables, indexes, views, and triggers.² Then PRAGMA table_info(orders) shows column names and types for a specific table.³ Unlike DuckDB sessions, SQLite does not offer Parquet export: use the Export button to download query results as CSV, JSON, or Excel.

Joining tables inside one database file

JOINs across tables work exactly as in any relational database: JOIN users u ON o.user_id = u.id retrieves the user record for every order row. Because every table lives in the same file, cross-table queries complete without any data movement. Self-joins and subqueries within a single SQLite file work normally, so you can answer questions that span multiple entity types without exporting or merging data first.

Inspecting schema with PRAGMA commands

Inspecting a SQLite database's schema goes beyond a simple table list. PRAGMA table_info(tablename) returns every column with its declared type, NOT NULL constraint, default value, and primary key flag.³ For databases with foreign key relationships, PRAGMA foreign_key_list(tablename) reveals which columns reference other tables and the action (CASCADE, RESTRICT, SET NULL) the foreign key applies on delete or update.

Discovering indexes, views, and triggers

Beyond tables, SQLite databases may contain indexes, views, and triggers. Inspect their CREATE statements when you need to understand constraints, generated columns, or framework-managed schema details. For databases created by ORM frameworks like SQLAlchemy or Room, these CREATE statements expose the canonical schema even when the framework's own documentation does not include it. Querying sqlite_master for type = 'index' reveals which columns the upstream application expected to filter on most often, giving you a practical map of the database's access patterns before you write your first analytical query.

SQLite dialect differences from DuckDB SQL

Because SQLite sessions run through sql.js rather than DuckDB, some DuckDB-specific syntax is not available. The :: cast operator (col::INTEGER) is a DuckDB extension; SQLite requires CAST(col AS INTEGER) instead. QUALIFY, PIVOT, UNPIVOT, and UNNEST are DuckDB-specific and raise a syntax error in SQLite sessions. Window functions work in SQLite because SQLite includes ROW_NUMBER, RANK, LAG, and LEAD support.⁴

For string concatenation, both dialects use the || operator. For type conversions, SQLite accepts type names REAL, INTEGER, TEXT, BLOB, and NUMERIC in CAST rather than DuckDB's DOUBLE, BIGINT, and VARCHAR. If a query written for DuckDB-backed files raises a syntax error in a SQLite session, rewrite DuckDB casts for SQLite using CAST() before re-running. The guidance is especially useful when you inherit undocumented databases.

When to use this

Use this when you have a .sqlite or .db file from a mobile app, a browser history export, a Datasette instance, or a local development database and want to run SQL queries across all its tables. Drop the file into the workbench and start with SELECT name FROM sqlite_master WHERE type = 'table' to see every table before you write your first JOIN.

Examples

SELECT name
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;

PRAGMA table_info(orders);

SELECT o.id,
       u.email,
       o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
LIMIT 50;

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status
ORDER BY n DESC;