In my previous team, we had come up with a pretty neat way of writing database integration tests: run sql queries under a transaction and simply rollback once the test is complete!

When writing integration and/or end-to-end tests for an application that interacts with a database, we usually need to bootstrap it with some test data. Ideally, we would also like to clean up test data at the end of a test run for predictable future runs. Note that depending upon the application, it might be possible to get away with only ever creating data, e.g. for a user-centric application, every test need only create data for a particular user and continue referencing data linked to that user. However, most of the time, we need to be diligent about cleaning up the database at the end of a test run.

Don't let dilbert get involved...

In order to make writing tests simpler and reduce boilerplate, we can use a sql connection under a transaction at the start of the test and then simply roll back the connection at the end thereby avoiding the need for an explicit cleanup. This can also be achieved with a JUnit rule:

public class SqlRollingBackConnectionPool extends ExternalResource {

    public SqlRollingBackConnectionPool(String environment) {
        // initialize environment
    }

    @Override
    protected void before() throws Throwable {
        super.before();
        
        // initialize connection pool here, e.g.
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
        cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
        cpds.setUser("admin");
        cpds.setPassword("admin-password");

        cpds.setAutocommit(false); // don't commit by default
    }

    @Override
    protected void after() {
        try {
            // rollback all operations and close connection pool, e.g.
            cpds.close(); 
            // note that by default c3p0 rolls back unresolved transactional work on close. 
        } catch (Exception e) {
          logger.error("Error on cleaning up after test", e);
        } finally {
            super.after();
        }
    }
    
    public SqlConnection getSqlConnection() {
        // start transaction and return sql connection
        SqlConnection con = cpds.getConnection();
        con.startTransaction();
        return con;
    }
}

The above rule can then be used in the unit tests as follows:

public class UserDbServiceTest {

    private SqlConnection sqlConnection;
    private UserDbService sut;

    @Rule
    public final SqlRollingBackConnectionPool pool = new SqlRollingBackConnectionPool("integration");

    @Before
    public final void setUp() throws Exception {
        sqlConnection = pool.getSqlConnection();
        sut = new UserDbService(sqlConnection);
    }

    // tests below, make sure that you don't call commit on the sql connection
}

Testing features that use a sql transaction themselves would probably require manual deletion of the data at the end of the test. This can also be achieved by setting the autocommit property of the connection to true at the start. Just make sure to set it back to false before returning the connection to the pool. Happy testing!