Spoke at the #GDG Santiago de Compostela 🎒🐚 #DevFest yesterday (in broken-ish Spanish, but the slides are in English) on going "From Web SQL to #SQLite implemented in #WebAssembly and backed by the Origin Private File System" (OPFS): https://goo.gle/devfest-santiago.
Even if you don't care about how we got there (that is, why Web SQL was deprecated), the SQLite and the OPFS parts are super exciting technologies well worth your attention—and they work in all modern browsers!
On another note, I found out yesterday #sqlite does not support 64-bit integers. This is unfortunate, as I’m working on a Discord bot that needs to store identifiers that are unsigned 64-bit integers (u64 in #rust).
I didn’t want to spin up a whole #postgresql database just to store a single table holding two IDs on each row. So, I opted in favour of storing them as strings. This should not be too much of an issue, as the bot is far from being resource-intensive.
But I’m wondering if anyone knows of an alternative to SQLite that’s still light and compatible with Rust’s #sqlx?
hey #rust people is there a better solution than #sqlite / #sqlx atm? im making a #tauri app and it just keeps getting in the way. i tried sled and a few other similar solutions but they all seem pretty far off from being ergonomic for a general purpose db. i just wanna like, store my rust data in something and retrieve it with some basic relationships. not have to worry about endianness.
If you have a composite primary key (uses more than one field) on one table and a second table has a foreign key which references the first, the foreign key must also be composite rather than individual foreign keys for the individual fields or you'll get the error message above even if you've already checked and double-checked that the required records are present.
It's amazing how writing down your thoughts about something, even if it's not to explicitly remember those things, can help give you clarity.
I just updated https://travisbriggs.com/garden/bandcamp/ with my progress on my FOSS Bandcamp release page alternative. In the process of explaining it, I realized that I could use a SQLite database without problem.
Get the best out of #git and #sqlite by having your database versioned in a human-readable format:
# Create a local git repo and a db file
$ git init
$ sqlite3 ./test.db
sqlite> create table test(id int primary key, value text);
$ git add test.db
# Set the diff format for *.db files
$ git config diff.sqlite3.binary true
$ git config diff.sqlite3.textconv "echo .dump | sqlite3"
$ echo '*.db diff=sqlite3' >> .gitattributes
# Do some stuff with the db
$ sqlite3 ./test.db
sqlite> insert into test values(1, 'a');
sqlite> insert into test values(2, 'b');
sqlite> insert into test values(3, 'c');
sqlite> update test set text = 'aaa' where id = 1;
sqlite> delete from test where id = 3;
# Check the diff
$ git diff
diff --git a/test.db b/test.db
index 9d6e6db..c9a7a08 100644
--- a/test.db
+++ b/test.db
@@ -1,4 +1,6 @@
...
CREATE TABLE test(id int primary key, text text);
+INSERT INTO test VALUES(1,'aaa');
+INSERT INTO test VALUES(2,'b');
COMMIT;
@mackuba@rademaker There is no such thing as a "best" one, it completely depends on the task at hand. At the low abstraction end you can use just the #SQLite API, it is really easy to deal with! At the very high abstraction end you have #CoreData / #SwiftData.
But yes, #GRDB is a really good choice somewhere in the middle. Very well done and maintained.
Program crashed while you were modifying a mail? Who knows what the on-disk file looks like now. You had it git-versioned? Good. If all goes well and git or the computer itself doesn't crash while updating things, that might be enough.
Also bestenfalls(?) pro DB-Typ&Version ein Docker-Image auf eigenem Port. Gern direkt mit initialen DBs&Tabellen aus nem zentralen SQL-Dump. Dann kann ich ein PHP Script damit connecten und iterieren.
Sowas muss doch schonmal jemand gemacht haben und ein docker-compose dafür besitzen, zB für QA/Testing? Finde aber nix.