We have a big test suite with around 300+ tests. Nowadays we are manually restoring database and creating few Data Objects and hard coding some data to feed into the tests. However, there are a lot of tests that need more data and we are trying to generate them run time with tests. Problem is when database changes or the test suite run on a different machine with a different version of the database they are failing. My question is

  • What is the best practice to handle this scenario?

There has to be some way to handle the database known state. Just don't know what it is.

We are using C#, NUnit and Selenium WebDriver for UI testings

3 Answers 3


As UI tests are slowish I think its important to be able to run them in parallel. In order to be able to run them in parallel, your data-management becomes even more complex, since if the tests use the same data-source they could change data into conflicting states and make tests randomly fail, that why the tests should all run in isolation.

Currently, I am working on a new setup which looks a bit like the following.


  • Minimal dataset needed for all the tests (Generated from SQL scripts)
  • Functions to generate users and test-data from code, preferable re-using the actual internal classes of the application. (To make sure when the application changes the generation of data also changes)
  • Testsets classes that describe and set up the actual test-data
  • Testsets are generated into a backup file and the code checks if the backup is still valid based on a delta number which indicates the db version (updated by the developers for each database change).

Each test run does the following:

  • Test initialize
    • Check if the backup datasets are still valid and generate new data if needed
    • Generate a unique id for the test
    • Restore a testset into a db with the unique id in the name
    • Configure application/test to use the correct DB
  • Run the test
    • Arrange (login, prepare extra application state, etc)
    • Act (Actions to test)
    • Assert (Verify actions are successful)
  • Test clean up
    • Remove DB
    • Check logs for errors

Restoring the database takes less than a second on our SSD's, not too much overhead here. For datasets that takes long to generate from scratch, we save the appended dataset to a separate backup file and restore it for each test run that needs it.

Because each test setups and uses its own personal database the test can be run anywhere and always work. The build server can now push the tests to multiple nodes and each test does not interfere with the other tests.

Developers running the tests do not need to setup any data locally, because the tests will do it for them. We made a separate test runner which setups a testset locally so people can use it to run tests against it manually to reproduce a found issue.

Additional informations for your specific situation:

  • Hey man. Thanks for the answer. How do you accomplish Functions to generate users and test-data from code, preferable re-using the actual internal classes of the application. (To make sure when the application changes the generation of data also changes) with Selenium or sql and what's your suggestion
    – saifur
    Feb 23, 2015 at 13:58
  • You could create a users class with a method createUser("name", "password", "otherdetailsyouneedfortest"). This method could use the application code to generate the user in the system, similar to the code executed when a users is using the maintenance screens to create users or by executing a SQL query. I would prefer not to use SQL queries, because if the data model changes you need to update two locations. The original code and the test-data generation. Maybe you can ask local application developers to help you setup something like this? Feb 23, 2015 at 19:19

This is a really interesting question. I don't think we're entirely happy with our approach, and we discuss it regularly, but these are our constraints and what we've done so far (framework has been in use for about 3 years).

1) We reload a skeleton database at the start of each test run. This has a minimal set of data, as little as possible - a user, minimal config. At one point we had an automated suite to generate this database regularly to ensure it stayed in sync.

  • We don't reload the DB for each test, because we don't want to slow down our test run.
  • At the time we started the framework, the underlying database was changing quite rapidly, and writing SQL scripts to modify the db directly would have required a lot of maintenance. It is still changing, just not as rapidly.

2) The general approach is that tests create the data they need via the API (e.g. customers, orders, etc), and if they make breaking changes (e.g. to config), then they need to undo them at the end of the test. Being able to use the API to set up data rather than the UI makes this much faster to do at run time. We also know the data will be representative, as that's how the UI creates the data (for the most part) so we don't have to worry about our test data gradually diverging from real live data.

The issue we're facing now is that even the very minimal set of config that we have is restrictive - so we need to consider how we can generate different config, either by having multiple different skeletons, or by being able to modify that config at runtime.

It is pretty easy to run the tests on another machine with a different version of the database - we just need to set up the automation user and change a few config settings (if they're needed by the subset of tests we're running). We have very little dependency on the actual underlying DB design - you could change 99% of the tables, and as long as the application code still worked, we'd still be able to run our tests. This has meant that for example, we have been able to use the UI tests to detect issues when making database changes in areas that aren't well covered by integration tests. This might not be an issue for you.

  • I appreciate you answer this. How do you accomplish ` We reload a skeleton database at the start of each test run`?
    – saifur
    Feb 23, 2015 at 13:55
  • 1
    In this case, we actually don't do that within the test framework - we run the tests from our CI server, and have a step that reloads the db in there (just something like "mysql testaccount < skeleton_database.sql"). We do actually have a service written by the developers for use of the integration tests that we could use to run SQL against the db - so I guess we could use that if we wanted to call it from within the test framework, but as it happens we don't.
    – testerab
    Feb 23, 2015 at 18:54

It's hard to provide input on your specific processes, but here's how I generally deal with Selenium automation. Maybe it can help you out.

  1. One separate database which contains all the data for the tests: data-driven testing. This means you'll likely have to compose Views (combining tables as necessary) which serve as the data source for test methods.
  2. Insert/Update/Delete scripts as a stored procedure on the application's database. Have your test class execute these scripts before running any tests. This way you force the 'variable' data in the application to a specific state which your automated tests expect.

Explanatory example:

  1. For a test method 'Customer login', the Selenium database provides [email protected] and password 1234. So the test method expects a customer profile screen after logging in with this data (if not it will fail).
  2. The insert/update/delete scripts are executed before running the case. It will (1) delete the existing [email protected] profile in the application database, then (2) insert the profile with the correct password and settings. This way we know for sure our test data is correct. If we don't execute this script, the profile could've been changed by manual tests, the password can be obsolete, ...
  • Thanks for your valuable answer. I sort of do this now. The problem with this though ours is a payroll application and a lot of data we need in order to replicate user behavior. If I want to write my insert/update scripts, seems like it would be lot more work than just the actual writing the test since the sql queries sometime will be massive. So I wanted to know a database known state as baseline and take it from there.
    – saifur
    Feb 21, 2015 at 15:01
  • 2
    @saifur in that case I would recommend taking a database snapshot which you can easily (manually) restore before starting the test suite.
    – FDM
    Feb 21, 2015 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.