rel:: [[Databases]] [[SQL]] # SQLite from <https://sqlite.org/index.html> > SQLite is a C-language library that implements a [small](https://sqlite.org/footprint.html), [fast](https://sqlite.org/fasterthanfs.html), [self-contained](https://sqlite.org/selfcontained.html), [high-reliability](https://sqlite.org/hirely.html), [full-featured](https://sqlite.org/fullsql.html), [[SQL]] database engine. SQLite is the [most used](https://sqlite.org/mostdeployed.html) database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. ## Reference - [docs](https://sqlite.org/docs.html) - [alphabetical list](https://sqlite.org/doclist.html) - [Appropriate Uses For SQLite](https://sqlite.org/whentouse.html) - [Distinctive Features](https://sqlite.org/different.html) - [Quirks](https://sqlite.org/quirks.html) - [How SQLite Is Tested](https://sqlite.org/testing.html)  - [SQLite is not a toy database](x-devonthink-item://321FD2A2-A66F-4787-A57F-A2BA560C216D) - [How To Corrupt An SQLite Database File](https://sqlite.org/howtocorrupt.html) - [pivot tables](https://readwise.io/reader/shared/01hpsabrbbstcmbxhmj83kmmn8) - <https://docs.sqlitecloud.io/docs/sqlite> - <https://kerkour.com/sqlite-for-servers> ### Utilities - [sqlite-utils](https://sqlite-utils.datasette.io/en/stable/cli.html) - [[cli]] tool and Python utility functions for manipulating SQLite databases ^1d51eb - `sqlite-utils transform` - [alter schema](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-transform-table) with `--type`, `--rename`, `--drop` etc - [sqldiff](https://www.sqlite.org/sqldiff.html) ### using with [[golang]] - <https://github.com/mattn/go-sqlite3> - `database/sql` driver - <https://pkg.go.dev/modernc.org/sqlite> - cross-compiled SQLite C-code to [[golang]], avoids CGO) overhead - <https://github.com/zombiezen/go-sqlite> - - uses <https://pkg.go.dev/modernc.org/sqlite> - fork of [[#alternative interface to database sql|crawshaw.io/sqlite]] #### alternative interface to `database/sql` <https://pkg.go.dev/crawshaw.io/sqlite> ##### reasons from:: <https://crawshaw.io/blog/go-and-sqlite> > Generic and simple usually means lowest-common-denominator. Fancy database features, or even relatively common but not widely used features, like nested transactions, are not well supported. And if your [[SQL]] database is conceptually different from the norm, it can get awkward. > > Using SQLite extensively, I am finding it awkward. SQLite is an unusual [[SQL]] database because it lives in process. There is no network protocol so some kinds of errors are not possible, and when errors are possible, they fall into better-understood categories (which means they sometimes should be Go panics, not errors). SQLite is often used as a file format, so streaming access to blobs is very useful. > > So I wrote my own SQLite interface: <https://crawshaw.io/sqlite.> ## Cool Projects ### Hosting SQLite databases on static websites [link](https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/) They are using a WASM-compiled SQLite engine + an HTTP-range-request based virtual filesystem to statically host [[SQL]] databases and lazy-load data for interactive queries in the browser. - uses [[#sql js|sql.js]] - <https://github.com/phiresky/sql.js-httpvfs> - a fork of and wrapper around [[#sql js|sql.js]] to provide a read-only HTTP-Range-request based virtual file system for SQLite. ### sqliteonline.com [link](https://sqliteonline.com/) also uses [[#sql js|sql.js]] ### sql.js [link](https://github.com/sql-js/sql.js/) SQLite library compiled to JS/WASM ### libSQL [link](https://github.com/libsql/libsql) A fork of [[SQLite]] by [Chiselstrike](https://chiselstrike.com/) and [[Glauber Costa]] ### Replication #### Verneuil > Verneuil ([vɛʁnœj](https://en.wikipedia.org/wiki/Auguste_Victor_Louis_Verneuil)) is a [VFS (OS abstraction layer)](https://www.sqlite.org/vfs.html) for [sqlite](https://www.sqlite.org/index.html) that accesses local database files like the default unix VFS while asynchronously replicating snapshots to [[AWS S3]]-compatible blob stores. [Verneuil: S3-backed asynchronous replication for SQLite](x-devonthink-item://A8BD91CC-3ADC-408F-BCC6-B7B100A113E4) [[AWS S3]] > Verneuil is a Linux-only Rust crate (with a fair amount of C) that uses SQLite’s extension mechanism to access database files on disk exactly like the default unix ™ VFS, while asynchronously replicating snapshots to a blob store like [[AWS S3]]. > Verneuil only supports databases with rollback journals, while Litestream only supports write-ahead logging (WAL) - eventually consistent read replicas - core abstraction: [replication buffer](https://github.com/backtrace-labs/verneuil/blob/main/doc/REPLICATION_BUFFER.md) ^ae3b74 - Three snapshots - staging - updated in-place after writes - ready - atomically published - consuming - consumed by readers - Snapshot - directory of content addressed chunks - differs from a delta log - bounded size on single producer, multiple consumer buffer - wait-free produce - wait-free consume (on non-empty buffer) - similar to [[Mobile Development MOC#croquet io]]? #### Lightstream bought by [[fly.io]] [Why I built Litestream](https://litestream.io/blog/why-i-built-litestream/) Live replication to facilitate disaster recovery and quick fail-over to a new leader. - WAL streamed to [[AWS S3]] - Simple, single-source-of-truth deployments - does not support read replicas; scaling reads is via traditional - vertical scaling + parallelism - sharding #### fly.io LiteFS > LiteFS is a distributed file system that transparently replicates SQLite databases. This lets you run your application like it's running against a local on-disk SQLite database but behind the scenes the database is replicated to all the nodes in your cluster. Unlike [[#Lightstream]], LiteFS allows horizontal read scaling. As of [[2023-01-04]], the application layer does need to route writes to the leader. There are plans to permit writes on local instances and have it routed back to the leader. Both SQLite rollback journal and WAL mode supported. - [docs](https://fly.io/docs/litefs/) - [how it works](https://fly.io/docs/litefs/how-it-works/) - [WAL mode announcement](https://fly.io/blog/wal-mode-in-litefs/)