How to Prepare Your Files
- Locate your .db or .sqlite database file
- Ensure the database is not locked by another application
- Upload the file directly or zip it first
- Max file size is 2GB
Guide
SQLite is a self-contained, serverless SQL database engine stored in a single file. It is widely used in mobile apps, desktop applications, embedded systems, and more.
What You Can Upload
.db,.sqlite,.sqlite3database files- Files without extension (detected via magic bytes)
- ZIP archives with multiple SQLite databases
What You Get Out
DataMeans extracts your data into multiple modern formats:
| Output | Description |
|---|---|
csv/{TableName}.csv | One CSV file per table with all row data |
xlsx/{TableName}.xlsx | Excel workbook per table |
xls/{TableName}.xls | Legacy Excel format per table |
json/{TableName}.json | JSON array of records per table |
json/{TableName}.jsonl | Newline-delimited JSON (streaming-friendly) |
postgres.sql | PostgreSQL CREATE TABLE + INSERT statements |
schema/schema-graph.json | Relationship graph for visualization |
schema/er-model.json | ER model for diagram tools |
report.json | Structured extraction report |
report.md | Human-readable extraction summary |
How to Export / Obtain Files
- Locate your SQLite database file
- Close any applications using the database
- Copy the database file (no special export needed)
- Upload directly or in a ZIP archive
Common locations:
- iOS apps:
~/Library/Developer/CoreSimulator/.../Documents/ - Android apps:
/data/data/app.package/databases/ - Desktop apps: Application data folder
Supported Features
- Full schema extraction (columns, types, constraints)
- Primary keys (single and composite)
- Foreign key relationships for ER diagrams
- Indexes (unique and non-unique)
- Views as read-only tables
- Streaming extraction for large tables
- Full UTF-8 and UTF-16 support
Known Limitations
- Encrypted databases (SQLCipher, SEE) not supported - requires decryption key
- WAL (Write-ahead log) files not processed - only main
.dbfile - BLOB columns >1MB are noted but not exported
- Virtual tables (FTS, R-Tree) may have limited support
Troubleshooting
| Issue | Solution |
|---|---|
| Database is locked | Close other applications using the file |
| Database is encrypted | Provide unencrypted copy |
| Not a valid SQLite database | Check file integrity or format |
| BLOB skipped | Expected for large binary data |
Last updated: January 2026
Technical reference
Overview
SQLite is a self-contained, file-based relational database management system that requires no server process or configuration. It implements a complete SQL database engine in a single library, storing the entire database (tables, indexes, triggers, views) in a single cross-platform file. SQLite is widely used in embedded systems, mobile applications, and desktop software due to its reliability, performance, and zero-administration requirements.
History and Background
- 2000: D. Richard Hipp begins SQLite development while working on a U.S. Navy contract.
- 2000: SQLite 1.0 released as public domain software, using GDBM-based storage.
- 2001: SQLite 2.0 replaces GDBM with a custom B-tree storage engine.
- 2004: SQLite 3.0 introduces a new file format with improved concurrency.
- 2013: SQLite 3.8.0 introduces the next-generation query planner and partial indexes.
- 2015: SQLite 3.9.0 adds the JSON1 extension, FTS5 full-text search, and indexes on expressions.
- 2018: SQLite 3.24.0 introduces UPSERT; 3.25.0 adds window functions.
- 2018: The U.S. Library of Congress lists SQLite as a recommended storage format for the preservation of datasets.
- 2021: SQLite 3.35.0 adds the RETURNING clause; 3.37.0 introduces STRICT tables.
- 2022: SQLite 3.40.0 adds support for compiling SQLite to WebAssembly for use in web browsers.
- 2023: SQLite 3.43.0 adds contentless-delete FTS5 indexes and query planner improvements.
- 2024: SQLite 3.45.0 introduces JSONB, a binary JSON representation stored as ordinary BLOB values.
File Format Specifications
Database File Structure:
- Single file, commonly with extension
.dbor.sqlite - Begins with the 16-byte UTF-8 header string
SQLite format 3\000 - Divided into fixed-size pages (default 4096 bytes)
- Page size is a power of two between 512 and 65,536 bytes, recorded at byte offset 16 of the header
- B-tree structures for tables and indexes
- Optional write-ahead logging (WAL) for concurrency
- File size limit: approximately 281 terabytes
- Maximum of 4,294,967,294 pages per database
- Cross-platform compatibility
Key Components:
- Database Header: First 100 bytes with format version, page size, etc.
- B-tree Pages: Organized as interior and leaf pages
- Records: Row payloads encoded as a varint header of serial-type codes followed by the column values
- Overflow Pages: Linked chain of pages holding cell payload too large to fit on one page
- Free Pages: Reusable space from deleted data
- Pointer Map: Helper pages used by auto_vacuum and incremental_vacuum modes
- Lock-Byte Page: Page spanning file offsets 1,073,741,824 to 1,073,742,335, reserved for file locking and never written by the core
- Schema Objects: Tables, indexes, views, triggers stored as SQL
File Format Versions:
- Version 1: Original GDBM-based storage (2000)
- Version 2: Custom B-tree format replacing GDBM (2001)
- Version 3: Current format (2004) with backward compatibility
- Schema formats within version 3: format 2 (3.1.3) permits rows with varying column counts, format 3 (3.1.4) permits non-NULL defaults on added columns, format 4 (3.3.0) adds DESC indexes and serial types 8 and 9
- WAL mode: Separate
-waland-shmfiles for transactions
Data Types and Structures
SQLite uses dynamic typing with storage classes:
| Storage Class | Description |
|---|---|
| NULL | Missing or unknown value |
| INTEGER | Signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on magnitude |
| REAL | 8-byte IEEE 754 floating point |
| TEXT | UTF-8, UTF-16BE, or UTF-16LE encoded text |
| BLOB | Binary data of any length |
Type Affinity:
- Columns can have type affinity: INTEGER, TEXT, BLOB, REAL, NUMERIC
- Affinity is derived from the declared type: "INT" yields INTEGER; "CHAR", "CLOB", or "TEXT" yield TEXT; "BLOB" or a missing type yields BLOB; "REAL", "FLOA", or "DOUB" yield REAL; all others NUMERIC
- Affinity influences how values are stored but doesn't restrict types
- NUMERIC affinity converts well-formed integer or real text literals to INTEGER or REAL
- Boolean values are stored as INTEGERs 0 and 1; the keywords TRUE and FALSE are accepted since version 3.23.0
- Dates and times have no dedicated storage class and are stored as ISO-8601 TEXT, Julian-day REAL, or Unix-epoch INTEGER values
Database Objects:
- Tables: B-tree structures with rowid as primary key
- Indexes: Separate B-trees for fast lookups
- Views: Stored SQL SELECT statements
- Triggers: Automatic actions on table changes
- Virtual Tables: Custom table implementations via modules
Version Differences
| Version | Year | Key Changes | Compatibility |
|---|---|---|---|
| 3.0 | 2004 | New file format, manifest typing, improved concurrency | File format incompatible with SQLite 2.x |
| 3.1/3.2 | 2005 | AUTOINCREMENT, ALTER TABLE RENAME/ADD COLUMN | After ADD COLUMN, file uses schema format 2, unreadable before 3.1.3 |
| 3.5.0 | 2007 | FTS3 full-text search | FTS3 is an optional compile-time extension |
| 3.6.19 | 2009 | Foreign key constraint enforcement | Disabled by default; enabled per connection via PRAGMA |
| 3.7.0 | 2010 | Write-ahead logging (WAL) | WAL databases unreadable by versions before 3.7.0 |
| 3.8.2 | 2013 | WITHOUT ROWID tables | Databases containing WITHOUT ROWID tables raise "malformed database schema" before 3.8.2 |
| 3.8.3 | 2014 | Common table expressions (WITH) | No file format change |
| 3.9.0 | 2015 | JSON1 extension, FTS5, indexes on expressions | JSON1 and FTS5 are optional compile-time extensions |
| 3.24/3.25 | 2018 | UPSERT (3.24.0), window functions (3.25.0) | No file format change |
| 3.31.0 | 2020 | Generated columns | Schemas with generated columns are reported as corrupt by versions before 3.31.0 |
| 3.35/3.37 | 2021 | RETURNING (3.35.0), STRICT tables (3.37.0) | STRICT tables require 3.37.0+ to read or write |
| 3.43.0 | 2023 | Contentless-delete FTS5 indexes, timediff() function | New FTS5 index option; core file format unchanged |
| 3.45.0 | 2024 | JSONB binary JSON functions | JSONB values are stored as ordinary BLOBs; no file format change |
Compatibility Notes:
- Files created by newer 3.x versions remain readable back to 3.0.0 unless newer features are used
- WAL mode changes the file header read/write version from 1 to 2
- Some features require specific version minimums
- Extensions like FTS and JSON1 are optional components
- SQLite 2.x and 3.x file formats are not compatible with each other
- The database text encoding (UTF-8, UTF-16LE, or UTF-16BE) is recorded at header offset 56 and applies to all text in the file
- Header read and write version numbers above 2 mark a database as unreadable or read-only, respectively, to current versions
Technical References
- SQLite Documentation
- SQLite File Format Specification
- SQLite Data Types
- SQLite Release History
- Wikipedia: SQLite
To learn how to use this format with DataMeans, see the User Guide.