Snapshotting PostgreSQL databases with templates using Spring JDBC
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.