Prisma to Drizzle migration complete! 🎉 Our journey and lessons learned

[oureboros]

Hi!

We recently migrated from Prisma to Drizzle and wanted to provide a quick write-up on our experience in case it helps anyone else. Your GitHub issues were super useful, which I reference below for other people coming across similar questions/problems.

Below was the strategy:

• Use Drizzle Kit pull to generate schema. This method worked pretty well. There was only one @todo that required action. Most of the changes to the schema afterward were to account for the Prisma magic that went away. The biggest effort was having to manually create all of the "relations" by hand to use the "include" relations: https://orm.drizzle.team/docs/rqb#include-relations
• Started with a single acceptance test and chipped away one issue at a time.

Here are the problems as I encountered them and the solutions:

1. I couldn't find any official guidance around testing. I looked at your integration tests and didn't want to bring Docker into the test suite. I ended up using the pattern of schema creation before the test suite and then truncating each table after each test. This approach works but requires running the tests one at a time.
   • Solution: Run push before launching tests (e.g., yarn drizzle:push && yarn jest), and then in the teardown, iterating over the tables and truncating (refactored from Prisma):

async function resetDatabase() {
    const tablenames = await db.execute(sql`SELECT tablename FROM pg_tables WHERE schemaname='public'`)
    const tables = tablenames
        .map(({ tablename }) => tablename)
        .filter((name) => name !== '__drizzle_migrations')
        .map((name) => `"public"."${name}"`)
        .join(', ')
    try {
        await db.execute(sql`TRUNCATE TABLE ${sql.raw(tables)} CASCADE;`)
    } catch (error) {
        console.log(error)
    }
}

2. Generate random UUID - [BUG]:set uuid to primarykey but can't set defaultRandom() #1238
   • Solution: Moved to domain
3. The date type is a string - [BUG]: Wrong data type for postgres date colum #1407
   • Solution: Refactored to use string
4. On update (e.g., updatedAt) - [FEATURE]: Prisma's updatedAt behavior. onUpdate API suggestion #956
   • Solution: Moved to domain
5. Schema migration, JSONB array: [BUG]: Incorrect type inference with multidimensional array field PostgreSQL #1028
   • Solution: Went with @dankochetov's suggestion in the issue
6. Prisma can be extended to add computed properties
   • Solution: I never liked the coupling, so I refactored moving to the application.
7. In Prisma, you can use include on create and update. I generically relied on this, allowing for side-loading resources on creation.
   • Solution: Temporarily handling on a case-by-case basis.
8. Tests are not gracefully exiting, and I realized it's the connection. I had middleware for cleaning up the connection with Prisma. This issue led me down the path of determining who I should use to close the connection: Drizzle or Postgres. It's taking some getting used to thinking of the ORM and driver as separate concerns. - Test case not exiting when connecting to my db using drizzle #1390
   • Solution: Added middleware for closing the connection
9. In Prisma, a reference to a query can be passed to the transaction as below.

const [value1, value2] = await db.$transaction([
  query1, query2
])

• Solution: Moved queries into transaction function. I'm sure there's a way around this, but I will attack it another day.

10. Prisma returns the count of row modified when using db.$executeRaw

• Solution: Update queries to use RETURNING table.id and look at result length

11. Upsert on composite keys.

• Solution: Not in docs - [FEATURE]: Support onConflict with composite primary keys #1029

12. After the migration, all tests passed in node. When running in unstable_dev, about a quarter of the tests failed with the following error: "Cannot perform I/O on behalf of a different request. I/O objects (such as streams, request/response bodies, and others) created in the context of one request handler cannot be accessed from a different request's handler. This is a limitation of Cloudflare Workers which allows us to improve overall performance."

• Solution: All failed tests shared that they were making more than one request per test. It turns out that initializing the database using the default example in the Drizzle docs will lead to this in Cloudflare since requests share the same client instance. I refactored to move the creation of the client and Drizzle instance per request. Thankful for the Cloudflare/Drizzle/Neon example; after the first review, "everything clicked." Otherwise, this Cloudflare error was vague, didn't have a stack trace, and would have been a lot of trial and error to resolve. Example mentioned above: https://github.com/drizzle-team/drizzle-orm/blob/main/examples/neon-cloudflare/src/index.ts

Why the migration?

I went to implement E2E testing of Cloudflare workers locally (using unstable_dev) and ran into an issue where only @prisma/client/edge can be used in a Cloudflare worker but requires using Prisma data proxy. After burning a lot of time and looking for local workarounds, it was the last straw, and here I am. There are other complaints, namely their documentation, but the inability to perform local E2E tests is unacceptable. The management team wanted to proceed with Prisma with bandaids, and I'm glad I opted for the late nights instead.

How long and how much effort?

• All said, it took roughly ~21 hours.
• In the end, LOC per repository dropped ~25%.
• As much as people like to complain about TypeScript, it made it 1000% easier to get through this migration. Granted, there would be less redundancy in the repositories without the explicitness. Clean architecture helped, too.

Comments/Feedback

• I love that Drizzle is lightweight. I hope it stays this way. I hope not to contradict this statement with the rest of the comments.
• I've worked hard to maintain no reliance on node and was pained adding node_compat to Wrangler to use Drizzle. This issue is indeed more with postgres.js.
• I love that the schema is TypeScript, but I prefer the schema to be abstracted, with less reliance on a single database (coming from a long tenure of SQLAlchemy).
• Generics for code reuse would be great, if possible. - [BUG]: Unable to access RelationalQueryBuilder type #1319
• It would be wonderful to have a similar DX using insert/update as a query. For example, in my repo pattern, I have a nice and concise method for querying. Then, for creating and updating, there's the extra step of importing the schema. Similar to eq, etc.

A bit about our project:

• This project is ten months old, an API powering a React Native app
• Clean architecture
• Nineteen tables
• Before migration, three dependencies: Prisma, Zod, Hono
• Hosting: Cloudflare and Neon

Thanks for all the hard work; Drizzle is awesome and community-friendly!✌🏻

[Angelelz]

This was a great read! Thanks for sharing!

All said, it took roughly ~21 hours.

You got some talented people!

In the end, LOC per repository dropped ~25%.

Some people have suggested that Drizzle is more verbose. This is very interesting.

[oureboros]

@Angelelz Thank you for the kind words! I took another peek at the before and after regarding the repository code. The method chaining in Drizzle versus object in Prisma contributes to the line drop. In all fairness, it's mostly syntax. Nevertheless, we have skinnier files as a result. 😊

[gabbrieu]

@oureboros when you say that you run one test at a time, did you say that the truncate approach doesn't work when running multiple tests files at once? (Something like running two tests files like login.test.ts and logout.test.ts).
And if so, could you please explain why?

[oureboros]

@gabbrieu In the case of using Jest for integration tests, running one test at a time (e.g., maxWorkers: 1 in jest.config.js) is to ensure the tests do not impact each other. Each test starts with empty tables. Running multiple tests with truncate per test would cause truncating while another test is running. Running multiple tests (simultaneously) is possible with interactive transactions (rollback at the end), but I have not devoted the time to explore this further with Drizzle.

[gabbrieu]

@oureboros oh i see. I'm not using Jest, I'm using the native bun implementation to test. I'll try to switch to jest to see if it works for me. My problem is that if I end the database connection in one test it is ending all connections in the other tests, even though i start the connection in the tests itself

[septatrix]

• I love that the schema is TypeScript, but I prefer the schema to be abstracted, with less reliance on a single database (coming from a long tenure of SQLAlchemy).

I created an issue for this a while ago: #1196

[naquiroz]

On update (e.g., updatedAt) - [FEATURE]: Prisma's updatedAt behavior. onUpdate API suggestion #956

• Solution: Moved to domain

Hi, @oureboros, one question: When you moved the updatedAt behavior to domain, how exactly did you do it?

[JohnAllenTech]

On update (e.g., updatedAt) - [FEATURE]: Prisma's updatedAt behavior. onUpdate API suggestion #956

• Solution: Moved to domain

Hi, @oureboros, one question: When you moved the updatedAt behavior to domain, how exactly did you do it?

Incase you missed it Drizzle now supports to .$onUpdate(() => new Date()) function for your schema