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.

A rust example project using SQLx and sqlite.

Read the pp_dataserver later to learn how to use Axum with a SQLite database.

but limited to the amalgamation

The use case of extracting a certain JSON path into a separate column and optionally index it seems to be the best use case I know

Enforce the type of the column. Well I didn't know it was possible to insert every types into one column :D

A SQLite fork that should run everywhere (in the browser and locally)

A basic reimplementation of Redis. I like to use SQLite for it.

How to configure SQLite for

Using a simple INT with Unix millisecond timestamps is the best for performance.
COUNT is slow, so it can be useful to keep track of them in a separate table.

Distributed SQLite databases can be achieved the same way as PostgresSQL: one writer and multiple replicated readers.

Great insights too :)

Generate a diagram of the sqlite

A dump of all stack exchange in sqlite files

Signal has open-sourced a SQLite extension that provides better support for non-latin languages (Chinese, Japanese, etc) in the Full-Text Search (FTS) virtual table.

“I ran a database company before this and I think the thing people like me never want to talk about is just about everyone has a few sub-10 gigabyte databases.” said Kurt Mackey, the CEO of Fly. “If you’re really in that category, you know this is super interesting because it’s SQL and it’s amazing for 10 gig databases.”

“[There’s] no need to get a database server up and running (or a docker container). It’s just a file. You can even send the database file to a coworker if you need some help with something.”

The software runs very fast without performance glitches, but there aren’t the same number of tools that can help support it. [...] I think the biggest complication for us is that there’s no tooling for it.

#idea #project

Le NoSQL est également plus adaptée pour le développement agile car les données ne sont pas structurées pendant les itérations.

Le SQL a réussi à traverser les âges car: il est simple, il est rapide et il est stable.

Les avantages de SQLite:

• facilité d'administration: tout le contenu peut être visualisé par lecture d'un fichier.
• facilité de déploiement: il est possible de pousser les BDD en production et de les migrer
• simplicité: sqlite est intégré avec tous les langages de programmation
• capacité: une BDD peut contenir plus d'un million de TB

Ses désavantages:

• requêtes concurrentes:
• les types de données limitées: NULL, INTEGER, REA L, TEXT, BLOB. Il n'y a pas de BOOL ni de dates.
• les données sont faiblement validées: "abcd" dans INTEGER o 20 charactères dans VARCHAR(5) passent.
• la sauvegarde des données est à faire soit même
• l'hébergement n'est pas possible avec tous les hébergeurs

Ok not bad at all. I still think Rust is not meant for prototyping, but let's give it a try.

Create webpages from SQL requests

Eḿbed SQL examples in a web page. It uses a browser-compatible compiled sqlite under the hood .