SQLite

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, .sqlite3 database 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:

How to Export / Obtain Files

1. Locate your SQLite database file
2. Close any applications using the database
3. Copy the database file (no special export needed)
4. 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 .db file
• BLOB columns >1MB are noted but not exported
• Virtual tables (FTS, R-Tree) may have limited support

Troubleshooting

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 .db or .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 -wal and -shm files for transactions

Data Types and Structures

SQLite uses dynamic typing with storage classes:

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

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.