Testing Applications against Oracle Databases with Testcontainers: 3 Ways to Initialize Data

Testcontainers is a popular open source library that provides lightweight, disposable instances of various software systems, including databases like PostgreSQL, MySQL, and Oracle; message brokers like RabbitMQ and Apache Kafka; and other types of applications like Elasticsearch and Redis. These instances, which are created programmatically, can be used to run integration tests that require these systems. Testcontainers also allows developers to customize and extend these template instances to meet their specific testing needs.

Oracle Database is a powerful relational database management system widely used in enterprise applications. It supports a wide range of data types and complex queries, and it’s known for its scalability, reliability, and security features. With Testcontainers, you can easily create and manage instances of Oracle Database to run integration tests to ensure that your code interacts with the database correctly and behaves as expected.

In this hands-on tutorial, you’ll learn three different methods for initialising data in an Oracle database using Testcontainers, including the pros, cons, and use cases of each. 

Prerequisites

To follow along with this article, you’ll need the following installed on your machine:

• Docker
• Java 17+
• Maven 3+
• An IDE of your choice for Java project development

This tutorial assumes that you have had exposure to Java programming, the Spring framework, JUnit tests, and Docker.

You’ll also need to set up Testcontainers and connect to Oracle Database.

Note that we will be using the Spring Boot framework of version 3.1.1 for this tutorial. You can add the following dependencies to your project’s build file—for example, the pom.xml file in the case of Maven—to make the Testcontainers libraries and Oracle JDBC driver available in your project:

<properties>
  <java.version>17</java.version>
  <testcontainers.version>1.18.3</testcontainers.version>
</properties>

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc8</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>junit-jupiter</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>oracle-xe</artifactId>
  <scope>test</scope>
</dependency>

You can create an instance of Oracle Database using Testcontainers as follows:

@Testcontainers
@SpringBootTest
class CurrencyExchangeServiceApplicationTests {

    @Container
    static OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-xe:21-slim-faststart");

    ....
    ....
}

The @Testcontainers annotation is a JUnit 5 Extension annotation that will scan for @Container annotated properties and automatically initialise them using JUnit Lifecycle callbacks. When this annotation is used, Testcontainers uses Docker daemon and sets up a containerised application environment for the test to run in.

Please refer to Testcontainers container lifecycle management using JUnit 5 Guide to understand how container lifecycle works on static and non-static fields.

The OracleContainer class is a part of Testcontainers library and provides a simple way to set up a containerised instance of the Oracle database. It allows you to configure the properties of the container, such as the image/tag version of Oracle you want to use, the username and password for the database, and the port to expose onto the host machine for listening to the incoming requests.

Method 1: execInContainer Function

The execInContainer() method provides a convenient way to run commands inside a container, which can be useful when initialising data for testing applications.

The following example shows how this method can be used to initialise an Oracle database with a table named currency that is required for a currency exchange service:

@BeforeAll
static void setUp() throws IOException, InterruptedException {
   String[] command = {"bash", "-c",
           "echo \"create table currency ( currency_numeric_code number primary key, currency varchar2(255) null, currency_alpha_code varchar2(255) null); " +
                   "\" | sqlplus -S " + oracleContainer.getUsername() + "/" +
                   oracleContainer.getPassword() + "@//localhost:1521/" + oracleContainer.getDatabaseName()
   };

   ExecResult execResult = oracleContainer.execInContainer(command);

   System.out.println("execResult is : " + execResult.getStdout());
   System.out.println("execResult error is : " + execResult.getStderr());
   System.out.println("execResult exit code is : " + execResult.getExitCode());
}

As you can see, the execInContainer() method accepts a command in string array format as its input and executes it inside the container launched. In the given example, you are instructing Testcontainers to connect sqlplus and execute the create table statement. You can check the execution result or status of the function call by using the ExecResult class, as shown in the example. This is a handy way to troubleshoot if there are any failures during the command execution time.

Pros:

The following are the advantages of using the execInContainer() method:

• Simplicity: You can run commands inside the container with a single line of code.
• Flexibility: You can use the function to run any command that you’d normally run inside a container, giving you the flexibility to initialize data in the way that you see fit.
• Repeatability: You can create a consistent and repeatable process for initializing data for testing, which is useful for maintaining the integrity of your tests over time.

Cons:

This method also has some disadvantages though:

