Drizzle ORM Is SQL in a JavaScript Hat — and Wears It Well
Drizzle ORM is described by its makers as “a headless TypeScript ORM with a head.” But what is an ORM? The abbreviation ORM is short for “object-relational mapping,” which is a programming technique used to convert data between incompatible type systems. In the case of Drizzle ORM, this means converting types from SQL to TypeScript.
In this article, I’ll be discussing Drizzle ORM’s approach to SQL, and why I think it’s an excellent solution for developers working with relational databases in TypeScript environments.
SQL Is a Typed Language
In JavaScript land, an ORM allows developers to interact with a relational database using method-based syntax rather than using raw SQL queries. Many JavaScript developers shy away from SQL, claiming it’s not type safe.
Claiming SQL isn’t type safe is like carrying a bucket of sand into the ocean and then complaining you can’t build a castle with it.
But SQL is a typed language, and thus it is type safe. When creating schema for tables in PostgreSQL, you define each column and give it a data type. For example:
Developers who claim SQL isn’t type safe mean that SQL types don’t work when SQL is moved out of its intended environment and into a different environment — e.g., a TypeScript environment.
But it’s not SQL’s responsibility to ensure its type system works in your environment — it’s yours. Claiming SQL isn’t type safe is like carrying a bucket of sand into the ocean and then complaining you can’t build a castle with it.
In such cases, an ORM can help preserve a language’s intended type safety, while transferring the same types to the new environment.
What Is Drizzle ORM?
By learning Drizzle ORM, you’re actually learning SQL — you just express it using method-based syntax.
Unlike some ORMs and JavaScript clients and SDKs, which have a tendency to reinvent the wheel by introducing very specific syntax (which usually works only in that one scenario), Drizzle ORM aims to be as SQL-like as possible — and as you probably already know, SQL is absolutely everywhere!
By learning Drizzle ORM, you’re actually learning SQL — you just express it using method-based syntax.
Here’s the same table schema as above, but this time written in JavaScript:
It looks very similar, right? For me this is a step in the right direction, and helps explain why I think Drizzle ORM is a nice alternative to SQL. It’s about as close as you can get to raw SQL — and the fundamental goals of SQL — without actually writing SQL.
The Goals of SQL
As outlined by Don Chamberlin (one of the principal designers of SQL), the design of the language was guided by four main goals.
- They wanted to use the term “tables” instead of “relations.”
- They wanted to base the language on ordinary English words like “select.”
- The language should have no special symbols, and it should be easy to type on a keyboard.
- They wanted it to have something that they called the “walk up and read” property. Meaning, in simple cases, a user with no special training should be able to understand a query just by reading it.
The goal I’m most keen to discuss is 4: the “walk up and read” property.
To demonstrate how faithfully Drizzle ORM adheres to the original goals of SQL, here’s a simple query that selects all the admin users from a table named users, and then orders them by their last name.
The first query is written in SQL; the second is written in JavaScript using Drizzle ORM.
Example SQL Query – Select
Example Drizzle ORM Query – Select
I think you’ll agree: Both queries can be read line by line without the need for any special training.
My only small gripe is the eq function used in the Drizzle ORM query, which could have been named equals to make it even easier to walk up and read.
SQL’s Gone Wild
However, things get a little more complex with SQL when you’re attempting to query from two different tables and a JOIN is used.
In the following example, the query selects orders from the orders table and joins it with customers from the customers table using the customer_id column, and then limits the results to 50.
As before, the first query is written in SQL and the second is written in JavaScript using Drizzle ORM.
Example SQL Query – Join
Drizzle ORM Query – Join
This isn’t an overly complex query, but Drizzle ORM is still hanging in there and is more or less the same as the SQL query. The only small difference between the two queries is that while SQL uses the keyword ON, Drizzle ORM’s query instead uses the eq function.
Production Query
Examples for demonstration purposes are one thing, but here’s something I’m currently using on my site.
I have two tables: One holds reaction data, the other holds analytical geolocation data. To create this feature, I’ve counted up the happy reactions submitted by visitors to my site, then joined them with the geolocation data to determine which city in the world submits the most happy reactions.
Here’s the SQL query I’ve used to create this feature, and just below is the Drizzle ORM equivalent.
Happy Reactions Production – SQL Query
Happy Reactions Production – Drizzle ORM Query
Now, this one is a little tricky because Drizzle ORM has started to stray away from SQL ever so slightly, but that’s not necessarily a bad thing.
The SQL query uses a common table expression (CTE) named happy_reactions. A CTE is a temporary result that you can reference from the main query. In this case, the CTE creates a count for the happy reactions from the last 30 days, and the main query selects from the CTE to retrieve the top 50 results.
The Drizzle ORM query achieves the same result but more directly, because of the method-based chaining syntax. This could be a skill issue on my part, but I’ve needed to utilize sql to access theCOUNTfunction.
Both queries are readable, but the Drizzle ORM query feels more straightforward because it removes the need for a separate CTE declaration. Instead, it accomplishes everything in a single chain of method-based calls — and I rather like it!
Beyond the Query
JavaScript tooling has come a long way over the years; pretty much everything in your stack can now be managed from the code base. This approach enables automated, consistent and repeatable deployment, along with management of applications, servers, storage and networks.
One such automation task might be to manage database migrations.
Drizzle Migrations
The Drizzle ORM team also makes Drizzle Kit. With Drizzle Kit, you can perform database schema updates, or migrations, by updating your schema and running a script.
For example, if I wanted to alter the users table (as outlined above) and add a new column named date_of_birth, I can make the changes to the schema and run drizzle-kit migrate.
Drizzle ORM schema files are version controlled just like all the other files in your JavaScript project, which means you’ll always have a single source of truth ensuring that both your application and your database are in sync.
Moreover, running this script as part of your CI/CD pipeline means that any changes pushed to production can happen at the same time.
Reusable Types
It’s super important in any application to provide type definitions so that you know how to handle the data. I’m sure you’re aware of the classic number-vs-string example, but if you’re not:
- If a = 1 and b = 1, and both are of the number type, then a + b = 2.
- But if a = 1 and b = 1, and both are of the string type, then a + b = 11.
Why? Because strings are concatenated. “1” + “1” is not the same as 1 + 1. As humans, we’re able to understand from the context that, in this scenario, we’re talking about mathematical arithmetic and would apply addition. JavaScript isn’t that smart, so it needs a little guidance. Adding types to an application can give JavaScript a helping hand to ensure that you end up with the intended result.
As such, TypeScript exists. TypeScript allow developers to statically type data, in much the same way that SQL has for roughly 40 years. It shouldn’t come as a surprise to you, but statically typing data is ever so helpful when developing complex applications, because it allows you to more accurately deal with the data your application receives.
To demonstrate what I mean, I’ll use the analytics table used in my site as an example.
Here I created a new TypeScript type (I’ve named it AnalyticsType), and then I was able to infer the types from the pgTable definitions using InferSelectModel from the drizzle-orm package. This allowed me to have both SQL schema types that will be defined in my PostgreSQL database as well as TypeScript types that I can use in my JavaScript application.
Suppose I had a query that selects the city, country and flag from the analytics table. For example:
The result is then passed on to a React component via a prop named analyticsData:
I can then use the TypeScript types in the component interface to ensure this component is only receiving the data it needs.
This simple component returns a list of visitors, and displays the city, country and flag — which are available within the data passed in via the analyticsData prop.
There is one little issue you’ll likely run into, though. In the table schema, there are a number of columns that I don’t necessarily need for this component. As such, I’m not querying for every column; I’m only querying for the city, country and flag.
The interface for this component — if it used the AnalyticsType created by using the InferSelectModel — would be missing some columns, and TypeScript would complain because the types aren’t inferred as optional — they are required.
To overcome this, I’ve used TypeScript’s built-in Pick method to extract only the types I need from AnalyticsType.
These are still the same types defined in the AnalyticsType interface, and still correspond to the types that have been defined in my table schema. Typing components in this way allows me to know exactly what data, and what type of data, I’m passing around my application.
You can read more about Drizzle ORMs types and type inferences in the docs: Type API.
Final Thoughts
I have to be honest: before Drizzle ORM, I was a little reluctant to adopt a JavaScript solution for writing SQL. But through my investigations, I’ve learned just how dedicated to the cause the Drizzle ORM team is. Wherever possible, they have faithfully followed the goals of the SQL language. The only places where they deviate slightly are where there are fundamental differences between the SQL and JavaScript languages.
Plus, it is super cool to have access to TypeScript types without needing any extra tooling. That gives me confidence when developing because I know my application is type safe from end to end.
The Drizzle ORM team has done an incredible job here, and if you’re writing SQL in TypeScript environments, I really think it’s worth a look. Drizzle ORM is really just SQL in a JavaScript hat, and they wear it well.
