Navigate back to the homepage

Using Testcontainers for Integration Testing

Mark Brown
October 20th, 2020 · 2 min read

👋 Introduction

As a follow on from my last post about running sql server in docker, I thought i’d write about something that I have just introduced for the first time into one of my projects – in the hope that some of you feel as passionate as I do about testing your production code (and not just the equivalent in H2-specific syntax 😱). I hope that through this post I can show you how easy it is to include this dependency into your project, and be on your way to having your integration tests running against your production database!

You can use any resource in your tests that has a docker image (E.g. databases, web browsers, message queues, etc) but in this post I will be outlining how to include testcontainers’ own MS SQL Server Module alongside Spock to write your integration tests.

All code in this post is available open source under the MIT licence on my GitHub, so please feel free to check it out use it as you wish for your own projects.

🧐 Requirements

In order to use testcontainers, you will of course need to have Docker installed and running. You will also need to add the the testcontainer depencies for MSSQLServer, for example (in Gradle);

1testCompile 'org.testcontainers:testcontainers:1.14.3'
2testCompile 'org.testcontainers:mssqlserver:1.14.3'

Since MSSQL Server is a licensed product – you will also need to accept the license or the container will fail to start – to do this simply place a file named container-license-acceptance.txt into test resources folder.

✍️ Writing your first test

Now that you have the testcontainer dependency on your classpath, its time to actually use it!

Define an Initializer

An Initializer – simply put – is what tells the ApplicationContext where your db is – by nature the Docker container gets a randomly allocated port so this cannot be statically set (or rather, should not be to avoid port collisions on startup).

1import org.springframework.context.ApplicationContextInitializer
2import org.springframework.context.ConfigurableApplicationContext
3import org.springframework.context.annotation.Profile
4import org.springframework.core.env.ConfigurableEnvironment
5import org.springframework.core.env.PropertiesPropertySource
6import org.springframework.test.context.ContextConfiguration
7import org.testcontainers.containers.MSSQLServerContainer
8import spock.lang.Shared
9import spock.lang.Specification
10
11import java.sql.Connection
12import java.sql.DriverManager
13import java.sql.PreparedStatement
14
15@Profile("mssql-test")
16@ContextConfiguration(initializers = Initializer.class)
17class SqlSpec extends Specification {
18
19 @Shared
20 static MSSQLServerContainer mssqlServerContainer
21
22 static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
23 @Override
24 void initialize(ConfigurableApplicationContext configurableApplicationContext) {
25
26 mssqlServerContainer = new MSSQLServerContainer()
27 mssqlServerContainer.start()
28
29 ConfigurableEnvironment environment = configurableApplicationContext.getEnvironment()
30
31 String db = environment.getProperty("test.db.name")
32 String connectionString = "${mssqlServerContainer.getJdbcUrl()};integratedSecurity=false;username=${mssqlServerContainer.getUsername()};password=${mssqlServerContainer.getPassword()}"
33 String jdbcUrl = connectionString + ";databaseName=${db}"
34
35 Connection connection = DriverManager.getConnection(connectionString)
36 PreparedStatement ps = connection.prepareStatement("IF(db_id(N'${db}') IS NULL) CREATE DATABASE ${db}")
37 ps.execute()
38
39 Properties props = new Properties()
40 props.put("spring.datasource.driver-class-name", mssqlServerContainer.getDriverClassName())
41 props.put("spring.datasource.url", jdbcUrl)
42
43 environment.getPropertySources().addFirst(new PropertiesPropertySource("myTestDBProps", props))
44 configurableApplicationContext.setEnvironment(environment);
45 }
46 }
47}

Above you can see how I initialise my ApplicationContext, first I manually start the MSSQL container, I then grab the JDBC url in order for me to set the spring.datasource.url – and that’s basically it! I have also added a few extras, like reading the database name from a properties file and creating the database with a PreparedStatement, neither of these are required – and in fact you may want to consider disabling spring.jpa.hibernate.ddl-auto entirely as it is not advised to use in production – instead opting for some other schema management solution, for example Flyway.

Use the test container in a test class

Now that you have defined an Initializer, you can use class inheritance for test execution.

1@SpringBootTest
2@ActiveProfiles("mssql-test")
3class SqlserverTests extends SqlSpec {
4 // Test functions
5}

Running your tests

With test containers set up for the test suite, running tests is easy – it just works! As you can see in the log output below, test containers takes it from here – It pulls the MSSQL Server image (if it does not exist), starts it, and then starts running your tests.

