SQLite

• Features
• Apps and Communities buidling on SQLite
• Tools
• Extensions
• Websites
• Scalable SQLite
• Articles
• Videos
• Discussions
• Tutorials
• Testimonials
• Companies Using SQLite
  • In Production
  • As Default Database
• Misc
• Alternatives
• Libraries
• People

Features

• Many Small Queries Are Efficient In SQLite
• Long term support until 2050
  • SQLite is a Recommended Storage Format for datasets according to the US Library of Congress (https://www.sqlite.org/locrsf.html)
• Energy savings due to no running database server

Apps and Communities buidling on SQLite

• GnuCash - App can use a SQLite database to store its data
• Fossil - Uses SQLite for storing the code, issues and the wiki for software projects
• Mergestat - Creates a SQLite database for git repositories
• Bibliothecula - Organize journal articles, e-books, and notes in a SQLite database
• gitea.io - Self-hosted Git service with support for SQLite
• Anki Flashcards
• Browser histories
• Audacity - aup3 format is based on SQLite (Audacity using SQLite now)
• Timeliner - All your digital life on a single timeline, stored locally
• The Winamp Skin Museum is powered by a SQLite3 database with 1.2GB of metadata
• Contextualise - Structured Knowledge
• gramps-project.org - Genealogy app using SQLite for storing family trees.
• github.com/yudhasubki/blockqueue - BlockQueue - SQLite-powered pub/sub for lean, fast messaging.
• sqlite-fs - Mount a SQLite database as a normal filesystem on Linux and macOS.

Check out sqlite.org/famous for a more comprehensive list.

Tools

• db-to-sqlite - CLI tool for exporting databases to SQLite.
• plainjob - Job scheduling library for SQLite.
• pglite-fusion - Embed SQLite databases in your PostgreSQL table.
• SQLite Rsync - Rsync for SQLite databases.
• sqlite-wasm-webrtc - Peer-to-peer collaborative database editing & sharing in the browser.

Extensions

• github.com/asg017/sqlite-ecosystem - Overview of all of asg017’s SQLite extensions
  • github.com/asg017/sqlite-http - SQLite extension for making HTTP requests purely in SQL
  • github.com/asg017/sqlite-html - SQLite extension for querying, manipulating, and creating HTML elements.
  • github.com/asg017/sqlite-lines - SQLite extension for reading large files line-by-line (NDJSON, logs, txt, etc.)
  • github.com/asg017/sqlite-vss - SQLite extension for efficient vector similarity search
• github.com/nalgeon/sqlean/ - Collection of SQLite extensions
• github.com/x2bool/xlite - SQLite extension to query Excel (.xlsx, .xls, .ods) files as virtual tables
• github.com/0x6b/libgsqlite - SQLite extension loading Google Sheets as virtual tables
• github.com/abiliojr/sqlite-js - Create new SQL functions in JavaScript****
• github.com/0x09/sqlite-statement-vtab - Define virtual tables and table-valued functions natively using SQL.
• github.com/abiliojr/sqlite-lua - Create new SQL functions written in Lua.****
• github.com/x2bool/sqliteclip - Work with operating system's clipboard.
• github.com/hoelzro/sqlite-lua-extension - Embed a Lua interpreter into SQLite
• github.com/Florents-Tselai/liteJQ - liteJQ is an SQLite extension, written in C, that brings jq support to SQLite using vanilla jqlib.
• github.com/JarkkoPar/sqlite-ndvss - No-Dependency Vector Similarity Search for SQLite.
• SQLSync - Collaborative offline-first wrapper around SQLite.
• Vectorlite - Fast and tunable vector search extension for SQLite.
• SQLite Jiff - Date-time extension that supports timezones, complex durations, and daylight savings calculations.

Websites

• Online playground
• sqltab.com - Online webapp for editing SQLite databases
• CSV SQL Tool - Run SQL queries on CSV files in your browser.

Scalable SQLite

• LiteCluster - Replicated, leaderless, ACID compliant & high availability SQLite

Articles

• 2024-11-14 - SQLite Index Visualization: Structure
• 2024-11-09 - SQLite does not do checksums
• 2024-11-02 - How the new sqlite3_rsync utility works
• 2024-10-25 - Using SQLite as Storage for Web Server Static Content
• 2024-10-17 - Sensible SQLite defaults
• 2024-10-13 - Optimizing a large SQLite database for reading
• 2024-10-05 - Tracking SQLite Changes
• 2024-09-25 - Reading SQLite Schema Tables the Hard Way
• 2024-08-16 - Auditing and Versioning Data in SQLite
• 2024-07-17 - Gotchas with SQLite in Production
• 2024-07-01 - Comparison of managed SQLite services
• 2024-06-19 - Performing cross-database queries with SQLite
• 2024-06-06 - Serving Binary Files from SQLite
• 2024-05-29 - Modern SQLite - Secure delete
• 2024-05-08 - Modern SQLite - Generated columns
• 2024-05-04 - Modern SQLite - STRICT tables
• 2024-04-30 - Backup strategies for SQLite in production
• 2024-04-15 - SQLite on Rails: The How and Why of Optimal Performance
• 2024-02-15 - Optimizing SQLite for servers
• 2023-02-09 - Building a pivot table in SQLite
• 2022-09-22 - Storing SQLite databases under git as plain text files
• 2022-08-24 - How SQLite Scales Read Concurrency
• 2022-08-21 - SQLite has pretty limited builtin functions
• 2022-07-31 - SQLite Performance Tuning
• 2022-06-23 - Building a remote SQLite explorer
• 2022-06-10 - SQLite: Past, Present, and Future
• 2022-02-20 - Data diffs - Algorithms for explaining what changed in a dataset
• 2018-07-30 - One process programming notes (with Go and SQLite)
• 2017-12-06 - SQLite DB Migrations with PRAGMA user_version
• 2015-12-07 - SeSQLite - Security Enhanced SQLite: Mandatory Access Control for Android databases

Tutorials

• The simplest SQLite common table expression tutorial
• exercism.org/tracks/sqlite/ - SQLite track with many exercises and automatic code analysis.
• SQLite for Beginners - YouTube tutorials by Mycelial
• 5 ways to implement case-insensitive search in SQLite with full Unicode support
• 2024-07-22 - Build your own SQLite

Videos

• SQLite: How it works, by Richard Hipp
• SQLite is enough

Discussions

• Experience with SQLite as a store of Files and Images
• Ask HN: Have you used SQLite as a primary database?

Testimonials

• https://newsletter.pragmaticengineer.com/p/bluesky

Usage Scenarios

In Production

• Pieter Levels’ startups (https://news.ycombinator.com/item?id=31152778)
  • nomads.com
  • Remote OK
  • …
• https://www.wikdict.com
• https://twitter.com/WhatGotDone/status/1459922490067668992

As Main Database

• RedwoodJS
• Ruby on Rails

More examples:

• https://runninginproduction.com/tags/sqlite
• https://news.ycombinator.com/item?id=31152490

Misc

• https://github.com/mrwilson/advent-of-code-2020 - Advent of code solved with SQLite
• github.com/vlcn-io/cr-sqlite - Convergent, Replicated SQLite. Multi-writer and CRDT support for SQLite.
• germ.gitbook.io/ws4sqlite - Query sqlite via HTTP.
• Animate SQL - Visualize SQL queries
• Postlite - Access remote SQLite databases over the Postgres wire protocol.
• sqlite-zstd - Transparent dictionary-based row-level compression for SQLite.
• https://github.com/pgspider/sqlite_fdw - SQLite Foreign Data Wrapper for PostgreSQL

Alternatives

• DuckDB - Embeddable SQL database for analytical queries.
• TogetherDB - Online database for developers.
• Hydra - The data warehouse for devs powered by Postgres.

Libraries

• Substrait - Cross platform way to express data transformation, relational algebra, standardized record expression and plans.

People

• Dwayne Richard Hipp - Inventor of SQLite
• Simon Willison - Python developer
  • Datasette
• Anton Zhiyanov - Golang developer
  • sqlime
  • sqlean
  • sqlpkg
  • codapi
  • SQL Window Functions Book
• Alex Garcia
  • sqlite-loadable-rs - A Rust framework for safe and performant SQLite extensions
  • sqlite-vss - Efficient vector search, based on Faiss
  • sqlite-xsv - Quickly reading CSV files and blobs
  • sqlite-regex - REGEXPsupport and other regular expression functions
  • sqlite-http - Making HTTP requests and querying responses
  • sqlite-html - Querying and generating HTML elements
• Rody Davis