How to apply Liquibase to AWS Serverless applications with NX Dev Tools on a monorepo

Published in

INNOMIZE

5 min readDec 17, 2019

Keeping track of your application’s database is not an easy task. Database schemas tend to mismatch in different environments, data in one of the databases may miss some crucial piece of data. Such occasions can be irritating, especially when caught in production.

The situation gets worse when the project is developed base on the microservice pattern. In this case, each of your services has its own database instance whose structure may differ from others’. So keeping track of your service’s databases can become a nightmare (See 6 Reasons to Version Control Your Database). Therefore it is useful if we can use database version control in the project.

In the previous article, I have introduced Building AWS Serverless applications with NX Dev Tools on a monorepo and the advances of Monorepo. In this article, I will step by step to apply Liquibase (database version control) to the AWS Serverless application.

Workspace structure

Base on the structure of Building AWS Serverless applications with NX Dev Tools on a monorepo, I add a new service.

ng g @nrwl/node:application learning-app

To apply Liquibase, I add some directories and workspace has structure.

Workspace
├── angular.json
├── apps
│   ├── greeting-app
│   │   ├── jest.config.js
│   │   ├── liquibase # This is database management for greeting app
│   │   │   ├── changelog-master.xml
│   │   │   ├── liquibase.properties
│   │   │   ├── orders.csv # Import data file
│   │   │   └── package.json # Use to manage version and scripts
│   │   ├── serverless.yml
│   │   ├── src
│   │   │   └── functions
│   │   │       └── greeting
│   │   │           ├── greeting.handler.ts
│   │   │           ├── greeting.yml
│   │   │           └── index.ts
│   │   ├── tsconfig.app.json
│   │   ├── tsconfig.json
│   │   ├── tsconfig.spec.json
│   │   └── tslint.json
│   ├── hello-app
│   │   ├── jest.config.js
│   │   ├── liquibase # This is database management for greeting app
│   │   │   ├── changelog-master.xml
│   │   │   ├── liquibase.properties
│   │   │   ├── package.json # Use to manage version and scripts
│   │   │   └── users.csv # Import data file
│   │   ├── serverless.yml
│   │   ├── src
│   │   │   └── functions
│   │   │       └── hello
│   │   │           ├── hello.handler.ts
│   │   │           ├── hello.yml
│   │   │           └── index.ts
│   │   ├── tsconfig.app.json
│   │   ├── tsconfig.json
│   │   ├── tsconfig.spec.json
│   │   └── tslint.json
│   └── learning-app
│       ├── jest.config.js
│       ├── serverless.yml
│       ├── src
│       │   └── functions
│       │       └── learning
│       │           ├── index.ts
│       │           ├── learning.handler.ts
│       │           └── learning.yml
│       ├── tsconfig.app.json
│       ├── tsconfig.json
│       ├── tsconfig.spec.json
│       └── tslint.json
├── jest.config.js
├── libs
│   └── common-util
│       ├── jest.config.js
│       ├── README.md
│       ├── src
│       │   ├── index.ts
│       │   └── lib
│       │       └── common-util.ts
│       ├── tsconfig.json
│       ├── tsconfig.lib.json
│       ├── tsconfig.spec.json
│       └── tslint.json
├── nx.json
├── package.json
├── package-lock.json
├── README.md
├── tools
│   ├── libs # libs for liquibase
│   │   ├── liquibase-core-3.5.4.jar
│   │   └── mysql-connector-java-8.0.18.jar
│   ├── schematics
│   └── tsconfig.tools.json
├── tsconfig.json
├── tslint.json
└── webpack.config.js

In this structure, I only use database version control for two services are hello-app and greeting-app. Each service has a different structure of the database (sometimes the database is located on a different host).

Applying Liquibase

To apply Liquibase, I update the content of changelog-master.xml, liquibase.properties, package.json, orders.csv in greeting app liquibase directory.

// package.json
{
  "name": "nx-lambda-liquibase-greeting-db",
  "version": "1.0.0",
  "license": "MIT",
  "scripts": {
    "dropAll": "java -jar ../../../tools/libs/liquibase-core-3.5.4.jar dropAll",
    "update": "java -jar ../../../tools/libs/liquibase-core-3.5.4.jar update",
    "init": "npm run dropAll && npm run update"
  }
}

// liquibase.properties
classpath=../../../tools/libs/mysql-connector-java-8.0.18.jar
driver=com.mysql.cj.jdbc.Driver
changeLogFile=changelog-master.xml
url=jdbc:mysql://db.example.com:3306/hiep_greeting?useSSL=false
username=root
password=password

// changelog-master.xml
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">

  <changeSet author="hiepdinh"
    id="1.0.0">
    <comment>Initialize the Liquibase setup for greeting app</comment>
  </changeSet>

  <changeSet id="1.0.1" author="hiepdinh">
    <comment>Create orders table</comment>
    <createTable tableName="orders">
      <column autoIncrement="true" name="id" type="INT">
        <constraints primaryKey="true"/>
      </column>
      <column name="name" type="VARCHAR(36)"/>
    </createTable>
  </changeSet>

  <changeSet id="1.0.2" author="hiepdinh" runOnChange="true">
    <comment>Load test data for orders table</comment>
    <loadUpdateData primaryKey="id" encoding="UTF-8"
          file="orders.csv"
          quotchar="&quot;" separator=","
          tableName="orders">
      <column header="name" name="name" type="STRING"/>
    </loadUpdateData>
  </changeSet>
</databaseChangeLog>

And update greeting.handler.ts of the greeting function to use MySql.

import { Handler, Context } from 'aws-lambda';
import * as serverlessMysql from 'serverless-mysql';

const mysql: serverlessMysql.ServerlessMysql = serverlessMysql({
  config: {
    host: 'db.example.com',
    database: 'hiep_greeting',
    user: 'root',
    password: 'password'
  }
});

export const handler: Handler = async (event: any, context: Context) => {
   // Run your query
   const results = await mysql.query('SELECT * FROM orders')

   // Run clean up function
   await mysql.end()

  return {
    statusCode: 200,
    headers: {
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(results)
  };
};

Similarly for hello-app service. You can see it here.

To run Liquibase CLI for each service, you only navigate the command line to the liquibase directory on this service and run command (see more here).

npm run dropAll # Drop all tables in databse 
npm run update # Update database with new changeset

But from we can use Liquibase as a separate project with the same result for database management, why do we need to apply it in this project. Because I want to use advances of monorepo pattern and apply trigger before deploying AWS Serverless application to run migrate database. This will benefit for DevOps.

Integrate the database migration before deploying AWS Serverless application

To run a database migration script in Serverless application, you need to install

npm install --save-dev serverless-scriptable-plugin

And update serverless.yml in greeting-app service. Similarly for hello-app service.

// Add this code to custom section
custom:
  scriptHooks:
    before:package:cleanup: cd apps/greeting-app/liquibase && npm run update

With before:package:cleanup is an event in Serverless lifecycle (see more here)

Deploying and Testing

I deploy three services and test them.

sls deploy --config apps/greeting-app/serverless.yml
sls deploy --config apps/hello-app/serverless.yml
sls deploy --config apps/learning-app/serverless.yml

Database result:

AWS Lambda result:

Conclusion

By applying Liquibase to AWS Serverless applications with NX Dev Tools on a monorepo, we can use advances of monorepo and database version management and constrain the database migration process that must run before deploying the application.

You can see the full source code here.

Originally published at https://innomizetech.com/blog