SQLite

cross-posted from: https://programming.dev/post/20627406

via https://news.ycombinator.com/item?id=41851051

via https://fosstodon.org/@anze3db/111347782255927112

A few nice new features in this release. Allowing ORDER BY aggregate functions could be very useful.

Interesting post on how to speed up batch inserts with SQLite.

Short post about how SQLite sometimes creates indexes just for a single query. The SQLite documentation has a little more information.

It can be worth setting up and monitoring the error log for SQLITE_WARNING_AUTOINDEX warnings as good indication a persistent index should be created.

Interview with Richard Hipp about the history of SQLite.

The main changes look to be contentless-delete FTS5 tables and performance enhancements to the query planner and JSON functions.

From the homepage:

This library and command-line utility helps create SQLite databases from an existing collection of data.

Most of the functionality is available as either a Python API or through the sqlite-utils command-line tool.

sqlite-utils is not intended to be a full ORM: the focus is utility helpers to make creating the initial database and populating it with data as productive as possible.

It is designed as a useful complement to Datasette.

Cleaning data with sqlite-utils and Datasette provides a tutorial introduction (and accompanying ten minute video) about using this tool.

Handy utility to diff SQLite databases.

The author managed to reach 56,600 req/s!

While written about Go, not all the techniques used are Go specific.

Explains how using WAL allows writes without blocking reads.

Converts MongoDB protocol to SQL using SQLite or PostgreSQL as the database engine.

Could be useful if wanting to migrate off MongoDB.

High level overview of how SQLite rollback journal works.

Registry of SQLite extensions along with a package manager to install them.

Interesting brief overview of SQLite internals.

A useful collection of extensions for SQLite

While not ready for production, it looks really promising.

There’s some tests showing > 1 million write transactions per second in some cases.

Saw this on Hacker News, seemed interesting.

Adds the ability for JSON functions to read JSON5 which could be handy.