Recently, I started migrating my monolith project to a Cloudflare Worker. At the same time, I decided to give Cloudflare D1 a try. Since my project already uses Prisma to handle a PostgreSQL database, I thought the migration would be almost painless to D1. (How naive I was in the beginning!)

First Issue: No Support for ENUM Data Types

The first roadblock I encountered was that Cloudflare D1, as a SQLite-like database, doesn’t support the ENUM data type. I uses ENUM almost in every table in my project, and Prisma makes it incredibly straightforward to implement and manage ENUM datatype.

To work around this limitation, I had to create separate tables to store the ENUM data manually. Technically, PostgreSQL also creates a sort of "table" behind the scenes when you define an ENUM datatype, but here, I had to do it myself. It wasn’t ideal, but I managed to solve this issue.

Then, Prisma told me, Cloudflare D1 does not support interactive transactions. Prisma have two type of methods to handle database transaction, interactive transactions and sequential operations. I use interactive transactions in every part of my code because it give me control on the flow. I getting this error when I migrated my code to worker and want to create a new user, then get the user ID to create user profile. This is very typical data design and storing. At this point, I still thinking maybe I can generate the UUID as the user ID, before create user and user profile, problem solved.

Second Issue: No Interactive Transactions

I found out that Cloudflare D1 doesn’t support interactive transactions. Prisma offers two ways to handle transactions: interactive transactions and sequential operations. In my code, I use interactive transactions extensively because they give me control over the flow of operations.

I found this issue, when I migrated my code to the worker and tried to create a new user, I needed the user ID immediately to create a corresponding user profile. This is a common and straightforward use case for database design. However, without interactive transactions, we don't know the user ID after the user creation, and can't create the user profile without user ID.

Although I started to feel annoyed at that moment, I still managed to work around this issue by creating the user ID beforehand. That seemed like a reasonable fix, and it worked to some in extent.

The Final Boss: No ACID Compliance

No ACID compliance is a no-go for my use case. While testing further, I encountered this warning from Prisma:

prisma:warn Cloudflare D1 does not support transactions yet. When using Prisma's D1 adapter, implicit & explicit transactions will be ignored and run as individual queries, which breaks the guarantees of the ACID properties of transactions. For more details see https://pris.ly/d/d1-transactions

Transactions are crucial for ensuring data integrity, especially in scenarios where multiple operations need to be treated as a single transaction.

Conclusion

For now, I've decided to stick with my existing PostgreSQL setup and save myself the headache. Today I learned that if something is working, it's better not to change it.