1. ____ _ __ _ _
2 /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
3( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
4 \\/ ___)| |_)| | | | | || (_| | ) ) ) )
5 ' |____| .__|_| |_|_| |_\__, | / / / /
6 =========|_|==============|___/=/_/_/_/
7 :: Spring Boot :: (v2.3.4.RELEASE)
8
92020-10-20 21:04:33.768 INFO 14778 --- [ main] o.t.d.DockerClientProviderStrategy : Loaded org.testcontainers.dockerclient.UnixSocketClientProviderStrategy from ~/.testcontainers.properties, will try it first
102020-10-20 21:04:34.339 INFO 14778 --- [ main] o.t.d.UnixSocketClientProviderStrategy : Accessing docker with local Unix socket
112020-10-20 21:04:34.339 INFO 14778 --- [ main] o.t.d.DockerClientProviderStrategy : Found Docker environment with local Unix socket (unix:///var/run/docker.sock)
122020-10-20 21:04:34.439 INFO 14778 --- [ main] org.testcontainers.DockerClientFactory : Docker host IP address is localhost
132020-10-20 21:04:34.470 INFO 14778 --- [ main] org.testcontainers.DockerClientFactory : Connected to docker:
14 Server Version: 19.03.12
15 API Version: 1.40
16 Operating System: Docker Desktop
17 Total Memory: 1990 MB
182020-10-20 21:04:34.577 INFO 14778 --- [ main] o.t.utility.RegistryAuthLocator : Credential helper/store (docker-credential-desktop) does not have credentials for index.docker.io
192020-10-20 21:04:34.988 INFO 14778 --- [ main] org.testcontainers.DockerClientFactory : Ryuk started - will monitor and terminate Testcontainers containers on JVM exit
202020-10-20 21:04:34.988 INFO 14778 --- [ main] org.testcontainers.DockerClientFactory : Checking the system...
212020-10-20 21:04:34.988 INFO 14778 --- [ main] org.testcontainers.DockerClientFactory : ✔︎ Docker server version should be at least 1.6.0
222020-10-20 21:04:35.073 INFO 14778 --- [ main] org.testcontainers.DockerClientFactory : ✔︎ Docker environment should have more than 2GB free disk space
232020-10-20 21:04:35.090 INFO 14778 --- [ main] ?.microsoft.com/mssql/server:2017-CU12] : Creating container for image: mcr.microsoft.com/mssql/server:2017-CU12
242020-10-20 21:04:35.129 INFO 14778 --- [ main] o.t.utility.RegistryAuthLocator : Credential helper/store (docker-credential-desktop) does not have credentials for mcr.microsoft.com
252020-10-20 21:04:35.181 INFO 14778 --- [ main] ?.microsoft.com/mssql/server:2017-CU12] : Starting container with ID: f5b16e1334fbc830620e946267359e522e998238466f279dbf57b33068da7a1a
262020-10-20 21:04:35.428 INFO 14778 --- [ main] ?.microsoft.com/mssql/server:2017-CU12] : Container mcr.microsoft.com/mssql/server:2017-CU12 is starting: f5b16e1334fbc830620e946267359e522e998238466f279dbf57b33068da7a1a
272020-10-20 21:04:35.438 INFO 14778 --- [ main] ?.microsoft.com/mssql/server:2017-CU12] : Waiting for database connection to become available at jdbc:sqlserver://localhost:32769 using query 'SELECT 1'
282020-10-20 21:04:35.566 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: eb4c676c-2bb2-4c47-8df0-ea0110c3fd0e Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
292020-10-20 21:04:35.674 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: c0fb5082-3b6b-4f27-82fa-d753e7fe0a38 Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
302020-10-20 21:04:35.879 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: b70dfe25-bd4a-4c5a-9975-5decf0969878 Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
312020-10-20 21:04:36.283 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: 049fe67a-0566-490b-88cf-db04e5f67be1 Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
322020-10-20 21:04:37.090 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: 2a6e9f21-ce7d-4552-83e3-bf10d121d4fb Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
332020-10-20 21:04:38.095 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: 68422ef9-cad0-4e55-8908-7ebef0a28e4c Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
342020-10-20 21:04:39.104 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: eb1cbb36-f853-4da8-9f39-e079b2eb908f Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
352020-10-20 21:04:40.107 WARN 14778 --- [ main] c.m.s.j.internals.SQLServerConnection : ConnectionID:1 ClientConnectionId: f7cde6b9-1ed2-4b1c-8bc2-cc7969f9907d Prelogin error: host localhost port 32769 Unexpected end of prelogin response after 0 bytes read
362020-10-20 21:04:41.305 INFO 14778 --- [ main] ?.microsoft.com/mssql/server:2017-CU12] : Container is started (JDBC URL: jdbc:sqlserver://localhost:32769)
372020-10-20 21:04:41.306 INFO 14778 --- [ main] ?.microsoft.com/mssql/server:2017-CU12] : Container mcr.microsoft.com/mssql/server:2017-CU12 started in PT6.215744S
382020-10-20 21:04:42.181 INFO 14778 --- [ main] c.m.e.TestContainers.SqlserverTests : Starting SqlserverTests on 127.0.0.1 with PID 14778 (started by mtjb in /blog-code-examples)

🧪 Conclusion

As you can see – if just a few short steps you can add the testcontainers dependency to your project, and an Initializer, and be on your way to writing your first test against MSSQL Server. At the time of writing this I have only been using testcontainers for a few weeks, but it is already finding more and more uses every day when it comes to automating some of our features – no longer do we have the excuse; “If we do it that way I can’t test that in H2”!

More articles from Mark Brown

Running Microsoft SQL Server.. on a Mac!

A few years ago I switched my main work machine from Windows to Mac – despite the reliance I have on SQL Server to run our application (Well, there’s also the dev-only H2 database, but even then I knew that wouldn’t fly due to some of the ‘subtle’ differences).

October 6th, 2020 · 1 min read

Introduction to Indexes, and the Primary Key

Database indexing is a development task. The most important information for indexing is not the storage system, or the configuration of the server - but instead how the application queries the data. This knowledge is not easily obtained from DBAs or external consultants - so it's on us, the developers.

August 18th, 2021 · 4 min read
© 2020–2021 Mark Brown
Link to $https://twitter.com/marktjbrownLink to $https://github.com/mtjbLink to $https://instagram.com/marktjbrownLink to $https://www.linkedin.com/in/mark-brown-9952b4a8/