The Truth About Prisma: Our Journey from MongoDB to SQL

Table of Contents

1. Introduction
2. Migrating from MongoDB to SQL
3. Choosing Prisma as the ORM
4. Understanding Prisma's Performance Issues
5. Addressing Prisma's Size and Performance Problems
6. The Challenge of Migrating Data to PlanetScale
7. Issues with Foreign Key Support in PlanetScale
8. Dropping PlanetScale and Switching to AWS Aurora
9. Introducing Geistly as an Alternative to Prisma
10. Conclusion

Introduction

In this article, we will explore the controversial topic of using Prisma as an ORM for database operations. Prisma has gained popularity among developers, but there are certain aspects that need to be discussed openly. We will dive into our experience of migrating from MongoDB to SQL, choosing Prisma as our ORM, and the performance issues we encountered. Additionally, we will address the challenges we faced with foreign key support in PlanetScale, our decision to switch to AWS Aurora, and the alternative solution we adopted called Geistly. So sit back, grab a snack, and let's dive deep into the world of Prisma.

Migrating from MongoDB to SQL

Our Journey with Prisma began when we made the decision to migrate from MongoDB to SQL. We wanted to move towards a more scalable database solution, and SQL seemed like the logical choice. Two members of our team had prior experience with Prisma and vouched for its reliability, so we decided to proceed with Prisma as our ORM. The migration process involved rewriting the entire schema and cleaning up corrupted data that had accumulated over time in the MongoDB database.

Choosing Prisma as the ORM

Prisma's enticing features, such as Next Generation node.js support, TypeScript ORM compatibility, and a clean syntax, made it an attractive choice for us. We believed that Prisma would provide a neater syntax for writing SQL queries, similar to how Mongoose was used for MongoDB. However, what we initially failed to realize was the size of the Prisma Package and its impact on our serverless architecture.

Understanding Prisma's Performance Issues

One of the first challenges we encountered was the significant increase in Prisma's size upon integration. Prisma ships with several Rust engines, resulting in a much larger deployment size compared to other ORMs. This became a concern for us as it directly affected the cold start time of our Lambdas and the available storage space. Prisma's reliance on these Rust engines for query execution raised questions about the necessity of such large binaries.

Addressing Prisma's Size and Performance Problems

To mitigate the size issue, we had to trim out unnecessary engines from our deployment, reducing Prisma's overall size. This process required us to remove engines like the Prisma migration engine and introspection engine, which we didn't need for our specific use case. Although we managed to bring down the deployment size, Prisma still required a significant amount of disk space, which was a non-negotiable requirement.

The Challenge of Migrating Data to PlanetScale

One of our initial motivations for choosing Prisma was its compatibility with PlanetScale, a MySQL database provider. However, we soon realized that PlanetScale did not support foreign key constraints, which were crucial for data integrity. This forced us to reconsider our database provider and explore other options that offered foreign key support.

Issues with Foreign Key Support in PlanetScale

The lack of foreign key support in PlanetScale posed a significant challenge for us. We needed a solution that would allow us to enforce data integrity and prevent the deletion of rows with dependencies. The complexity of setting up local instances of Vitess and the limitations we faced with permissions and role management made it impractical to use PlanetScale for our migration process. We needed a database setup that would allow us to test and run migration scripts locally.

Dropping PlanetScale and Switching to AWS Aurora

Given the limitations we faced with PlanetScale, we made the difficult decision to drop it as our database provider. Instead, we opted for AWS Aurora Serverless V2 for PostgreSQL. This allowed us to bring the database within our AWS architecture, ensuring better control, performance, and scalability. The switch to AWS Aurora meant rewriting our entire backend code, but the benefits outweighed the challenges.

Introducing Geistly as an Alternative to Prisma

With Prisma out of the picture, we searched for an alternative solution that would address our specific needs. We came across Geistly, a query builder that provided Type completions for our TypeScript backend and ensured the safety of our code through parameterized queries. Although Geistly is not an ORM like Prisma, it proved to be a reliable tool for our database operations.

Conclusion

Our experience with Prisma and the decision to migrate away from MongoDB and PlanetScale was not without its challenges. We encountered performance issues, size limitations, and lack of foreign key support, which led us to explore alternative solutions. While Prisma offers some great features and tooling, it ultimately did not Align with our specific requirements. The switch to AWS Aurora and adoption of Geistly provided us with a more reliable, performant, and scalable solution for database operations in our architecture.