Love Your Database (LYDB)

Statement of Purpose

Much of software development these days involves some sort of presentation layer in front of a database. Whether that layer is a REST API, or a traditional web app, you’ll write some Ruby/Python/PHP/Difference Engine code, and that code mediates between the end user and a database.

Because of a combination of accidents of history, such applications nearly always willfully neglect the abilities of the database. It’s practically holy writ in the Ruby on Rails world, and the idea seems pretty widespread, that we should put all the smarts and most of the complexity in the application server layer, and we treat the database as a dumb data bucket.

This neglect is a the very definition of a tragedy: we are needlessly suffering due to our own choices. Although this might count as comedy, as it is a tragedy decades in the making.

In this essay, I’ll explore briefly how we got here and why the results are enormously wasteful. In work to come, I’ll explain the forgotten power of the relational database, and I’ll describe in detail how to reclaim that power to make applications more robust, easier to write, faster, able to generate fluffy kittens on demand, and so on.

The virtues of the relational model

The relational database has the great virtue of simplicity. Your precious data is stored in what offers (more or less mathematically provably) the best possible tradeoff between simplicity and generality.

I don’t have room to go into the theory behind that statement here, but if you step back from SQL and consider tables, notice that every value in a normalized relational database is top-level, equal to all others. Structure doesn’t get any simpler.

A relational database provides atop this simple flat collection of just grouped and named values a surprisingly small number of operations that let you take this basic data store and elaborate it into all the forms and expressions of that data that you might need.

Enter SQL

However, as seems to be the iron rule in the computer industry, the standard for a relational data store that wound up winning in the market was the fairly awful Structured Query Language (SQL).

SQL is verbose, yet hard to read, hard to write and hard to implement. It wasn’t actually quite a proper relational database. It didn’t support following recursive relationships. It made some unnecessarily rigid assumptions about how the data was stored. It lacked lots of things that any reasonable programming language should have, such as subroutines or the ability to define your own types.

It is important also that the first (relatively) easy to use open source SQL database was MySQL, which was so basic and kludgey, even compared to the SQL standard, that it was really not suited to be used as anything but a dumb data bucket.

Here lies the roots of our tragedy: for quite a while there, the only practical way to store data was an SQL database, and if you favored open source, MySQL.

The modern SQL database

Through actual decades of work (PostgreSQL, for example, traces its roots to Ingres, which saw its first release in 1974), modern relational databases transcend the limitations of SQL to nevertheless deliver pretty darn well on the promises of the original relational theory.

Back up from your current stack and ponder this: what if you had a standard Model Server. Something like your current application server, but it separates out just the model part. By this I mean it has all of the model part: the validations, the callbacks, the batch processes, the security model, everything. Imagine if every part of the model from your current complex web app was its own isolated application. And then you could easily and transparently just use that model from any other program, in any programming language.

That’s what a modern SQL database server is. It’s not a data server; it’s a model server. And the database can do a damn sight better job of that than your Rails app can, just hanging off the side of it. That, right there, is the crux of Love Your Database.

How we got here

I think we got here something like this: Ruby on Rails really changed the landscape of web development frameworks in the mid-oughts. At the time, the most practical choice of open source databases was MySQL, so that’s what they used. And MySQL is awful at being anything but a dumb data bucket, so that’s how they treated it.

And we got used to it, and turned it into an actual thing, and folks came up with justifications for it. And now it’s holy writ. But I’ve never heard of a good justification for this position (unless you’re still using MySQL).

Where we should be

It’s really easy to point to a host of benefits from putting all your model logic in the database:

• it’s going to be faster, probably a lot faster;
• any logic available in the database can easily be shared by multiple client applications;
• it’s much easier to handle concurrent users not mucking each other up; and
• you can access the model from any language.

Postgres, in particular, has some amazing features that go well beyond these basics. It does let you define your own types. It can access many other sorts of data (other relational databases, other non-relational databases, elasticsearch and the like, even email and LDAP servers) and let you treat them just tables in your database. You can program it in any of something like 20 programming languages (by which I mean you write triggers, stored procedures and the like in all those languages). Its built-in full-text search gets up toward the features of dedicated tools like elasticsearch. It has robust support for XML, JSON and key-value stores, so it has the limited use cases for “NoSQL” covered. And it has about the best GIS support in any SQL database.

Put all that together, and someone saying you shouldn’t take advantage of those features surely has the onus on them to explain why. Until someone gives you a good answer to that, I invite you to join me while I explore how to move the model parts of your application into your database server where they belong.