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 toprisma
.
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 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
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, }, });
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
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
Dennis O'Keeffe
Melbourne, Australia
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