My journal is now running on a new site that's pretty much the same on the front-end, except for the fact that it has a chronological list of my journal entries in all their glory (they are paginated 10/page). But at the back-end everything is stored in an SQLite database.

Source code: https://github.com/kevquirk/journal/

Guest Lecture at Saarland University, on June 25th, 2024

SQLite embedded in the browser as WASM executable.

If you look at my previous choices you will see there is in general a move to reducing the number of dependencies. The older and more crusty I get the more I appreciate having a single binary I can just deploy.

https://searchcode.com/

Decoupling storage from compute is the default architecture because it’s a really good idea.

It isn’t because SQLite might lose your data (it won’t), or it doesn’t scale well (it scale’s just fine)

A blazingly fast, open-source backend with type-safe REST & realtime APIs, built-in JS/ES6/TS runtime, SSR, authentication, and admin UI built on Rust, SQLite & V8.

Simplify with fewer moving parts: an easy to self-host single-executable with everything you need to focus on your mobile, web or desktop application. Sub-millisecond latencies eliminate the need for dedicated caches, no more stale or inconsistent data.

We can expect a x8 speedup for a big transaction.

An extension to support queries with regex

Here are some web-page-based client-side tools to extract some kinds of low-level information that cannot be done through SQL or SQLite’s C API.

libSQL is a portability in WASM of SQLite.

The Turso project experiment a rewrite of SQLite in Rust with some technical implementation in mind:

Limbo is a research project to build a SQLite compatible in-process database in Rust with native async support. The libSQL project, on the other hand, is an open source, open contribution fork of SQLite, with focus on production features such as replication, backups, encryption, and so on. There is no hard dependency between the two projects. Of course, if Limbo becomes widely successful, we might consider merging with libSQL, but that is something that will be decided in the future.

A list of SQLite GUIs

https://rosefinchapp.com/

https://observablehq.com/documentation/cells/data-table

https://sqlitestudio.pl/

https://dbeaver.io/ / https://www.dbvis.com/

https://lite.datasette.io/ <3

A firefox browser extension "SQLite Manager" https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager-webext/

A collection of projects using SQLite

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA foreign_keys = ON;
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 2147483648;
PRAGMA page_size = 8192;

SQLite simplifies the overall architecture. That's why you would pick it with Rails.

This is because Rails, with 2 decades of battle-tested solutions extracted from production applications, provides unparalleled conceptual compression, while SQLite, with its single file database and embedded executable, provides truly unique operational compression.

As we further discuss backup strategies, please remember, that having a remote volume mounted as a primary or a secondary disk in your system means you have filesystem access to a distributed data store that you use by simply doing file operations (e.g. cat, tail, cp, touch, mkdir, etc.).

I would simply mention that features that exist in ZFS, Btrfs or XFS (not a CoW fs but has some CoW features). [...] both ZFS and Btrfs offer transparent filesystem compression, meaning even the stored, deduplicated pages can be further reduced in size.

"Thing" backup strategies:

• backup data pages in the file verbatim
• pack data pages as you copy them (byte by byte copy with database cleanup beforehand)
• dump the data as SQL commands

"How" backup strategies:

• litestream: copy the db changes depending on the WAL
• SQLite .backup command creates an exact page by page replica of the database file at the point of invoking the command.
• SQLite VACUUM INTO
• SQLite .dump
• good old cp with --reflink=always in a transaction

This is why databases accessed over a socket instead of being an embedded library are actually a great abstraction, not necessarily a technical one, but an organizational abstraction! During development it can be a simple container running on your developers machines, while in production it can be anything from a container running on the same server as your application, or a distributed cluster accessed through the network.