Automating CRUD Testing

There are a number of different patterns we can use to automate CRUD testing.  At the very minimum, we want to test one operation of each: Create, Read, Update, and Delete.  For the purposes of this discussion, let's assume the following:

1. We are testing the simple form used in this post
2. We are doing UI automation (API automation will be discussed in a future post)

This is the pattern I like to use when testing CRUD.  I'm writing in Specflow/Cucumber for simplicity:

Scenario: Adding a user
Given I am adding a new user
When I add a first name and save
Then I navigate to the Users page
And I verify that the first name is present

Scenario: Updating a user
Given I am updating a user
When I change the first name and save
Then I navigate to the User page
And I verify that the first name has been updated

Scenario: Deleting a user
Given I am deleting a user
When I delete the first name and save
Then I navigate to the Users page
And I verify that the first name is not present

These three tests have tested Create, Update, and Delete. The first two tests are also testing Read, because we are retrieving the user for our assertions.  Therefore, with these three tests I'm testing the basic functionality of CRUD.

Some may argue that these tests are not idempotent.  Idempotency means that a test can be run again and again with the same results.  I can't run the third test over and over again with the same results, for example, because after the first time I run it, the user is no longer there to delete.

If we wanted to solve the idempotency issue, we could write a test like this:

Scenario: CRUD testing of user
Given I am testing CRUD
When I add a first name and save
And I verify that the first name is present
When I change the first name and save
And I verify that the first name has been updated
When I delete the first name and save
Then I verify that the first name has been deleted

This one scenario tests all the CRUD functionality of the form.  But it also has three different assertions.  I prefer to keep my UI tests down to one or two assertions.

When my original three scenarios are looked at as a group, they are idempotent together.  My tests are responsible for creating and deleting my data.

It would also be a good idea to test some negative scenarios with our CRUD testing, such as creating a user with an invalid first name, and updating a user with an invalid first name.  These tests could look like this:

Scenario: Creating a user with an invalid first name
Given I am adding a new user
When I enter an invalid first name and save
Then I verify that I receive the appropriate error message on the page
And I navigate to the User page
And I verify that the user has not been added

Scenario: Updating a user with an invalid first name
Given I am updating an existing user
When I update the first name with an invalid value and save
Then I verify that I receive the appropriate error message on the page
And I navigate to the User page
And I verify that the existing first name has not been updated

The first scenario is idempotent, because nothing is actually being added to the database.  The second scenario is also idempotent, but it requires an existing user.  We could assume that our user will always be present in the database, but if someone inadvertently deletes it, our test will fail.  In this case, it would be good to add in a Before step and an After step that will create the user at the beginning of the test suite and delete it when the suite is over.

These five scenarios- the three to test the happy path, and the two negative tests- would be a great regression suite for our simple form.  This is a very simple form, with just one field, which is not exactly a real-world scenario.  But it is a good way to start thinking about automated UI test patterns.

CRUD Testing Part II- Update and Delete

In last week's post, we started looking at CRUD testing.  As you recall, CRUD stands for Create, Read, Update, and Delete.  Last week we discussed testing Create and Update operations, and now we will continue by looking at Update and Delete.

In our discussion of the Read operation last week, I mentioned how important it is to test scenarios where the data in the database is invalid.  This is also true for Update operations.  Just because a text field is supposed to be required and have a certain number of characters doesn't mean that's how it is in the database!

Below is a matrix of testing scenarios for editing a text field.  Remember from last week that our hypothetical text field has the following validation rules:  it is a required field, it must have at least two characters, it must have 40 or fewer characters, and it should only have alphanumeric characters or hyphens and apostrophes; no other symbols are allowed.  As with the Create operation, be sure to test that the newly edited field is correct in the UI and in the database after the update.

For the bad values that you are testing with, be sure to vary them so you are covering a number of different validation scenarios.  For the good values that you are testing with, make sure to test hyphens and apostrophes, numbers and letters, and the upper and lower limits of the character count. 

