#sqlite

Turns out that when we re-envision what we considered to be simply a byte layout specification was actually better off being a database, a lot of machinery we have been using for decades simply stops being necessary. The program is the database, and the database is the program.

Ordinary SQLite files, one sharded database—parallel writes, PostgreSQL, HTTP, Rust, Python, and shard-safe IDs.

A lightweight, embedded document database written in Rust with ACID transactions and MVCC support.

N+1 Queries Are Not A Problem With SQLite SQLite is not client/server, however. The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.

Every single wart that annoys us today, used to be a reasonable feature that someone relied on in their production code. Every wart we see today is a testament to the care the maintainers put into backward compatibility. If we choose a technology today, we want one that saves us from future maintenance by keeping our wartful code running – even if we don’t yet know it is wartful. The best indicator of this is whether the technology has warts today.

Random morning thoughts: how many people actually visit this blog? Where do they come from? What if I could just spend a little time to build my own analytics?

I've written about this a few times before, so I just started a sqlite-busy tag to collect my notes together on a single page.

The tl;dr on this, to prevent SQLITE_BUSY errors even when setting a timeout, don’t ever upgrade transactions to read-write. If you know you are going to write in a transaction, use ‘BEGIN IMMEDIATE’, or start off with the write. But do read on for why this is so, and how other databases struggle with this problem as well.

Django package enabling user-specific SQLite databases

Especially for side projects with limited traffic requirements and scaling concerns, the promise of SQLite is that it can remove a lot of ongoing hassles. Without a separate database, cache, or queue, there is less network traffic (because the SQLite file is local), and less servers to manage, maintain, and backup.

A blazingly fast, open-source application server with type-safe APIs, built-in JS/ES6/TS Runtime, Auth, and Admin UI built on Rust, SQLite & V8.

Create, edit, browse SQLite databases.

DB Browser for SQLite (DB4S) is a high quality, visual, open source tool designed for people who want to create, search, and edit SQLite or SQLCipher database files. DB4S gives a familiar spreadsheet-like interface on the database in addition to providing a full SQL query facility. It works with Windows, macOS, and most versions of Linux and Unix. Documentation for the program is on the wiki.

Embed an SQLite database in your PostgreSQL table. AKA multitenancy has been solved.

After learning about indexes, I understood their basic structure, but I wanted to dig deeper — to explore the data structure, understand the algorithm, and learn how the index data is stored on disk. The theory and actual implementation can differ, so I decided to explore this topic further.

CLI for SQLite Databases with auto-completion and syntax highlighting

The following command causes REPLICA to become a copy of ORIGIN: $ sqlite3_rsync ORIGIN REPLICA ?OPTIONS?

Sometimes Litestream can be overkill for projects with a small database that do not have high durability requirements. In these cases, it may be more appropriate to simply back up your database daily or hourly. This approach can also be used in conjunction with Litestream as a fallback. You can never have too many backups!

For Clace, the decision was made to use SQLite for app files storage instead of using the file system. The reasoning was mainly to be able to do atomic version changes. When updating an app, since there could be lots of files being updated, using a database would allow all changes to be done atomically in a transaction. This would prevent broken web pages from being served during a version change.

The json_extract(X,P1,P2,...) extracts and returns one or more values from the well-formed JSON at X.

sqlite-lembed is a SQLite extension for generating text embeddings, meant to work alongside sqlite-vec. With a single embeddings model file provided in the .gguf format, you can generate embeddings using regular SQL functions, and store them directly inside your SQLite database. No extra server, process, or configuration needed!

The most common use-case for vectors in databases is for K-nearest-neighbors (KNN) queries. You'll have a table of vectors, and you'll want to find the K closest

You can use SQLite's builtin full-text search (FTS5) extension and semantic search with sqlite-vec to create "hybrid search" in your applications. You can combine results using different methods like keyword-first, re-ranking by "semantics", and reciprocal rank fusion. Best of all, since it's all in SQLite, experiments and prototypes are cheap and easy, no 3rd party services required!

Scaling SQLite databases to many concurrent readers and multiple gigabytes while maintaining 100k SELECTs per second

Scaling SQLite databases to many concurrent readers and multiple gigabytes while maintaining 100k SELECTs per second

Dogsheep is a collection of tools for personal analytics using SQLite and Datasette.

Dogsheep is a collection of tools for personal analytics using SQLite and Datasette.

This makes it possible to insert JSON straight into SQLite and then have it extract data and index them, i.e. you can treat SQLite as a document database. This has been possible with PostgreSQL and obviously is what something like Elastic provides but having it available in an embedded database is very nice for lightweight stuff.

"so I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem"

"so I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem"

So, here is my thought experiment -- whenever you have the urge of building a custom file format, just don't.

So, here is my thought experiment -- whenever you have the urge of building a custom file format, just don't.

Wordpress with support for SQLite, among other things.