Are SQL query builder libraries and ORMs more trouble than they’re worth? A couple of days ago, I read Stop using Knex.js via a link on Reddit.
I understand the point the author is trying to make. And I used to be pretty firmly opposed to ORMs and query builders.
I like SQL a lot. In one of the large applications I’ve worked on, every database option from basic CRUD to very complex queries happened in stored procedures in the database. While it was a tad tedious sometimes, it was also really damn fast. And I wrote a heck of a lot of SQL.
But over time, I’ve come to appreciate the benefits of a full-featured ORM. I think ORMs can be great tools for developers who have lived and breathed SQL for a while. Then, you can reasonably infer what the ORM is going to do under the hood.
Recently, I worked on a new .NET Core project. I used Entity Framework Core with it. I could’ve written a SQL script to create all the tables for me, but instead, I created my data model in C#, and then let EF Core generate the migrations to create the database tables.
And to be honest, I found that using C# as a notation for describing my tables and relationships between them nicer than using SQL to do it. But if I hadn’t spent a decent amount of time learning how to create normalized relation data models using SQL, I probably would’ve done a crappy job of expressing my data model in C#. So I suppose that understanding the relational nature of relational databases is the key here. Using SQL sort of forces you to think relationally. And as a bonus, I ended up with a nice set of C# classes that I can use in my application.
On the query builder side of things, EF Core is pretty darn good. I can generally predict what SQL it’s going to generate. And if it ever does go off the rails, EF Core offers a raw SQL escape hatch. You just give it the SQL query, tell it what class to map the query back to, and things generally just work. Sure, there are times where I could write better SQL than EF Core generates. But in most cases, the difference isn’t big enough to matter. And when it does matter, the hand-crafted SQL usually tends to be a short-lived fix. Before long, you’re on the road to super-fast Redis caching. So the faster SQL doesn’t end up mattering.
Query builders and ORMs are tools that can be used badly or used poorly. It’s easy to get into trouble if you use them as a crutch because you don’t really understand how the relational data you’re using works. But if you do have a solid grasp of relational fundamentals, you can use ORMs and query builders as force multipliers.