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
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?
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?
