Search code examples
sql-serverflyway

Are Flyway schemas merely informational or do they affect function?

I have a SQL Server database that uses schemas to logically group objects; there are ten schemas in the database.

If I baseline my database and create the schema history table in the “foo” schema, will Flyway apply a migration from the migration folder that operates on an object in the “bar” schema?

Do I need one folder of migration scripts for each schema in the database? The documentation explains how to specify schemas on the command line but doesn’t make it clear as to why I must.

Solution

  • The list of schemas on the command line has two effects:

    • the first named schema is where the history table goes
    • the named schemas are the ones that are cleaned by flyway clean

    (Note - in 6.1 the command line defaultSchema parameter was introduced to separate these usages)

    Migrations can refer to any schema in the database that you have access to - indeed, some objects may exist in one schema but depend on objects in another. If you're happy with the history table to go in dbo, and want to control the whole database with Flyway, just don't set these parameters. A folder of scripts per schema may help you with maintaining them but it is not necessary.