198 private links
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://observablehq.com/documentation/cells/data-table
https://dbeaver.io/ / https://www.dbvis.com/
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
.backupcommand 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
cpwith--reflink=alwaysin 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 :)