# Using Prisma For Migrations And DrizzleORM For Querying

> This post is for you if you are migrating across from a Prisma application or just want to check out the performance difference with DrizzleORM.

- Canonical: https://dennisokeeffe.com/blog/2023-11-29-prisma-migrations-drizzle-orm-querying
- Published: 2023-11-28
- Updated: 2023-11-28
- Tags: nodejs, typescript, drizzleorm, prisma, orm, postgresql

## Introduction

I love the Prisma developer experience, but some of the performance issues have left things to be desired.

I wanted to experiment a bit more with DrizzleORM and see how it compared to Prisma. I also wanted to see how easy it was to migrate from Prisma to DrizzleORM.

This post will look at the idea of using Prisma for migrations and DrizzleORM for the application after using introspection on the database. I won't speculate too much on the DX (developer experience) of doing things this way, but it may be a useful experiment that is worth sharing.

## Prerequisites

- Working knowledge of Prisma.
- Working knowledge of DrizzleORM.
- Working knowledge of NextJS (we are simply using that as a layer to run the code).

This post assumes that you have a working configuration of Postgres set up on your machine.

## Setting up the NextJS project

In the terminal, run the following:

```bash
npx create-next-app@latest --ts demo-prisma-migrate-drizzle-introspection
```

There will be a few prompts. Chose whatever you like here.

Afterwards, change into the directory and install the following dependencies:

```bash
cd demo-prisma-migrate-drizzle-introspection

# For Prisma
pnpm add prisma@latest

# We then initialize Prisma
pnpx prisma init

# For DrizzleORM
pnpm add drizzle-orm
# For Drizzle Kit (used for introspections)
pnpm add -D drizzle-kit

# Install the lib to connect with postgres
pnpm add postgres pg
```

## Configuring Prisma

We are going to update the schema for a basic blog application. We will have an `Author` and `Post` model.

Update `prisma/schema.prisma` to the following:

```prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Model for Author
model Author {
  id    String @id @default(cuid())
  name  String
  email String @unique
  posts Post[]
}

// Model for Post
model Post {
  id        String  @id @default(cuid())
  title     String
  content   String?
  published Boolean @default(false)
  authorId  String
  author    Author  @relation(fields: [authorId], references: [id])
}
```

At this stage, I am assuming you already have a Postgres database set up (as per the prerequisites).

Update your `.env` file to point to this database:

```bash
DATABASE_URL="postgresql://prisma:prisma@localhost:5432/demo_prisma_drizzle?schema=public"
```

> In my case, `demo_prisma_drizzle` is the name of the local database I have set up and I have set the username and password to `prisma`.

Run the migration using the following:

```bash
pnpx prisma migrate dev
```

You will be prompted for a name. I chose `init`.

Once this is run, your database should be in sync with your schema and set up with the `Author` and `Post` tables.

Our next step is to run the introspection on the database to generate the DrizzleORM models.

## Introspection with Drizzle Kit

We need to add the Drizzle configuration file to the root of our application. Create a file called `drizzle.config.ts` and add the following:

```typescript
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./drizzle/schema.ts",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});
```

Once the configuration is set up, we can run the introspection to generate the models.

```bash
pnpm drizzle-kit introspect:pg
```

If the output is successful, you will have a new folder `drizzle` with the models generated within `schema.ts`.

## Adding Records with Drizzle Studio

We can launch Drizzle Studio to add some records to our database.

That being said, this is a bit of a hack way to do it. NextJS compiler defaults to ES5, which is unsupported. See [this issue](https://github.com/drizzle-team/drizzle-orm/issues/803) for more details.

For now, you can either add records however you want or follow my interim solution just to get the records in.

Update `tsconfig.json` so that `"target": "es5"` is changed to `"target": "es6"`. You will need to change this after adding the records.

Now running Studio should enable it to launch correctly.

```bash
pnpm drizzle-kit studio
```

> Please note, at the time of writing, you should be using Node v18 or greater for this to work.

At this point, you should be able to add some records to the database at `https://local.drizzle.studio`.

![Drizzle Studio](https://cdn.dennisokeeffe.com/assets/2023-11-29-prisma-migrations-drizzle-orm-querying/1-drizzle-studio.webp)

For the sake of brevity, I will just add a few authors.

![Drizzle Studio > Authors](https://cdn.dennisokeeffe.com/assets/2023-11-29-prisma-migrations-drizzle-orm-querying/2-drizzle-studio-authors.webp)

At this point, we can now update the `tsconfig.json` back to `"target": "es5"`.

Once that is done, let's just into the NextJS application and query our users with Drizzle.

## Querying Authors With Drizzle

We are going to create a simple page that will query the authors and display them on the page.

The Drizzle code is straightforward and a full query is as follows:

```tsx
const result = await db.query.author.findMany({
  with: {
    posts: true,
  },
});
```

We can use this code within the page, get our users and map over them to display them on the page.

For the sake of keeping things simple, we will update `src/app/page.tsx` in order to display the authors.

```tsx
import * as schema from "../../drizzle/schema";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const queryClient = postgres(
  "postgresql://prisma:prisma@localhost:5432/demo_prisma_drizzle?search_path=public"
);
const db = drizzle(queryClient, { schema });

export default async function Home() {
  const result = await db.query.author.findMany();

  return (
    <main className="flex min-h-screen flex-col items-center justify-between p-24">
      <h1 className="text-4xl font-bold">Authors</h1>
      <div className="flex flex-col items-center justify-center space-y-4">
        {result.map((author) => (
          <div key={author.id}>
            <h2 className="text-2xl font-bold">{author.name}</h2>
          </div>
        ))}
      </div>
    </main>
  );
}
```

In production instances, do not inline everything into one page like I have done for the spike. In practice, you will likely want to abstract out the database connection and queries into a separate file and maybe take advantage of server actions.

> Something worth noting is that where Prisma uses `?schema=public`, Drizzle uses `?search_path=public`.

At this point, you should be able to run the application and see the authors displayed on the page.

## Running The Application

To run the application, run the following:

```bash
pnpm dev
```

Head to the browser and go to `http://localhost:3000` (or whatever port is provided in the console).

You should see the authors displayed on the page.

![Home page](https://cdn.dennisokeeffe.com/assets/2023-11-29-prisma-migrations-drizzle-orm-querying/3-home.webp)

## Conclusion

In this post, we have looked at how we can use Prisma for migrations and DrizzleORM for querying the database using Drizzle Kit introspection.

## References and Further Reading

1. [DrizzleORM](https://orm.drizzle.team/docs/overview)
2. [Drizzle Kit Introspection](https://orm.drizzle.team/kit-docs/overview#introspecting-with-db-pull)
3. [Prisma](https://www.prisma.io/)
4. [Blog code](https://github.com/okeeffed/demo-prisma-migrate-drizzle-introspection)

_Photo credit: [`boliviainteligente`](https://unsplash.com/@boliviainteligente)_