• Limited visibility: Running commands inside a container using the execInContainer() method gives you limited visibility into what’s happening inside the container. If something goes wrong, it can be difficult to determine the cause of the problem. Although ExecResult provides a way to check the result or status of the function call, it provides only basic information such as the exit code, stdout, and stderr output. It won’t be enough for certain cases. For example, if a command fails due to a syntax error, it’s difficult to troubleshoot with only the ExecResult output.
• Potential for errors: When you want to chain multiple commands for execution, you have to write statements that span across multiple lines and chain one beneath the other. This creates a lot of room for making syntactical errors.
• Slower performance: Running commands inside a container using the execInContainer() method can be slower than running them directly on the host machine. This can be an issue if you need to initialize a large amount of data for testing.

Use Cases:

The execInContainer() method works well for executing a small number of commands. For example, you could execute DCL commands to grant or revoke privileges, create an initial set of database object definitions such as creating tables or views, and so on. In general, if you need to execute only simple or few commands, the simplicity and flexibility offered by the execInContainer() method can make it a good choice.

Method 2: Mount Data File into the Container and Run

Mounting one or more data files into the container is another way to initialise data for testing applications against Oracle databases with Testcontainers. We can create a sql script, currency-dataset.sql as follows:

insert into currency (currency,currency_alpha_code,currency_numeric_code) values ('Australian Dollar','AUD',36);
insert into currency (currency,currency_alpha_code,currency_numeric_code) values ('New Zealand Dollar','NZD',554);
insert into currency (currency,currency_alpha_code,currency_numeric_code) values ('Kuwaiti Dinar','KWD',414);
commit;

We can then mounting it into the container so that the database can access the data during the testing process, as illustrated in the example below:

@Test
void assertByMountingFilesInContainer() throws IOException, InterruptedException {
   String dataFileName = "currency-dataset.sql";

   oracleContainer.copyFileToContainer(MountableFile.forClasspathResource(dataFileName),
           "/" + dataFileName);

   String[] command = {"sqlplus", "-s", oracleContainer.getUsername() +
           "/" + oracleContainer.getPassword() + "@//localhost:1521/" + oracleContainer.getDatabaseName(),
           "@/" + dataFileName
   };

   ExecResult execResult = oracleContainer.execInContainer(command);

   System.out.println("execResult is : " + execResult.getStdout());
   System.out.println("execResult error is : " + execResult.getStderr());
   System.out.println("execResult exit code is : " + execResult.getExitCode());

   // Assert the data load action

   List<Integer> currencyList = new ArrayList<>();
   currencyList.add(554);
   List<Currency> currencies = currencyRepository.findAllById(currencyList);
   System.out.println("Number of currencies found: " + currencies.size());
   System.out.println("Fetched currency is : " + currencies.get(0).getCurrency());
   //Thread.sleep(120000);
   assert currencies.size() == 1;
   Assertions.assertEquals("New Zealand Dollar", currencies.get(0).getCurrency());
}

In this example, the data set file currency-dataset.sql that contains database initialisation commands is copied from the host machine to the test docker container using the copyFileToContainer() method. The MountableFile.forClasspathResource() method used as part of the copyFileToContainer() call lets you mount a file from the classpath as a Docker volume in a test container, which is an easy way to provide initialization data or configuration files for the containerized application. Once the file is mounted onto the container, you can use the execInContainer() function to execute the sqlplus command to run the target data set file.

Pros:

The following are the advantages of mounting a data file into the container:

• Faster performance: Since there is less overhead involved in accessing the data file within the container, this method typically results in faster performance compared to the execInContainer() method.
• Improved visibility: Mounting the data file directly into the container gives you improved visibility into the data being initialised, making it easier to debug issues or troubleshoot problems.
• Simplicity: You only need to create the data file on the host machine and then mount it into the container, making this method relatively simple to implement.

Cons:

The following are the downsides of this method:

• Complexity when dealing with several tables or large data: Initializing several tables or large amounts of data may require you to split the data into multiple files for better manageability or perform other preprocessing steps to make it usable by the database.
• Absence of enforcing standardization: You might fall into the trap of mismanaging the folder structure for holding files, file names, or versions, especially if you split files to handle many database commands for different tables or views. No standardization is enforced unless you do it manually.

Use Cases:

Mounting a data file into the container is most appropriate when you need to initialise a large amount of data quickly with good visibility into the data. This method is particularly well suited for when the data can be represented in a file format that can be read by the database, such as an SQL file that you can execute in command using SQL Plus.

