Is there a way to run initial SQL when creating an RDS database instance using CloudFormation?

Question

I am creating an RDS instance using CloudFormation using this:

"Resources": {
        "myDB": {
            "Type": "AWS::RDS::DBInstance",
            "Properties": {
                "AllocatedStorage": "5",
                "DBInstanceClass": "db.m1.small",
                "Engine": "MySQL",
                "EngineVersion": "5.5",
                "DBName": "mydb",
                "MasterUsername": {
                    "Ref": "DBUser"
                },
                "MasterUserPassword": {
                    "Ref": "DBPassword"
                },
                "DBParameterGroupName": {
                    "Ref": "myRDSParamGroup"
                }
            }
        }

and it all works. But I need to run initial SQL on the DB when its created, to setup my apps schema. My current approach is to have the app self migrating, but I'd like to do it in the CloudFormation definition. Is this possible?

Faustofaustus

Answer 1

No, it's not possible. However, you could have an EC2 instance connect to your RDS instance to do it. I'd probably store a .sql file in S3 and use a cloud-init script on the EC2 instance to download the file and execute it.

Adara

Today, using a Lambda function + CustomResource makes much more sense than an EC2 instance – Pru

@OdedNiv I am trying to do exactly the same thing. Would you mind sharing the lambda function and the custom resource? – Delciedelcina

@RishiGoel I don't have one with me but Lambda is very simple and the docs are pretty straight forward. I may create one soon in which case I will post it here – Pru

One thing I have tried is to have an SNS topic connected to the NotificationARNs that the cloudformation templates have. From there, have a lambda function listening to all the events through the NotificationARNS. When the event is CREATE_FINISHED, launch whatever behaviour you want. More info => mbejda.com/handling-aws-cloudformation-events – Darelldarelle

Too bad this isn't a stock feature of RDS. Regardless of whether you create a DB Instance via CLI, Console, SDK, CloudFormation, or Beanstalk, there should be an option to run a specified script. Just like you can run a user data script on EC2 instances. – Merlon

Adding to what Oded commented, see this blog on using a Lambda custom resource. – Groot

Answer 2

It would also be possible to create a CloudFormation custom resource. There is a good discussion about how to build one using SNS here; it is also possible to build one using Lambda. Custom resources are essentially just RPCs, so it wouldn't be difficult to create one to initialize a database with a schema, for example.

Camelliacamelopard

I would like to avoid to let my beanstalk/EC2 resource to be in charge of loading database schema / data on init. I would like the database init schema/data would be managed by another way to separate concern and do not mix applicative centric app with provisioning stuff. A custom cloudformation resource in charge of getting sql script and inserting schema/data into the RDS database seems good, maybe using lambda and then we need to find a way to delete this extra custom resource once useless. Would be cool if someone find a standard way to do this kind of classic requirement: init DB – Belostok

I agree with everything you're saying -- schema is a deployment concern. It's possible one reason a "standard" doesn't exist is due to access restrictions. For a custom resource to initialize your database, it must have write permissions, which is undesirable. I'm facing a similar issue myself, though; if I end up building a custom resource, I'll come back and share what I did for reference in the hopes it would be useful to you. – Camelliacamelopard

Did you come up with a solution? I am facing the same problem. I was thinking about using ansible playbook for it but I wonder if there is a proper solution that can be done in the cloudformation realm – Siphon

I did not. I ended up just working around it, which isn't hard because we spin new stacks up and down fairly rarely in my use case. That approach is obviously a non-starter for a more high-volume use case. If I had to solve the problem today, I would set up a custom resource as linked above. – Camelliacamelopard

Answer 3

CloudFormation still doesn't hold any solutions for us, but hopefully they will add Database Migration Service support soon.

In the meantime, there is great solution if you're using CodePipeline: create a migration stage that invokes a Lambda function to run your migration. I stumbled across this guide for invoking Lambda from CodePipeline that may be helpful for those unfamiliar.

Conifer

Answer 4

Another option is to use DBSnapshotIdentifier property for AWS::RDS::DBInstance resource. The only catch is that you need to have a DB loaded in AWS to create the snapshot in the first place. From then on, you can automate your cloudformation stack to be using it though.

DBSnapshotIdentifier: Name (ARN) of the DB snapshot that's used to restore the DB instance.

If the property contains a value (other than an empty string), AWS CloudFormation creates a database from the specified snapshot.

After you restore a DB instance with a DBSnapshotIdentifier property, you must specify the same DBSnapshotIdentifier property for any future updates to the DB instance. When you specify this property for an update, the DB instance is not restored from the DB snapshot again, and the data in the database is not changed. However, if you don't specify the DBSnapshotIdentifier property, an empty DB instance is created, and the original DB instance is deleted.

Look in the doc from more info: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html#cfn-rds-dbinstance-dbsnapshotidentifier

Sheaff

Answer 5

While not supported by Cloudformation directly there are already AWS CDK Constructs that can help. I'm the author of one such package cloudformation-sql-run, which you can use as follows:

const db = new rds.DatabaseInstance(this, 'db', {
  databaseName: 'sqlrunexample',
  engine: DatabaseInstanceEngine.postgres({
    version: PostgresEngineVersion.VER_10
  }),
  vpc: vpc,
  removalPolicy: RemovalPolicy.DESTROY
})

const createItems = new SqlRun(this, 'Create Items', {
  vpc: vpc,
  connection: SqlRunConnection.fromDatabaseInstance(db),
  up: {
    run: [{
      sql: `CREATE TABLE items(name varchar)`
    }, {
      sql: `INSERT INTO items(name) VALUE (:secret)`,
      parameters: {
        secret: SqlSecret.fromSecretsManager(password)
      }
    }],
  },
  down: {
    run: [{
      sql: `DROP TABLE items`
    }]
  }
});

Neptunian