Up Your Testing Skills: Using SQL for Software Testing

My first job out of college was a QA analyst for the company that I had been a call center analyst at all through college. I came to the new role with a very deep understanding of the application and was directed to validate requirements based on that understanding. However, that left me essentially validating the UI acted the way the business wanted it to. What I was missing was the backend validations. You see, time and time again throughout my testing career, the UI has passed validations but the data validations on the backend have failed. This is why SQL quickly became a useful and consistently used tool in my toolbox.

What Exactly is SQL?

SQL, or Structured Query Language, is a language used to communicate with databases. It allows users to create, modify, and query databases, making it a valuable tool for testers.

Ways to Use SQL

Test Data Creation: SQL has many uses in testing and one primary use is to create test data. For example, if an application is designed to store and retrieve customer information, test data could be created to include a range of different customer data sets to simulate user journeys and ensure the application is handling data correctly.

You could use SQL statements to insert rows of test data into a specified database table. This can be useful for quickly creating a variety of data sets needed for executing tests. For example, I might use this SQL statement to insert a new row into a ‘customers‘ table:

INSERT INTO customers (name, address, phone) VALUES ('Johanna South, '123 Main St', '555-777-1312');

INSERT INTO <table name> (column1, column2, column 3) VALUES (value for Column1, value for Column2, value for Column3)

In the above INSERT statement, you can see there are column names in the first set of parenthesis. Following that is an additional set of parentheses that include the values I want to insert into the aforementioned set of parenthesis.

Data Validations: SQL can also be useful when validating backend results of a test you’re executing. As an example, after validating the results of a test to ensure that a customer’s information is correctly displayed in the UI, an SQL query could be used to verify that the correct information is matched and flows properly into the database tables and fields.

Verifying the backend results of a test involves using SQL queries to check the data stored in the database. For example, let’s say that an application is designed to store customer information, including the customer’s name, address, and phone number. You could create a test case to ensure that this information is correctly stored and retrieved by the application.

After running the test, you could use a SQL query to verify the results by selecting the relevant rows from the database table and checking the values stored in each column. For example, I would use a query like this:

SELECT * FROM customers WHERE name = 'Johanna South';

This query would return all rows from the ‘customers‘ table where the name is ‘Johanna South’. You could then check the returned rows to ensure that the correct information (e.g., address, phone number) is displayed.

Alternatively, you could use a SQL function to verify the results in a more automated way. For example, the following query would count the number of rows in the customers table where the name column is ‘Johanna South’:

SELECT COUNT(*) FROM customers WHERE name = 'Johanna South';

You could then compare the returned count to the expected value (e.g., 1, if there is only one customer named ‘Johanna South’) to verify that the correct number of rows was returned.

Data Manipulation: In addition to creating and verifying test data, SQL can also be used to manipulate the database as part of a test. For example, a test might involve adding a new column to a table or changing the data type of an existing column. These types of tests can help ensure that the software is able to handle changes to the underlying database structure.

Here are a few examples of using SQL for data manipulation:

• Updating test data: The following SQL statement would update the phone number for a customer with the name ‘Johanna South’:

UPDATE customers SET phone = '555-555-1313' WHERE name = 'Johanna South';

• Deleting test data: The following SQL statement would delete all rows from the customers table where the name column is ‘Johanna South’:

DELETE FROM customers WHERE name = 'Johanna South';

Lessons Learned: Don’t forget to add the where when deleting records. You can quickly and easily delete entire database tables if you’re not careful!

Conclusion

Overall, SQL is a powerful tool for software testing that allows you to create, modify, and verify data on the backend. Sometimes the UI can be untrustworthy so ensuring your data is properly flowing on the backend is a necessary step in testing.