[Avg. reading time: 5 minutes]
SQLite
Its a Serverless - Embedded database. Database engine is a library compiled into your Application.
-
The entire database is one file on disk.
-
It’s self-contained - needs no external dependencies.
-
It’s the most widely deployed database in the world.
How It’s Different from “Big” Databases
- No client-server architecture - your app directly reads/writes the database file
- No network overhead - everything is local file I/O
- No configuration - no setup, no admin, no user management
- Lightweight - the library is only a few hundred KB
- Single writer at a time - multiple readers OK, but writes are serialized
Key Architectural Concepts
ACID Properties:
- Transactions are atomic, consistent, isolated, durable
- Even if your app crashes mid-write, database stays consistent
Locking & Concurrency:
- Database-level locking (not row or table level like PostgreSQL)
- Write transactions block other writers
- This is fine for mobile/embedded, problematic for high-concurrency servers
Storage & Pages:
- Data stored in fixed-size pages (default 4KB)
- Understanding page size matters for performance tuning
When to Use SQLite
- Mobile apps (iOS, Android)
- Desktop applications
- Embedded systems (IoT devices, cars, planes)
- Small-to-medium websites (< 100K hits/day)
- Local caching
- Application file format (instead of XML/JSON)
- Development/testing
When not to Use SQLite
- High-concurrency web apps with many simultaneous writers
- Distributed systems needing replication
- Client-server architectures where you need central control
- Applications requiring fine-grained access control
Performance Characteristics
- Extremely fast for reads
- Very fast for writes on local storage
- Slower on network drives (NFS, cloud mounts)
- Indexes work like other databases - crucial for query performance
- Analyze your queries - use EXPLAIN QUERY PLAN
Demo
git clone https://github.com/gchandra10/python_sqlite_demo