SQLite
Getting Started
- SQLite In 5 Minutes Or Less - A very quick introduction to programming with SQLite.
Documentations
Important links to official documentations:
- Write-Ahead Log (WAL) Mode - Transaction control using a write-ahead log offers more concurrency and is often faster than the default rollback transactions. This document explains how to use WAL mode for improved performance.
- Command-Line Shell (sqlite3.exe) - Notes on using the "sqlite3.exe" command-line interface that can be used to create, modify, and query arbitrary SQLite database files.
- Overview Of The Optimizer - A quick overview of the various query optimizations that are attempted by the SQLite code generator.
- Quirks, Caveats, and Gotchas In SQLite
Articles and Blog Posts
- SQLite Internals: How The World's Most Used Database Works
- 35% Faster Than The Filesystem - This article points out that reading blobs out of an SQLite database is often faster than reading the same blobs from individual files in the filesystem.
- Scaling SQLite to 4M QPS on a Single Server (EC2 vs Bare Metal) (2018)
- SQLite Internals: Pages & B-trees
- How the SQLite Virtual Machine Works
- How SQLite Scales Read Concurrency
- How SQLite Helps You Do ACID
- SQLite Renaissance - Address the question: "What's the reason SQLite has been a trending topic for data/backend use cases recently (2022)? It's been around for 20+ years. I'm just curious if something about our world fundamentally changed.". TL;DR:
embeddable at the edge (WASM, etc.)
easy connection pooling for serverless (flatfile)
embeddable at the runtime (browser, -as-a-library, etc.)
NFS getting faster, so easy and cheap alternative for blogs, etc.
- Consider SQLite
- I'm All-In on Server-Side SQLite
- SQLite is not a toy database
- One process programming notes (with Go and SQLite)
- A database for 2022
- Hosting SQLite databases on Github Pages or any static file hoster
- One-liner for running queries against CSV files with SQLite
- Towards Inserting One Billion Rows in SQLite Under A Minute
- Inserting 130M SQLite Rows per Minute...from a Scripting Language
- JSON and virtual columns in SQLite
- CoreRecursive Podcast: The Untold Story of SQLite With Richard Hipp
- A Minimalist Guide to SQLite
- SQLite Wasm in the browser backed by the Origin Private File System - Use SQLite to handle all your storage needs performantly on the web. Finally!
- SQLite or PostgreSQL? It's Complicated!
- Store SQLite in Cloudflare Durable Objects
- Replacing Elasticsearch with Rust and SQLite
- Exploring search relevance algorithms with SQLite
- SQLite's automatic indexes
- Writing a Custom SQLite Function (in C) - Part 1
- SQLite-based databases on the postgres protocol
- Benchmarking SQLite Performance in Go
- SQLite has pretty limited builtin functions
- SQLite performance tuning
Projects
- benbjohnson/Litestream - Streaming replication for SQLite.
- Introducing LiteFS
- DuckDB - An embeddable SQL database like SQLite, but supports Postgres features.
- Static.wiki - A read-only Wikipedia using only static assets, WASM, 43 GB SQLite file & no backend.
- phiresky/sqlite-zstd - Reduce SQLite database size by up to 80% with transparent compression.
- benbjohnson/Postlite - Postgres wire compatible SQLite proxy.
- Datasette: instantly create and publish an API for your SQLite databases
- nalgeon/sqlite-plus - A set of missing SQLite extensions.
- NocoDB - Open source Airtable alternative — Turns any MySQL, PostgreSQL, SQL Server, SQLite & MariaDB into a smart-spreadsheet.
- asg017/sqlite-xsv - The fastest CSV SQLite extension, written in Rust.
- libSQL - A fork of SQLite that is both open source, and open contributions.
- canonical/dqlite - Embeddable, replicated and fault tolerant SQL engine.
- rqlite - Lightweight, distributed relational database built on SQLite.
Books
- SQLite Database System Design and Implementation (2015) by Sibsankar Haldar - This book provides a comprehensive description of SQLite database system. It describes design principles, engineering trade-offs, implementation issues, and operations of SQLite.
Communities
Tweets
Videos
SQLite Hackers
If you're intrested in building a SQLite clone from scratch, see my database systems development notes. Below are some good technical documentations you can reference while doing that.
- How To Compile SQLite - Instructions and hints for compiling SQLite C code and integrating that code with your own application.
- Official technical and design docs:
- Architecture - An architectural overview of the SQLite library, useful for those who want to hack the code.
- Virtual Filesystem - The "VFS" object is the interface between the SQLite core and the underlying operating system. Learn more about how the VFS object works and how to create new VFS objects from this article.
- SQLite File Format - A description of the format used for SQLite database and journal files, and other details required to create software to read and write SQLite databases without using SQLite.
- How SQLite Is Tested - The reliability and robustness of SQLite is achieved in large part by thorough and careful testing. This document identifies the many tests that occur before every release of SQLite.
- How I found a bug in SQLite
- sqlite-loadable-rs: A framework for building SQLite Extensions in Rust
- Extending SQLite with Rust
- What would SQLite look like if written in Rust? (blog series)
- SQLite in Go, with and without cgo
Experimental
- HC-tree - A project attempting to develop a new database backend that improves upon regular SQLite: high-concurrency, support for replication, and beyond 16TiB database size limit.
Research
Papers
- SQLite: Past, Present, and Future by University of Wisconsin-Madison and SQLite developers, 2022, VLDB - The paper compares SQLite and DuckDB, and describes some optimization work to make SQLite perform better for analytical queries. Related: Notes on the SQLite DuckDB paper by Simon Willison.