Overview and Best Practices of Database Testing - XenonStack

What is Database Testing?

The database is an essential part for any system or application. Everything exists by data. If there is no data, nothing is possible. Database instability may cause the systems to behave unexpectedly. Either a database gets crashed, or the data is stored in a database in an unorganized way, in both the cases data will become useless. So database testing helps us to find out such vulnerabilities in a database system to protect a database from an unstable state.

Types of Testing


Black Box Testing

  • Data Mapping.
  • Verifying stored and retrieved data.
  • Uses black box testing techniques such as equivalence partitioning and boundary value analysis.

White Box Testing

  • Testing database triggers and logical views.
  • Validating data models and database schema.
  • Checking referential integrity and database consistency.
  • Use white box testing techniques such as statement coverage, decision coverage, condition coverage.

Benefits of Database Testing

  • It speeds an overall cycle to create a room to test large data sets.
  • It also helps in processing complex transaction behaviors.

Best Practises of Database Testing

  • Read, Write, Trigger Tables – It involves reading, writing, insertion, updation, and deletion of data to be processed. Triggering of tables involves Validation of modified records.
  • BackUp Tables – It includes Continuous Execution and backup of modified data followed by testing including backup of write tables.
  • Protect Target Environment and determine which code safe to deploy.
  • Availability of Database Administrators.
  • Test Environment Configuration.
  • Analyze the predicted needs of hardware, software, resources.
  • Implement Scalability and Security of the database schema.
  • Deliver small and frequent releases to determine issues and get instant feedback.
  • Tasks Automation.
  • Timely execution of scheduled jobs.

Types of Database Tests

Sometimes changes in database and testing available limited copies of databases may also cause failure of the system hence tester should take care that while testing extra copy of data should be available. While database testing, it not only covers a unit testing for databases. Instead of that it also includes many types of testing executed at a database.

  • Unit Testing
  • Load Testing
  • Security Testing

Testing on Multiple Databases

There are a different kind of databases available at which database testing is possible. These databases explained below –

  • Production database – This database comes with live data hence testing cannot be performed on this data.
  • Local development database – It is a database where most of the testing is carried out.
  • Populated development database – This database is shared by all developers, to run an application.This database is used to check that our application can work with a large amount of data, instead of testing only a few records which are present in a test database.Ensures application can work with a large amount of data.
  • Deployment database – It is a database where the tests are run before deployment to make sure all the local database changes applied to this database.

Database Migrations

There is a need of a database migration while doing a database testing. Database migration allows for following things –

  • Recreate the database from scratch.
  • Clarify the state of the database.
  • Migrate from the current version of the database to a newer one.

How Database Testing Works?

  • Write test cases using Db Unit for a specific database supported by DbUnit.
  • Specify a DbUnit test cases for project in a file named as filename_test.go.
  • While execution of test cases actual and expected values are compared.
  • Result fails if the expected value from database after execution of result is not equal to expected value.
  • Result succeeds if the expected value from a database after execution of result is equal to expected value.

DB Unit

  • Db Unit is a J Unit extension target at database-driven projects put database into a known state between test runs.
  • It mainly focuses on testing of relational databases.
  • Avoid myriad of problems that occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.
  • Db-unit can export and import your database data to and from XML datasets.
  • Since version 2.0, Db-unit can also work with massive datasets when used in streaming mode. Db-unit can also help you to verify that your database data match an expected set of values.

Here Db unit is used to testing on PostgreSQL database –

  • Db-unit is ideal as an add-on testing framework which relies heavily on databases.
  • Db-unit comes in need to do an integration testing.

Some of the databases supported by a DB Unit –

  • Oracle
  • SQL Server
  • MySQL
  • DB2
  • PostgreSQL
  • Derby

How to Adopt Database Testing?

In this demo, the actual result checked with the expected result. The demo built by the following things –

  • Database used: PostgreSQL
  • The language used: GoLang
  • Automation for: DB unit

Write test cases for the database using Db unit.

  • In this data present in the database is checked, On a basis, if a data come after execution of the query from the database is equal to an expected result.
  • Result come after execution of query compared with the actual result.

The following steps need to be followed to run test cases using Db unit. These steps written below can be easily understood by looking at a flow mentioned above diagram.

  • Firstly write test cases for a specific database supported by a DB unit.
  • Whenever test cases written, then execute these test cases using a command discussed as follows.
  • After execution result comes in the form of the pass and fail.
  • If a result is a pass, then the actual result is equal to the expected result. If the status failed, then the actual result is not equal to the expected result.

Test cases can be run using the following command

Go test -v -tags integration

Commands to Test DataBase Result for both cases either pass or fail shown below-

  • If expected and actual value matches to each other. Then a test case will move, and the following result will be visible.
  • If an expected result and actual result is not equivalent to each other, then the test case will fail, and the effect will occur.

Database Testing Tools

Some of the tools mentioned below that help to execute these type of testing on a database.

  • Database Benchmark
  • Database Rider
  • Db stress
  • DbUnit
  • DB Test Driven
  • HammerDB
  • JdbcSlim
  • JDBC (Java DataBase Delta Testing)
  • Nbi
  • NoSQLMap
  • NoSQLUnit
  • ruby-plsql-spec
  • SeLite
  • sqlmap
  • tSQLt
  • Tsung
  • utPLSQL