Strong opinions, weakly held

Tag: databases (page 2 of 2)

Limit 1

Here’s a free tip. When you’re making ad hoc changes to a database directly from the database client, it is a good idea to stick LIMIT 1 on your UPDATE and DELETE statements if your intent is to only update one row. Even if you want to update many rows, I still recommend a dry run with LIMIT 1 first.

Then if you make an error in your boolean logic or leave something out of the WHERE clause, the wreckage will be limited. It’s much easier to deal with the loss or unwanted alteration of one record than a whole database.

Of course, wrapping your ad hoc queries inside a transaction is also a brilliant idea. I recommend that approach as well assuming it’s available.

The danger of SQL illiteracy

Despite growing interest in other storage media, relational databases are still the dominant data storage medium for Web applications. Indeed, as SQLite and other databases that can be embedded in applications grow in popularity, we’re seeing the use of relational databases expand even further.

It seems to me, though, that actual knowledge of SQL seems to be falling. I blame this on the growing popularity of persistence frameworks that abstract the database away, allowing developers to interact without databases without writing much (or any) SQL. Ruby on Rails has ActiveRecord, Django has its own persistence components, and there are many ORM frameworks for Java that work very well. There are even a number of persistence frameworks for PHP. As developers come to rely on these frameworks, their SQL skills grow rusty. Many developers don’t even learn SQL in depth, period.

This is a big liability. Even if you don’t have to produce SQL to include in your application, you still need to know it to write ad hoc queries. Knowing SQL lets you write a single query to answer questions like, “What’s the average total for orders received from customers in Texas over each of the last six months?” I’ve seen developers try to take care of those kinds of questions by pulling back individual records and aggregating the results in code. They ought to be using SQL efficiently instead.

In the short term, the answer for me is that I’m going to be asking more SQL questions in interviews. If you’re a developer and you’re not incredibly comfortable with SQL, you should probably hit the books. The other thing I’d add is that languages and frameworks come and go, but SQL, CSS, HTML, and JavaScript are going to be with us for a long, long time. Any investment you make in those areas will certainly pay off.

Links for April 2nd

Newer posts

© 2023 rc3.org

Theme by Anders NorenUp ↑