Automate test data management & database seeding by integrating Liquibase into your testing framework

What does the QA (Quality Assurance) team need to test an application or website properly? Test data. 

But test data is nothing without an easy, convenient way to upload it into your database as part of the automated testing process. 

A comprehensive database DevOps solution, Liquibase provides a way to version, track, and deploy database changes with ease. That makes it ideal for uploading test data into the database during automated test executions, eliminating the need for JDBC queries or developing separate APIs for database seeding.

With Liquibase, you have three integration methods available for efficiently managing and resetting test data within your test framework, including via:

• Liquibase Maven plugin
• Liquibase Core API
• Liquibase CLI (command-line interface)

Each method has its advantages and disadvantages, so let’s explore them further.

For the purpose of this exploration, the steps below apply to the popular Java + Maven + Selenium WebDriver framework. Imagine a website that reads data from a database. You need to verify that the correct data is displayed on the web page. 

In this example, there is a dummy PHP website that reads data from a MySQL database. You can find the project example via this link. 

While you know how to write your Selenium test and assert what is displayed on the page, the challenge lies in:

• Managing test data
• Seeding the database
• Making changes to the test data
• Clearing the database at the end or the beginning of the test execution

Luckily, Liquibase can handle these challenges and integrate them into the automated test workflow. Depending on your setup, here are three methods for managing, seeding, changing, and executing test data when QAing websites and applications. 

Method 1: Liquibase-Maven plugin

For the first example, let’s use the Liquibase Maven plugin. This method is the most convenient, quickest, and easiest way to integrate Liquibase into a test framework built on Maven.

First, add it to your pom.xml file:

<plugin>
 <groupId>org.liquibase</groupId>
 <artifactId>liquibase-maven-plugin</artifactId>
 <version>4.27.0</version>
</plugin>

Then, add your database configuration to the pom.xml plugin section, where the URL is the URL of your test database, and username and password are the credentials for your test database. The ChangeLog file is where your test data will be stored. 

<configuration>
 <changeLogFile>src/main/resources/liquibase_files/changelog_mvn.sql</changeLogFile>
 <url>jdbc:mysql://localhost:3306/db</url>
 <username>root</username>
 <password>password</password>
</configuration>

Don’t forget to add the JDBC driver for your database to your pom.xml file.

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>8.0.33</version>
</dependency>

And decide when you want the database seeding and resetting to occur by adding an execution phase for Liquibase.

<executions>
 <execution>
   <id>liquibase-drop-all</id>
   <phase>process-test-resources</phase>
   <goals>
     <goal>dropAll</goal>
   </goals>
 </execution>
 <execution>
   <id>liquibase-update</id>
   <phase>process-test-resources</phase>
   <goals>
     <goal>update</goal>
   </goals>
 </execution>
</executions>

That’s it!

Run the mvn test command and Liquibase will clean and then seed the test database with the necessary data.

Method 2: Liquibase Core API

The Liquibase API method is slightly more difficult to implement, but it’s helpful if you:

• Need more flexibility
• Don’t want to seed the database during any specific Maven execution phase
• Want to seed or update your database directly during test execution

Simply add Liquibase Core as a dependency in your Maven project and use the Liquibase API in your tests.

<dependency>
 <groupId>org.liquibase</groupId>
 <artifactId>liquibase-core</artifactId>
 <version>4.27.0</version>
</dependency>

Once this dependency is set, you can create a LiquibaseAPIExecutor class to execute Liquibase commands.

import liquibase.Contexts;
import liquibase.Liquibase;
import liquibase.database.Database;
import liquibase.database.DatabaseFactory;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.LiquibaseException;
import liquibase.resource.ClassLoaderResourceAccessor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class LiquibaseAPIExecutor {

 private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/db";
 private static final String DEFAULT_USER = "root";
 private static final String DEFAULT_PASSWORD = "password";

 private String url;
 private String user;
 private String password;

 // Constructor using default values
 public LiquibaseAPIExecutor() {
   this(DEFAULT_URL, DEFAULT_USER, DEFAULT_PASSWORD);
 }

 // Constructor using custom values
 public LiquibaseAPIExecutor(String url, String user, String password) {
   this.url = url;
   this.user = user;
   this.password = password;
 }

 private void executeLiquibaseCommand(String changelogFile, LiquibaseCommandExecutor commandExecutor) throws ClassNotFoundException, SQLException, LiquibaseException {
   Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection conn = DriverManager.getConnection(url, user, password)) {
     Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(conn));
     Liquibase liquibase = new Liquibase(changelogFile, new ClassLoaderResourceAccessor(), database);
     commandExecutor.execute(liquibase);
   } // Connection will be closed automatically
 }

 public void update(String changelogFile) throws ClassNotFoundException, SQLException, LiquibaseException {
   executeLiquibaseCommand(changelogFile, liquibase -> {
     liquibase.update(new Contexts());
   });
 }

 public void dropAll(String changelogFile) throws ClassNotFoundException, SQLException, LiquibaseException {
   executeLiquibaseCommand(changelogFile, liquibase -> {
     liquibase.dropAll();
   });
 }

