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

<dependency>
   <groupId>com.opentable.components</groupId>
   <artifactId>otj-pg-embedded</artifactId>
   <version>0.11.4</version>
   <scope>test</scope>
 </dependency>
Open table provides a great library for embedding postgres. click here

Abstract Integration Test

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Initialiser.class)
@TestPropertySource("classpath:application-test.properties")
public class AbstractIntegrationTest {

  private static EmbeddedPostgres embeddedPostgres;

  @BeforeClass
  public static void initialise() throws Exception{
    if(embeddedPostgres == null) {
      //Create an instance of embedded postgress
      embeddedPostgres = EmbeddedPostgres.builder()
                                         .setPort(5433).start();

      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

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5433/EXAMPLEDB
spring.datasource.username=postgres
spring.datasource.password=password
Since we are using spring boot, we put connection string in application-test.properties

Junit DB Test

public class SomeDaoTest extends AbstractIntegrationTest {  

   @Autowired DataSource dataSource;
   
   @Test 
   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.

3 comments:

  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(EmbeddedPostgres.java:585)
    at com.opentable.db.postgres.embedded.EmbeddedPostgres.initdb(EmbeddedPostgres.java:227)
    at com.opentable.db.postgres.embedded.EmbeddedPostgres.(EmbeddedPostgres.java:145)
    at com.opentable.db.postgres.embedded.EmbeddedPostgres$Builder.start(EmbeddedPostgres.java:572)
    at com.vmware.postgresql.AbstractIntegrationTest.initialise(AbstractIntegrationTest.java:26)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

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

      Delete
  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

    ReplyDelete