Method 3: Flyway Migrations

Using Flyway is a popular approach for initializing data in a database for development, testing, and even production deployment. Flyway is a database migration tool that lets you manage and version control database schema changes. You can spin up a temporary containerized database for testing with Testcontainers and use Flyway to apply the required schema changes and seed the database with test data.

To use Flyway in a Spring Boot project, you’d add the following code in your build file pom.xml:

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>

You’ll then place your database migration scripts in the resources directory in the standardized naming convention enforced by Flyway.

Next, you’d add the spring.flyway.baseline-on-migrate=true property in your application.properties file of the Spring Boot app, which will autocreate a baseline version of the schema history table. This allows Flyway to start with an existing database and initialize the data automatically by executing the database scripts in the resource path directory.

This basic setup lets you proceed with the following test method:

@Test
void assertFlywayDataInitialization() {
   // Assert the data load action

   List<Integer> currencyList = new ArrayList<>();
   currencyList.add(392);
   List<Currency> currencies = currencyRepository.findAllById(currencyList);
   System.out.println("Number of currencies found: " + currencies.size());
   System.out.println("Fetched currency is : " + currencies.get(0).getCurrency());
   //Thread.sleep(120000);
   assert currencies.size() == 1;
   Assertions.assertEquals("Yen", currencies.get(0).getCurrency());
}

Since Flyway initializes the data automatically, the test above simply uses the application repository class to assert the data loaded by Flyway.

Pros:

Using Flyway has the following advantages:

• Structured approach: Flyway provides a structured, repeatable approach to database migrations that makes it easy to manage changes to your database over time. This can be especially useful in a testing environment, where you need to make sure that your database is in a known state before each test is run.
• Improved visibility: Flyway lets you easily view the state of your database and track changes to it over time, making it a good choice for testing environments where visibility into the state of the database is important.
• Versatility: Flyway can be used to initialise data in a wide variety of formats and structures, such as SQL (as demonstrated in this article), CSV, JSON, and so on. Flyway can handle non-SQL format files through custom-developed Java-based migration classes.

Cons:

It also has these disadvantages:

• Considerable learning curve: Getting up and running with Flyway involves a few steps, as you’ve seen in this tutorial. But Flyway can be complex to use for customized requirements, especially for those who are new to database migrations. This can make it a challenging choice for teams that are just getting started with testing their applications against an Oracle database.
• Maintenance burden: Flyway migrations can become difficult to maintain over time, especially if the structure of your database changes frequently. Keeping track of the state of your database and making sure that migrations are updated to reflect any changes can add a maintenance burden to your tests.

Use Cases:

Since Flyway is a robust database initialization library, it works well for meeting complex or customized requirements. However, because it’s challenging to use, it’s most appropriate for teams familiar with database migrations that need a structured, repeatable approach to managing changes to their database over time.

Conclusion

This article discussed three of the most popular methods for initializing data in an Oracle database using Testcontainers. The execInContainer() method is the simplest and is useful for straightforward initialization cases with simple or few commands. The file-mounting approach works well if you need to deal with one or two files with multiple commands. Using Flyway is the most sophisticated approach but also the most challenging to implement. Use it if your application requires a complex data initialization strategy for covering diverse test cases.

You can find the complete source code for this article in this repository.

Testcontainers simplifies the development and testing process and makes it easier to write, run, and automate integration tests by creating isolated environments for data and dependencies. It’s also useful for end-to-end testing, continuous integration and continuous delivery (CI/CD), and automating the testing and deployment process.

Testing against a real Oracle system with Testcontainers can also eliminate environment differences and ensure consistent results across different platforms. For instance, M1 Macs use Apple’s own ARM-based processors. If you have built a Docker image using an M1 Mac, it won’t work directly or as is when applied on Linux machines that use processors based on x86 or x64 architectures.

Testcontainers provides a reliable and flexible solution for testing against real Oracle databases and increases confidence in the applications being tested. If you want to ensure consistent results across different platforms and streamline your testing processes, consider using Testcontainers to test against real Oracle databases.

---

by Rajkumar Venkatasamy

Rajkumar has nearly sixteen years of experience in the software industry as a developer, data modeler, tester, project lead, product consultant, data architect, ETL specialist, and technical architect. Currently he is a principal architect at an MNC. He has hands-on experience in various technologies, tools, and libraries.