Drizzle: ORM with SQL Superpowers🚀
Tags: webdev, docker, opensource, programming
Published: 2023-09-25 13:35:38

> 💡 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_:

```bash
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

```bash
pnpx create-next-app@latest
```

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

![Docker Compose Yaml File](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7yb8yu6yes6sel28jjfz.png)

We simply host a PostgreSQL service on port **5432** (default) and map its original volume to <u>**./data/db**</u> (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.

```bash
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 <u>drizzle.config.ts</u> file in the root folder of the project. Make sure to declare an env named `DATABASE_URL` with the connection string in <u>.env.local</u>.

```typescript
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 **<u>/src/lib/db/schema.ts</u>** as shown in the drizzle config file.

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

```typescript
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 <u>migrate.ts</u> in <u>**/src/lib/db**</u>.

```typescript
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 <u>package.json</u>

```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.

<br>

- **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 <u>migrate.ts</u> file that we wrote earlier.

<br>

- **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_ - <u>localhost:4983</u>

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

![Demo GIF](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uyl7msvg2ojs7vuqiqkn.gif)

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**"

<mark>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**.</mark>

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.📈

![Star Increase Chart in Drizzle Repository](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w05zg1mp5heh2tcdl223.png)

So, that is it for this article. I hope this is useful for every one of you. 🤗

See you next time✌️

<img width="100%" style="width:100%" src="https://media.giphy.com/media/kGCuRgmbnO9EI/giphy.gif">

> Drop your thoughts in the comments section below. 👇

***

Feel free to follow me to get notified when new articles are out 😉

{% embed https://dev.to/shricodev %}

Let's stay connected.

- GitHub: https://github.com/shricodev
- LinkedIn: https://linkedin.com/in/shrijal007
- X (Twitter): https://twitter.com/shricodevv