Posts tagged: database

Absolutely incredible what Preston is doing with his time. What a life changing experience this must be for him. Good job to Turso for making this happen. We are going to end up with very feature rich file based databases out of this that the whole world will benefit from.

This talk about live store really made me think about database transactions in a new way. They are talking about live-store, and the complexity of distributed applications like a notes app with the ability to go offline and continue working. The complexity of resyncing each instance is not simple, conflict resolution accross all the possible installs that may or may not even be online is a really hard problem. They go deep on discussing an event driven paradigm that is driven off of a log of events and how this changes how we deal with databases. Using the event log as the source of truth we can do things like forget about database migrations, we can replay all of the events onto a new database. Its very interesting to rethink in terms of a log system that speaks in terms of understandable events (not table operations) as the source of truth for an application.

Aaron Francis is a database master, pumped for thsi dude and all that he is able to accomplish.

Ben Johnson was on the Changelog a few years back covering his work on litestream, and talks about why he chose to go open source, but not open contribution.

You should have a good reason to move off of sqlite.

Very inspiring talk, TLDR, you probably don’t need a database server. sqlite will probably be faster, simpler to maintain, and simpler to test your application.

`litestream` is a sick cli tool for steaming replicas of sqlite. It automatically does daily snapshots, and streams all of the writes to the replica live.

install #

Install is fast using installer, no compilation, just copy the binary and run.

As applications scale to the edge, to put compute as close to the user as possible, database queries back to the master node get slower and slower. Enter sqlite replication, put the database wtih the application code and replicate from master.

SQLite is the next big database trend. with more horizontal scaling, close to user read heavy applications, having your database in the same application stack makes a lot of sense. Tools like litestream are going to enable global distribution in an impressive way.

Fly.io’s solution to sqlite managed backups.I definitely want to look into this a bit, but moreso the tech under the hook litestream.

sqlite has 3 different tokenizers, porter, ascii, trigram.

These can be used with sqlite-utils.

sqlite-utils enable-fts --tokenize porter database.db post title message tags

And with the python api.

datasette really does everything doesn’t it!

After a nasty time with alembic upgrades, thoughts is about to get a new users table. This may have came from incorrectly setting up alembic for sqlite from the start, but I was able to fix the issue with this GitHub issue.

alembic sqlite ValueError: Constraint must have a name

The change I needed to make to get my migration to run.

![[None]]

Since using alembic I have been just running out a new revision checking its content and deleting it if its empty, today I learned there is an alembic check command to check for operations that need to be created.

duckdb is a new in process database that has been making its rounds in analytics for its high performance in those applications.

Mother duck is a centeralized server that brings manages storage, data sharing and an ide to duckdb.

A neovim database client that I need to check out.

Enable full-text search in sqlite using sqlite-utils.

I want to like jq, but I think Simon is selling me on sqlite, maybe its just me but this looks readable, hackable, editable, memorizable. Everytime I try jq, and its 5 minutes fussing with it just to get the most basic thing to work. I know enough sql out of the gate to make this work off the top of my head

insert a json array directly into into sqlite with sqlite-utils.