DoltHub Blog

That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/)

But also, this is the same argument people generally give against using DB stored procedures. And, despite using sprocs often myself, I generally agree with them — the tooling around sprocs really sucks. Sprocs are "DB objects as state" (like rows in a table are state); but they could be a lot more than that.

My secret dream, as an infrastructure engineer and DBA, is that, on boot, each version of each application that uses an RDBMS could "register" or "zero-install" a client schema with said RDBMS — i.e. a namespace of persistent virtual DB objects, unique to that build of that application — but where the underlying physical DB objects are not necessarily unique, instead being shared as needed between builds and even between applications. (Think: what would happen if you put the Plan9 designers in charge of the evolution of the SQL standard.)

Such a "client schema" would specify, among other things, the full source code of any sprocs the application expects to be able to call. These would be able to be internally deduplicated — e.g. registered by content-hash into an sproc store on the DB (compare/contrast: Redis EVALSHA), and then "symbolically linked" to a particular name in the client schema.

Ideally, in applications that use client schemas, every SQL query that's a static literal at app build time, would be compiled into a boot-time registration of, and…

> Somebody always wants to undelete something
> or examine it to see why it was deleted
> or see who changed something, or blah blah blah

People for whom this resonates should look into Dolt: https://dolthub.com/.

It's a mysql-compat database that is versioned and forkable - basically imagine git and mysql had a baby. Every transaction creates an entry in the commit log that lets you see when, how, and why the database changed. And just like git you can `dolt checkout <hash>` to see what the data was like at some point in time, or `dolt checkout -b temp-branch <hash>` to make exploratory changes to some historical version, or `dolt revert` to revert a transaction... etc.

There is a lot more power that comes with making the entire database versioned and forkable by default. For example it makes it much easier to recover from catastrophic bad code pushes, etc.

note: Dolt was forked from Noms, my previous project, but I don't work for Dolt or have a stake. Just a fan.

> I'd love to see what this map looks like filtered for specific services.

Sure. I did something like that with our insurance data. Did a quick folium visualization. You can start from here:

https://www.dolthub.com/repositories/dolthub/hospital-prices-allpayers/doc/main

A limitation of our dataset is it excludes percentage-based contracts. So those won't show up.

Anyways, if you have the hospital NPI you want, you can look up all of its dollar-amount negotiated rates in this table. You can link that to the `hospital` table, geocode however you want, and make a map of the different rates. Note, however, that the insurance data published here is kind of a mess and, I think, contains many many mistakes (originating in the source data.)

BTW, I'm not sure if this insurance-derived hospital data exists anywhere else. I believe it doesn't.

There's a trend of versioned databases that could address these issues. I've seen at least Dolt [1] for relational:

"Dolt is a data format and a database. The Dolt database provides a command line interface and a MySQL compatible Server for reading and writing data."

... and TerminusDB [2] for graphs:

"TerminusDB is an open-source knowledge graph database that provides reliable, private & efficient revision control & collaboration."

I don't know if Dolt supports stored procedures, but in any case versioning a database would be an amazingly powerful and convenient thing, if it can be done without sacrificing too much DB performance.

Worth investigating and has the potential of changing the way we write apps.

--

1: https://www.dolthub.com/docs/tutorials/installation/

2: https://terminusdb.com/

I just learned of Neon a few weeks ago. From the looks of it, Neon supports branching, but it doesn't support merging. Xata supports both branching and merging, however it only applies to the schema.

Dolt (and eventually DoltgreSQL) handles everything. Branching, merging, diffing, cherry-picking, commits, and more. Working with both the schema and data. On top of that, we also have DoltHub (https://www.dolthub.com/) that's analogous to GitHub, and DoltLab (https://www.dolthub.com/#doltlab) that's analogous to GitLab. We are targeting the entire ecosystem from the bottom up.

Our implementation of the Merkle DAG data structure is optimized for tabular data since Dolt is a SQL database, but a lot of it generalizes to other kinds of data. For example, we put a lot of work into making sure that chunk sizes had a predictable distribution, since very large chunks kill query performance:

https://www.dolthub.com/blog/2022-06-27-prolly-chunker/

We also have a lot of work to do with preserving structural sharing across schema changes, which we're not great at yet.

https://www.dolthub.com/blog/2024-01-19-structural-sharing-with-schema-changes/