Controlling database state in Selenium UI Testing

Question

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

Answer 1

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.

Requirements:

• 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:

• How to use the setup and teardown in nUnit
• Restoring database from C#

Answer 2

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.

Answer 3

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, ...