基于AWS CloudFormation的SQL Server实例：如何让Flyway在创建flyway_schema_history表前先创建目标数据库？

Got it, let's work through this problem step by step. The core issue here is that when you connect Flyway to a SQL Server instance without specifying a database in the JDBC URL, it defaults to using the master database—where your user doesn't have permissions to create tables. Since CloudFormation can't spin up a database alongside the SQL Server instance, we need to make Flyway handle database creation upfront.

Here are two reliable, actionable solutions:

Solution 1: Use Flyway Callback Scripts to Create Database First #

Flyway supports callback scripts that run at specific stages of the migration process. We can use a beforeMigrate callback to create the target database (if it doesn't exist) and switch to it before any migrations run.

1. Create the callback script
   In your Flyway sql directory, create a subdirectory called callbacks (or use the default callback location) and add a script named beforeMigrate__create_target_db.sql (the naming follows the [event]__[description].sql format):

   -- Check if target database exists; create it if not
   IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'YourTargetDatabase')
   BEGIN
       CREATE DATABASE YourTargetDatabase;
   END
   
   -- Switch to the target database so Flyway uses it for all subsequent operations
   USE YourTargetDatabase;
   

2. Verify permissions
   Make sure the Flyway service account has the CREATE DATABASE permission on the SQL Server instance, plus full permissions (like db_owner or granular CREATE TABLE, ALTER etc.) on the target database once it's created. You can grant these via SQL after the CloudFormation instance is provisioned, or use a CloudFormation custom resource to run the grant script automatically.

Solution 2: Configure JDBC URL + Init SQL #

Another approach is to specify the target database in your JDBC URL and use Flyway's initSql configuration to create the database before connecting to it.

1. Update the JDBC URL
   Add the databaseName parameter to your JDBC URL:

   jdbc:sqlserver://db.tracking.sandbox.aws.xxx.com:1433;databaseName=YourTargetDatabase
   

2. Set the initSql property
   In your Flyway configuration (whether it's flyway.conf, environment variables, or code), set the initSql value to create the target database if it doesn't exist:

   IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'YourTargetDatabase') CREATE DATABASE YourTargetDatabase;
   

   Flyway will execute this SQL immediately after establishing the connection to the instance, ensuring the database exists before trying to connect to it via the JDBC URL.

Key Notes #

• Both solutions ensure Flyway creates the flyway_schema_history table in your target database instead of master, avoiding the permission error entirely.
• Always test permissions first: your Flyway user needs enough privileges to create databases at the instance level, and manage objects in the target database.
• If using CloudFormation, you can automate permission grants by adding a AWS::CloudFormation::CustomResource that runs a SQL script to assign roles/permissions to the Flyway user once the SQL Server instance is online.

内容的提问来源于stack exchange，提问作者Feng Lei