Back to home

Using Prisma For Migrations And DrizzleORM For Querying

Published: Nov 28, 2023

Last updated: Nov 28, 2023

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:

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:

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:

// 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:

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:

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:

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.

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

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

Drizzle Studio

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

Drizzle Studio > 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, }, });

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

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:

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

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

  1. DrizzleORM
  2. Drizzle Kit Introspection
  3. Prisma
  4. Blog code

Photo credit: boliviainteligente

Personal image

Dennis O'Keeffe

@dennisokeeffe92
  • Melbourne, Australia

Hi, I am a professional Software Engineer. Formerly of Culture Amp, UsabilityHub, Present Company and NightGuru.
I am currently working on Visibuild.

1,200+ PEOPLE ALREADY JOINED ❤️️

Get fresh posts + news direct to your inbox.

No spam. We only send you relevant content.

Using Prisma For Migrations And DrizzleORM For Querying

Introduction

Share this post