Drizzle and multi-tenancy
I've been given a project that requires a degree of multi-tenancy. The system database will contain users, organizations, sub-organizations and all the links between these entities, then all tenant-specific entities would reside in the separate database. The client is asking for a separate database per tenant for two reasons:
2 is a little more complicated, and I'm asking for further clarification on if this is a legal thing or not.
Personally, I'm mostly concerned about the prospect of needing to run migrations on dozens of identical but separate databases. While it looks like it can be done rather easily with Drizzle by just looping over the tenants and building a new instance of the client, I've done multi-database multi-tenancy before and it was a bit of a pain, not something I would like to repeat.
Right now I'm considering using Turso as it has a high limit on database count and databases can be created programmatically via their API.
On the other hand, just using a single MySQL/Postgres instance, switching databases via the
I'm also unsure how Turso would handle constant re-creations of the Drizzle client every time separate tenants require a connection, instead of maintaining the same one connection.
Any recommendations on approach or if I'm missing anything I should be considering?
- to prevent data leakage between tenants
- to allow tenants to pick their region
2 is a little more complicated, and I'm asking for further clarification on if this is a legal thing or not.
Personally, I'm mostly concerned about the prospect of needing to run migrations on dozens of identical but separate databases. While it looks like it can be done rather easily with Drizzle by just looping over the tenants and building a new instance of the client, I've done multi-database multi-tenancy before and it was a bit of a pain, not something I would like to repeat.
Right now I'm considering using Turso as it has a high limit on database count and databases can be created programmatically via their API.
On the other hand, just using a single MySQL/Postgres instance, switching databases via the
USE statement, and keeping everything within SQL sounds easier than having another layer via the Turso API.I'm also unsure how Turso would handle constant re-creations of the Drizzle client every time separate tenants require a connection, instead of maintaining the same one connection.
Any recommendations on approach or if I'm missing anything I should be considering?
