This guide provides a straightforward approach to migrating a basic Prisma project to Drizzle ORM. Although the example focuses on PostgreSQL, the process is similar for other supported databases.
Overview of the migration process
Regardless of your application type or API layer, the steps to transition from Prisma to Drizzle ORM remain consistent:
Install Drizzle ORM & Drizzle Kit
Setup Drizzle config file
Introspect your database
Connect Drizzle ORM to your database
Transition your Prisma queries to Drizzle ORM queries
These steps are applicable whether you’re developing a REST API (for example, with Express, Koa, or NestJS) or any other type of application that utilizes Prisma for database interactions.
Overview of the Prisma project
For this guide, we’ll use a REST API built with Express as a sample project to migrate to Drizzle ORM. It has four entities:
The models have the following relations:
one-to-many between Supplier and Product
many-to-many between Order and Product
For many-to-many relation we will create a join table order_details, so Order and Product entities will have one-to-many relations with OrderDetail entity.
The corresponding tables have been created using a generated Prisma migration.
This guide uses the following file structure:
Install Drizzle ORM & Drizzle Kit
The first step is to install Drizzle ORM and pg package which we will use as a driver. The second step is to install Drizzle Kit and types for pg. Drizzle Kit - CLI companion for automatic SQL migrations generation and rapid prototyping.
npm
yarn
pnpm
bun
Setup Drizzle config file
Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.
Create a drizzle.config.ts file in the root of your project and add the following content:
Introspect your database
Drizzle Kit provides a CLI command to introspect your database and generate a schema file. The schema file contains all the information about your database tables, columns, relations, and indices.
This command will generate a schema.ts file, along with snapshots and migrations in the src/drizzle folder.
Also, if you want to use relational queries, you have to update your schema file with relational tables:
Now we have the following file structure:
Connect Drizzle ORM to your database
Create a db.ts file in the src/drizzle folder and set up your database configuration:
Transition your Prisma queries to Drizzle ORM queries
In this section, we will show you how to replace several queries from Prisma with Drizzle ORM.
Replace insert queries
We will show how to insert new rows into suppliers and products tables.
POST /suppliers
With Drizzle ORM, the query is implemented as follows:
POST /products
With Drizzle ORM, the query is implemented as follows:
Be careful with the unitPrice field. In Prisma it’s a number type, but in Drizzle ORM it’s a string type, which can handle more than 16383 digits after the decimal point, unlike the number type.
Replace select queries
In this section we will show how to select one row, multiple rows, count rows, filter rows, join tables and paginate results.
GET /products/:id
In Drizzle ORM, the query is implemented as follows:
Response will be type-safe with both ORMs.
GET /products
In Drizzle ORM, the query is implemented as follows:
Response will be type-safe with both ORMs.
GET /orders/:id
In Prisma, aggregate functions require using the aggregate method. For complex queries, the $queryRaw method is used, which is not type-safe.
We want to select id, orderDate and shipCountry fields from orders table and by using aggregation functions sum totalPrice of order, totalQuantity of products in the order and count totalProducts in the order.
In Drizzle ORM, the query is implemented as follows:
In Drizzle ORM, the result will be type-safe with aggregations too.
Note: as of now aggregations are not supported in relational queries, so you have to use core queries.
Replace update queries
In this section, we will show you how to update multiple rows.
PATCH /suppliers/:id
In Drizzle ORM, the query is implemented as follows:
Replace delete queries
In this section, we will show you how to delete a single row and multiple rows using transactions.
DELETE /orders/:id
In Drizzle ORM, the query is implemented as follows: