SQLite DB Viewer

Understanding SQLite and Browser-Based Database Inspection

SQLite is the most widely deployed database engine in the world. It powers billions of mobile applications on iOS and Android, desktop software such as web browsers and media players, embedded IoT devices, and countless serverless software architectures. Unlike traditional client-server database management systems like PostgreSQL, MySQL, or Microsoft SQL Server, SQLite does not run as a separate background server process. Instead, it is an in-process, self-contained, serverless database engine where the entire database - including tables, views, indices, triggers, and all row data - resides within a single cross-platform disk file.

Developers, QA engineers, security researchers, and data analysts frequently encounter SQLite database files (.db, .sqlite, and .sqlite3) when inspecting application caches, debugging mobile apps, auditing local storage, or analyzing historical device data. Historically, inspecting the contents of these database files required downloading and installing heavy native desktop utilities such as DB Browser for SQLite, installing command-line CLI tools, or setting up dedicated IDE plugins. Furthermore, using online database viewers often raised serious security concerns, as traditional web converters required uploading proprietary or sensitive database files to third-party remote servers.

Tooltri's SQLite DB Viewer eliminates both friction points. It provides an immediate, full-featured database browsing experience directly inside your web browser. By combining client-side file access APIs with a compiled WebAssembly (WASM) SQLite engine running inside a background Web Worker, you can drag and drop any SQLite file and instantly browse its schema, inspect table relationships, sort and filter records, and export data - all with zero server upload and complete privacy.

The Architecture of an SQLite Database File

To appreciate how client-side database parsing operates, it helps to understand how an SQLite file is structured internally on disk:

• The 100-Byte File Header: Every valid SQLite 3 database file begins with a strict 100-byte header. The first 16 bytes contain the exact ASCII magic string "SQLite format 3\000". This header also encodes critical operational parameters, including the database page size (typically 4,096 bytes in modern versions), file format write and read versions, reserved space per page, payload fractions, change counters, and schema format versions.
• B-Tree Page Organization: SQLite organizes its entire storage layout into fixed-size pages. Tables and indices are stored as B-Trees. Interior pages act as navigation trees containing pointers to child pages, while leaf pages store the actual table records (payloads) or index entries. This hierarchical design allows SQLite to jump to specific rows in logarithmic time rather than scanning every byte linearly from the beginning of the file.
• The Schema Catalog (sqlite_master): Unlike flat file formats like CSV or TSV, SQLite maintains a centralized metadata catalog in a special internal table called sqlite_master (also aliased as sqlite_schema). This table stores the definitive schema for every table, view, index, and trigger in the database, including the raw SQL text used to create them.
• Variable-Length Record Encoding: Inside each leaf page, rows are encoded using an optimized binary format known as a record payload. SQLite serializes headers containing variable-length integer indicators (varints) that declare the data type and byte length of each column value, followed immediately by the raw column data. This design allows SQLite to store mixed data types compactly without wasting unused storage space.

The Power of Client-Side WebAssembly (WASM)

Running a full-scale C database engine inside a web browser was once considered impossible. Early web database inspection tools had to rely either on transmitting your file over the internet to a backend Python, PHP, or Node.js server, or on fragile pure-JavaScript parsers that lacked complete SQL compatibility and crashed on complex schemas.

The advent of WebAssembly (WASM) transformed browser capabilities. WebAssembly is a low-level, binary instruction format designed as a portable compilation target for high-performance languages like C, C++, and Rust. The SQLite development team maintains official support for compiling SQLite's battle-tested C source code directly to WASM using the Emscripten compiler toolchain.

Our SQLite DB Viewer leverages sql.js, a lightweight and highly optimized WebAssembly build of SQLite:

1. Direct Memory Instantiation: When you select a database file on your device, the browser's FileReader API reads the file into an ArrayBuffer in memory. This raw memory buffer is passed directly to the WebAssembly virtual machine, which mounts it as an in-memory virtual disk.
2. Native C Performance: Because the database queries execute within compiled WebAssembly bytecode, operations run at near-native CPU speeds. Filtering, sorting, and page scans execute in fractions of a millisecond.
3. Complete Query Engine Fidelity: Because the underlying engine is real SQLite compiled from source, every schema quirk, column constraint, type conversion, and built-in SQLite function behaves identically to the desktop SQLite library.
4. Zero Network Footprint: At no stage is any portion of your file sent over the network. All computation is isolated to your local browser environment, making it completely compliant with strict data privacy guidelines and air-gapped security workflows.

Web Worker Architecture: Keeping the Interface Responsive

Processing large databases containing hundreds of thousands of rows can consume substantial CPU resources. If database parsing and query execution ran on the browser's main thread, the user interface would freeze during file loading or large table queries, causing dropped animation frames and "Page Unresponsive" browser warnings.

To provide a smooth, fluid user experience, Tooltri's SQLite DB Viewer executes all database operations inside a dedicated HTML5 Web Worker:

• Off-Thread Processing: When a database file is dropped into the uploader, the binary buffer is transferred to the worker thread. The worker initializes the WebAssembly module, mounts the database, extracts the schema, counts table rows, and returns a lightweight metadata summary to the UI thread.
• Asynchronous Message Passing: Every interaction - selecting a table, changing pages, clicking a sort header, or typing a substring filter - dispatches a structured message to the worker. The worker executes the requested query against the in-memory database and posts the formatted result back to the main UI.
• Smooth Micro-Interactions: Because the main execution thread remains completely free from heavy computation, user interface transitions, hover states, scroll bars, and modals remain fluid and responsive at 60 frames per second, regardless of database size.

