Shrijal Acharya

Posted on Sep 25, 2023 • Edited on Nov 16

Drizzle: ORM with SQL Superpowers🚀

#webdev #docker #opensource #programming

💡 Hey folks, if you've clicked on this article, I assume you're familiar with Prisma or have worked with SQL before.

let's discuss Drizzle⚡ - the hot new open-source alternative to Prisma!

🤷‍♂️ What is Drizzle?

Drizzle is a TypeScript ORM for SQL databases(PostgreSQL, MySQL, ...) designed with maximum type safety.

It comes with a handy built-in drizzle-kit CLI which helps in the SQL migrations if we make any changes to the scheme file.

➡️ Installation - NodeJS-PostgreSQL:

pnpm add drizzle-orm pg
pnpm add -D drizzle-kit @types/pg

How do you choose between Prisma and Drizzle?

👉 If you are familiar with SQL queries go with Drizzle, else Prisma is a bit intuitive for beginners. It's this simple! 😉

🧑‍💻 Schema and Migrations

I will demonstrate how the Schema and Migrations work in Drizzle by building a NodeJS + PostgreSQL (with docker) project.

1. Initialize a NextJS project

pnpx create-next-app@latest

2. Now, let's initialize the docker-compose.yaml file in the root of the project with the following contents

We simply host a PostgreSQL service on port 5432 (default) and map its original volume to ./data/db (for data persistance) in our current folder, with the admin interface on port 8080.

NOTE: We are also using environment variables to store credentials for the database.

3. Spin up the docker container

Go to the root of your project and run this command. It's better to have it up and running since we will be performing migrations and all.

docker compose -f docker-compose.yaml up

💡 If you are on Windows, make sure to have your docker daemon running before performing this step.

4. Now, we set up the drizzle config in the drizzle.config.ts file in the root folder of the project. Make sure to declare an env named DATABASE_URL with the connection string in .env.local.

import type { Config } from "drizzle-kit";
import * as dotenv from "dotenv";

dotenv.config({ path: ".env.local" });

if (!process.env.DATABASE_URL) throw new Error("DATABASE_URL is not set");

export default {
  schema: "./src/lib/db/*", // any schema file inside the /db folder.
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL,
  },
} satisfies Config;

5. Create table schema. I am taking the schema file from my previous project.

Declare a schema.ts in /src/lib/db/schema.ts as shown in the drizzle config file.

project-name
└── src/
    ├── app/
    │   └── ...
    ├── lib/
    │   └── db/
    │       ├── schema.ts
    │       └── ...
    └── ...

import {
  integer,
  pgEnum,
  pgTable,
  serial,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";

// role can be either "user" or "bot"
export const userBotEnum = pgEnum("user_bot_enum", ["user", "bot"]);

export const chats = pgTable("chats", {
  id: serial("id").primaryKey(),
  docName: text("doc_name").notNull(),
  docUrl: text("doc_url").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  userId: varchar("user_id", { length: 255 }).notNull(),
  // This is specific to the AWS S3
  fileKey: text("file_key").notNull(),
});

// export the type declaration of chats.
export type ChatType = typeof chats.$inferSelect;

export const messages = pgTable("messages", {
  id: serial("id").primaryKey(),
  chatId: integer("chat_id")
    .references(() => chats.id)
    .notNull(),
  content: text("content").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  role: userBotEnum("role").notNull(),
});

The schema file should be pretty intuitive. We are simply creating a schema for two tables chats and messages. The only thing new is the references() function.

Basically, we are creating a relation between the chatId in the messages and chats.id in the chats which helps us get the chatId specific to a chat in the messages table.

6. After writing the schema file. we also need a way to push the migrations to the database itself.

Create a migrate.ts in /src/lib/db.

import { Pool } from "pg";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { drizzle } from "drizzle-orm/node-postgres";

import * as dotenv from "dotenv";

dotenv.config({ path: ".env.local" });

if (!process.env.DATABASE_URL)
  throw new Error("Cannot migrate. DATABASE_URL is not set");

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
export const db = drizzle(pool);

async function main() {
  console.log("Running your migrations...");
  await migrate(db, { migrationsFolder: "drizzle" });
  console.log("Woohoo! Migrations completed!");
  return;
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});

We are connecting to the Postgres server with the pg driver using the Pool connection. Now the migration script is ready.

We are now finally ready to push our schemas to the database.
Add the following in the scripts section in the package.json

    "generate": "drizzle-kit generate:pg",
    "push": "node -r esbuild-register src/lib/db/migrate.ts",
    "studio": "drizzle-kit studio --host 127.0.0.1"

generate - This is the command responsible for generating the migration files. Upon running this command you should see a new drizzle folder created on the topmost level of the project with the .sql files. generate:pg is using the Postgres (pg) driver to generate the migrations.

push - This is the command responsible for pushing the migration files to the actual docker PostgreSQL database hosted on our local machine. We are actually executing the migrate.ts file that we wrote earlier.

studio - This is not so necessary command, but this is something you can use as an alternative to the adminer interface. Upon running this script, you get a web interface to interact with the database in the port 4983 - localhost:4983

Finally, run these commands sequentially. And now you should have your drizzle studio running as well. Open localhost:4983 or localhost:8080 for adminer interface.

See, it is this simple. Now, you can prepare a schema file, generate its migrations, and push the changes to the database.🫡

⚖️ Comparison with other ORMs

Drizzle is designed to be both simple and customizable while still staying fast.

When comparing Drizzle to other ORMs, such as Prisma, it becomes clear that there are differences in their approach. Drizzle focuses on providing a flexible query builder, resembling SQL usage, and excels in serverless🚀 environments as it's built on top of TypeScript.

On the other hand, TypeORM, a popular ORM, emphasizes type annotations and schema management. Drizzle, while still lightweight, offers a compelling alternative for query building and database access.

😕 Is Drizzle suitable for your production app?

Drizzle is a fairly new ORM, so there may still be some work to be done. However, it is already compatible with most of the SQL databases out there.

If you are familiar with relational databases, then Drizzle can be a go-to choice for any production-level application. 💯

🚀 Final Thoughts!

✨ As they say — When it comes to Drizzle - we're a thin TypeScript layer on top of SQL. The ORM's main philosophy is "If you know SQL, you know Drizzle ORM"

They aren't trying to be the next Prisma. It's more of a query builder than a full-fledged ORM, like Kysely or Knex.

It is performance-centric, has fully-fledged joints, and lets developers fall in 😍 with SQL again!

Lately, they have gained a lot of popularity over their repository.📈