What is the best way to reset a database in a known state when testing database operations?

Question

I am writing tests with JUnit for some methods working in a test database.

I need to reset the database to its original state after each @Test . I am wondering how best to do this.

Is there any method in EntityManager ? Or should I just delete everything manually or using an SQL statement? Would it be better to just reset and recreate the entire database?

Answer 1

One of the methods that I used in the past is to recreate the database from scratch by simply copying the database from the standard “test database” and using this in tests.

This technique works if:

• Your scheme does not change much (otherwise it’s a pain to keep in order)
• You are using something like hibernate, which is database independent.

This has the following advantages:

• It works with code that manages its transactions. My integration tests are performed under a unit. For example, when I test a batch process, I call Batch.main () from junit and check the material before and after. I do not want to change the transaction processing in the test code.
• It is fast enough. If the files are small enough, speed is not a problem.
• This makes it easy to run integration tests on the ci server. Database files are checked by code. No need to create a real database.

And the following disadvantages:

• Test database files must be supported with a real database. If you constantly add columns, this can be a pain.
• There is code to manage jdbc urls because they change for each test.

I use this with Oracle as a production / integration database and hsqldb as a test database. It works very well. hsqldb is a single file, so it is easy to copy.

So, in @Before, using hsqldb, you copy the file to a place like target / it / database / name_of_test.script. This is confirmed in the test.

In @ After that, you delete the file (or just leave it to who cares). With hsqldb, you'll also need SHUTDOWN so you can delete the file.

You can also use @Rule, which extends from ExternalResource, which is the best way to manage your resources.

One more tip: if you use maven or something like this, you can create the database in the target. I am using target / it. This way, the copies of the databases are deleted when I do, and mvn clean. For my batches, I actually copy all my other property files, etc. To this directory, so I do not receive any files in strange places.

Answer 2

The easiest way is to simply discard all changes after each test. This requires a transactional RDBMS and a custom test runner or the like that brings each test to its own transaction. Spring AbstractTransactionalJUnit4SpringContextTests does just that.

Answer 3

DBUnit can reset your database between tests and even populate it with predefined test data.

Answer 4

I answer this more for my own reference, but here it goes. The answer assumes a SQL Server database for each developer.

Basic approach

• Use DBUnit to store an XML file of known state. You can extract this file after you have configured the database, or you can create it from scratch. Put this file in your version control along with the scripts that invoke DBUnit to populate the database with it.

• In your tests, call the above scripts using @Before .

Acceleration 1

Once this works, set up an approach to speed things up. Here is an approach to a SQL Server database.

Before DBUnit completely destroy the database:

 EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'; EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'; EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON DELETE FROM ?'; 

After DBUnit, Restore Constraints

 EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'; EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'; 

Acceleration 2

Use SQL Server RESTORE functionality. In my tests, this runs at 25% of the time that DBUnit takes. If (and only if) this is an important factor in the duration of the test, you should study this approach.

The following classes show implementation using Spring JDBC, JTDS, and CDI injections. This is intended to work in tests inside the container, where the container can create its own database connections that need to be stopped.

 import java.io.File; import java.sql.SQLException; import javax.inject.Inject; import javax.sql.DataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.jdbc.core.JdbcTemplate; /** * Allows the DB to be reset quickly using SQL restore, at the price of * additional complexity. Recommended to vanilla DBUnit unless speed is * necessary. * * @author aocathain * */ @SuppressWarnings({ "PMD.SignatureDeclareThrowsException" }) public abstract class DbResetterSO { protected final Logger logger = LoggerFactory.getLogger(getClass()); /** * Deliberately created in the target dir, so that on mvn clean, it is * deleted and will be recreated. */ private final File backupFile = new File( "target\\test-classes\\db-backup.bak"); @Inject private OtherDbConnections otherDbConnections; /** * Backs up the database, if a backup doesn't exist. * * @param masterDataSource * a datasource with sufficient rights to do RESTORE DATABASE. It * must not be connected to the database being restored, so * should have db master as its default db. */ public void backup(final DataSource masterDataSource) throws Exception { final JdbcTemplate masterJdbcTemplate = new JdbcTemplate( masterDataSource); if (backupFile.exists()) { logger.debug("File {} already exists, not backing up", backupFile); } else { otherDbConnections.start(); setupDbWithDbUnit(); otherDbConnections.stop(); logger.debug("Backing up"); masterJdbcTemplate.execute("BACKUP DATABASE [" + getDbName() + "] TO DISK ='" + backupFile.getAbsolutePath() + "'"); logger.debug("Finished backing up"); otherDbConnections.start(); } } /** * Restores the database * * @param masterDataSource * a datasource with sufficient rights to do RESTORE DATABASE. It * must not be connected to the database being restored, so * should have db master as its default db. */ public void restore(final DataSource masterDataSource) throws SQLException { final JdbcTemplate masterJdbcTemplate = new JdbcTemplate( masterDataSource); if (!backupFile.exists()) { throw new IllegalStateException(backupFile.getAbsolutePath() + " must have been created already"); } otherDbConnections.stop(); logger.debug("Setting to single user"); masterJdbcTemplate.execute("ALTER DATABASE [" + getDbName() + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"); logger.info("Restoring"); masterJdbcTemplate.execute("RESTORE DATABASE [" + getDbName() + "] FROM DISK ='" + backupFile.getAbsolutePath() + "' WITH REPLACE"); logger.debug("Setting to multi user"); masterJdbcTemplate.execute("ALTER DATABASE [" + getDbName() + "] SET MULTI_USER;"); otherDbConnections.start(); } /** * @return Name of the DB on the SQL server instance */ protected abstract String getDbName(); /** * Sets up the DB to the required known state. Can be slow, since it only * run once, during the initial backup. Can use the DB connections from otherDbConnections. */ protected abstract void setupDbWithDbUnit() throws Exception; } import java.sql.SQLException; /** * To SQL RESTORE the db, all other connections to that DB must be stopped. Implementations of this interface must * have control of all other connections. * * @author aocathain * */ public interface OtherDbConnections { /** * Restarts all connections */ void start() throws SQLException; /** * Stops all connections */ void stop() throws SQLException; } import java.sql.Connection; import java.sql.SQLException; import javax.annotation.PostConstruct; import javax.annotation.PreDestroy; import javax.enterprise.inject.Produces; import javax.inject.Named; import javax.inject.Singleton; import javax.sql.DataSource; import net.sourceforge.jtds.jdbcx.JtdsDataSource; import org.springframework.jdbc.datasource.DelegatingDataSource; import org.springframework.jdbc.datasource.SingleConnectionDataSource; /** * Implements OtherDbConnections for the DbResetter and provides the DataSource during in-container tests. * * @author aocathain * */ @Singleton @SuppressWarnings({ "PMD.AvoidUsingVolatile" }) public abstract class ResettableDataSourceProviderSO implements OtherDbConnections { private volatile Connection connection; private volatile SingleConnectionDataSource scds; private final DelegatingDataSource dgds = new DelegatingDataSource(); @Produces @Named("in-container-ds") public DataSource resettableDatasource() throws SQLException { return dgds; } @Override @PostConstruct public void start() throws SQLException { final JtdsDataSource ds = new JtdsDataSource(); ds.setServerName("localhost"); ds.setDatabaseName(dbName()); connection = ds.getConnection(username(), password()); scds = new SingleConnectionDataSource(connection, true); dgds.setTargetDataSource(scds); } protected abstract String password(); protected abstract String username(); protected abstract String dbName(); @Override @PreDestroy public void stop() throws SQLException { if (null != connection) { scds.destroy(); connection.close(); } } }