sqlite.md

title	SQLite

SQLite

Litestream & Postlite are neat. Like using deno_sqlite for scripts that query SQLite. SQLite, Rusqlite, Rusqlite Migration are great SQLite libs for Rust. sqlc is nice for Go. GRDB is nice for Swift.

I use SQLite for whenever I need an embedded database. Local apps etc. It works great for local first architectures like Replicache & Logux. If I don't need the embedded factor, I prefer using EdgeDB. Want to try using ws4sqlite.

Simple-Graph seems like a nice graph db plugin for SQLite. sqlite-zstd is amazing for compression.

Go SQLite database/sql tutorial is nice. SQLite Internals is great read.

Notes

• SQLite can import CSV data directly. Start sqlite, then: .mode csv .import some_data_file.csv your_table_name
• SQLite now has first class support for JSON.
• SQLite has amazing full text search
• SQLite is getting more popular because: 1. embeddable at the edge (WASM, etc.). 2. Easy connection pooling for serverless (flatfile). 3. Embeddable at the runtime (browser, -as-a-library, etc.). 4. NFS getting faster, so easy and cheap alternative for blogs, etc.

Links

• Rosefinch - SQL client for Mac. (HN)
• EXPLAIN QUERY PLAN - SQL command that is used to obtain a high-level description of the strategy or plan that SQLite uses to implement a specific SQL query.
• FTS5 - SQLite virtual table module that provides full-text search functionality to database applications.
• Awesome SQLite
• All things around SQLite
• SQLite 3.32.0 (2020) (HN)
• Scaling SQLite to 4M QPS on a Single Server (EC2 vs Bare Metal) (2018) (HN)
• sqlite-web - Web-based SQLite database browser written in Python.
• DB Browser for SQLite - High quality, visual, open source tool to create, design, and edit database files compatible with SQLite.
• SQLancer - Detecting Logic Bugs in DBMS. (HN)
• SQLite As An Application File Format (HN) (Lobsters)
• How does SQLite work? (2014) (HN)
• SQLite Localtime Date Modifier is Slow (2019)
• Why SQLite succeeded as a database (2016)
• Appropriate Uses For SQLite
• SQLite 3.33 adds support for UPDATE FROM (HN)
• SQLite: Protégé of PostgreSQL (2014) (Tweet)
• SQLite Quirks and Gotchas
• sqlite-utils - Python CLI utility and library for manipulating SQLite databases. (Docs) (HN) (Article)
• CG/SQL - Easy, accurate SQLite code generation. (Article)
• SQLite: Begin Concurrent
• SQLite now allows multiple recursive SELECT statements in a single recursive CTE (2020) (HN)
• Making SQLite Upserts Snappy (with Python and SQLAlchemy) (2020)
• pgsqlite - PostgreSQL extension that embeds SQLite3 and uses large objects as storage.
• sqlite-diffable - Tools for dumping/loading a SQLite database to diffable directory structure.
• hybrid-disk-cache - Hybrid disk cache library that utilized both the solid SQLite3 and file system.
• SQLite briefing for Linux kernel hackers (HN)
• Richard Hipp - SQLite lecture (2015)
• SQLite as a Document Database (2020) (HN)
• Dqlite - High-Availability SQLite. (Code)
• LiteTree - SQLite with Branches. (HN)
• Working with SQLite using Go and Python (2020)
• sandman2 - Generates a RESTful API service from your existing database, without requiring you to write a line of code. Works with SQLite.
• node-sqlite - SQLite client for Node.js applications with SQL-based migrations API written in TypeScript.
• What If OpenDocument Used SQLite?
• The Use Of assert() In SQLite
• simple-graph - Graph database in SQLite. (Lobsters) (HN)
• SQLite is 35% Faster Than The Filesystem
• SQLiteC++ - Smart and easy to use C++ SQLite3 wrapper.
• sqlite-worker - Simple, and persistent, SQLite database for Web and Workers.
• Grafito - Portable, Serverless & Lightweight SQLite-based Graph Database.
• GRDB.swift - Toolkit for SQLite databases, with a focus on application development. (Web)
• edge-sql - Serverless edge worker embedding SQLite using Cloudflare Workers and WASM. (Code) (HN)
• Litestream - Streaming replication for SQLite. (Why I Built Litestream (2021)) (HN) (Lobsters) (Web)
• Rqlite - Lightweight, distributed relational database built on SQLite. (HN) (Article) (Talk) (Lobsters) (Articles) (pyrqlite) (jepsen.rqlite) (Testing consistency of rqlite) (HN)
• rrqlite - Rust implementation of Rqlite.
• SQLite CREATE TABLE Parser
• sqlite-transform - Tool for running transformations on columns in a SQLite database.
• SQLite DB Migrations with PRAGMA user_version
• Many Small Queries Are Efficient In SQLite
• Rust SQLite - SQLite clone from scratch in Rust.
• Command Line Shell for SQLite (HN)
• sqleet - Encryption extension for SQLite3.
• SQLite Viewer - View SQLite file online. (Code)
• Amalgalite - SQLite embedded in a Ruby extension.
• Cross-Database Queries in SQLite (2021) (HN)
• Build SQLite Extensions in Go
• What’s new in SQLite 3.35 (2021) (HN)
• SQLite is not a toy database (2021) (HN) (Lobsters) (HN)
• SQLite Plus - All the missing SQLite functions. (HN)
• Dogsheep - Tools for personal analytics using SQLite and Datasette. (Code)
• Bionic - Convert data exports from various services to a single SQLite database.
• SquirrelByte - SQLite-based JSON document server. (Web) (HN)
• Ask HN: Deploying SQLite on a Production Website (2021)
• SQLite the only database you will ever need in most cases (2021) (Lobsters) (HN)
• csvs-to-sqlite - Convert CSV files into a SQLite database.
• SQLiter - Minimal multiplatform sqlite library.
• Litestream/s6 Example - Example of running a Go application in the same container as Litestream by using s6-overlay. Allows developers to release their SQLite-based application and provide replication in a single container.
• Hosting SQLite databases on GitHub Pages or any static file hoster (2021) (Code) (HN) (HN)
• SQLite performance tuning (2020)
• SQLite on the Jamstack (2021)
• A Hitchiker's Guide to SQLite with Python (2021)
• wa-sqlite - WebAssembly SQLite with experimental support for browser storage extensions.
• Sqliteviz - Single-page offline-first PWA for fully client-side visualization of SQLite databases or CSV. (HN)
• SQLite in production with WAL (2020) (HN)
• Deno SQLite Module
• Simple application with an HTTP server & SQLite database
• SQLFluff - SQL linter and auto-formatter for Humans. (Code) (HN)
• SQLite Archiver
• How to Setup Replicated Highly Available SQLite with rqlite (2021)
• Ask HN: What is the SQLite of nosql databases? (2021)
• Using sqlite3 as a notekeeping document graph with automatic reference indexing (2021) (Lobsters) (HN)
• The Untold Story of SQLite with Richard Hipp (2021) (HN)
• SQLite query inside a Bash function
• RSQLite - Simple SQLite clone written in Rust.
• Atomic Commit In SQLite (Lobsters)
• Inserting One Billion Rows in SQLite Under A Minute (2021) (HN) (Reddit)
• Inserting 130M SQLite Rows per Minute...from a Scripting Language (2021) (HN)
• Detecting cycles in tag parent-child relationship in sqlite3
• Tricks for making SQLite fast (2021)
• dogsheep-beta - Build a search index across content from multiple SQLite database tables and run faceted searches against it using Datasette.
• absurd-sql.js-backend - sqlite3 in ur indexeddb.
• SQLite Is Dynamically Typed (2020) (HN)
• Sqlite µLogger - Fast and Lean database logger that can log data into Sqlite databases even with SRAM as low as 2kb.
• Apply conversion functions to data in SQLite columns with the sqlite-utils CLI tool (2021)
• A future for SQL on the web (2021) (HN) (Tweet)
• sqlite-s3-query - Python function to query SQLite files stored on S3.
• Strict Tables – Column type constraints in SQLite (HN)
• SQLite-TUI - TUI for viewing SQLite databases, written in Go. (HN)
• Exqlite - Elixir SQLite3 library.
• A Minimalist Guide to SQLite (2017)
• SQLime - Online SQLite playground for debugging and sharing SQL snippets. (Code) (HN)
• Implementing a graph database in SQLite
• Comparing SQLite, DuckDB and Arrow (2021) (HN)
• sqlite3 Rewritten in RiiR Rust (Lobsters)
• SQLite WAL Mode
• SQLite Index Recommendations
• APSW - Python SQLite wrapper.
• sqlite-zstd - Transparent dictionary-based row-level compression for SQLite. (HN)
• deno_sqlite3 - Non-blocking, asynchronous FFI bindings to SQlite3.
• SQLite grammar for tree-sitter
• libsqlfs - Library that implements a POSIX style filesystem on top of an SQLite database.
• The less trodden path with sqlite (2021)
• SQLiteGraph - Graph Database for Julia, built on top of SQLite.jl.
• SQLite.jl - Julia interface to the SQLite library.
• SQLite Release 3.37.0 (2021) (HN)
• SQLite Viewer - SQLite Viewer for VSCode. (Code)
• SQLite in a PWA (Anita) with FileSystemAccessAPI (2021) (HN)
• Production Applications Using SQLite & Go (2021)
• Verneuil - S3-backed asynchronous replication for SQLite. (Article)
• go-mysql-sqlite-server - Implementation of a SQLite backend for go-mysql-server.
• dblite - SQLite extension for accessing other SQL databases, in SQLite.
• SQLite – The Session Extension (HN)
• SQLite driver for Go database/sql
• ChiselStore - Embeddable, distributed SQLite for Rust, powered by Little Raft. (HN)
• deno_sqlite - Fast, native bindings to SQLite3 C API, using Deno FFI.
• graphdb - SQLite based graph database for storing native python objects and their relationships to each other.
• SQLiteLib - Easily build a custom SQLite static library for use in macOS and iOS frameworks and apps.
• Consider SQLite (2021) (HN) (Lobsters)
• r2d2-sqlite - r2d2 connection pool for sqlite.
• GopherCon 2021: Ben Johnson - Building Production Applications Using Go & SQLite
• Fast SQLite PWA notebook for CSV files (HN)
• Bashing JSON into Shape with SQLite (2022) (Lobsters)
• RecordLite - Library (and executable) that declaratively maintains SQLite tables and views of semi-structured data.
• sqlite_protobuf - Protobuf Extension for SQLite.
• The ultimate SQLite extension set (Lobsters)
• Make the “semantic web” web 3.0 again – with the help of SQLite (2022) (HN)
• Many Small Queries Are Efficient in SQLite (HN)
• sqlite3vfshttp - Go sqlite VFS for querying databases over http(s).
• Containerize Go and SQLite with Docker (2022) (HN)
• Store SQLite in Cloudflare Durable Objects (2022) (HN) (Code)
• sqlite-vfs - Build SQLite virtual file systems (VFS) by implementing a simple Rust trait.
• SQLite Visual Query Builder - Help you write SQLite queries with a visual interface. (Code)
• One process programming notes (with Go and SQLite) (2018)
• sqlite_backup - Tool to snapshot sqlite databases you don't own.
• DBHub.io - SQLite storage "in the cloud". (Code)
• wasm-sqlite - SQLite compiled to WASM with pluggable page storage.
• Using SQLite and Datasette with Fly Volumes (2022)
• LumoSQL - Modification of SQLite embedded data storage library. Offers multiple backend storage systems selectable by the user and proposes other integrity and security features. (HN)
• BFT-SQLITE - Tendermint + Sqlite3 = BFT Database Replication.
• Litestream Read Replica Demo - Demo application for running live read replication on fly.io with Litestream.
• Google Drive to SQLite - Create a SQLite database containing metadata from Google Drive. (HN)
• Libsqlite3 - Based on libsqlite3-sys, this package builds SQLite statically with some aditionnal extensions.
• New JSON query operators in SQLite 3.38.0 (2022) (HN)
• jx-sqlite - JSON query expressions using SQLite.
• SQLite compiled to WebAssembly and WASI
• SQLite Online (HN)
• Ws4sqlite - Query SQLite via HTTP. (HN)
• tuql - Automatically create a GraphQL server from a SQLite database or a SQL file.
• sqc - SQLite CLI with syntax highlighting and pretty tables by default.
• sqlitescanner - DuckDB extension to read SQLite databases. Allows directly running DuckDB queries on data stored in SQLite files. (HN)
• SQLite Foreign Data Wrapper for PostgreSQL
• Postlite - Postgres wire compatible SQLite proxy. (HN)
• A database for 2022 (Tweet) (HN) (Lobsters)
• SQLite B-Tree Module (HN)
• SQLite Database File Format
• sqlite-parquet-vtable - SQLite virtual table extension to expose Parquet files as SQL tables.
• Litestream Read Replica Example - Example of how to setup and deploy a multi-node SQLite database using Litestream's live read replication feature.
• numbsql - Run Numba compiled functions into SQLite.
• Migrating from SQLite to PostgreSQL (HN)
• Golang SQLite database/sql (2022)
• SQLite WASM - Run SQLite on the web, using WebAssembly.
• Ask HN: Have you used SQLite as a primary database? (2022)
• Port the SQLite parser to Go
• Awesome SQLite (HN)
• How to Corrupt an SQLite Database File (HN)
• Simple declarative schema migration for SQLite (2022) (HN)
• Sqldiff - SQLite Database Difference Utility.
• Generated columns in SQLite (Lobsters)
• I'm all-in on server-side SQLite (2022) (HN) (Lobsters)
• SQLite in Go, with and without cgo (2022) (HN)
• Cron-based backup for SQLite (HN)
• JSON and Virtual Columns in SQLite (2022) (HN)
• Tiny SQLite3 - Tiny cross-platform client for SQLite3, with the official precompiled binaries as the only third-party dependencies.
• Temporary tables in SQLite (2022)
• Using SQLite for multi-user multi-write app effectively
• Custom distributed data warehouse using compressed SQLite DB files in S3 that were queried using Lambda functions orchestrated by PostgreSQL running a custom foreign data wrapper (Tweet)
• bakelite - Pure Go SQLite file exporter.
• SQLite 3 Fiddle (HN)
• Generating a GraphQL API from schema using SQLite
• The Design of SQLite4 (HN)
• One-liner for running queries against CSV files with SQLite (HN)
• sqlite-lines - SQLite extension for reading lines from a file or blob.
• Building a remote SQLite explorer
• litequeue - Queue implemented on top of SQLite.
• litedict - SQLite as a Python dictionary.
• litexplore - Python web app that lets you explore remote SQLite databases over SSH connections without having to copy the full DB or manually use the CLI.
• What would SQLite look like if written in Rust? (2021)
• SQLite Release 3.39.0 (2022) (HN)
• SQLite Code of Ethics (HN)
• Trong ORM - Build reactive interfaces on top of SQLite for any platform with any framework or lib.
• SQLite or PostgreSQL? It's Complicated (HN)
• Extending SQLite with Rust (Code)
• Live Read Replicas for SQLite (HN)
• The SQLite Index Suggester (HN)
• XLite - Query Excel and Open Document spreadsheets as SQLite virtual tables. (HN) (Article)
• SQLite 3.39.2 (2022) (HN)
• SpatiaLite - Extends SQLite core to support Spatial SQL capabilities. (HN)
• LiteFS - FUSE-based file system for replicating SQLite. (HN) (Intro) (HN) (Lobsters)
• cfsqlite - Foundation of offline, local-first and edge deployed software.
• SQLite3_Web_VFS - SQLite3 extension for read-only HTTP(S) database access.
• LTX - Go library for the LTX file format.
• SQLite Internals: Pages and B-trees (2022) (HN)
• SQLite VFS extension - SQLite3 extension for read/write storage compression with Zstandard.
• SQLite-HTML - SQLite extension for querying, manipulating, and creating HTML elements. (HN)
• Sqinn - SQLite over stdin/stdout.
• libgsqlite - SQLite extension which loads a Google Sheet as a virtual table.
• SQLite store for node cache manager
• How SQLite Helps You Do ACID (2022) (HN)
• SQLite-HTTP - SQLite extension for making HTTP requests. (Article) (HN)
• Turning SQLite into a distributed database (2022) (HN)
• SQLite: Past, Present, and Future (HN)
• How SQLite scales read concurrency (2022) (HN)
• SQLite has pretty limited builtin functions (2022)
• MojoFS - Versioning, userspace filesystem for SQLite DB.
• sqlite_wasm - SQLite extension for running arbitrary WASM bundles via Wasmtime.
• Mycelial - Library, synchronize anywhere. Mycelite unifies your Edge and Cloud SQLite instances, peer-to-peer and without any data middleware. (GitHub)
• How the SQLite Virtual Machine Works (2022) (HN)
• Marmot - Distributed SQLite replicator. (Reddit)
• Protobuf Extension for SQLite - SQLite extension for extracting values from serialized Protobuf messages.
• s3sqlite - Query SQLite databases in S3 using s3fs. (HN)
• Litements - Data structures implemented on top of SQLite. (GitHub)
• SQLite Weekly - Weekly SQLite news, articles and extensions.
• Deno SQLite Shell
• Scaling Pocketbase with Marmot (2022)
• Pgsqlite - Pure Python module to import SQLite databases into Postgres. (Article) (HN)
• Pocketbase with Lightstream
• Flyweight - ORM for SQLite and NodeJS. (HN)
• libSQL - Open source, open contribution fork of SQLite.
• SQLite: QEMU all over again? (2022) (HN) (Lobsters)
• great.db - Powerful, human-friendly database library for JavaScript using SQLite.
• SQLite and the N+1 (no) problem (2022)
• Geocode SQLite - Geocode rows in a SQLite database table.
• Quirks, Caveats, and Gotchas In SQLite (HN)
• Litestream: Making Single-Node Deployments Cool Again (Ben Johnson) (2022)
• Making a change to SQLite source code (2022) (HN)
• SQLite in the browser with WASM/JS (HN) (Tweet)
• Soul - SQLite RESTful server. (HN)
• SQLite – Partial Indexes (HN)
• How To Corrupt An SQLite Database File (HN)
• Who needs MLflow when you have SQLite? (2022) (HN)
• SQLite Release 3.40.0 (2022) (HN) (HN)
• How LiteFS Works (HN)
• SQLite WASM Vite
• SQLite3MultipleCiphers - SQLite3 encryption extension with support for multiple ciphers.
• Fast SQLite Inserts - Test scripts to generate a SQLite DB with 1B rows in fastest possible way. (Lobsters)
• Pocketbase Auth Library
• Go and SQLite in the Cloud (2022) (HN)
• SQLite-loadable-rs - Framework for writing fast and performant SQLite extensions in Rust. (Intro) (HN)
• How I found a bug in SQLite (2022) (HN)
• sqlite3_ext - Build loadable extensions for SQLite using Rust.
• SQLite Internals: How The World's Most Used Database Works (HN)