Wednesday, 4 April 2018

Testing database querries with embedded Postgres

In many database driven applications it can be useful to automate the testing of SQL against a real database. However setting up and maintaining a database on a build server isn't ideal. Databases such as Postgres and MySQL provide embeddable variants, which can be used for testing purposes. A database instance can be created for testing and then removed.

The example below shows how this can be done with postgres within a spring boot application. This provides a clean state for testing each time. The database state would usually be set up using a db migration library such as flyway, however that is out of scope for this example, see here for more on flyway and spring boot.

Maven Dependency

Open table provides a great library for embedding postgres. click here

Abstract Integration Test

@SpringBootTest(classes = Initialiser.class)
public class AbstractIntegrationTest {

  private static EmbeddedPostgres embeddedPostgres;

  public static void initialise() throws Exception{
    if(embeddedPostgres == null) {
      //Create an instance of embedded postgress
      embeddedPostgres = EmbeddedPostgres.builder()

      try (Connection conn = embeddedPostgres.getPostgresDatabase().getConnection()) {
        Statement statement = conn.createStatement();
        statement.execute("CREATE DATABASE EXAMPLEDB");
All test interacting with the database will extend this class. This assures that a single postgres is created per jvm. On port 5433. We then create a database named EXAMPLEDB.

Application Properties

Since we are using spring boot, we put connection string in

Junit DB Test

public class SomeDaoTest extends AbstractIntegrationTest {  

   @Autowired DataSource dataSource;
   public void exampleTest(){
     try(Connection conn = dataSource.getConnection()){
       Statement statement = conn.createStatement();
       statement.executeQuery("SELECT * FROM EXAMPLE_TABLE")
     catch (Exception ex){
       throw new IllegalStateException(ex);


Now we can run tests against the db like above. The above test should fail since the table does not exist.


  1. I keep getting below errors, any idea how to resolve this:

    INFO: Detected a Darwin x86_64 system
    Oct 11, 2018 5:02:28 PM com.opentable.db.postgres.embedded.EmbeddedPostgres prepareBinaries
    INFO: Postgres binaries at /var/folders/q8/my3v_jgs2qg7tdfn2gkclp3h0000gq/T/embedded-pg/PG-76e3fe6dbd25aadb17522eed9421df02

    java.lang.IllegalStateException: Process [/var/folders/q8/my3v_jgs2qg7tdfn2gkclp3h0000gq/T/embedded-pg/PG-76e3fe6dbd25aadb17522eed9421df02/bin/initdb, -A, trust, -U, postgres, -D, /var/folders/q8/my3v_jgs2qg7tdfn2gkclp3h0000gq/T/epg7314782493963180383, -E, UTF-8] failed

    at com.opentable.db.postgres.embedded.EmbeddedPostgres.system(
    at com.opentable.db.postgres.embedded.EmbeddedPostgres.initdb(
    at com.opentable.db.postgres.embedded.EmbeddedPostgres.(
    at com.opentable.db.postgres.embedded.EmbeddedPostgres$Builder.start(
    at com.vmware.postgresql.AbstractIntegrationTest.initialise(
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(
    at java.lang.reflect.Method.invoke(
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(
    at org.junit.internal.runners.statements.RunBefores.evaluate(
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(
    at com.intellij.rt.execution.junit.JUnitStarter.main(

    1. If you are on MacOS then brew install postgresql should fix this

  2. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work! single board computer