David Zhang
Nov 3, 2018 · 5 min read
Migrating Aurora schema with AWS Lambda

This story is part of a series to get started with Relational Databases in Serverless:


If you are working with relational databases, and often release code that could require schema changes, you should consider managing your databases with schema migrations.

When developing software applications backed by a database, developers typically develop the application source code in tandem with an evolving database schema. The code typically has rigid expectations of what columns, tables and constraints are present in the database schema whenever it needs to interact with one, so only the version of database schema against which the code was developed is considered fully compatible with that version of source code. (wikipedia 📖)

Schema migrations will help you:

  • Keep track of schema changes
  • Easily reproduce them to get to the current schema
  • Keep the different instances of your database in sync (production, staging, local)
  • Rollbacks in case of broken deployments

Migrations & Serverless

Everything sounds great, so what’s the deal with Serverless and Lambda functions 🤔?

If you want to run migrations via a Lambda function, the issue comes down to how migrations and Lambda functions work at their core:

  • For migrations, we need to keep track of the schema changes. Know what has been upped or downed to guess what the current status of our database is. We need to keep track of our migration state.
  • Lambda functions run in stateless compute containers. As migrations are run via Lambda functions, you can not save the migration state in the container, as it will most probably die short after. So if another migration is triggered at another point in time, it won’t be able to know where the migration is at.

So, to be able to run schema migrations via Lambda functions, we just need to find a way to store our migration state, right 🙃?


Let’s get started!

Solution overview

  • We’ll use umzug (which is part of the more complete sequelize package) to manage our migrations

The umzug lib is a framework agnostic migration tool for Node.JS. The tool itself is not specifically related to databases but basically provides a clean API for running and rolling back tasks.

  • As a migration is running, we’ll save its state in the database it is updating

PS: I’m using serverless-mysql to run queries — see first post. So whenever mysql is called in the following examples, it refers to this library.

Umzug migration files

Let’s create a first migration that will create a table user with columns id , createdAt , and updatedAt . The rollback of this is DROP TABLE user; .

// 00_create-user-table.js
...
module.exports = {
up: () => mysql.query('CREATE TABLE IF NOT EXISTS user (createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, updatedAt DATETIME ON UPDATE CURRENT_TIMESTAMP, id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT)',
down: () => mysql.query('DROP TABLE user')
};

And save this file in a /migrations directory.

Umzug migration script

1. Let’s configure Umzug:

const Umzug = require('umzug');
const migrationStorePath = '/tmp/migrations.json';
const umzug = new Umzug({
storage: 'json',
storageOptions: {
path: migrationStorePath;
},
migrations: {
path: '<PATH TO /migrations DIRECTORY>'
}
});

The main point we need to be careful of is the migrationStorePath, as Lambda only allows us to write in /tmp folder.

2. Create a migration table if it does not exist already:

const migrationTableQuery = 'CREATE TABLE IF NOT EXISTS migration (createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, updatedAt DATETIME ON UPDATE CURRENT_TIMESTAMP, name STRING)';await mysql.query(migrationTableQuery);

3. Before running migrations, we’ll get all the migrations that have been run in the past:

const migrations = await mysql.query('SELECT * FROM migration ORDER BY createdAt');

4. Copy those migrations into the migrationStorePath , as Umzug will use this store to know which migration file should be run or not:

// transform migrations into an umzug accepted format
const umzugMigrations = migrations.map(migration => migration.name);
// write into umzug migration store
fs.writeFileSync(migrationStorePath, JSON.stringify(umzugMigrations), { flag: 'w' });

5. Create callbacks on umzug migrated events:

// When a migration file is executed, add it to our migration table
function addMigration() {
return async (name) => {
console.log(`${name} migrated`);
try {
await mysql.query(`INSERT INTO migration (name) VALUES("${name}.js")`);
console.log(`${name} inserted into migration table`);
} catch (error) {
console.error(`${name} could not be inserted to migration table`);
}
await mysql.end();
};
}
// When umzug finished to execute a migration file, call addMigration
umzug.on('migrated', addMigration());

We can do the same with down , and adding a callback for umzug’s reverted events. We’ll then DELETE FROM migration WHERE name="${name}.js" .

6. Run our migrations via umzug:

umzug.up()
.then((result) => {
console.log(`Migration completed: ${JSON.stringify(result)}`);
})
.catch(error => throw error);

Let’s test!

You can create a function triggered via http, that will run the migration script (see first post for all vpc config etc…):

# serverless.yml
...
functions:
migrate:
handler: src/functions/migration/handler.migrate
vpc:
securityGroupIds:
- Fn::GetAtt: ServerlessVPC.DefaultSecurityGroup
subnetIds:
- Ref: ServerlessSubnetA
- Ref: ServerlessSubnetB
- Ref: ServerlessSubnetC
events:
- http:
path: /migrate
method: get
...

Call your endpoint and you should be all set 🎊 🌞 🍒!

You can check your function logs in Cloudwatch to see what happened.

You can connect to your database (see second post) to make sure the table has been created with the right schema.


What’s next?

We used schema migrations to manage our relational database with umzug. The trick here was to store the migrations’ state in the database, and make sure it is always up to date.

Though it would be painful to always have to think to run migrations as we deploy new code on Lambda. We’ll see in the next post how to automatically run migrations as new code is deployed on Lambda.

Checkout the other stories of the series:

mos-engineering

Mission driven engineers fixing student financial aid. https://mos.com — We are hiring 🙃

David Zhang

Written by

mos-engineering

Mission driven engineers fixing student financial aid. https://mos.com — We are hiring 🙃

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade