In my last blog post, I went over how we initially implemented foreign keys on our database—Dolt, which fuses a fully MySQL-compatible database with Git-style versioning capabilities—and how our first decision resulted in a complete rewrite of foreign keys as the database matured. For foreign keys, we truly thought we were making the best decision at the time. For our versioning functions...well we knew they were a terrible idea before ever implementing them.

Versioning Functions?

As mentioned earlier, Dolt is a database that is a drop-in replacement for MySQL. It also supports Git-style versioning, which functions exactly how you'd expect. git checkout main switches to the main branch in Git with dolt checkout main doing the same. git commit -m "my message" adds a commit to your branch, with dolt commit -m "my message" doing the same. You can treat your data the same as you treat your source code (or you can use Dolt as one of the simplest-to-setup SQL databases out there). There is a disconnect though. The above Git and Dolt examples are executed on the command line, but how would you use these features as a client connected to a running Dolt server? That's where the versioning functions come in.

To make it super-simple, we decided to make the functions map directly to the command line. So dolt commit -m "my message" would become SELECT DOLT_COMMIT("-m", "my message");...wait SELECT? Isn't SELECT supposed to display data rather than modify anything? Well yes, that would be correct! We, however, had an issue at that time, as our alternatives would look something like INSERT DOLT_COMMIT(...);, so we chose the lesser of evils. We knew it was bad, we knew it was terrible, but we eventually learned to live with it until we implemented the functionality that we needed to support these commands The Correct Way ©.

Stored Procedures > SELECT

I apologize for lying to you. When I said we eventually learned to live with it, I actually meant they learned to live with it: everyone else but me. As horrendous as it was, it worked and it worked well, but my sleep quality deteriorated significantly knowing we were using a SELECT statement to run commands. Last year, I was finally able to implement stored procedures, which gave us the correct statement to use: CALL.

CALL DOLT_COMMIT("-m", "my message"); Just Makes Sense.

Sure, we needed to extend the CALL syntax to allow a variable number of parameters for versioning procedures, but that wouldn't take long. There was, however, an issue, as such an addition (and the subsequent procedure implementations) was classified as low-priority. I groaned and whined but ultimately decided to remain employed, so those additions were put on hold.

A year later, I was eventually able to classify my sanity as high-priority, and the only remedy was to add versioning procedures. So, almost 3 years after the public launch of Dolt, we finally have The Correct Way © of using the versioning commands from within SQL.

What about those nasty SELECTs?

They remain for now. We're officially deprecating most of them, and they'll be removed altogether in a future release. From the perspective of an end-user, it's just changing your SELECT to a CALL, as everything else remains the same: name, parameters, etc. It's also worth mentioning that I did not create a stored procedure for every Dolt function, as some functions actually made sense with a SELECT. You can check them out the old functions and the new procedures in our documentation.

Conclusion

When building a new database, you often have to make decisions that make the most sense at that time based on the circumstances present, even if those decisions don't actually make sense. Sometimes, they're just adopted as quirks of the product, and other times adamant employees are able to make a change 😊.

If you enjoyed reading this blog, perhaps check out some of our other blogs! You can keep up to date with us through Twitter, or you can chat directly with us through Discord. You can even check out our ever-growing list of open databases at DoltHub!