Finally, let's discuss Delete.  The main thing to test with Delete is that the value has been deleted from both the UI and the database.  But just as with Read and Update, you'll want to make sure that you can delete bad values.  For example, if your First Name field has forty-one characters in it (violating the rule that only 40 characters or less should be added), you'll want to make sure that you can delete it in the UI.  

You may be wondering how to find all these invalid values for testing.  While it is possible to find them by searching through the existing records, the easiest way to test them is to put them in yourself!  This is why it's important to know the appropriate query language, such as SQL, for your database.  

For those who are wondering why testing with bad values is important, I once had a scenario where I was testing contact information for a person.  There were a number of incorrectly formatted phone numbers in the database.  When I tried to update any of the contact information for the person, I got the message that the record couldn't be updated, because there was invalid data present.  This was true even if I was trying to update the invalid phone number in question!  Please be sure to check for scenarios like these; your end-user will thank you!

CRUD Testing Part I- Create and Read

In spite of its unappealing name, CRUD testing is extremely important!  CRUD stands for Create, Read, Update, and Delete.  As any tester knows, much of our testing involves these operations.  Today we'll discuss the best ways to test Create and Read. 

The most important thing to know about testing CRUD is that it's not enough just to rely on what you are seeing in your UI to confirm that a field's value has been created or changed.  This is because the UI will sometimes cache a value for more efficient loading in the browser.  What you need to do to be absolutely sure that the value has changed is to check the database where your value is stored.  So you'll be confirming that your value is set in two places: the UI and the database.  If you are doing API testing as well, you can actually confirm in three places, but we'll save discussing API testing for another post.

Testing a Create Operation:

This text field looks similar to the one we looked at last week, but now we know what it does!  This is a form to add a user.  We'll enter the first name of the user into the text field and click Submit.  Next, we'll take a look at the Users page of our imaginary application and verify that the new user is present:

And there it is!  Finally, we need to query our database to make sure that the value has saved correctly there.  In our imaginary database, this can be done by running

SELECT * from Users

This will give us a result that looks a lot like what we are seeing in the UI, so I won't include a screenshot here.

To thoroughly test the Create function, you can use some of the same ideas that we talked about in last week's post.  Verify that valid entries of all kinds are saved to the database.

Testing a Read Operation:

We actually started testing the Read operation when we checked the Users page to verify that our new user was added.  But there is something else that is important to test!  We need to find out what happens when bad data is in the database and we are trying to view it in the UI.

Let's take a look at what some bad data might look like in the database:

In our imaginary application, there are some constraints in the UI for the First Name field.  It is a required field, it must have at least two characters, it must have 40 or fewer characters, and it should only have alphanumeric characters or hyphens and apostrophes; no other symbols are allowed.  As we can see in our table, we've got lots of bad data:

• User 2 has no entry for First Name
• User 3 has an empty string for a First Name
• User 4 is violating the rule that the name must have at least two characters
• User 5 is violating the rule that the name must have 40 or fewer characters
• User 6 is violating the rule that only hyphens and apostrophes are allowed for symbols

What should happen when we view the Users list in our application?  That will depend on what the product designers decide.  They may choose to display bad data as long as it is not a security risk, such as the First Name for User 6, which is actually a Stored XSS attack.  Whatever the rules for display are, it's important to test that those rules are respected.  

You may be saying to yourself (or a developer may be saying to you), "Displaying bad data won't be an issue, because we are putting good validation in place to make sure that bad data won't get in the database to begin with."  While this is absolutely standard practice today, there will always be cases where bad data will slip in.  I once tested a PATCH operation where phone numbers could be inserted into a record.  I discovered that while validation was taking place when the PATCH body was formed correctly, there was an edge case where a PATCH body was formed incorrectly and accepted without validation.  Any developer that incorrectly coded the PATCH operation could inadvertently allow bad phone numbers into the database!  

