Automated database testing with Nightwatch.js
When to use automated database testing
Sometimes the most effective way to validate the pass or fail criteria of automated tests written in the UI with frameworks such as Nightwatch.js or Selenium-webdriver is by writing assertions against the database in your test cases directly.
For example, in a typical automated test case you may create a new account, try to login as the user, and verify the user profile page has the information you signed up with.
What if some of the information the requirements are supposed to validate aren't observable in the front end?
You'd need a way to query the database and assert that the correct values made it there.
In addition, navigating through the UI can be time consuming and may be immaterial to your test case. If you are only interested that the new user record is created after filling in a form you can assert against a database query immediately after your submit the form. This saves time on test execution and since there are less steps in the UI this pattern lends itself to being more reliable and repeatable.
Examples of when to use database test assertions from the webdriver
- Cases where the database logic is coded into the view
- When results are not observable in the UI
- When unit and integration tests can't be used
- To save time and increase reliability
This database testing tutorial will teach database testing with Nightwatch.js custom assertions
Nightwatch.js custom assertions for MSSQL
One way to verify the correct database records exist after your automated test actions is to verify a record count matching your WHERE clause. Using Nightwatch.js custom assertions you can assert database record counts as pass and fail criteria in your automated tests.
On npm I published a package you can use in your Nightwatch tests called nightwatch-mssql-assertions
This would allow you to database assertions in your test like the following
browser.assert.recordCountIs(2, tableName, whereClause)
A simple chained example would be asserting no records exist for a user, creating a new user in the UI, then verifying a record exists for that user.
let whereJohnWick = "first_name = 'John' AND last_name = 'Wick'";browser.assert.recordCountIs(0, "people", whereJohnWick).url('http://foo.bar/createUser?fname=John&lName=Wick').assert.recordCountIs(1, "people", whereJohnWick)
Installing nightwatch-mssql-assertions
In a command prompt at your Nightwatch test project
npm install nightwatch-mssql-assertions --save
After, modify your nightwatch.json configuration file to add a custom assertions path to the nightwatch-mssql-assertions package
"custom_assertions_path": ["./node_modules/nightwatch-mssql-assertions/src/assertions"],
Then, in the test settings section, add globals values that include the database credentials, address, port, and database name.
"test_settings": {"default": {"desiredCapabilities": {"browserName": "chrome"},"globals": {"dbUsername": "sa","dbPassword": "ThisIsAStrongP@assword!SortOf","dbAddress": "localhost","dbPort": 1433,"dbName": "nightwatchDb"}}}
Running Nightwatch database tests
Once the Nightwatch custom assertions package for MSSQL is downloaded you can execute your test automation by calling nightwatch at the command prompt and get output similar to this.
Running: Verify John Wick count√ Testing if the record count (first_name = 'John' AND last_name = 'Wick') equals 0 (88ms)√ Testing if element <body> contains text 'Added john wick' (27ms)√ Testing if the record count (first_name = 'John' AND last_name = 'Wick') equals 3 (89ms)√ Testing if element <body> contains text 'Removed john wick' (21ms)√ Testing if the record count (first_name = 'John' AND last_name = 'Wick') equals 0 (93ms)√ Testing if the record count (first_name = 'Jane' AND last_name = 'Doe') equals 1 (86ms)OK. 6 assertions passed. (512ms)
As shown in the test output you can mix element assertions and database assertions in the same test case.
Full database testing example
I found some tutorials for database testing with selenium, but not much with Nightwatch so created one and put the full source code on my GitHub to accompany this article. It covers this testing tutorial in greater detail with more examples.
To make it an easier walk through it has a bundled docker image with Microsoft SQL server (mssql) preseeded with records to accompany the tests along with a simple node express app for adding and removing people to allow for more interesting example tests.
Database testing with Nightwatch.js on GitHub
module.exports = {'@tags': ['database'],afterEach: function (browser) {browser.url('http://localhost:8080/reset');},'Verify only one Jane Doe': function (browser) {browser.assert.recordCountIs(1, "people", "first_name = 'Jane' AND last_name = 'Doe'");},'Verify only one John Doe': function (browser) {browser.assert.recordCountIs(1, "people", "first_name = 'John' AND last_name = 'Doe'");},'Verify only 3 people exist': function (browser) {browser.assert.recordCountIs(3, "people");},'Verify John Wick count': function (browser) {let addJohnWick = 'http://localhost:8080/addPerson/john/wick';let removeJohnWick = 'http://localhost:8080/removePerson/john/wick';let whereJohnWick = "first_name = 'John' AND last_name = 'Wick'";browser.assert.recordCountIs(0, "people", whereJohnWick).url(addJohnWick).assert.containsText("body", "Added john wick").url(addJohnWick).url(addJohnWick).assert.recordCountIs(3, "people", whereJohnWick).url(removeJohnWick).assert.containsText("body", "Removed john wick").assert.recordCountIs(0, "people", whereJohnWick).assert.recordCountIs(1, "people", "first_name = 'Jane' AND last_name = 'Doe'");}}
Final considerations
To make your tests repeatable you need to find a way of tearing down your data once your tests finish running. Otherwise the counts may change on the next test run.
In my example, I built an endpoint that resets the table back to the original seeded data in the Nightwatch afterEach test hook that is run after each test. I've also seen custom DELETE queries used or database snapshot and restore as options to consider.
I hope you found this Nightwatch tutorial helpful. Feel free to reach out on twitter and share the article with friends. Thanks!