Supporting multiple engines in one app that uses db migrations
Hi,
I am mid-execution of my project which I'd like to release as Open Source. With this in mind, I want users to be able to choose their db - e.g. use SQLite on Cloudflare, postgres on Kubernetes deployments etc.
This leads me to the following assumptions:
1. The application must be able to update both schema and data safely between versions - for this schema migrations sound great, but...
2. Whoever deploys the app must be able to run the app with the chosen DB engine.
3. Switching between DB engines would require running a custom migration tool.
4. Switching DB will not happen on runtime, so the app doesn't have to load different Drizzle context
Can Drizzle support such a scenario? I imagine on each release I'd have to have separate migrations per supported dialect - that's not fun, but I'm willing to accept this as a development cost. But can I have one schema used with different configs (I imagine some schema parts would have to have minor changes between engines)? Did anyone do something similar? What tips would you have for this workflow not to become a pain for long-term development? For example, would there be an easy way to generate migrations for multiple engines with one command? Or can the studio work on multiple connections based on one schema?
I'm experienced developer, so I'm not afraid of getting things done with CI and my automation scripts, but I've not yet had a chance of working with Drizzle, so I'm looking for good tips before I start writing any kind of code around.
I am mid-execution of my project which I'd like to release as Open Source. With this in mind, I want users to be able to choose their db - e.g. use SQLite on Cloudflare, postgres on Kubernetes deployments etc.
This leads me to the following assumptions:
1. The application must be able to update both schema and data safely between versions - for this schema migrations sound great, but...
2. Whoever deploys the app must be able to run the app with the chosen DB engine.
3. Switching between DB engines would require running a custom migration tool.
4. Switching DB will not happen on runtime, so the app doesn't have to load different Drizzle context
Can Drizzle support such a scenario? I imagine on each release I'd have to have separate migrations per supported dialect - that's not fun, but I'm willing to accept this as a development cost. But can I have one schema used with different configs (I imagine some schema parts would have to have minor changes between engines)? Did anyone do something similar? What tips would you have for this workflow not to become a pain for long-term development? For example, would there be an easy way to generate migrations for multiple engines with one command? Or can the studio work on multiple connections based on one schema?
I'm experienced developer, so I'm not afraid of getting things done with CI and my automation scripts, but I've not yet had a chance of working with Drizzle, so I'm looking for good tips before I start writing any kind of code around.