A good rule of thumb for testing any Create and Read operation is to assume that anything can go wrong and test accordingly.  We'll continue imagining what can go wrong next week, when we test Update and Delete operations.  

Testing a Text Field

A text field in an application seems so ordinary, and yet it is one of the most important things we can test.  Why?  Because text fields provide an entryway into an application and its underlying database.  Validation on a text field is what keeps lousy data from getting into the database, which can cause all sorts of problems for end users and engineers. It can also prevent cross-site scripting attacks and SQL injection attacks. 

There are a myriad of ways to test a text field, and I will be outlining several in this post.  First, let's imagine that we are testing the text field with absolutely no information about what it does:

• Click Submit without filling in the text field
• Press the space bar several times in the text field and then click Submit
• See how many characters you can fit in the text field and then click Submit (an excellent tool to count characters is https://lettercount.com)
• Fill the field with as many numbers as you can and then click Submit
• Add a negative sign, fill the field with as many numbers as you can and then click Submit
• Enter every non-alphanumeric field on the keyboard and click Submit.  If you get an error, see if you can narrow down which key (or keys) is causing the error.
• Enter in non-ASCII characters and emojis and click Submit.  If you get an error, see if you can narrow down which symbol (or symbols) is causing the error.
• Try cross-site scripting by entering in this script: <script>alert("I hacked this!")</script>  If on Submit, you get a popup message, then you know the field is vulnerable to cross-site scripting.
• Try a SQL injection attack, such as FOO'); DROP TABLE USERS; -- (Don't try this on your Production database!)

Next, let's assume that you have some knowledge about what is supposed to be entered into this text field and what the boundaries are on the data:

• Try putting in a value that is a different data type from what is expected; for example, if this text field is expecting a value of currency, try putting in a string or a date
• If the field is expecting a string, try putting in a string with one fewer characters than is expected, one more character than is expected, the lower limit of characters that is expected, the upper limit of characters that is expected, and twice the maximum number of characters expected
• If the field is expecting a numeric value, try putting the maximum value, the minimum value, a value above the maximum, a value below the minimum, and a value twice the maximum value
• If the field is expecting an integer, try submitting a value with a decimal point
• If the field is expecting a float, try submitting a value with two decimal points and a value that begins with a decimal point
• If the field is expecting a value of currency, try submitting a value with more than two digits after the decimal point
• If the field is expecting a date, try putting in the maximum date, the minimum date, one day over the maximum date, one day before the minimum date, and a date one hundred years above or below the limit
• For date fields, try entering a date that doesn't make sense, such as 6/31/17 or 13/13/17  (There are many more ways to test date fields; I'll touch on this in a later post)
• If the field is expecting a time, try entering a time that doesn't make sense, such as 25:15
• If the field is expecting a phone number, try entering a number that doesn't conform to the expected format (There are many, MANY more ways to test phone numbers; I'll touch on this in a later post as well)

For all of the above tests, find out what sort of error message you are supposed to be receiving, and verify that you are getting the correct message.

Finally, let's think about automation.  Assuming you have very thoroughly tested your text field manually, it's probably not necessary to automate every single one of your tests.  Moreover, most forms have more than one text field, and having many tests for each individual field could result in many time-consuming tests.  But here are some suggestions for what you might want to automate:

• submitting a null value
• submitting an empty string 
• submitting a value that meets the criteria (the "happy path")
• submitting the maximum number of characters or maximum value
• submitting the minimum number of characters or minimum value
• submitting just above the maximum characters or value
• submitting just below the minimum characters or value

This list of tests is not meant to be completely exhaustive; it's just a way to get you to start to think about the vast number of tests you can run on a single field.  Don't ever assume that the developer who coded the field has put in the appropriate validation; check it for yourself!  I tested a date field once where there was a limit put on the year that could be entered so it couldn't be before 1900 or after the present year.  While I did receive the appropriate message when I entered a value of 1880, I discovered that I could enter a date from the year 1300!