SQLite Database
SQLite stores a full relational database in a single cross-platform binary file. FileDex provides a technical reference for the 100-byte header, B-tree page layout, WAL journaling, and corruption recovery procedures.
SQLite databases require the SQLite engine for queries. File format conversion is not applicable.
Common questions
How many databases use SQLite worldwide?
SQLite estimates over 1 trillion active deployments. It ships in every Android and iOS device, every major web browser (Chrome, Firefox, Safari), macOS, Windows 10/11, and most TV set-top boxes and IoT devices. It is the most widely deployed database engine by a large margin.
Can multiple processes write to a SQLite database simultaneously?
SQLite supports unlimited concurrent readers but serializes writes with a database-level lock. WAL (Write-Ahead Logging) mode allows readers to proceed during writes, but only one writer can hold the lock at a time. For high-concurrency write workloads, use a client-server database like PostgreSQL.
How do I recover a corrupted SQLite database?
Run PRAGMA integrity_check to identify the corruption scope. Try sqlite3 corrupt.db '.dump' | sqlite3 recovered.db to export readable data to a new file. For deeper recovery, use the .recover command (SQLite 3.29.0+) which bypasses the B-tree and scans raw pages for extractable records.
What is the maximum size of a SQLite database?
The theoretical maximum is 281 terabytes (2^47 bytes with the default page size of 4096). In practice, SQLite databases work well up to tens of gigabytes. Performance degrades with very large databases primarily due to filesystem limitations and lack of concurrent write support.
What makes .SQLITE special
What is a SQLite file?
SQLite is a self-contained, serverless, zero-configuration database engine that stores an entire database in a single file. It's the most deployed database engine in the world, used in every smartphone, most web browsers, and countless applications.
Continue reading — full technical deep dive
How to open SQLite files
- DB Browser for SQLite (Windows, macOS, Linux) — Free
- DBeaver (Windows, macOS, Linux) — Free, universal
- sqlite3 (CLI) — Built-in on macOS/Linux
- SQLiteStudio (Windows, macOS, Linux) — Free
- Python —
import sqlite3built-in module
Technical specifications
| Property | Value |
|---|---|
| Type | Embedded relational database |
| ACID | Full ACID compliance |
| Max Database Size | 281 TB |
| Max Row Size | 1 GB |
| Concurrent Reads | Unlimited |
| SQL Standard | Most of SQL-92 |
Common use cases
- Mobile apps: iOS Core Data, Android Room
- Web browsers: Chrome, Firefox, Safari local storage
- IoT devices: Embedded data storage
- Testing: In-memory database for unit tests
.SQLITE compared to alternatives
| Formats | Criteria | Winner |
|---|---|---|
| .SQLITE vs .MYSQL | Deployment model SQLite is a single file with zero configuration. MySQL requires a running server process, user accounts, network configuration, and ongoing administration. | SQLITE wins |
| .SQLITE vs .MYSQL | Concurrent writes MySQL handles thousands of concurrent write connections using row-level locking. SQLite serializes all writes through a single database-level lock. | MYSQL wins |
| .SQLITE vs .POSTGRESQL | SQL feature completeness PostgreSQL supports FULL OUTER JOIN, advanced ALTER TABLE, stored procedures (PL/pgSQL), custom types, and extensions (PostGIS, pg_trgm). SQLite lacks many of these features. | POSTGRESQL wins |
| .SQLITE vs .JSON FILE | Query capability SQLite supports SQL queries with JOINs, aggregation, window functions, and indexes for efficient lookups. JSON files require loading into memory and iterating through all records for every query. | SQLITE wins |
Technical reference
- MIME Type
application/vnd.sqlite3- Magic Bytes
53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00SQLite format 3\0 text signature.- Developer
- D. Richard Hipp
- Year Introduced
- 2000
- Open Standard
- Yes — View specification
SQLite format 3\0 text signature.
Binary Structure
A SQLite database file begins with a 100-byte header containing the magic string 'SQLite format 3\000' (16 bytes), followed by configuration fields defining page size, file format versions, page counts, and encoding. The remainder of the file is divided into fixed-size pages. Page 1 starts at byte 0 and contains the header plus the root B-tree page for the sqlite_master table (the schema table). Subsequent pages are B-tree pages (table or index interior/leaf), overflow pages (for records exceeding one page), free pages (available for reuse), or pointer-map pages (in auto-vacuum mode). Table B-trees store records as variable-length cells containing the rowid and serialized column values. Index B-trees store index key values with pointers to the corresponding table rows. Each B-tree page has a page header (8 or 12 bytes) describing the page type, cell count, free space offset, and rightmost child pointer (interior pages only). The file change counter at offset 24 increments on every write transaction, enabling cache invalidation. In WAL (Write-Ahead Logging) mode, changes are written to a separate -wal file before being checkpointed back to the main database. A -journal file is used in rollback journal mode for atomic commits and crash recovery.
| Offset | Length | Field | Example | Description |
|---|---|---|---|---|
0x00 | 16 bytes | Magic string | 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 | Header string 'SQLite format 3\0' — identifies the file as a SQLite database |
0x10 | 2 bytes | Page size | 10 00 (4096) | Database page size in bytes. Must be a power of 2 between 512 and 65536. Value 1 means 65536. |
0x12 | 1 byte | Write format version | 01 | File format write version. 1 = rollback journal, 2 = WAL mode. |
0x13 | 1 byte | Read format version | 01 | File format read version. 1 = rollback journal, 2 = WAL mode. |
0x14 | 1 byte | Reserved space | 00 | Bytes of unused space at end of each page. Usually 0. |
0x18 | 4 bytes | File change counter | 00 00 00 2A | Incremented on every write transaction. Used for cache validation. |
0x1C | 4 bytes | Database size (pages) | 00 00 00 08 | Total number of pages in the database file. |
0x20 | 4 bytes | First freelist trunk page | 00 00 00 00 | Page number of the first freelist trunk page. 0 if no free pages. |
0x24 | 4 bytes | Total freelist pages | 00 00 00 00 | Total number of freelist pages (available for reuse). |
0x28 | 4 bytes | Schema cookie | 00 00 00 05 | Incremented when the database schema changes. Triggers prepared statement recompilation. |
0x2C | 4 bytes | Schema format number | 00 00 00 04 | Schema format version. Current version is 4. |
0x34 | 4 bytes | Suggested cache size | 00 00 00 00 | Default page cache size suggested by PRAGMA default_cache_size. |
0x38 | 4 bytes | Text encoding | 00 00 00 01 | Text encoding. 1 = UTF-8, 2 = UTF-16le, 3 = UTF-16be. |
0x3C | 4 bytes | User version | 00 00 00 00 | User-defined version number. Set via PRAGMA user_version. Used by applications for schema versioning. |
0x60 | 4 bytes | SQLite version number | 00 38 12 00 (3.39.0) | SQLite library version that most recently wrote the database, in decimal encoding. |
Attack Vectors
- SQL injection via application code — applications that concatenate user input into SQLite queries are vulnerable to the same SQL injection attacks as any SQL database
- Malicious database files — a crafted .sqlite file with corrupted B-tree pages or oversized records can trigger buffer overflows or denial of service in applications that open untrusted database files
- Data extraction from device — SQLite databases on mobile devices (SMS, contacts, browser history, app data) are forensic targets; unencrypted .sqlite files are trivially readable
- ATTACH DATABASE attack — if an application allows user-controlled ATTACH DATABASE statements, an attacker can read or create arbitrary files on the filesystem
Mitigation: FileDex processes SQLite files entirely in the browser using sql.js (SQLite compiled to WebAssembly). No server-side access, no filesystem exposure, no network transmission of database contents.