DEV Community

Cover image for SQLite Database: A Complete Guide for Developers (Architecture, Internals, and Everything You Must Know)
Farhad Rahimi Klie
Farhad Rahimi Klie

Posted on • Edited on

SQLite Database: A Complete Guide for Developers (Architecture, Internals, and Everything You Must Know)

#beginners #database #sql #sqlite

SQLite is one of the most widely deployed database engines in the world. It powers mobile apps, browsers, embedded systems, IoT devices, desktop applications, and even operating systems. Yet, many developers underestimate it or misunderstand how it works internally.

This article is a complete, no-shortcuts guide to SQLite, covering architecture, storage engine, SQL features, transactions, concurrency, indexing, performance, limitations, and real-world use cases.

1. What Is SQLite?

SQLite is:

  • An embedded relational database
  • Serverless
  • Zero-configuration
  • Stored in a single file
  • ACID-compliant

Unlike MySQL or PostgreSQL, SQLite does not run as a separate server process. The database engine is embedded directly into your application as a library.

Key Characteristics

Feature SQLite
Server process ❌ No
Configuration ❌ None
Database size Up to ~281 TB
Transactions ✅ ACID
SQL support ✅ Rich
Network access ❌ Local only

2. SQLite Architecture (Internal Design)

SQLite has a layered architecture, designed for simplicity, portability, and reliability.

High-Level Architecture 

Application
   ↓
SQL Compiler
   ↓
Virtual Machine (VDBE)
   ↓
B-Tree Engine
   ↓
Pager
   ↓
OS Interface
   ↓
Database File
Enter fullscreen mode Exit fullscreen mode

3. SQL Compiler

The SQL Compiler converts SQL text into executable bytecode.

Compiler Phases

  1. Tokenizer
  • Breaks SQL into tokens (SELECT, FROM, identifiers, literals)
  1. Parser
  • Builds an Abstract Syntax Tree (AST)
  1. Code Generator
  • Converts AST into VDBE instructions

SQLite does not interpret SQL directly. It compiles SQL into bytecode.

4. Virtual Database Engine (VDBE)

The VDBE is SQLite’s virtual machine.

  • Executes compiled bytecode
  • Stack-based engine
  • Similar to a CPU for SQL

Example VDBE Operations

  • OpenRead
  • Rewind
  • Column
  • Compare
  • Next
  • ResultRow

This design allows SQLite to be:

  • Portable
  • Deterministic
  • Efficient

5. Storage Engine: B-Tree

SQLite stores all data using B-Trees.

Types of B-Trees

Type Purpose
Table B-Tree Stores table rows
Index B-Tree Stores indexes

Table Storage

  • Rows are stored by ROWID
  • ROWID is a signed 64-bit integer
  • Can be replaced with WITHOUT ROWID

6. Database File Format

SQLite databases are single disk files.

File Structure

  • File Header (100 bytes)
  • Pages (default: 4096 bytes)
  • B-Tree Pages
  • Free Pages
  • Overflow Pages

Page Types

  • Interior Table Page
  • Leaf Table Page
  • Interior Index Page
  • Leaf Index Page

7. Pager Module (Critical Component)

The Pager is responsible for:

  • Reading/writing pages
  • Cache management
  • Atomic commits
  • Rollbacks
  • Crash recovery

Without the pager, SQLite cannot guarantee ACID properties.

8. ACID Compliance in SQLite

SQLite fully supports ACID transactions.

Atomicity

  • Achieved via rollback journal or WAL

Consistency

  • Enforced through constraints and transactions

Isolation

  • Serializable by default
  • Lock-based concurrency

Durability

  • Data persists after crashes

9. Journaling Modes

SQLite uses journaling to protect data integrity.

Rollback Journal (Default)

  • Copies original pages before modification
  • Restores on crash

WAL (Write-Ahead Logging)

  • Writes changes to WAL file
  • Better concurrency
  • Faster reads 
PRAGMA journal_mode=WAL;
Enter fullscreen mode Exit fullscreen mode

10. Locking and Concurrency Model

SQLite uses file-level locking.

Lock Types

Lock Purpose
SHARED Reading
RESERVED Preparing write
PENDING About to write
EXCLUSIVE Writing

