Drizzle - An ORM that lets you just write SQL

If you're a developer who's already comfortable with SQL, you know how frustrating it can be to switch to an ORM that requires you to learn a whole new query language.

And if you’ve never heard of an ORM before, it’s basically a library on top of a database. Typically, it handles both connecting to the database and all your interactions with the database.

The problem is that most ORMs provide layers of abstraction between you and the SQL you are trying to write. This can be a polarizing topic, but with some ORMs, I find myself wanting to just write SQL directly instead of spending time understanding that libraries specific syntax. Take this for example:

db.emailTable.findMany({
  where: {
    email: {
      endsWith: 'customer.com',
    },
  },
})

Reading that block of code isn’t the problem - it’s pretty clear what it does. Writing it, however, is another story. If we wanted to instead write this in SQL, we’d get something that looks like this:

SELECT * 
  FROM emails
  WHERE email like '%customer.com'

and trying to go from that query to the code snippet above can be challenging.

At PropelAuth, we build a good amount of example applications in different languages and frameworks, and ramping up on ORMs can be a pain when all we want to do is write SQL.

Enter Drizzle

That's where Drizzle comes in - their philosophy is simple: "if you know SQL, you know Drizzle ORM." That same query above, in Drizzle, looks like this:

db.select()
  .from(emailTable)
  .where(like(emailTable.email, "%customer.com"))
  .all()

and that’s really exciting! You get both a syntax that looks incredibly similar to writing in raw SQL, type safety, and autocomplete to guide you through the queries.

But before we get ahead of ourselves, let’s look at a few more examples - including how we can define our schema in the first place.

Setting up Drizzle

In your application, you need to install Drizzle. We’re going to use SQLite, but there are drivers for Postgres and MySQL as well.

npm i drizzle-orm better-sqlite3
npm i -D drizzle-kit

Creating a Schema

Let’s now create our first schema in a new folder, db/schema.ts:

import {integer, sqliteTable, text} from 'drizzle-orm/sqlite-core';

export const postsTable = sqliteTable('post', {
    id: integer('id').primaryKey(),
    username: text('username').notNull(),
    text: text('text').notNull(),
    createdAt: integer('created_at', {mode: 'timestamp'}).notNull().defaultNow(),
})

This defines a table called post which has an ID, username, text, and a timestamp. If you had a separate users column, you could instead use a foreign key:

userId: text('user_id').references(() => users.id), // inline foreign key

From there, we can tell it where the database is. For SQLite, we can just use a file:

import {drizzle} from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

// ...

const sqlite = new Database('sqlite.db');
export const db = drizzle(sqlite);

The last thing we need to do is migrate the database so that it matches our schema. We’ve already installed drizzle-kit which will generate the migrations for us:

$ npm exec drizzle-kit generate:sqlite --out migrations --schema db/schema.ts

1 tables
comments 4 columns 0 indexes 0 fks

[✓] Your SQL migration file ➜ migrations/0000_aspiring_whiplash.sql 🚀

This just generates the migration file, it doesn’t set up the database for us. We can either apply that SQL file ourselves, or we can do it within application (back in the schema.ts file):

import {migrate} from "drizzle-orm/better-sqlite3/migrator";
// ...
migrate(db, {migrationsFolder: './migrations'});

And now, when we run our application, the database will match the schema we defined. Let’s start querying it.

Fetching Data

We saw one example of fetching data already, but now let’s add a few more options. Here’s an example query we might want to run:

SELECT * 
    FROM post 
    ORDER BY created_at DESC
    LIMIT 10
    OFFSET 40

and with Drizzle it looks like:

export function fetchPosts(pageSize: number, pageNumber: number) {
    return db.select()
             .from(postsTable)
             .orderBy(desc(postsTable.createdAt))
             .limit(pageSize)
             .offset(pageSize * pageNumber)
             .all()
}

This is pretty easy to read, but more important than that - it was simple to write. You autocomplete your way down through methods that you already understand (from, orderBy, limit, offset) because they are just SQL.

Small aside: What type to return?

Drizzle provides InferModel so you can get explicit types for querying and inserting data.

export type Post = InferModel<typeof postsTable>
export type InsertPost = InferModel<typeof postsTable, 'insert'>

How are these different? InsertPost, for example, doesn’t require you to pass in created_at since it has a default set.

We can now go back and update our function with this type:

export function fetchPosts(pageSize: number, pageNumber: number): Post[] {

Inserting Data

There’s not much to say here other than, once again, this looks exactly like the SQL query:

export function createPost(insertPost: InsertPost): Post {
    return db.insert(postsTable)
             .values(insertPost)
             .returning()
             .get()
}

The real ORM test… Joins

It’s pretty clear that Drizzle has done a great job matching SQL syntax for select/insert, but what about joins? To me, the true test of an ORM is how it handles joins because of how complicated the query can get.

If we rewrite our schema a bit so that the username is on a separate user table:

export const usersTable = sqliteTable('user', {
    id: integer('id').primaryKey(),
    username: text('username').notNull(),
    // other fields that we don't care about
})

export const postsTable = sqliteTable('post', {
    id: integer('id').primaryKey(),
    userId: text('user_id').references(() => usersTable.id),
    text: text('text').notNull(),
    createdAt: integer('created_at', {mode: 'timestamp'}).notNull().defaultNow(),
})

Then we can write a join like this:

db.select({
  id: postsTable.id,
  username: usersTable.username,
  text: postsTable.text,
  createdAt: postsTable.text
})
  .from(postsTable)
  .innerJoin(usersTable, eq(postsTable.userId, usersTable.id))
  .all()

This is what sold me.

I’ve used ORMs before where reading the documentation about how joins work requires you to read through lengthy descriptions about how relationships between tables work. With Drizzle, this syntax feels natural and is more about supporting my desire to just write SQL safely instead of making me learn something new.

Summary

Drizzle is a new, exciting Typescript ORM that really nailed their syntax. If you know SQL, you can ramp up and start writing queries incredibly quickly.

Beyond just selects and inserts, Drizzle even nails the syntax for joins and aggregations, which is hard to find in the ORM space.

Have you tried Drizzle yet? Let us know what you think!