Serverless schema maintenance in .Net

Part1 - The basics

If you are writing a serverless application on AWS that requires a relational database backend, then you’ll no doubt be using Aurora Serverless (v1 or v2) in either the MySQL or PostgreSql flavours. One challenge with using a relational database is ensuring that the database schema is appropriately maintained as you develop your application. The necessity to specify a very strict schema can often work at odds with an incremental/agile approach to application development. As such you need to be able to reliably take your database from an old version of the schema to a new version of the schema before you start using new code against the database.

Ideally this should be integrated into your CI/CD pipeline to ensure that it is repeatable and reliable. Using AWS Lambda, and a .Net library called DbUp, you can achieve a schema upgrade process controlled by commiting scripts to your source cotrol repository. In this post, I will set up the basics of a working approach. I will continue to explore and improve this approach over upcoming posts.

While the usual approach to using DbUp is to create a console application that can be executed as part of a build script, I’ve taken a slightly different approach to integrate it into my AWS CodePipeline. AWS Lambda provides a very natural way to execute schema migrations in a serverless application.

The first step is to create a lambda function in your cloudformation. Using a SAM template that would look sonmething like this:

  DbUpFunction:
    Type: AWS::Serverless::Function
    Properties:
      Handler: dbupdemo::dbupdemo.Functions::SchemaUpgrade
      Runtime: dotnet6
      CodeUri: ""
      MemorySize: 1024
      Timeout: 900
      Environment:
        DBEndpoint: !Ref DBEndpoint
        DBName: dbupdemo
        DBSecret: !Ref DBSecretArn

There is no event to trigger this function, we’ll look at options for that later. You’ll also see that I have set the Timeout to the maximum 15 minutes (900 seconds). This is to ensure that the migration has ample time to complete. There are also some key environment variables passed in to the lambda to allow it to set up a connection to the database. The values for these can either be passed in as a parameter to the cloudformation template or if these resources are provisioned alongside the lambda, they can be referenced from within the same template. You will also need to attach a role to your lambda function that allows it to access any required AWS resources, for example a secrets manager secret where the login credentials are stored.

Next we need to implement this function. After adding the DbUp package like so:

dotnet add package DbUp

We can begin to add the necessary elements to the project. DbUp works primarily on SQL scripts, and we can easily embed these directly into the assembly that the lambda is executing. This can simply be done by adding the files relative to the csproj file like this

  <ItemGroup>
    <EmbeddedResource Include="Schema\Script0001 - Initial Database.sql" />
    <EmbeddedResource Include="Schema\Script0002 - Add Email Column.sql" />
    <EmbeddedResource Include="Schema\Script0003 - Stored Procedure to Update Customer.sql" />
  </ItemGroup>

Once this is done, you can use the DbUp extensions and Assembly reflection to form a statement like this.

    var upgrader = DeployChanges.To
        .MySqlDatabase(connectionString)
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .WithExecutionTimeout(TimeSpan.FromSeconds(300))
        .Build();

    var result = upgrader.PerformUpgrade();

This code will search the current assembly for any scripts that are embedded. The PerformUpgrade() method will then take that list of scripts, and compare it against the list of scripts that have already been executed (stored in its journaling table, which is normally named SchemaVersions), and execute only the scripts required to bring the database up to date.

This lambda can now be included as part of the AWS CodePipeline, either directly through an “Invoke” Lambda action, or my preferred mechanism which is using the “Invoke” AWS Step Function action (see AWS CodePipeline Action documentation for details).

  - Name: ProdDatabaseUpgrade
    RoleArn: !Ref ProdCrossAccountRoleARN
    ActionTypeId:
      Category: Invoke
      Owner: AWS
      Provider: StepFunctions
      Version: '1'
    Configuration:
      StateMachineArn: "#{ProdBackendDeploy.PipelineStepFunction}"
      InputType: Literal
      Input: |
        {
          ...
        }
    RunOrder: 5

There are 2 reasons I prefer the step function approach. The first is that the Invoke Lambda action requires you to implement a callback mechanism in the lambda, which I feel dirties the lambdas single purpose, not to mention adds some non-trivial permissions challenges if you are working with Cross-Account deployments. Secondly, there may be other tasks you want to do alongside the database upgrade that can be coordinated by the step function. For instance, one sneaky thing I have done in the past to deal with the Aurora Serverless V1 cold start time is to create a lambda function whose sole purpose is to “pre-warm” the database server. This is done by simply trying to open a connection to the database. Once the connection is open, the lambda exits, and anything from that point onwards that relies on the database does not need to concern itself with whether or not the database is running.

I have found this approach to database schema migrations to work very well, but there are a few possible issues that could surface with this approach. I would like to tackle some of these in part 2 of this series.