How To Use Prisma For Data Modeling And Drizzle For Queries.
June 28th, 2024
June 28th, 2024
The modern web development era has given birth to some of the greatest technologies. But with this great power comes the great responsibility of choosing the right technology for the right job. This can be daunting, especially for new developers. Luckily, when it comes to full-stack development with Next.js, it’s fairly simple to choose between an ORM. The most common picks are Prisma and Drizzle, mainly due to them being open-source and type-safe.
These ORMs are often used separately as they have their own strengths and weaknesses. Prisma is known for its phenomenal developer experience while Drizzle shines when it comes to speed. Using both these ORMs in the same project may not be the best idea, but it is possible. It will allow you to easily design schemas and migrate them while having a fast backend. If you already have an API built out with Prisma, here are some cases when you might want to go with this dual ORM strategy:
If you find anything confusing while following this guide, feel free to use this repository as a reference. I am planning to create a proper standalone starter temple for a Prisma and Drizzle dual ORM Next.js app very soon.
Before we set up a PostgreSQL instance on Neon, just know that both Prisma and Drizzle do not support all database engines. Drizzle supports every PostgreSQL, MySQL and SQLite database while Prisma supports those along with MongoDB and CockroachDB.
Head over to Neon, create an account if you do not have one already, and then create a project. You will be able to create multiple databases within a single project. The free tier will bless you with a single project with 5GB storage across all databases.
Navigate to your dashboard and create a database by clicking on the database dropdown. You will be required to give it a name. Alternatively, you can choose to use an existing database.
Click on the dropdown under the database dropdown and choose the connection string. Make sure you have the correct database selected.
Now copy the connection string and go back to your project. Create a .env
file if you don’t have one and paste your connection string. It should look something like this:
DATABASE_URL="postgresql://username:password@hostname:port/database"
In our dual ORM strategy, Prisma will be used to make the database schema and will be responsible for pushing the schema to the database. It will act as the creator and sender.
Assuming you already have a Next.js app with typescript ready to go, we are going to install Prisma.
npm install prisma --save-dev
The following command will create a prisma
directory that contains a schema.prisma
file. It will hold our database connection and data models.
npx prisma init
Now we are going to connect Prisma to our database. Add the following to the top of your file:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
To migrate our database schema, we will need to install the Prisma client.
npm install @prisma/client
Just to have something to test against, let’s make 2 simple models with a relationship. The first will be a user that can create many quotes. The second will be a quote that can only have one owner.
model User {
id String @id @default(uuid())
name String
email String
quotes Quote[]
createAt DateTime @default(now())
}
model Quote {
id String @id @default(uuid())
content String
userId String
user User @relation(fields: [userId], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @default(now())
@@index([userId])
}
Drizzle will be used to pull the schema from the database and make the actual queries from out backend. It will act as a reliever and consumer.
First, we are going to install the Drizzle ORM itself and the Neon serverless adapter for it.
npm install drizzle-orm @neondatabase/serverless
Drizzle kit is a CLI that will help us generate the schema and relations from our database. It also lets us view our data locally using Drizzle Studio.
npm install drizzle-kit --save-dev
Make a drizzle
directory at the root of your project. Inside that, make an index.ts
file and paste the following code into it:
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);
We will also have to set up a drizzle.config.ts
file within the drizzle
directory. This just tells Drizzle where the schema is and where to generate the migrations.
import { defineConfig } from "drizzle-kit";
export default defineConfig({
dialect: "postgresql",
schema: "./drizzle/schema.ts",
out: "./drizzle",
dbCredentials: {
url: process.env.DATABASE_URL!
}
});
Checkpoint - your project structure should look something like this right now:
Quotes-App
├── app
├── drizzle
│ └── index.ts
└── prisma
└── schema.prisma
To migrate our schema to the Neon database, we run the push command that comes with the Prisma client:
npx prisma db push
Head over to your Neon dashboard and go to the tables view. You can see the schema you just pushed in there.
Now that our schema is out of Prisma, we can use Drizzle kits pull command to automatically generate the data models and relations in Drizzle.
npx drizzle-kit introspect
After Drizzle is done pulling the schema, it will generate 2 new files in the drizzle
directory: a schema.ts
file and a relations.ts
file. Your new structure should look like this:
Quotes-App
├── app
├── drizzle
│ ├── index.ts
│ ├── schema.ts
│ └── relations.ts
└── prisma
└── schema.prisma
Let’s quickly edit our drizzle/index.ts
to tell Drizzle where our new schemas and relations are:
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
// import new data models and relations
import * as relations from "@/drizzle/relations";
import * as models from "@/drizzle/schema";
const sql = neon(process.env.DATABASE_URL!);
// tell drizzle to use them
export const db = drizzle(sql, { schema: { ...models, ...relations } });
You are now all set to import the database anywhere and start using it.
import { db } from "@/drizzle";
Running the Prisma pull
and Drizzle introspect
command every time you update your schema will get annoying. To make our lives a bit easier, let’s create a custom NPM script to do both of these for us. Add this to your package.json
:
"scripts": {
"db:new": "npx prisma db push && npx drizzle-kit introspect"
}
Now all you have to do is run the following command:
npm run db:new
One of the things that makes Prisma shine is prisma generate
. This command automatically generates types from your schema and lets you use them in your code.
import { User, Quote } from "@prisma/client";
Drizzle on the other hand has no such command. Instead, we will have to infer the types from the data models ourselves. Make a types
directory with an index.ts
file in it. In our case, we will need to make types for our User
and Quote
model.
import { User, Quote } from "@/drizzle/schema";
export type User = typeof User.$inferSelect;
export type Quote = typeof Quote.$inferSelect;
export type QuoteUser = Quote & { User: User };
And with that, we have successfully set up a Prisma ORM and Drizzle ORM in the same project.
Using both Prisma and Drizzle in the same project may not be for everyone, but it can be done. Setting up this dual ORM strategy allows us to enjoy the convenient Prisma schema language while having a performant backend. I wanted to implement this in a project of mine, but I already had ~35 API endpoints and ~15 server actions built out using Prisma. Converting these queries to Drizzle seemed like a nightmare, so I stuck with a full Prisma setup. That being said, I hope this guide helps you out.