Database testing can be done in various ways – be it manual testing or automated testing. Even within the manual testing, there are multiple ways to approach the testing. As a software tester, if you are new to the organization, you’re less likely to know the process for database testing. And each organization has it’s own procedure for testing the database.
In this post, I’ll explain how to test the database in most generic way, from which you can extend and adopt the method that suits your testing style or company.
There are two ways you can proceed with the databasse testing. You can verify the database content by executing the queries or you can manually check if the application execution does the database interaction in exepcted way. In the second method, you don’t have to write the queries but instead you have to use the application like end user and verify the database.
Testing database is like any other application with the difference that applies to specific to that of database.
- Prepare the environment
- Run a test
- Check test result
- Validate according to the expected results
- Report the results and fix the bugs or database specific issues.
Let’s discuss the each step one by one.
Environment : Depending on the type of the database selected for the project e.g. like MySQL or MSSQL, you have to get access credentials for the database. You need to know SQL queries in case if you choose to execute the queries. If you’re using NoSQL (Like MongoDB or CouchDB) as database for the project then setting up the environment is different to than popular database like SQLite or MySQL. Make sure you get clean database without any prior test data. Find out the test data parameters based on the project and use them accordingly.
Test Execution : Create queries for insert, update, delete and modify. Make sure you use the test data along with the specific test cases to add your data. Make sure the queries used while execution are same as that of the one used inside the application. Use both negative and positive test cases and adjust the queries accordingly to test the database.
Test Logging: Once you start executing the test, you have to log the test result into the test log. This is to log results of both positive and negative test cases. Check if the results are logged as soon as the test executed. Check if the tests are resulted in any warning or error, and note the respective values down.
Verify Test Results: Once you execute all the test and log the test outcomes. Verify if all the imporatnt tests are executed. Also do check ther results of the test and verify if the test data was correct. Check if the logged result has any errors and verify the tests. Check if all the tests produced the results as per the exepcted results. If not, file the report with bug in next step.
Defect Reporting: After verification of the test, report the defects found in the verification of the tests.
In order to test the SQL database you need to have knowledge of sequel queries. You need to know how to create database and table, insert entry, modify table, drop table, delete entry, update entry. In case of NoSQL database the queries are lot different and require you to know JSON and key & index, along with the concept of key-value pair in the database.
Do test your assumptions about the test data and make sure you execute all the important tests first.
Do note that direct database querying on the live application server is not recommended. So make sure you are using production server to test the database. You may even have to clean or drop the database before executing new tests, so it is better to use the production server for testing.
What are some tools for testing the database?
Each database vendor has atleast one GUI tool for execution of queries. Apart from the GUI tools, most of the database have their own port to access via command line. You can use either way to execute the queries or verify the database.
MySQL has workbench tool that allows developers, administrators and testers to create and modify the database. You can generate reports and perform the necessary operations for database testing using this tool. If you are developing PHP based application with MySQL then use PHPMyAdmin for testing and executing the queries.
Oracle SQL Developer is the tool to test the database developed under Oracle.
MS-SQL Studio has both express and commercial editions for the users to perform the database testing and execution operations.
SQLite Studio is the tool that is unofficial yet allows database operations on SQLite database. Navicat Inc has the GUI applications for SQLite and other databases, which you may want to consider if you are interested in more user friendly UI tools for DB testing.
I hope this helps you get started with the database testing. In future, I’ll cover database testing using automation tools like QTP etc. Feel free to let me know your comments and suggestions below. I’d appreciate if you share this post on social media. 🙂