Schema Inspection and Relationship Mapping

Once a database is loaded, understanding its overall architecture is the first task. The left-hand schema navigation panel provides an instant, structured breakdown of the database:

• Tables and Views: The schema parser distinguishes standard physical tables from virtual views. Each entry displays a row count badge, allowing you to instantly identify which tables contain active data and which are empty.
• Column Attributes and Data Types: Expanding any table reveals its complete column manifest. For every column, the viewer displays the declared data type, whether the column has a NOT NULL constraint, its default fallback value if configured, and a highlighted key indicator for Primary Key columns.
• Foreign Key Detection: SQLite databases often define relational constraints linking child tables to parent records. The viewer runs PRAGMA foreign_key_list on each table to identify foreign keys, displaying a link badge that details the referencing column, target table, and destination column on hover.
• Schema SQL Inspection: Need to see the exact SQL statement used to construct a table or view? The viewer extracts the original DDL statement from sqlite_master and displays it in a dedicated code modal, complete with a one-click copy button for quick migration to migrations scripts or ORM models.

Dynamic Pagination and Memory Optimization

A common pitfall in web-based data grids is loading an entire table into JavaScript arrays before rendering. In a table with 100,000 rows and 20 columns, materializing 2,000,000 JavaScript objects can easily consume hundreds of megabytes of RAM, triggering browser garbage collection pauses and crashing memory-constrained mobile browsers.

Our data grid uses a streaming windowed approach powered directly by SQL:

• LIMIT and OFFSET Queries: When you request a page size of 25, 50, 100, or 500 rows, the worker executes a targeted query: SELECT * FROM "table" LIMIT ? OFFSET ?. Only the visible slice of records is converted into JavaScript objects.
• Serverless In-Engine Sorting: Clicking any column header updates the SQL query with an ORDER BY "column" ASC or DESC clause. The sorting is handled natively inside SQLite's optimized C algorithms rather than slow JavaScript array sorting.
• Substring Search Filtering: When you enter text into the search filter, the worker dynamically constructs a parameterized query that matches the substring across all text-castable columns (WHERE CAST("col1" AS TEXT) LIKE ? OR ...). The grid calculates the matching row count dynamically, ensuring that pagination controls accurately reflect the filtered subset.
• Sticky Headers and Column Sizing: Tables with dozens of columns remain easily navigable thanks to sticky column headers and smooth horizontal scrolling, while row numbering provides continuous orientation.

Handling SQLite Dynamic Typing, NULLs, and BLOBs

SQLite has a unique type system compared to other SQL databases. While systems like PostgreSQL enforce strict static typing, SQLite uses dynamic type affinity. A column declared as INTEGER can technically store a text string or a floating-point number, and the engine determines the storage class on a per-value basis.

Our viewer accurately renders each storage class with clear visual indicators:

• NULL vs Empty String: One of the most frequent bugs in data analysis is confusing a NULL (the absence of a value) with an empty string (""). The data grid explicitly styles NULL values with a muted, rounded badge, preventing any ambiguity during data audits.
• Binary BLOB Handling: SQLite allows columns to store raw binary large objects (BLOBs), such as images, cryptographic keys, compressed archives, or protocol buffers. Attempting to render raw binary data as text causes garbled characters and browser slowdowns. The viewer automatically identifies BLOB cells, displays a compact badge with the exact byte size (e.g., "BLOB (64 B)"), and provides a cell inspection modal that shows a formatted hexadecimal preview.
• Cell Value Inspector: When working with long text payloads - such as JSON strings, markdown notes, stack traces, or UUIDs - table cells truncate excess text to keep row heights compact. Clicking any cell opens an inspector modal with the complete, untruncated content and a quick copy button.

Exporting and Data Interoperability

Inspecting data is only part of the workflow. Developers often need to extract slices of a database for reports, documentation, or import into other tools:

• Copy as JSON: Exports the currently visible page of records as a formatted JSON array of objects. This is ideal for copying test fixtures into unit tests, mocking API responses, or seeding frontend state.
• Copy as CSV: Formats the active page as standard comma-separated values following RFC 4180 rules, properly quoting strings containing commas, quotes, or line breaks.
• Download Full Table as CSV: Streams every row in the selected table from the WebAssembly database, generates a CSV payload, and triggers a direct browser download without loading intermediate rows into DOM elements.
• Copy Schema SQL: Extracts the exact CREATE TABLE DDL statement for quick re-use in database setup scripts, migrations, or database diagramming tools.

Privacy, Security, and Compliance

In modern software development, databases frequently contain sensitive records: user credentials, customer contact information, financial ledgers, private API tokens, and proprietary application state. Uploading these files to remote web services poses serious security and compliance risks under regulations such as GDPR, HIPAA, and CCPA.

Tooltri's SQLite DB Viewer is architected from the ground up to provide ironclad privacy:

• No File Uploads: Your database file is never transmitted to api.tooltri.com or any server. The network inspector will confirm zero outbound upload requests when opening a file.
• Memory Isolation: Data resides strictly within your browser tab's temporary volatile memory. Closing or refreshing the tab, or clicking "Reset", immediately releases the WebAssembly instance and purges all database memory.
• No Telemetry or Tracking: No query parameters, table names, schema definitions, or cell values are logged, tracked, or analyzed.
• Offline Functionality: Once loaded, the viewer runs entirely offline. You can disconnect your internet connection and continue browsing, sorting, and exporting tables with zero disruption.