Same API for multiple databases
Hello, I am exploring the possibility of a binding that works for different databases.
I remember this post that @Andrew Sherman made on the T3 stack server
Totally agree that it's easier to do, and with Prisma abstraction, it's much simpler to achieve. ... When we started development and considered how to provide an interface for multiple database communication, we debated whether to create a common helper to combine them all and how that would work. After several rounds of conversations, we decided to fully separate the database types, even if it meant duplicating some of the common logic. It was an architectural tradeoff to make them independent and then modify each specific implementation and evolve each database API separately. ... We always aimed to leverage as much functionality as possible from a specific database. For example, if I use the returning feature in PostgreSQL to perform a single query on insert, I will lose the ability to easily switch to MySQL because I would need to rewrite the insertion logic and perform two queries (if the ID is not auto-incremented). This tradeoff is a choice I make. I prefer having control over the number of queries and taking advantage of specific database features rather than having an abstraction to switch between databases.My questions are: 1. Did you somewhere document the difficulties/incompatibilities that would be encountered with that approach? 2. What has Prisma sacrificed to achieve a universal API? 3. Have you considered the option of using a generic to modify the types according to the database? For example, db<pg> could have the returning feature you mentioned but with db<sqlite> it gives an error. 4. I'm thinking that instead of a universal API, at least an API between 2 dbms may be more viable. Between pg, mysql and sqlite, if you had to choose 2 to unify in the same API, which 2 do you think would be easier?
2 Replies
1. We did not, but we will definitely do that.
2. Prisma does a great job of abstracting away the specifics of database dialects, but it may lose some specific features per dialect. This can lead to bigger problems once you have a larger codebase and need something specific from a database. For small CRUD projects, I assume you would not hit such limitations often.
3. How is it different from what we have now?
4. Could you elaborate on that more?
3. I guess it's the difference between migrating from one rdbms to another. You wouldn't have to change the imports, and you would share as much of the API as possible.
4. similar to the previous point. Perhaps a common API can be made between 2 rdbms without having to sacrifice anything specific to one, or almost nothing. If you try to do it with 3 or more databases the compromises can be very large, but with 2 it may be feasible.