Learn Mad Database Skillz at YAPC::NA 2009

A few weeks ago, I twittered that, in my opinion, application developers should really learn how to use databases. And by that I mean SQL, really. I know that a lot of app developers like to use ORMs to access the database, so that you don't have to really think about it, but most ORMs are written by people who don't like databases, don't like SQL, haven't taken the time to learn it in any depth, and thus don't write very good SQL. And even if they do like SQL, that usually means they provide a way for you to execute SQL queries directly. The reason? Because the ORM does not really understand how building more and more complex queries can have negative performance issues, and that there is more than one way to do it. It's pretty common to have to go back to custom SQL to solve performance issues. So to solve those problems, you gotta understand SQL.

Another sin of application developers is to try to use very standard SQL syntax and features when writing SQL queries, so that they can easily be ported to other databases. Well, if you're going to do that, you might as well use an ORM, but never mind. Think about it this way: If you were writing an application in Ruby, would you avoid the use of blocks because you might someday want to port it to Perl? And how often have you decided to port an application to another database, anyway? Sure, some OSS projects add support for new databases, but they seldom drop support for one RDBMS in favor of another.

If you're writing an application in Perl, it pays to learn idiomatic Perl. If you're writing it in Ruby, it pays to use idiomatic Ruby. So why would you settle for anything less when using an RDBMS? SQL is, after all, just another programming language, and the various dialects have their advantages and disadvantages. Learning how SQL really works and how to leverage the features of your RDBMS will only improve the performance, reliability, and scalability of your app. If your Perl or Ruby or Python code doesn't look like C, why would you write least-common denominator ANSI-92 compliant SQL? You have a powerful programming language and application server with an amazing array of features and capabilities. Use them!

All of which is a very long-winded way to say that it really, truly pays to learn the ins and outs of SQL, just like any other language. And if you're a Perl hacker, you have a great opportunity to do just that at YAPC::NA 10 this summer. In reponse to my tweet, YAPC organizer Robert Blackwell replied in agreement, and pointed out that famed SQL expert Joe Celko, author of numerous volumes on SQL syntax and techniques, will be offering two classes on SQL at YAPC:

This is a great opportunity to expand your knowledge of SQL, how it works, and why it's so powerful. (Even if you're not fond of the idea of relational databases, think of it as an opportunity to follow Tom Christiansen's injunction and learn a bit about logical programming.) Celko knows SQL like nobody's business, and will be sharing that knowledge in two remarkably cheap courses. Even if you're not a Perl hacker, if you want to really learn the ins and outs of SQL-- how to write idiomatic SQL to match the mad skillz you already apply to your application code, you could hardly do better than to get in on these deals and drink from the Celko firehose. I only wish I was going to be there (alas, prior plans interfered). But do please tell me all about it!


Dave Rolsky wrote:

I don't like SQL, but I know it pretty well. My latest ORM does reflect that, I think.

eggyknap wrote:

I gave a presentation a few weeks ago on precisely this topic: http://eggyknap.blogspot.com/2009/03/fun-with-sql.html

Leandro DUTRA wrote:

Data Adminiſtrator

Celko is dangerous. He doesn’t really underſtand ðe relational model, and ðus goes around ‘ſelling’ his bafflin’ neſted ſets hierarchical meß. Steer clear of him…

gregj wrote:

video celko

I hope someone records his lecture. I can't be in states atm, so...

Manni Wood wrote:

Love the comparison of avoiding Ruby blocks in case you port to Perl. Search also "ORM: the Vietnam of Computer Science". Observation: programmers who don't want to learn SQL are, more directly, programamers who don't want to learn a new programming language. I think we can all see the problem there. Finally, after you've learned SQL, you'll end up using a SQL mapper like iBATIS, and not an ORM.

Garth Gilmour wrote:

Mixed Metaphors

It seems to me that you're mixing your metaphors here:

If you’re writing an application in Perl, it pays to learn idiomatic Perl. If you’re writing it in Ruby, it pays to use idiomatic Ruby. So why would you settle for anything less when using an RDBMS?

Because idiomatic Perl/Ruby/Java/C++ works the same way in all implementations. Idiomatic means making the best use of the standard features whilst avoiding historical baggage, not taking advantage of ones that only exist in your compiler/platform/interpreter/virtual machine.

One of the main reasons why C++ fell and Java took over is that in Java file access, networking, crypotgraphy etc... all work the same way regardless of where your code is running. Developers learned the hard way in the 1990's that one-way-for-UNIX-and-one-for-Windows quickly degenerates into one-way-for-Solaris-and-one-for-HPUX and after that its just chaos.

SQL is, after all, just another programming language, and the various dialects have their advantages and disadvantages

As I understand it SQL is a declarative query language (or started out as one anyway). Its against the entire concept of a query language to have different versions. Look at regular expressions where every programming language supports Perl 5 regex's or XML where every modern programming language supports XPath. Both of these have the same complexity (or higher) than SQL and the same need for optimization. Yet I dont have to relearn regular expressions when I move engine or XPath which I switch parser - so why should I have to adjust my SQL when I move database?

alex pilosov wrote:

Damn right

Yes, people who don't understand SQL are doomed to reinvent it. Badly.

The proliferation of DBIx:: modules on CPAN is a testament to it. One of worst offenders is Request Tracker - bogosity that results in a dozen requests sent to the server for a simple request.

Ali wrote:


The difference between T-SQL and PL/SQL only server to be a barrier of exist or of switching from one RDBMS to another.

Both language are almost equally powerful.

The difference between Ruby and Perl is intrinsic and subtle.

T-SQL to PL/SQL should be more akin to what JRuby is to Ruby, they should be identical, any extra should be in the platform no in the language semantic, like how that Java libraries are available for JRuby, which for some tasks makes JRuby more suitable

There are many good reasons to make your application DBMS portable, but there is even more and better reasons to use MVC, separation of concerns and insulate Data Access logic from your business logic!

Laurence Rowe wrote:

Just get a better ORM!

Try SQLAlchemy. It really works well as underneath the ORM there is a sql abstraction layer with a proper understanding of the underlying relational algebra. This means I don't have to use full ORM mapping when it is not appropriate, but I'm still able to use the sql abstraction layer to write my queries in a portable manner. This means I can develop (and have my tests run with) sqlite, which is a whole lot quicker and easier than starting up oracle in a VM.

ORMs can be a useful abstraction layer. It means I don't have to write my own code for working out which records have changed and need to be flushed to the database.

Of course a good understanding of relational databases is important for application programmers who use them, but learning the intricacies of each sql dialect seems a waste of time to me.

Theory wrote:


@Dave—I agree that you know SQL pretty well, but my question is this (not having really looked at Fey: How much of SQL have you had to re-implement in Perl? And, relatedly, which SQL?

@Leandro—That's subject to debate, I suppose. But Celko certainly knows more about SQL and databases than any application hacker I know. So I would suggest, to app hackers, that they attend the talk, learn more, and don't check your brain at the door.

@gregj—Yeah, me too!

@Manni—I'm happy just writing SQL, frankly. These days I write stored procedures and functions in SQL, then let the app front-end use those. App developers seem pretty comfortable with a functional interface. :-)

@Garth—I agree that “Idiomatic means making the best use of the standard features whilst avoiding historical baggage,”but the enforcement of a least-common-denominator standard on app developers has been to the detriment of usability, maintainability, and performance. Put another way, many application developers hang on to the historical baggage! As the standard evolves, it will become easier to write standard-compliant SQL, but ANSI 92 ain't it. Hell, I could just rename this post “No, Really, Learn SQL,”because application developers know only a tiny sliver of the standard (never mind that not all app vendors implement the standard equally). So you can look at it both ways. When I encourage using taking advantage of implementation-specific features, many of those features are in fact standards-compliant. I think it's okay to use stuff that's not directly in the standard, too, but hell, if people just want to use the standards-compliant stuff, there's so much more than what they use!

Oh, and I will be using named captures in Perl 5.10 regular expressions, depsite the fact that they're not supported in Ruby regular expressions (yet).

@alex—Hallelujah brother, preach! (To be fair to RT, it is symptomatic of its time and the knowledge of its creator at that time—as is Bricolage.

@Ali—I believe that SQL is the data access logic. It should also encapsulate your business rules. That's what it excels at.

@Laurence—The touble with an ORM like SQLAlchemy—and I laud its appreciation of SQL—is that, except for relatively simple things, you're going to have to write the SQL yourself anyway. So why not just learn it? Let me put it this way: I don't want an abstraction layer so that I can write Ruby libraries in Perl—I can just write them in Ruby! But the idea of needing to learn each SQL dialect is a red herring. Almost no one ever ports their applications from one database to another. So just take advantage of the dialect you're using, especially if there are standards-compliant constructs that you're not using (and even if some other database you might theortically someday use doesn't support such contrcts—but then why would you ever use such a database?)

Thanks for the great comments, everybody, much appreciated!