Using Prisma For Migrations And DrizzleORM For Querying

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

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:

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

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

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

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
copy

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:

// 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])
}
copy

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:

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

Run the migration using the following:

pnpx prisma migrate dev
copy

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:

import { defineConfig } from "drizzle-kit";

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

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

pnpm drizzle-kit introspect:pg
copy

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

pnpm drizzle-kit studio
copy

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

Drizzle Studio

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

Drizzle Studio > Authors

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:

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

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.

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>
  );
}
copy

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.

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:

pnpm dev
copy

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

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

Photo credit: boliviainteligente