Querying SQL databases in 2024
In this article, we will compare SQL, Query Builders and ORMs. This is not a new discussion, but the tools evolve, so it makes sense to revisit this topic once in a while.
Throughout my development career, I have come in touch with multiple databases. Most of them were relational databases, aka. SQL dbs.
There are plenty of popular drivers and libraries for various programming languages to access data within those databases. In this article, we will focus on three common approaches to querying SQL databases.
At the end of this article, you should be able to tell which approach is best suited for your application.
Distinction
Let’s start off with building a common understanding of what those three approaches are.
Plain SQL
SQL is the language that relational databases understand. There is an SQL standard, but every database has its own SQL dialect.
Because of this, with plain SQL we often target one single database implementation.
Using plain SQL gives us maximum control and performance. It also allows us to use all features that the database provides to us.
Query Builders
Query builders allow us to create queries by using the builder pattern. We chain various parts of the query together by calling methods on the builder instance.
This abstraction allows us to build more database-agnostic queries. The query builder can generate the SQL for a configured dialect.
And with TypeScript or any other statically typed language, we are more likely to detect semantic or syntactic errors of our queries at compile time.
Compared with plain SQL, by using query builders, we trade a bit of control and performance for compile time validation and more unified queries.
ORMs
An Object-Relational Mapper (ORM) is even more abstract than a query builder. As the name suggests, ORMs map objects to relational database records and vice versa. Relations can be resolved, and a whole record tree can be fetched, created, updated or deleted in a single abstract query.
Like the query builders, ORMs provide type-safety. And compared to query builders, we trade even more control and some performance for more abstract data fetching.
SQL vs. Query Builders
Let’s start our comparison with SQL and query builders.
As mentioned above, query builders aim to provide a type-safe way to build SQL queries. This improves the development experience and speed.
They also partly eliminate differences between dialects and make our queries more portable across database engines.
All query builders I came in touch with (jOOQ, Knex and Kysely) provide ways to execute raw queries to use database-specific features, so you don’t miss out on those if you use a query builder.
Speaking of Kysely, I would be happy to create a video about this. If you are interested, let me know in the comments below.
The downsides of query builders are that they add additional payload to your app and a bit of runtime overhead when building the queries. Both impacts are insignificant for most applications.
Here a little side note: In Go, there is a tool called sqlc. It allows us to write our Queries in SQL. The SQL compiler then generates functions and types automatically, which we can be picked up in our data access layer. So we don’t need any runtime dependency anymore. I just saw that there is also a package sqlc-gen-typescript in development which adapts sqlc to TypeScript.
Query Builders vs. ORMs
For context: I mainly used Hibernate/JPA for Java and Objection with TypeScript so far.
While building queries with a query builder is only a thin abstraction layer, most ORMs will let you execute operations on database records without requiring much knowledge of SQL.
You can do graph operations with a single high-level call to your ORM, and this may trigger multiple queries within the library without you noticing it.
This sounds very nice, doesn’t it?
This approach works quite well for simple CRUD operations. However, in more complex applications, there can be various types of issues, depending on the concrete ORM.
In the past, I sometimes faced issues related to internal caching (Hibernate) and inefficient fetching of related entities.
If those issues are not well documented, we quickly find ourselves digging deep into the implementation of the ORM to understand what is going wrong or causes performance issues. When we eventually spot the issue, it may be quite difficult or even impossible to solve it without falling back to a query builder or even native SQL.
So, if you use a traditional ORM, you may come less in touch with SQL, but you will learn a lot about this specific ORM. And I think we both know which skill will more valuable in the long term.
An approach that I find promising is the one of Drizzle. Drizzle is quite new and already gained a lot of adoption. If I went the ORM way, I would take a look at this library next. It is basically a query builder with additional abstractions. And a comforting promise from their side is:
Drizzle always outputs exactly 1 SQL query — https://orm.drizzle.team/docs
This makes things much easier to reason about.
Conclusion
As a summary, here is a table of the strengths and weaknesses of the three querying approaches, we just discussed:
Remarks:
- SQL is our baseline, so we don’t see any ratings here.
- Because performance and payload are not impacted when we use a compile time query builder like sqlc.
- The dev speed is initially faster for ORMs, but in more complex apps it decreases.
- The DX of type-safe query builders is already very nice. When we look at ORMs, internal issues can decrease the development experience.
All in all, I suggest defaulting to a query builder. The type safety really pays off and the impact on the execution performance and the payload are very low. In TypeScript, I have had the best experience with Kysely so far.
If you are building an app with a lot of simple CRUD operations, and you don’t mind the extra payload of an ORM, then I won’t stop you. I would recommend trying something like Drizzle, where you start off building SQL-like queries and only occasionally use graph operations.
I wish you happy coding!