Database Protocols Are Underwhelming

Summary

If you’ve been in this trade for a while, you have probably seen dozens of debates on the merits and problems of SQL as a relational database query language. As an ORM maintainer, I have a few gripes with SQL, but overall it is workable, and anyway, it has so much inertia that there’s no point fantasizing about a replacement. However one database-adjacent topic I don’t think I’ve ever seen any discussions about, and that I think could be improved, is the protocols exposed by these databases to execute queries. Relational databases are very impressive pieces of technology, but their client protocol makes me wonder if they ever considered being used by anything other than a human typing commands in a CLI interface. I also happen to maintain the Redis client for Ruby, and while the Redis protocol is far from perfect, I think there are some things it does better than PostgreSQL and MySQL protocols, which are the two I am somewhat familiar with. Mutable State, Lot Of Mutable State You’ve probably never seen them, because they’re not logged by default, but when Active Record connects to your database it starts by executing several database-specific queries, which I generally call the “prelude”. Which queries are sent exactly depends on how you configured Active Record, but for most people, it will be the default. In the case of MySQL it will look like this: SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',STRICT_ALL_TABLES,NO_AUTO_VALUE_ON_ZERO'), @@SESSION.wait_timeout = 2147483 For PostgreSQL, there’s a bit more: SET client_min_messages TO 'warning'; SET standard_conforming_strings = on; SET intervalstyle = iso_8601; SET SESSION timezone TO 'UTC' In both cases the idea is the same, we’re configuring the connection, making it behave differently. And there’s nothing wrong with the general idea of that, as a database gets older, new modes and features get introduced so for backward compatibility reasons you have to opt-in to them. My issue with this however is that you can se...