// Extend the list of commands based on your needs. Here is the // complete list of Liquibase commands:
// https://docs.liquibase.com/commands/command-list.html

 @FunctionalInterface
 private interface LiquibaseCommandExecutor {
   void execute(Liquibase liquibase) throws LiquibaseException;
 }
}

Then, you will use this LiquibaseAPIExecutor directly in your tests.

@BeforeClass
public static void seedDatabase() throws ClassNotFoundException, SQLException, LiquibaseException {
 // Initialize liquibase
 LiquibaseAPIExecutor liquibaseAPIExecutor = new LiquibaseAPIExecutor();

 // Clean up database before seeding by executing liquibase dropALL
 liquibaseAPIExecutor.dropAll("liquibase_files/changelog_api.sql");

 // Seed database by executing liquibase update
 liquibaseAPIExecutor.update("liquibase_files/changelog_api.sql");
}

Here, you can see that Liquibase seeds the database with test data right before the test execution. If you need to clean the database right after test execution, use Liquibase's dropAll in the AfterClass method.

Method 3: Liquibase CLI 

As an alternative to using the Liquibase API, you can use the Liquibase CLI (command-line interface) by adding the Liquibase installer to your test framework. With this method, you can centrally store elements such as URLs, changelogs, usernames, passwords, and more since the CLI can utilize a liquibase.properties file. 

In the example below, Liquibase CLI is added to a bin directory and a LiquibaseCLIExecutor class is created to teach the framework how to deal with Liquibase CLI commands within the test framework.

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

public class LiquibaseCLIExecutor {

 public void executeLiquibaseCommand(String pathToLiquibaseFolder, String... liquibaseCommandParts) {
   try {
     List<String> commands = new ArrayList<>();
     for (String part : liquibaseCommandParts) {
       commands.add(part);
     }

     for (String cmdPart : commands) {
       System.out.print(cmdPart + " ");
     }
     System.out.println();

     ProcessBuilder builder = new ProcessBuilder(commands);
     builder.directory(new File(pathToLiquibaseFolder));

     Process process = builder.start();

     BufferedReader stdInput = new BufferedReader(new InputStreamReader(process.getInputStream()));
     String s;
     while ((s = stdInput.readLine()) != null) {
       System.out.println(s);
     }

     BufferedReader stdError = new BufferedReader(new InputStreamReader(process.getErrorStream()));
     while ((s = stdError.readLine()) != null) {
       System.err.println(s);
     }

     int exitVal = process.waitFor();
     if (exitVal == 0) {
       System.out.println("Liquibase command executed successfully.");
     } else {
       System.err.println("Liquibase command execution failed.");
     }

   } catch (IOException | InterruptedException e) {
     e.printStackTrace();
   }
 }
}

You can use this LiquibaseCLIExecutor in your tests like this example below.

@BeforeClass
public static void seedDatabase() {
 // Initialize liquibase
 LiquibaseCLIExecutor liquibase = new LiquibaseCLIExecutor();

 // Clean up database before seeding by executing liquibase dropALL
 liquibase.executeLiquibaseCommand(prop.getProperty("liquibase.dir"), // Path to your Liquibase folder
     prop.getProperty("liquibase.executable"), // Liquibase executable
     "--defaults-file", basePath + "/" + prop.getProperty("liquibase.properties"), // File with Liquibase configuration: JDBC URL, username, password, etc.
     "dropAll" // Liquibase command
 );

 // Seed database by executing liquibase update
 liquibase.executeLiquibaseCommand(prop.getProperty("liquibase.dir"), // Path to your Liquibase folder
     prop.getProperty("liquibase.executable"), // Liquibase executable
     "--defaults-file", basePath + "/" + prop.getProperty("liquibase.properties"), // File with Liquibase configuration: JDBC URL, username, password, etc.
     "update" // Liquibase command
 );
}

Although the Liquibase CLI is the most complex method to integrate, it offers all the benefits of the full CLI version, including the ability to work with properties files and access all Liquibase features.

Embrace the flexibility of Liquibase

You can streamline and automate database management within your testing framework with any one of these three methods. They all enable you to clean, seed, update, and manage your test databases at any stage of the test execution, either during the Maven Build Lifecycle or during the test execution itself. 

To choose the right method for your pipelines, consider your specific project’s needs and existing setup. 

To learn more, explore Liquibase’s ChangeLogs and update command or head to the Community, where you can learn interactively from the AI chatbot.