2

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:

  • Customer A: user_A with default schema schema_A
  • Customer B: user_B with default schema schema_B
  • Customer C: 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?

Sheraz Khan
  • 43
  • 2
  • 5

1 Answers1

1

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.

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
  • That is true and I already have a proof of concept working where I am looping through each schema and creating new Flyway instance for each schema, that works well (it is our plan B). In fact in that case we wouldn't want to put schema_version table in dbo as it won't be "global", they would reside in their respective schema's. However the down side of this is that we have no guarantee that all schema's in same database are in the same exact "state". One schema could be version 2.0.1 and another could be 2.0.2 which is what we are trying to avoid. Any reason for not having a schema callback? – Sheraz Khan Jul 16 '14 at 14:28
  • For now we will go with separate Flyway instance per schema. Turns out that may be a better approach for a variety of other reasons. – Sheraz Khan Jul 22 '14 at 21:34
  • No you can't. This works in every database but sql server, which this is about. Unless, I'm missing something, of course. – Jasper Jul 19 '18 at 15:03
  • I was missing: placeholders. I think the answer would be better with a mention of them (and I can't actually change my vote without a change in the answer), but I don't quite know how to work the mention into this answer. – Jasper Jul 20 '18 at 09:37