Concurrency Reality

  • Multiple readers allowed
  • Only one writer at a time

11. Data Types (Dynamic Typing)

SQLite uses manifest typing.

Core Storage Classes

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

Type Affinity

Columns have affinities:

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

SQLite does not enforce strict column types.

12. Tables and Schema

Creating Tables 

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);
Enter fullscreen mode Exit fullscreen mode

WITHOUT ROWID Tables 

CREATE TABLE orders (
  order_id INTEGER,
  user_id INTEGER,
  PRIMARY KEY (order_id, user_id)
) WITHOUT ROWID;
Enter fullscreen mode Exit fullscreen mode

Used for performance and space optimization.

13. Indexing

Indexes are also B-Trees.

Index Types

  • Single-column
  • Multi-column
  • UNIQUE
  • Partial Indexes 
CREATE INDEX idx_user_email ON users(email);
Enter fullscreen mode Exit fullscreen mode

Indexes speed up reads but slow writes.

14. Query Planner and Optimization

SQLite has a cost-based query planner.

EXPLAIN QUERY PLAN 

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'x';
Enter fullscreen mode Exit fullscreen mode

Planner decisions depend on:

  • Index availability
  • Table size
  • WHERE clauses

15. Transactions

Explicit Transactions 

BEGIN;
INSERT INTO users VALUES (...);
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Transaction Modes

  • DEFERRED
  • IMMEDIATE
  • EXCLUSIVE 
BEGIN IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

16. Constraints

SQLite supports:

  • PRIMARY KEY
  • UNIQUE
  • NOT NULL
  • CHECK
  • FOREIGN KEY

Foreign keys must be enabled:

PRAGMA foreign_keys = ON;
Enter fullscreen mode Exit fullscreen mode

17. Views, Triggers, and Virtual Tables

Views 

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = 1;
Enter fullscreen mode Exit fullscreen mode

Triggers 

CREATE TRIGGER log_insert
AFTER INSERT ON users
BEGIN
  INSERT INTO logs VALUES (...);
END;
Enter fullscreen mode Exit fullscreen mode

Virtual Tables

Used for:

  • Full-Text Search (FTS)
  • JSON
  • CSV
  • Custom engines

18. Full-Text Search (FTS5) 

CREATE VIRTUAL TABLE articles USING fts5(title, content);
Enter fullscreen mode Exit fullscreen mode

Used for search engines and document indexing.

19. JSON Support

SQLite has built-in JSON functions:

  • json_extract
  • json_set
  • json_array 
SELECT json_extract(data, '$.name') FROM users;
Enter fullscreen mode Exit fullscreen mode

20. Performance Characteristics

Strengths

  • Extremely fast reads
  • Low memory footprint
  • Minimal latency

Weaknesses

  • Single writer limitation
  • Not suitable for high-write concurrency systems

21. Installation

Linux 

sudo apt install sqlite3
Enter fullscreen mode Exit fullscreen mode

macOS 

brew install sqlite
Enter fullscreen mode Exit fullscreen mode

Windows

  • Download precompiled binaries
  • Or use bundled versions

Embedded Use

  • Linked directly into application
  • No daemon or service

22. Use Cases

SQLite is ideal for:

  • Mobile applications
  • Desktop software
  • Embedded systems
  • Local caching
  • Configuration storage
  • Prototyping
  • Edge computing

Not ideal for:

  • High-traffic multi-user systems
  • Distributed databases

23. SQLite vs Other Databases

Feature SQLite PostgreSQL
Server No Yes
Scalability Limited High
Setup Zero Complex
Performance Excellent local Excellent distributed

24. Limitations (Be Honest)

  • One writer at a time
  • No native replication
  • No user management
  • No stored procedures
  • Not a network database

SQLite is not “small” — it is purpose-built.

25. Final Thoughts

SQLite is not a toy database. It is a high-quality, battle-tested, industrial-grade storage engine optimized for reliability, simplicity, and correctness.

If you understand SQLite deeply, you understand:

  • Database internals
  • Storage engines
  • ACID guarantees
  • Real-world tradeoffs

Top comments (0)