SQLite - Overview

SQLite: The Modern Embedded Database

This guide provides a contemporary overview of SQLite, its role in modern software development, and how it stands out as a powerful, lightweight database solution.

What is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Unlike client-server databases (like MySQL or PostgreSQL), SQLite is serverless and embedded directly into the end application. The entire database (definitions, tables, indices, and the data itself) is stored as a single cross-platform file on a host machine.

Its source code is in the public domain, making it free for any purpose, commercial or private.

Why Choose SQLite in 2024?

SQLite’s unique architecture makes it ideal for a wide range of modern applications:

• Serverless Architecture: No separate server process to install, set up, configure, manage, or troubleshoot. This simplifies deployment and reduces operational overhead.
• Zero-Configuration: SQLite works out of the box. There is no configuration file and no administrative tasks are needed.
• Transactional ACID Compliance: Transactions are atomic, consistent, isolated, and durable (ACID), even after system crashes and power failures. This ensures data integrity.
• Mobile & Desktop Applications: It’s the native data storage engine for Android, iOS (via Core Data), and macOS. It’s perfect for desktop applications built with frameworks like Electron or Tauri.
• Local-First & Offline-First Apps: Powers applications that need to work reliably offline by storing data locally before synchronizing with a central server.
• Prototyping & Development: An excellent choice for the development and testing phase of larger applications that will eventually use a client-server database.
• Data Analysis & Caching: Used for analyzing medium-sized datasets or as a local cache for data fetched from remote APIs.
• Internet of Things (IoT) & Edge Computing: Its small footprint and high reliability make it perfect for resource-constrained devices.

Key Milestones & Evolution

• 2000: D. Richard Hipp creates SQLite 1.0.
• 2013 (v3.8.0): Introduction of Common Table Expressions (CTEs), a powerful feature for writing readable, recursive queries.
• 2015 (v3.9.0): Added support for UPSERT (INSERT ON CONFLICT).
• 2018 (v3.25.0): Window Functions were added, enabling complex analytical queries. ALTER TABLE RENAME COLUMN support was also added.
• 2021 (v3.35.0): ALTER TABLE DROP COLUMN support was added.
• 2022 (v3.39.0): RIGHT and FULL OUTER JOIN support was finally added, bringing SQLite’s JOIN capabilities in line with other major databases.
• 2023+: Continued performance improvements, JSON function enhancements, and ongoing feature development.

[Updated] SQLite Feature Support & Limitations

Modern SQLite is highly capable, but it’s important to understand its design philosophy. The following is an updated look at its features, debunking some outdated information.

Feature	Status & Modern Practice
Outer Joins	Fully Supported. LEFT, RIGHT, and FULL OUTER JOIN are all implemented (since v3.39.0). The old limitation of ‘only LEFT JOIN’ is no longer true.
ALTER TABLE	Largely Supported. RENAME TABLE, ADD COLUMN, RENAME COLUMN, and DROP COLUMN are all supported. Modifying a column’s type or constraints still requires a more complex migration procedure (create new table, copy data, drop old table).
Updatable Views	Views are read-only by default. However, you can create INSTEAD OF triggers on a view to intercept INSERT, UPDATE, or DELETE operations and apply the logic to the underlying tables, effectively making the view updatable.
User Management	Not Supported by Design. SQLite has no concept of users, GRANT, or REVOKE. Access control is handled at the filesystem level (i.e., who can read/write the database file).
Triggers	FOR EACH ROW triggers are fully supported. FOR EACH STATEMENT triggers are not. This is a deliberate design choice that fits the majority of use cases.

SQLite Command Categories

SQLite uses standard SQL commands, which can be grouped by function:

• Data Definition Language (DDL): Defines the database schema. (CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX)
• Data Manipulation Language (DML): For adding, updating, or deleting data. (INSERT, UPDATE, DELETE)
• Data Query Language (DQL): Used to retrieve data. (The SELECT statement)
• Transaction Control Language (TCL): Manages transactions to ensure data integrity. (BEGIN TRANSACTION, COMMIT, ROLLBACK)