Intro

Drizzle ORM is a TypeScript ORM. Prisma is also type safe and it is very easy to use. Prisma has extensive documentation that is beautifully designed and clearly laid out. So why, then, would we consider Drizzle?

One of my favourite features of Drizzle and the thing that captured my interest was that it very SQL-like. Their motto is 'If you know SQL, you know Drizzle". Now, I know SQL, with a focus on PostgreSQL, but I don't get to use it too often, mainly choosing Prisma for my projects. With Drizzle though, I can get back close to SQL but still have the ease of use an ORM.

Setup

In this post, we're not going to do any project or build anything in particular. This is solely focused on setting it up and using the Drizzle API. That said, we will have to have a database setup and I will be using Supabase. It's simple and easy to set up, so I won't cover that here.

We will need the following packages.

npm i drizzle-kit drizzle-orm postgres tsx dotenv

Create your Supabase database and make sure to put the connecting string as a DATABASE_URl in your file .env file.

Now we will need create a config file called drizzle.config.ts in the route of our project.

drizzle.config.ts

import type { Config } from "drizzle-kit";
import "dotenv/config";

export default {
  schema: "./src/lib/drizzle/schema/*",
  out: "./src/lib/drizzle/migrations",
  driver: "pg",
  verbose: true,
  dbCredentials: {
    connectionString: process.env.DATABASE_URL as string,
  },
} satisfies Config;

Schema: We will have a multiple schema files, so we add an asterisk at the end of our schema string.

Out: The out line tells Drizzle where we want our migrations files to go.

Driver: Options 'pg' | 'mysql2' | 'better-sqlite' | 'libsql' | 'turso' | 'd1'. We are using PostgreSQL, so we use 'pg'.

Verbose: For longer logs on push commands.

Schema

Let's create a very simple schema for our users.

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

Drizzle vs Prisma Schemas and Models

Prisma models are directly comparable to Drizzle Schemas. One advantage of Drizzle is we are writing these in typescript, and it is just a regular JavaScript function. However, I enjoy the structure of Prisma models much better than this. Let's take a look at a basic user setup for next auth to compare.

Prisma model for a basic next auth user.

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
}

model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?  @db.Text
  access_token       String?  @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?  @db.Text
  session_state      String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

Drizzle model for a basic next auth user.

export const users = pgTable("user", {
 id: text("id").notNull().primaryKey(),
 name: text("name"),
 email: text("email").notNull(),
 emailVerified: timestamp("emailVerified", { mode: "date" }),
 image: text("image"),
})

export const accounts = pgTable(
"account",
{
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  type: text("type").$type<AdapterAccount["type"]>().notNull(),
  provider: text("provider").notNull(),
  providerAccountId: text("providerAccountId").notNull(),
  refresh_token: text("refresh_token"),
  access_token: text("access_token"),
  expires_at: integer("expires_at"),
  token_type: text("token_type"),
  scope: text("scope"),
   id_token: text("id_token"),
  session_state: text("session_state"),
},
(account) => ({
  compoundKey: primaryKey({ columns: [account.provider, account.providerAccountId] }),
})
)

I find the Drizzle schema more difficult to read than the Prisma model, just mainly due to it being easier on the eyes. That said, it's not so unpleasant as to put me off using it altogether. There's a lot of good stuff to come, so hopefully it hasn't put you off either.

Generating Migrate Files

Now we need to generate the migration files. To do this we will configure a script in our package json file.

"generate": "drizzle-kit generate:pg"

npm run dev

Migrating Changes

We will configure another script in our package json file.

"migrate": "tsx -r dotenv/config src/lib/drizzle/migrate.ts",

Breaking the script down, we are using the package tsx to run our Typescript file, telling it to require the dotenv/config package and run the file migrate.ts. Now we will need to go and create that file before running it, naturally.

migrate.ts

import { db, connection } from "./index";
import { migrate } from "drizzle-orm/postgres-js/migrator";

const runDrizzleMigrate = async () => {
  try {
    await migrate(db, { migrationsFolder: "src/lib/drizzle/migrations" });
    console.log("Migrations completed successfully");
  } catch (error) {
    console.log(error);  
  } finally {
    await connection.end();
  }
};
runDrizzleMigrate();

Here, we are using the provided migrate function from Drizzle. We drop our connection otherwise the script will continue to hang.

npm run migrate

Inserting a User

// Drizzle
const user = await db.insert(users).values({ name: "John" }).returning();

// Prisma
const user = await db.user.create({data: {name: 'John'}})

Updating a User

// Drizzle
await db.update(users)
  .set({ name: 'Jane' })
  .where(eq(users.name, 'John'));

// Prisma 
await db.user.update({
  where: {
    name: 'John,
  },
  data: {
    name: 'Jane',
  },
});

Deleting a User

// Drizzle
await db.delete(users).where(eq(users.name, 'Dan'));

// Prisma
await db.user.delete({where: {name: 'John'}})

Reading a user

Drizzle offers to ways to query users as mentioned earlier. They have relational (prisma-like) and SQL like.

// Drizzle SQL-like
const users = await db.select().from(users); 

// Drizzle relational
const users = await db.query.users.findMany()

// Prisma 
const users = await db.user.findMany()

What about Relations and more complex operations?

Above are the simple CRUD operations with no filtering, sorting, limiting, or relations. This will be covered in the next post, so look out for part 2.

Summary

Drizzle offers a Typescript ORM, so there is no new syntax to even learn when interacting with your database. It does however, offer the opportunity to remove a little of the disguise that ORMs provide whilst still retaining the ability to use an easy to understand and more logical API with its relational querying.