SQLite, test-driven development, and the inscrutable SQL standard

I interviewed Dr D Richard Hipp, the main author of SQLite, for the Guardian Newspaper.

Among the things I found interesting is that he attributes the high reliability of his database engine to the extensive test suite included in the code. I’m not sure whether he practices test-driven development as such, but it is a great case study for the advantages of integrating tests with your code. One of the points he made was that the test suite enables him to replace entire subsystems and be confident that nothing gets broken. By contrast, I have heard of cases where key sections of code in large, old applications is marked “do not touch” because nobody dares to risk the consequences.

I also asked him about the importance of standards in software development. He gave me an answer that somewhat surprised me:

When I coded up SQLite I did not refer to any official SQL standard. I used the PostgreSQL documentation. That was my reference. If you’ve ever picked up a copy of one of the official SQL standards you will find it largely inscrutable. They are next to impossible to make sense of. Even for particular details of syntax you can study it, and they are so vague that you can’t really understand what they mean. So a strategy we’ve used when there’s some question about how something should work is we write a little test script and run it on lots of popular SQL database engines, PostgreSQL, MySQL, Oracle, and try and find a consensus. Then we code to make SQLite work the same as everybody else does. Clearly that’s not the right way to do a standard, but in practice the implementations vary so widely that it’s the only practical thing to do.

Many companies boast about how they respect and observe software standards; sometimes the reality is more pragmatic than you might have thought.

2 thoughts on “SQLite, test-driven development, and the inscrutable SQL standard”

  1. It’s no coincidence that he mentions PostgreSQL in particular; their documentation (like their software) is superb.

Leave a Reply

Your email address will not be published. Required fields are marked *