Browsed by
Tag: database

Snapshotting PostgreSQL databases with templates using Spring JDBC

Snapshotting PostgreSQL databases with templates using Spring JDBC

postgresql logo Nowadays with the rise of more and more complex systems software testing is more important then ever. This not only includes unit testing but also integration-, end to end- and acceptance tests. For some of these test scenarios it is important to have a defined state of your database which could be reproduced in example between different test suites. To achieve this goal there are different options. It is possible to simply rebuild a docker container in which the database runs and migrate to the actual state with a tool like flyway (here I described how to do that with a Java EE application) but this may take some time and seems only suitable between independent test runs. Another option would be to delete all table data (truncate) and repopulate all the needed test data with appropriate SQL Scripts. This should be the fastest option but it has the disadvantage that the SQL scripts has to be maintained manually and you could only reset a state that is kept in this SQL scripts.
When I was faced with this task I recently used a nice feature of the PostgreSQL database for a snapshot like behaviour. Postgres comes with a build-in template mechanism which is used when a new database is created. For this a freshly installed Postgres instance comes with a default database named ‘postgres’ and two templates named ‘template0’ and ‘template1’. The idea is that you can modify template1 as you like to suite your needs for creating new empty databases and let template0 as it is to still have a plain default. You can read more about this in the Postgres documentation. Additional to that it is possible to create your own custom templates which can be copies of actual databases. By doing that you can effectively “roll back” at any time later. In my case this was really fast (your mileage may vary depending on the size of your database), had a minimum of code to be written and comes with the benefit that it is possible to make the template at runtime when the tester has created a state he would like to reproduce.

I chose to do all the needed operations with Spring JDBC as it is very convenient because the use of the JdbcTemplate handles all the connecting/disconnecting and error handling for you. All you need to do is to pass it a datasource and use standard SQL statements as query parameters for the template. To learn more about it you can take a look at the excellent Spring documentation about data access with JDBC but I guess you will be able to follow my code snippets here as well.

@Component
public class DefaultDataSource extends DriverManagerDataSource {

  @PostConstruct
  public void init() {
    this.setDriverClassName("org.postgresql.Driver");
    this.setUrl("jdbc:postgresql://localhost:5432/postgres");
    this.setUsername("user");
    this.setPassword("password");
  }
}

This class defines a datasource for a standard Postgres db running on your local machine at the standard port. The @Component annotation is used by Spring  to identify it as a Spring bean which makes it injectable via the @Autowired annotation while the @PostConstruct annotation gets the init method called after construction of the object.

Read More Read More

Integrating Flyway with Java EE and using its datasource

Integrating Flyway with Java EE and using its datasource

Flyway is my favourite tool when it comes to database migrations because its convention over configuration approach makes it fairly easy to use while bootstrapping and configuration is reduced to a minimum. For those of you who don’t know anything about database migration tools (evolutionary database design) in short the concept is to track SQL scripts with your source code which reflect the actual version of your database that matches the code version to run properly. This could be done without any framework but what tools like Flyway or liquibase bring to the table is the ability to check automatically at build- or start-time if the database is at the latest available version and if not run all available SQL scripts from the actual database version to the newest one available. Flyway can be used with all the major SQL dialects, many different relational databases and has plugins for a huge variety of frameworks to be integrated into. For more detailed information visit the projects website.

As I’m a heavy Java EE user I ran into the problem that Java EE has no special support by Flyway. My problems with this are that there is no build in way to get the actual JPA datasource (defined in the persistence.xml) and there is no solution provided that wires Flyway in the startup process to run the migration scripts at startup. Flyway provides integration plugins with this functionalities for other frameworks like Spring Boot but fortunately it is not that complicated to realise the same thing in Java EE.

In many scenarios it is no problem to trigger Flyway in the right moment as it is possible to integrate it in the maven build process or start it manually via the command line but both solutions doesn’t fit my needs. I want to have Flyway check my database at startup as the production server is not the server where the build process happens and I want to avoid a situation where I have to execute a shell command manually after deploying a new version. For this cases Flyway can be started from the source code as well. As it has to be run before the application starts and tries using the database you have to make sure that the Flyway scripts are run before that. I found two solutions to make this work. The first one uses Hibernates “Service Provider Interface” (SPI) -I use WildFly in this example which comes with Hibernate- to register a new integrator and grab the datasource via a little bit of reflection code while the second approach (which is my favourited solution) defines a new singleton bean which uses @resource injection to get the datasource.

Hibernate SPI integrator solution

Read More Read More