Skip to main content

Migrate from Drizzle

This guide describes how to migrate from Drizzle to Prisma ORM. It uses a sample project based off of the Drizzle Next.js example as a sample project to demonstrate the migration steps. You can find the example used for this guide on GitHub.

note

this migration guide uses Neon PostgreSQL as the example database, but it equally applies to any other relational database that are supported by Prisma ORM.

You can learn how Prisma ORM compares to Drizzle on the Prisma ORM vs Drizzle page.

Overview of the migration process​

Note that the steps for migrating from Drizzle to Prisma ORM are always the same, no matter what kind of application or API layer you're building:

  1. Install the Prisma CLI
  2. Introspect your database
  3. Create a baseline migration
  4. Install Prisma Client
  5. Gradually replace your Drizzle queries with Prisma Client

These steps apply, no matter if you're building a REST API (e.g. with Express, koa or NestJS), a GraphQL API (e.g. with Apollo Server, TypeGraphQL or Nexus) or any other kind of application that uses Drizzle for database access.

Prisma ORM lends itself really well for incremental adoption. This means, you don't have migrate your entire project from Drizzle to Prisma ORM at once, but rather you can step-by-step move your database queries from Drizzle to Prisma ORM.

Step 1. Install the Prisma CLI​

The first step to adopt Prisma ORM is to install the Prisma CLI in your project:

npm install prisma --save-dev

Step 2. Introspect your database​

2.1. Set up Prisma ORM​

Before you can introspect your database, you need to set up your Prisma schema and connect Prisma to your database. Run the following command in the root of your project to create a basic Prisma schema file:

npx prisma init

This command created a new directory called prisma with the following files for you:

  • schema.prisma: Your Prisma schema that specifies your database connection and models
  • .env: A dotenv to configure your database connection URL as an environment variable
note

you may already have a .env file. If so, the prisma init command will append lines to it rather than creating a new file.

The Prisma schema currently looks as follows:

prisma/schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}
tip

If you're using VS Code, be sure to install the Prisma VS Code extension for syntax highlighting, formatting, auto-completion and a lot more cool features.

2.2. Connect your database​

If you're not using PostgreSQL, you need to adjust the provider field on the datasource block to the database you currently use:

schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Once that's done, you can configure your database connection URL in the .env file. Drizzle and Prisma ORM use the same format for connection URLs, so your existing connection URL should work fine.

2.3. Introspect your database using Prisma ORM​

With your connection URL in place, you can introspect your database to generate your Prisma models:

npx prisma db pull

If you're using the sample project the following model would be created:

prisma/schema.prisma
model todo {
  id   Int     @id
  text String
  done Boolean @default(false)
}

The generated Prisma model represents a database table. Prisma models are the foundation for your programmatic Prisma Client API which allows you to send queries to your database.

2.4. Create a baseline migration​

To continue using Prisma Migrate to evolve your database schema, you will need to baseline your database.

First, create a migrations directory and add a directory inside with your preferred name for the migration. In this example, we will use 0_init as the migration name:

mkdir -p prisma/migrations/0_init

Next, generate the migration file with prisma migrate diff. Use the following arguments:

  • --from-empty: assumes the data model you're migrating from is empty
  • --to-schema-datamodel: the current database state using the URL in the datasource block
  • --script: output a SQL script
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql

Review the generated migration to ensure everything is correct.

Next, mark the migration as applied using prisma migrate resolve with the --applied argument.

npx prisma migrate resolve --applied 0_init

The command will mark 0_init as applied by adding it to the _prisma_migrations table.

You now have a baseline for your current database schema. To make further changes to your database schema, you can update your Prisma schema and use prisma migrate dev to apply the changes to your database.

2.5. Adjust the Prisma schema (optional)​

Models that are generated via introspection currently exactly map to your database tables. In this section, you'll learn how you can adjust the naming of the Prisma models to adhere to Prisma ORM's naming conventions.

All of these adjustment are entirely optional and you are free to skip to the next step already if you don't want to adjust anything for now. You can go back and make the adjustments at any later point.

As opposed to the current camelCase notation of Drizzle models, Prisma ORM's naming conventions are:

  • PascalCase for model names
  • camelCase for field names

You can adjust the naming by mapping the Prisma model and field names to the existing table and column names in the underlying database using @@map and @map.

Here's an example on how you could modify the model above:

prisma/schema.prisma
model Todo {
  id   Int     @id
  text String
  done Boolean @default(false)

  @@map("todo")
}

Step 3. Install and generate Prisma Client​

As a next step, you can install Prisma Client in your project so that you can start replacing the database queries in your project that are currently made with Drizzle:

npm install @prisma/client

After installing, you need to run generate in order to have your schema reflected in TypeScript types and autocomplete.

npx prisma generate

Step 4. Replace your Drizzle queries with Prisma Client​

In this section, we'll show a few sample queries that are being migrated from Drizzle to Prisma Client based on the example routes from the sample REST API project. For a comprehensive overview of how the Prisma Client API differs from Drizzle, check out the comparison page.

First, to set up the PrismaClient instance that you'll use to send database queries from the various route handlers. Create a new file named prisma.ts in the db directory:

touch db/prisma.ts

Now, instantiate PrismaClient and export it from the file so you can use it in your route handlers later:

db/prisma.ts
import { PrismaClient } from '@prisma/client'

export const prisma = new PrismaClient()

4.1. Replacing getData queries​

The fullstack Next.js app has several actions including getData.

The getData action is currently implemented as follows:

actions/todoActions.ts
import db from "@/db/drizzle";
import { todo } from "@/db/schema";

export const getData = async () => {
  const data = await db.select().from(todo);
  return data;
};

Here is the same action implemented using Prisma Client:

src/controllers/FeedAction.ts
import { prisma } from "@/db/prisma";

export const getData = async () => {
  const data = await prisma.todo.findMany();
  return data;
};

4.2. Replacing queries in POST requests​

The sample project has four actions that are utilized during POST requests:

  • addTodo: Creates a new Todo record
  • deleteTodo: Deletes an existing Todo record
  • toggleTodo: Toggles the boolean done field on an existing Todo record
  • editTodo: Edits the text field on an existing Todo record

addTodo​

The addTodo action is currently implemented as follows:

actions/todoActions.ts
import { revalidatePath } from "next/cache";

import db from "@/db/drizzle";
import { todo } from "@/db/schema";

export const addTodo = async (id: number, text: string) => {
  await db.insert(todo).values({
    id: id,
    text: text,
  });
  revalidatePath("/");
};

Here is the same action implemented using Prisma Client:

actions/todoActions.ts
import { revalidatePath } from "next/cache";

import { prisma } from "@/db/prisma";

export const addTodo = async (id: number, text: string) => {
  await prisma.todo.create({
    data: { id, text },
  })
  revalidatePath("/");
};

deleteTodo​

The deleteTodo action is currently implemented as follows:

actions/todoActions.ts
import { eq } from "drizzle-orm";
import { revalidatePath } from "next/cache";

import db from "@/db/drizzle";
import { todo } from "@/db/schema";

export const deleteTodo = async (id: number) => {
  await db.delete(todo).where(eq(todo.id, id));
  revalidatePath("/");
};

Here is the same action implemented using Prisma Client:

actions/todoActions.ts
import { revalidatePath } from "next/cache";

import { prisma } from "@/db/prisma";

export const deleteTodo = async (id: number) => {
  await prisma.todo.delete({ where: { id } });
  revalidatePath("/");
};

toggleTodo​

The ToggleTodo action is currently implemented as follows:

actions/todoActions.ts
import { eq, not } from "drizzle-orm";
import { revalidatePath } from "next/cache";

import db from "@/db/drizzle";
import { todo } from "@/db/schema";

export const toggleTodo = async (id: number) => {
  await db
    .update(todo)
    .set({
      done: not(todo.done),
    })
    .where(eq(todo.id, id));
  revalidatePath("/");
};

Here is the same action implemented using Prisma Client:

actions/todoActions.ts
import { revalidatePath } from "next/cache";

import { prisma } from "@/db/prisma";

export const toggleTodo = async (id: number) => {
  const todo = await prisma.todo.findUnique({ where: { id } });
  if (todo) {
    await prisma.todo.update({
      where: { id: todo.id },
      data: { done: !todo.done },
    })
    revalidatePath("/");
  }
};

Note that Prisma ORM does not have the ability to edit a boolean field "in place", so the record must be fetched before hand.

editTodo​

The editTodo action is currently implemented as follows:

actions/todoActions.ts
import { eq } from "drizzle-orm";
import { revalidatePath } from "next/cache";

import db from "@/db/drizzle";
import { todo } from "@/db/schema";

export const editTodo = async (id: number, text: string) => {
  await db
    .update(todo)
    .set({
      text: text,
    })
    .where(eq(todo.id, id));

  revalidatePath("/");
};

Here is the same action implemented using Prisma Client:

actions/todoActions.ts
import { revalidatePath } from "next/cache";

import { prisma } from "@/db/prisma";

export const editTodo = async (id: number, text: string) => {
  await prisma.todo.update({
    where: { id },
    data: { text },
  })
  revalidatePath("/");
};

More​

Implicit many-to-many relations​

Unlike Drizzle, Prisma ORM allows you to model many-to-many relations implicitly. That is, a many-to-many relation where you do not have to manage the relation table (also sometimes called JOIN table) explicitly in your schema. Here is an example comparing Drizzle with Prisma ORM:

schema.ts
import { boolean, integer, pgTable, serial, text } from "drizzle-orm/pg-core";

export const posts = pgTable('post', {
  id: serial('serial').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false).notNull(),
});

export const categories = pgTable('category', {
  id: serial('serial').primaryKey(),
  name: text('name').notNull(),
});

export const postsToCategories = pgTable('posts_to_categories', {
  postId: integer('post_id').notNull().references(() => users.id),
  categoryId: integer('category_id').notNull().references(() => chatGroups.id),
});

This schema is equivalent to the following Prisma schema:

schema.prisma
model Post {
  id                Int                @id @default(autoincrement())
  title             String
  content           String?
  published         Boolean            @default(false)
  postsToCategories PostToCategories[]

  @@map("post")
}

model Category {
  id                Int                @id @default(autoincrement())
  name              String
  postsToCategories PostToCategories[]

  @@map("category")
}

model PostToCategories {
  postId     Int
  categoryId Int
  category   Category @relation(fields: [categoryId], references: [id])
  post       Post     @relation(fields: [postId], references: [id])

  @@id([postId, categoryId])
  @@index([postId])
  @@index([categoryId])
  @@map("posts_to_categories")
}

In this Prisma schema, the many-to-many relation is modeled explicitly via the relation table PostToCategories.

By instead adhering to the conventions for Prisma ORM relation tables, the relation could look as follows:

schema.prisma
model Post {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  published  Boolean    @default(false)
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

This would also result in a more ergonomic and less verbose Prisma Client API to modify the records in this relation, because you have a direct path from Post to Category (and the other way around) instead of needing to traverse the PostToCategories model first.

warning

If your database provider requires tables to have primary keys then you have to use explicit syntax, and manually create the join model with a primary key. This is because relation tables (JOIN tables) created by Prisma ORM (expressed via @relation) for many-to-many relations using implicit syntax do not have primary keys.