Database Testing

As software testers we often take for granted the fact that our application has a database behind it.  We tend to focus on the visible, such as the user interface, or the application logic of the API when we are testing.  But it's important to test the database as well!  Below are six ways to test your application's database.

1. Verify that the data fields are the correct type

Each data field in the database will be a specific type, such as int, float, string, or datetime.  Verify that each field's datatype is appropriate.  For example, a date field in the application should be saved as a datetime type rather than as a string.  This might not seem like a big deal, but if sorting functionality is added to the application, you'll find that April 10 is sorted before January 11 because the dates are being sorted alphabetically.

2. Verify that the fields that are required in the database are also required in the API and the UI

Generally each record in a database will have some fields that are required.  If a field is required in the database, it should also be required in the API and the UI.  If the UI doesn't set the last name field as required, but the database has set it as required, a user could submit a new record without a last name and the database will return an error.

3. Verify that the parameters set on the fields in the database match the parameters in the API and the UI

Data fields in a database will have specific limits set, such as a character limit for a string, or a maximum value for an int. The fields in the API and the UI should have the same limits.  If the limits do not match, confusion can ensue.  Let's say for example that your application has a field for a street address.  In the UI the developer has set the character limit to 50 characters, but in the database, the limit is set to 40.  If a user types in a street address with 45 characters, the UI will accept that value, but the database will not.  This will result in the record not being saved, and it won't be obvious to the user what the problem is.

4. Verify that sensitive data, such as user passwords, are encrypted in the database

I once worked for a company that did not encrypt their user passwords.  I had access to the production database, which meant that I could see the username and password for every single customer of the company.  It should be pretty obvious that this was a huge security risk!  Passwords should always be encrypted in the database so there is no way for anyone but the user to know what their password is.

5. Verify that your database supports all your API's operations

Just because your POST request returns a 200 doesn't mean that the data was saved to the database correctly.  When you make an API request, make sure that the database saved every field correctly.  I have seen situations where a PUT request did not result in every new value being saved to the database.  I've also seen a situation where a PATCH request updated the correct fields, but set every other field to null!  Be sure to test every available CRUD operation, and check every field for accuracy.  Also make sure to change field values from null to an entry, and from an entry to null.  If a field you are testing is a string, determine with your team whether empty strings will be allowed, and test going from null to an empty string, an empty string to a value, from a value to an empty string, and so on.

In addition, if your API is going to support the DELETE operation, find out from your team whether those deletes will be hard-deletes, meaning that the record will be completely removed from the database, or soft-deletes, meaning that the record stays in the database, but is moved to another table or stays in the existing table with a "deleted" flag set.  Then test the DELETE operation to verify that it is behaving as expected.

6. Verify that leading and trailing spaces are removed when saving to the database

Have you ever had trouble logging in to an application where you are sure you have the right username?  The problem could be that when the username was originally created, a trailing space was accidentally added, and it was saved to the database with the extra space.  When you tried to log in as "doglvr49", the system was expecting "doglvr49 ".  Similarly, if you entered a contact's last name as " Jones" instead of "Jones" and the leading space wasn't trimmed, and you tried to sort the contacts alphabetically, you'd find " Jones" before "Allan".  When you are testing text fields in your application, try testing them with leading and trailing whitespaces, and then verify in the database that those spaces have been trimmed.

Issues such as having a mismatch between character limits in the database and the UI, or having a string instead of an int, or having trailing spaces in a record, can seem like small issues, but they can result in big problems.  By following these six steps in database testing, you will help ensure that your end users will have a good experience entering and retrieving data.