How to reset your test automation database?

Since the web application you test probably contains dynamic data, we need a way to reset your test automation database back to scratch every time your test suite runs.

This is because some of your tests will most likely post test data to your application’s database, which changes the state of your application.

Next time you run the same test suite again, any previous test assertion PASS may suddenly result in a FAILURE. Not because your application is broken, but simply because your first test run changed certain data that makes subsequent test runs fail.

This is something we want to avoid under all circumstances, because the last thing you want is to chase bugs in your system that do not even exist. Here is how you do it:

<?xml version="1.0" encoding="UTF-8" ?>
<testsuite>
    <fixture>
        <setup label="Reset DB"><![CDATA[mysql --host={YOUR_DB_HOST} --user={YOUR_DB_USER} --password={YOUR_DB_PW} {YOUR_DB_NAME} < {YOUR_DB_SQL_PATH}.sql]]></setup>
    </fixture>
    <userstory title="Test that I can reset the test database">
        <scenario>
            <step title="Submit a 'Test Comment'">
                <url>http://automateyourtests.com/demo_application/wp-comments-post.php</url>
                <post><![CDATA[comment=Test+Comment&comment_post_ID=1]]></post>
            </step>
            <step title="Test that 'Test Comment' appears on the web page">
                <url>http://automateyourtests.com/demo_application/uncategorized/hello-world</url>
                <contains>Test Comment</contains>
            </step>
            <step title="Reset the test database in between">
                <setup>Reset DB</setup>
            </step>
            <step title="Finally test that 'Test Comment' does NOT appear on the web page anymore, because in the last step I reset the database back to its original state">
                <url>http://automateyourtests.com/demo_application/</url>
                <notcontains>Test Comment</notcontains>
            </step>
        </scenario>
    </userstory>
</testsuite>

I recommend to reset your automated testing database at least once every time your test suite runs, but often you may even want to reset it again and again in between of some of your test scenarios.

Resetting the application’s database should only be done on a system where no other users are dependant on their data. Ideally, you will have a dedicated test server for your application with a dedicated test database.

This test database you may want to carefully populate with test seed data, which best facilitates your actual test scenarios (e.g. 3 users, 2 user groups, 3 products, 2 product categories …).