Configuring MySQL with CircleCI

Introduction

In this article, we explained how to configure more than one data source in a Spring Boot Application.

The source repository for the article uses CircleCI for CI builds. To build the source code, we needed to add a MySQL database for the JUnit integration tests.

In this article, we will explain how to set up MySQL with CircleCI for use with your integration tests.

We need to add a MySQL Docker image to add to our CircleCI configuration file.

We can either pull it from the docker hub or from CircleCI. CircleCI has its own docker hub that has pre-built images that have a lot of conveniences. It is recommended to use the CircleCI MySQL image because of its convenience.

We are going to update our CircleCI configuration file, .circleci/config.yml by adding a few commands that we are going to discuss in the following steps listed below. At the end of this article, we will provide the full configuration file with all the commands added in each of the steps. We are going to follow the steps below:

Add MySQL Docker image into our CircleCI configuration.
Configure the data source properties to be referenced by our application. These properties include the database username, password, and schema.
Configure MySQL server connection settings.
Start MySQL service.
Create database(s). In this example, we will use the three databases from our previous post.
Build the application.

Step 1: Adding MySQL

In CircleCI 2.0 we must declare our data source configuration explicitly because it may be possible that multiple pre-built or custom images may be in use. In this example, we will be using MySQL as our database server and we will be using CircleCI’s pre-built MySQL image.

image: circleci/mysql:[TAG]

We will add the above line in our CircleCI configuration file and please take note that we must replace the [TAG] with the version of MySQL that we are going to use.

There are several versions of MySQL that are preset by CircleCI. We can find these versions here. In our example, we will use version 5.7 of MySQL. Therefore this command will look like this.

image: circleci/mysql:5.7

Step 2: Data source configuration

In our previous example, we configured three data sources, one for storing member information the other one for storing card details, and the last one for storing cardholder information. Thus we are going to set the database properties of each data source.

jobs:
 build:
   docker:
   - image: circleci/openjdk:8u212-b04-stretch
     #specify system properties
     environment:
       DB_ROOT_USER: root
       DB_DRIVER: com.mysql.cj.jdbc.Driver
       DB_PASS: ""
       MEMBER_DB_URL: jdbc:mysql://localhost:3306/memberdb
       CARD_DB_URL: jdbc:mysql://localhost:3306/carddb
       CARD_HOLDER_DB_URL: jdbc:mysql://localhost:3306/cardholderdb

These database properties will be referenced from our application. As indicated above we have defined these properties under the environment tag of the JDK image. It is clear that our data sources will be using the same database server. Therefore we will create three databases in this server.

@Bean
public DataSource cardDataSource(Environment environment) {
   DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
   dataSourceBuilder.driverClassName(environment.getRequiredProperty("DB_DRIVER"));
   dataSourceBuilder.url(environment.getRequiredProperty("CARD_DB_URL"));
   dataSourceBuilder.username(environment.getRequiredProperty("DB_ROOT_USER"));
   dataSourceBuilder.password(environment.getRequiredProperty("DB_PASS"));
   return dataSourceBuilder.build();
}

The above code snippet shows how the application will be referencing the card database properties. The variable name DB_ROOT_USER in line number 6 of the above code snippet is defined in our CircleCI configuration and it holds the value of the database username.

Step 3: MySQL server configuration

In this step, we are going to add our own MySQL server configuration settings. These changes will not affect existing databases in the MySQL server even after startup. In this example, we are going to set an empty password for the root user. We will add the following lines to our configuration file.

#mysql image from circleCI docker hub
- image: circleci/mysql:5.7
  environment:
    MYSQL_ALLOW_EMPTY_PASSWORD: yes
    MYSQL_ROOT_PASSWORD: ''

However, setting an empty password is a security risk and it is not recommended in production environments unless if you know what you are doing. Kindly note that our application will be using the root user to connect to the database server. In the above code snippet, Line number 4 declares an optional setting, MYSQL_ALLOW_EMPTY_PASSWORD. By setting yes to this variable it means that our database service container will start with a blank password for the root user. Line number 5, sets an empty password for the root user.

Step 4: Starting database service

Our application has more than one service and it may experience some issues. For instance, when a service depending on MySQL starts before MySQL is ready to accept connections. We can avoid this by using dockerize. This command will force the database server to be ready to accept connections in order to avoid the issue we described above. We will add this setting as indicated below in line number.

- run:
   name: Wait for database service on the tcp protocol
   command: dockerize -wait tcp://localhost:3306 -timeout 1m

Step 4: Creating databases

We are going to first install a MySQL client. We will use this client to interact with our database via the mysql command. Therefore we will be able to execute MySQL statements using this command.

- run: sudo apt-get install mysql-client

Now that we have the MySQL client, it means that we are now able to pass commands for creating our application databases using the mysql command. Let us add the commands for creating our three databases.

- run: mysql -h 127.0.0.1 -u root -e "create database carddb"
- run: mysql -h 127.0.0.1 -u root -e "create database memberdb"  
- run: mysql -h 127.0.0.1 -u root -e "create database caholderdb"

Step 5: Building our application

We have finished setting up MySQL with CircleCI. Find below our complete CircleCI configuration file after adding the lines described in each of the steps that we followed.

# Java Maven CircleCI 2.0 configuration file
# Check https://circleci.com/docs/2.0/language-java/ for more details
version: 2
jobs:
  build:
    docker:
    # specify the version you desire here
    - image: circleci/openjdk:11.0.3-jdk-stretch
      #specify system properties
      environment:
        DB_ROOT_USER: root
        DB_URL: jdbc:mysql://localhost:3306/testdb
        DB_DRIVER: com.mysql.cj.jdbc.Driver
        DB_PASS: ""
  # Customize the JVM maximum heap limit
      MAVEN_OPTS: -Xmx3200m
      # Specify our database service here
      # CircleCI maintains a library of pre-built images
    - image: circleci/mysql:5.7
      environment:
        MYSQL_ALLOW_EMPTY_PASSWORD: yes
        MYSQL_ROOT_PASSWORD: ''
    working_directory: ~/repo
      steps:
    - checkout
    - run:
        name: Wait for Database Connection
        # preinstalled in circleci/* docker image
        command: dockerize -wait tcp://localhost:3306 -timeout 1m
  #install mysql client
    - run: sudo apt-get install mysql-client
  # create databases
    - run: mysql -h 127.0.0.1 -u root -e "create database carddb"
    - run: mysql -h 127.0.0.1 -u root -e "create database cardholderdb"
    - run: mysql -h 127.0.0.1 -u root -e "create database memberdb"
    # Download and cache dependencies
    - restore_cache:
        keys:
        -  v1-dependencies-{{ checksum "pom.xml" }}
        # fallback to using the latest cache if no exact match is found
        -  v1-dependencies-
    - run: mvn dependency:go-offline
    - save_cache:
        paths:
        - ~/.m2
        key: v1-dependencies-{{ checksum "pom.xml" }}
        # run tests!
    - run: mvn integration-test

Now we can commit the changes that we made and push to our source code manager for a build automation process to take place.