DEV Community

Cover image for Cloudflare D1 and Prisma: Not a Good Combination (For Now)
Kven Ho
Kven Ho

Posted on

2

Cloudflare D1 and Prisma: Not a Good Combination (For Now)

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.

Create another table for Enum in SQLite

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

Cloudflare D1 does not support 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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay