375 private links
A nice python project.
The author creates an extension for 3 uuid functions in Rust.
Tradeoffs: big extension size (330KB for simple uuids)
About using SQLite:
As mentioned in an earlier post the two biggest pain points are the "slow" schema changes on 10M+ rows tables locking the entire database for 10+ seconds, and the difficulty to implement automated failover. But it rocks for services that don't need 99.999 % of availability.
After a few years of using both (see Optimizing SQLite for servers for example), I've found that SQLite particularly shines when used for internal services or public services where a small amount of downtime is tolerable.
So, I choose PostgreSQL (preferably with a managed provider) if the service needs (close to) 100% uptime, if the service needs more than 5 Gbps of bandwidth or if the database is expected to grow larger than 200GB. [...] Bascially, all the situations where running on a single server is not possible.
It's important to note that with the advent of DuckDB, Parquet and Apache Iceberg, there is less and less reasons to stuff your main database with
useless junktimeseries data, and instead only keep some kind of materialized view and send the raw data to S3. Thus, there are less and less reasons for your main database to be over 200 GB.
Dump the database as SQL statements instead of copying it with indexes. Then compress the resulting txt file.
# Create the backup
sqlite3 my_db.sqlite .dump | gzip -c > my_db.sqlite.txt.gz
# Reconstruct the database from the text file
cat my_local_database.db.txt | sqlite3 my_local_database.dbAs complete script example:
# Create a gzip-compressed text file on the server
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
# Copy the gzip-compressed text file to my local machine
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
# Remove the gzip-compressed text file from my server
ssh username@server "rm my_remote_database.db.txt.gz"
# Uncompress the text file
gunzip my_local_database.db.txt.gz
# Reconstruct the database from the text file
cat my_local_database.db.txt | sqlite3 my_local_database.db
# Remove the local text file
rm my_local_database.db.txtThere should be better ways though.
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.
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://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;