We have a multi-tenant SaaS application using the "multi-schema" strategy i.e. every customer has dedicated schema in the same database instance. We are using MS SQL Server as the database which switches between schema's through the "default schema" setting for SQL Server "users". For example customers A, B and C are configured in SQL Server as follows:
user_A with default schema schema_A user_B with default schema schema_B user_C with default schema schema_C ... and so on.In our application, we switch DataSource connection to point to correct schema for each customer by setting the SQL Server "user" on the Connection by executing following SQL before every query:
EXECUTE AS USER = 'user_A';
This poses some problems for us when trying to use Flyway for managing state of schema versions - in a global manner. Since flyway's schema support only takes in a list of schema names this does not work for MS SQL Server. Flyway performs the migrations on the default schema of the user provided with the DataSource configuration; which in SQL Server's case, the "user" needs to vary per Customer/schema.
Ideally we would have a callback like FlywayCallback.beforeEachSchemaMigrate(Connection) that will let us set the desired User Context per schema by executing the "Execute as User" statement before each migration per schema. Not sure why that hook isn't there?
Another short coming in flyway is the convention of using first schema in list of schema's, as the one holding the schema_version table. This is not desired in a SQL Server based multi-tenant environment. As we cannot assume that the schema containing the schema_version table is also a real Customer schema. Keep in mind in a SaaS application like ours, schema's per tenant/customer are created/destroyed on the fly. When a user sign's up, part of the provisioning process creates the new schema based on some conventions. So the list of schema's is dynamic for us.
Ideally we can tell Flyway to use a given schema to create the schema_version table without trying to run the migrations on that schema. Typically this would be the dbo schema (which is default schema in SQL Server). We use dbo schema to hold tables that are global in nature across all tenants, schema_version would be considered a global table.
So in the end after a successful migration, our database should look like the following:
- dbo.schema_version
- schema_A.my_tables
- schema_B.my_tables
- schema_C.my_tables
All of the above schema's being in the same "State", dictated and controlled by dbo.schema_version table.
Is this currently possible?
You have to approach the problem differently. Instead of one execution of Flyway for everyone, go for one per schema. You can wrap Flyway in a loop and feed it with the correct configuration for one tenant per iteration. You will end up with one schema_version table per tenant, but they can all be individually named and still live in the dbo schema.