Stop Crippling Your Database (1/6): Notes on Sqlphobia and Database Agnosticism
There is a tendency in the Rails community to avoid SQL like the plague. We don’t want to write SQL, we don’t want to read SQL and, above all, we don’t want to understand SQL. That’s what database administrators do, isn’t it? It’s their job, not ours. If we don’t have a DBA (those guys are pretty damn expensive), we have ActiveRecord and its fairy-dust-powered interfaces. We, as a community, suffer from sqlphobia.
This is all understandable: writing SQL feels like moving a step down, rather than up, the ladder of abstractions. That’s not what we’ve been trained to do. The software development world has been progressively shifting from pure SQL to database abstraction libraries or even full-fledged ORMs. It just seemed right to simplify such a complex part of the ecosystem and let developers focus on the Things That Matter™.
The database is still a part of your application, though, and a pretty important one as well. You’ll regret neglecting it later on, when you’ll have performance problems the size of a baobab and no idea of where they’re coming from, because you don’t know what an index is, what a foreign key does or that your DBMS can do much more than SELECT/INSERT/UPDATE/DELETE.
As I’ll show you, it’s often less trouble to write SQL than to avoid it. Databases have already solved some of the problems we use ORMs for, and they’re inherently better at maintaining data integrity than any application written with any scripting or programming language or framework or library that ever existed on the face of Earth can hope to be.
This is a call to action for all developers to get rid of their sqlphobia and start giving their databases the attention they deserve. Life’s too short to not learn how common table expressions work.
A Thought on Database Agnosticism
Some of the techniques illustrated in this series are not database-agnostic. Even if all popular databases implement them, they all do it in a slightly different way, which means your application will not be portable and will be locked into that specific DBMS the second you employ them.
Database agnosticism, unfortunately, is another advantage of ORMs highly emphasized and loved in the software community, particularly among the ranks of beginners (even though it’s not rare to see seasoned developers considering it a paramount feature of modern web applications). We have this wonderful image in our minds of an application that runs everywhere, across different environments, databases, operating systems and languages. Well, it doesn’t work like that.
Let me put it very clearly: database lock-in is a non-problem, which makes database agnosticism a non-solution. Unless you’re working on an open-source CMS which must be deployable in a number of wildly different contexts, you will not have to worry about it. Just pick a good DBMS to begin with and stick with it. I recommend and use PostgreSQL because of its superior adherence to the ACID standard, but many people out there are using MySQL (almost) without any issues.
First of all, when was the last time you switched DBMS in the course of a project? That just doesn’t happen: as the basic requirements are clear since the beginning of a project (if they’re not, it’s not SQL you should worry about), so is the DBMS that should be used.
Even if a migration was in order, it’s such a rare occasion that the advantages of writing your own SQL are worth the hassle of rewriting it run on the new DBMS. With a good suite of integration tests (you do have those, right?), it shouldn’t be too hard to get everything to work as before. If it is, it probably means your code wasn’t good enough to begin with.
So, stop making excuses: your database is a Thing That Matters™. Don’t fear it and start treating it like one — it will reward you for your trouble.
This was the first of a series of six articles on how to improve the way you interact with your DBMS, especially in the context of Rails development. The next one will be about triggers, SQL’s callbacks. Stay tuned!
