Testcontainers make it easy to test your database integration by providing docker containers for many databases, which can be started from within your unittest. This is great, but it requires a bit of work to get started since the databases are empty. Liquibase is a tool to manage database schema changes. Combining the two is an easy way to get the right schema to use in your tests. For this post we’re going to use both frameworks to setup and test a MySQL database.

Let’s start with creating a test which uses testcontainers. First we need to add some maven dependencies.

    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>testcontainers</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>mysql</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>junit-jupiter</artifactId>
      <scope>test</scope>
    </dependency>

We’re also going to add a dependencyManagement section:

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers-bom</artifactId>
        <version>1.16.3</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

That should contain all the dependencies we need. Docker also needs to be installed in order for the container to start.

Now, let’s create a test that uses a MySQL container.

@Testcontainers
public class TestcontainersTest {

    @Container
    public MySQLContainer<?> mySQLContainer = new MySQLContainer("mysql:8.0.28");

    @Test
    public void testContainer(){
        mySQLContainer.start();
        try(Connection conn = mySQLContainer.createConnection("")){
            Statement statement = conn.createStatement();
            statement.execute(mySQLContainer.getTestQueryString());
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            int result = resultSet.getInt(1);
            System.out.println(result);
        } catch (SQLException e){
            fail(e.getMessage());
        }
    }
}

The first thing to notice is the @Testcontainers annotation. This will tell the Testcontainers framework to look for containers to start. Which brings us to the next thing, the @Container annotation. This will create a container. In this case the container will be a MySQL container, specifically running version 8.0.28.

Once the container has been created, it is not started yet. In the first line of our test, we start it by calling the .start() method. After that, we request a JDBC connection to the container, and we’re set.

But we don’t have a useful database yet. That’s where Liquibase comes in. We need to add the Liquibase dependency, create the scripts that update the database, and tell Liquibase to run those scripts.

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

We’ll save the liquibase scripts in /src/main/resources/database, so we can access them from the unittest.

<?xml version="1.0" encoding="UTF-8"?>
<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">
    <include file="change_001.xml" relativeToChangelogFile="true" />
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?>
<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 id="001" author="ghyze.nl">
        <createTable tableName="PERSON">
            <column name="id" type="INT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_person" />
            </column>
            <column name = "firstname" type="varchar(255)" />
            <column name = "lastname" type="varchar(255)" />
        </createTable>
    </changeSet>
</databaseChangeLog>

And then we add the code to execute the scripts to the unittest:

    @Test
    public void testContainer(){
        mySQLContainer.start();
        try(Connection conn = mySQLContainer.createConnection("")){
            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(conn));
            Liquibase liquibase = new Liquibase("/database/changelog.xml", new ClassLoaderResourceAccessor(), database);
            liquibase.update("test");

			// some more code
        } catch (SQLException | LiquibaseException e){
            fail(e.getMessage());
        }
    }

The liquibase.update() statement needs a context. This is a way to execute or leave out some scripts. For our test this doesn’t really matter, so we just put in “test”.

And now to proof that it works, let’s insert a record in the database and read it again:

    @Test
    public void testContainer(){
        mySQLContainer.start();
        try(Connection conn = mySQLContainer.createConnection("")){
            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(conn));
            Liquibase liquibase = new Liquibase("/database/changelog.xml", new ClassLoaderResourceAccessor(), database);
            liquibase.update("test");

            PreparedStatement preparedStatement = conn.prepareStatement("insert into PERSON values(?, ?, ?)");
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "john");
            preparedStatement.setString(3, "doe");
            preparedStatement.execute();

            Statement statement = conn.createStatement();
            statement.execute("select * from PERSON");
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            int id = resultSet.getInt(1);
            String firstName = resultSet.getString(2);
            String lastName = resultSet.getString(3);
            System.out.println("id: "+id+", first name:"+firstName+", last name:"+lastName);
        } catch (SQLException | LiquibaseException e){
            fail(e.getMessage());
        }
    }

And the result should look something like this:

Conclusion

When your system uses a database, that database should be tested. There are different ways to setup and test the database, and Testcontainers is one of them. The advantage of using testcontainers is that the test is close to how the software will run on production. The disadvantage is that these tests are slow, and should be treated more as integration tests than regular unittests. In other words, you don’t want to run these tests all the time. Also, Docker needs to be installed and running. But just starting a MySQL container isn’t enough. We can’t test an empty database. Liquibase can be used to setup the database to get it in a testable state.Related: alligator attacks in texas statistics, can you take losartan and olmesartan together, why did kelly palmer leave king of queens, glass semi flush mount ceiling light, james e anderson obituary, postman client certificate not sent, forever fleece yarn patterns, home child care provider pilot 2022, boulder rock vape problems, does leticia bufoni have a kid, marks and spencer ladies coats, postman client certificate not sent, daniel alfonzo bullhead city az, watts family genealogy, do caleb and ashley get divorced on heartland,Related: what do you like least about learning, is gene hackman in yellowstone, marriott seafood buffet gold coast voucher, albuquerque gun shows 2022, alfie solomons skin condition, drummond family osage murders, delhivery academy login, netherlands residence hall hofstra address, how to open kadoya sesame oil bottle, kristin ess hair gloss allergic reaction, highest paid college coaches all sports, aerospace manufacturing company vp matt, mercadante funeral home obituaries, fbi miami shootout autopsy photos, position de la lune dans le ciel en direct,Related: vw type 4 performance heads, recent deaths in newberg oregon, can scabies live in shoes, which of these is a run on sentence before lunch, gs 13 pay scale 2022 washington dc, scotland squire phoenix, carta astral ascendente y descendente, how much does rick jeanneret make, , what school does grayson chrisley go to, ethan ratke longest kick, slide rock sedona water temperature, stephen a smith basketball career stats, fox 5 dc news anchor fired for harassment, treatment goals for independent living skills,Related: sarina glow led color changing touch light kit, gregory godzik dug own grave, gulf coast boat tours, prayer points to heal kidney and liver disease, faint line on lateral flow test after an hour, john mcwhorter wife, is great value cheese vegetarian, acuity scheduling redirect, lance west centerbridge, brisbane grammar school term dates 2022, mary maxwell comedian age, 17 paseo verde santa barbara ca, pixar software engineer interview, is starbucks included in ncl drink package, heart concert tour 1979,Related: arched doorway trim ideas, ernest burkhart pardon, new mexico standard specifications for public works construction, nsw chs athletics merchandise, west philly shooting last night, george alexander singer photo, jonathan vaughters obituary, gregory lafayette cause of death, dasha navalnaya stanford, mau mau spanish slang, why did graham elliot change his name, first families of isle of wight, virginia, private caravan hire waterside weymouth, travel man: 48 hours in prague, craigslist ri jobs general labor,Related: how to clear paccar engine codes, royal irish regiment rangers, jeep wrangler dash lights red dot, awesome tanks 2 unblocked no flash, uchtdorf conference talk, knox county schools special education, signs an aries woman has a crush on you, troy construction palm springs, isabel oakeshott father, how to clean levoit humidifier, evelo comfort package, david sabatini family, marcus johnson obituary, cat choir where are they now, red onion documentary inmates,Related: murrah high school student killed herself, king jesus ministry events 2022, tati beauty second palette, jobs hiring immediately brooklyn, ceo salary $50 million dollar company, popular colors in the 1940s, immortal taoist spiritual mountain levels, luke harper funeral photos, wdrb news anchor salaries, ellen sandler related to adam, sydney grammar hscninja, chris wood augusta high school, ward 38 leicester royal infirmary, travel basketball bloomington il, zhou nutrition lawsuit,