87 How to speak SQL in the shower

I got this idea while I was taking a shower the other night, so bear with me. :) It has to do with mixing SQL prepared statements with gettext in order to obtain a sort of framework that will wield unexpected benefits.

Let us start.

If you are familiar with SQL prepared statements you probably already know how useful they can be. First, they can boost the performance of individual SQL queries in various amounts, depending on the complexity of the query and how much it’s going to be reused. Second, they eliminate the risk of SQL injection, since they are parametrized ie. they act like functions, where the parameters are never given the first chance to pretend they’re of another type than they’re supposed to be, or to exceed their storage space and mess with the query itself.

For a while I’ve been entertaining the idea of building an entire framework (a class, a function library, an API, take your pick) around prepared statements. Nowadays almost any SQL database worth its salt supports them. The idea was that every last query that occurs in your code should pass through this framework, which would transparently make use of prepared statements as much as possible.

Caution: Consider this one carefully. It’s entirely possible that the small overhead induced by using prepared statements everywhere can negatively affect your project after all.

This can be trivially implemented with a single function and perhaps a global or static hashtable. Watch the following pseudo-code:

function PrepStatQuery(query, parameters ...) {
  define global or static hashtable
  define hash as md5(query text)
  if hash not present in hashtable {
    prepare statement(hash, query)
    record hash in the hashtable
  }
  inject parameters into the query text
  execute the query via the prepared statement
  return the result of the query
}

So far so good. But watching this unfold it’s suddenly obvious that one problem will be to keep identical queries identical. Mess it up even slightly, add a single extra space, and the hash won’t match. It won’t be a big issue, but it will negate the whole point.

This brings us to even deeper waters, into the issue of SQL queries in general. Many projects end up with SQL statements all over the code. Let’s think about it. We have all these pieces of text, spread all over the code. We need to keep redundancy as low as possible, we need to globally change the same one all over the code when necessary and so on. What does this remind you of?

Are you pondering what I’m pondering? If you’re thinking “gettext!” then you’re absolutely right. Gettext is the perfect solution to this problem. How about using it to manage our SQL queries? Instead of doing query("SELECT * FROM users") you would do query(_("SELECT * FROM users")). And you get to manage your queries centrally, using the gettext tools.

And we get yet another bonus. Gettext is nice for redundancy control, but that’s not its main strength. It’s main purpose is translation. What could we be translating SQL to? Well, how about a different SQL dialect? Let’s say that you wrote your project to use a certain SQL database natively (Postgres, for example) and now you want to make it work with MySQL too. If you used gettext, you can just send a gettext file to your SQL guy; he gets a column full of Postgres queries on the left and he has to write the MySQL correspondents in the right column. When he’s done he sends you back the file, you tell your project to use the MySQL “translation” and suddenly your projects “speaks” MySQL. This is true separation of SQL from programming code; your DB people no longer need to become intimately acquainted with your programming language, any more than the person translating your texts to Spanish needs to know how to program.

Let us count the benefits we get from these crazy ideas:

• A cache for your SQL queries, which serves to boost performance.
• Eliminate the possibility of SQL injection at the core.
• Control SQL queries all over your project centrally and keep redundancy in check.
• Separate your SQL from your programming language and allow your project to be ported to a different database in a straightforward, easy manner.

That’s a pretty good outcome for a 5″ shower. (And I’m gonna miss them during the next month or so. :()