How do you test your CRUD operations?

08 October 2020 • 9 minutes

Written by Eban Escott

image for 'How do you test your CRUD operations?'

Testing is the key to great quality software and it can be seriously hard to do. Some developers skip this all important step for a number of reasons, one of those reasons could be that they do not know how to test something. Well, that excuse is now out, in this article we follow through a solid test plan for CRUD operations and by the end of this article you will be testing like a pro.

There are some pretty cool tricks you should know to test your CRUD operations. If you are not familiar with CRUD, you can learn more about CRUD operations here, or just carry forward and pick it up as you go.

CRUD (create, read, update, and delete) are the 4 operations you can perform on any data. As it should be with all software engineering, you should consider how to test the software to ensure quality and security.

The following sketch shows the flow of testing. The thing being tested is the process. Some input will come into the process and some output will follow. You may need to do some setup (like seeding a database or setting up a mock) before the test begins. Finally, you compare the output to the expected to see if the test passes.

Codebots Test Diagram

1. Identify what you are testing

The application we are going to test, is a typical CRUD style application that administrators of web applications are very used to. I would not be surprised if you have come across something like this before. For your specific case, you might be testing SQL CRUD operations or the like, but the ideas behind the test plan can be adapted to different situations.

The following screenshot shows an entity diagram (think database schema). The application is a movies catalogue. There are movies, categories, and actors. This will be sufficient for discussing test plans for CRUD.

Codebots Movies Entity

The application has multiple user interfaces (UIs) to allow someone to administer the data on the application. In the following screenshot, you can see the list page. From this page you can create a new movie, select a movie to update or delete it. Or you can simply read the movie by searching and clicking on the view button.

Codebots Movies List Page

When you click the create button, it shows the details for a new movie. This page is also displayed to edit (or update) a movie as well. The following screenshot shows what our movie create and update page looks like.

Codebots Movies Create Page

Between the above 2 user interfaces a user can perform CRUD operations on the movie data. It is possible to have different user experiences and have different variations of these pages, but this is a nice and clean UI that is easy to teach people.

2. Make a plan

First up, you want to come up with a test plan so you can get good test coverage. This is called your test selection criteria. There are lots of different approaches to coming up with your tests and in this example, we are going to look at using a requirements-based criteria plus a data coverage criteria to come up with something pretty solid. We will discuss some other test selection criteria’s later on in the summary.

Before you get stuck into the test plan, we want to let you know about something that is known in software engineering circles but can be quite shocking for non-techies. There is no such thing as completely tested software! Well, for non-trivial sized applications anyway. It is theoretically impossible to do this … there are simply too many possibilities and in some cases, it would take more time than has been available in the Universe. So, since we cannot ever achieve complete 100% coverage, we must be smart about how to test and get good coverage.

Now here comes some terms to remember so make sure you have your thinking cap on. These terms are used by software testers and they are worth knowing. First the application we are testing is called the System Under Test (SUT). In our case, it is our movie application. Next are oracles, and like the Matrix, these oracle are suppose to be ways to work out if a test has passed or failed.

The next couple of sub-sections are the oracles for our test plan. It is split into positive tests and negative tests. Positive tests are scenarios that test a user flow that does not produce any errors, i.e. something the user can do without running into any problems. Negative tests are scenarios that test a user flow that produces errors, but they are expected. For example, if a user does enter a required field then an error message appears.

Positive Tests

The positive test cases are the regular user flows that are expected:

The oracles for the positive test cases are found in the following table. The subscript numbers align the oracles to the test cases.

CRUD Database HTML
C1reate new row, no change to other rows list page, new entity in list
R1ead no changes entity page, entity values displayed
U1pdate change to this row only list page, updated entity in list
D1elete deleted row, no change to other rows list page, entity deleted from list

Negative Tests

The negative test cases are the irregular user flows that are expected:

The oracles to the negative test cases are found in the following table. Again, the subscript numbers align to the test cases above.

CRUD Database HTML
C2reate no changes entity page, error message displayed
C3reate no changes list page, no changes to list
R2ead no changes list page, no changes to list
U2pdate no changes list page, no changes to list, error message displayed
U3pdate no changes entity page, error message displayed
U4pdate no changes list page, no changes to list
D2elete no changes list page, error message displayed
D3elete no changes list page, no changes to list

At this point we have a pretty solid test plan, but we can extend it further using a data coverage criteria to get better coverage. A lot of bugs in software engineering occur on the boundaries of conditions so that’s where you should concentrate your test cases. For example, if you required a number that was less than 10, then 10 is the boundary and you should consider testing 9, 10, and 11 as input.

There is a really cool algorithm you can use called base choice. It was originally published by Ammann and Offutt and goes something like this: the first test case chosen should be the base choice (think positive test case), then each subsequent test case should only change one input value from the base choice (think negative test cases); by changing only one input value at a time you partitioning the tests and this can help find bugs more effectively. Something like this could be worth considering for your test plan.

3. Write some tests

Now that we have a test plan, the next step is to write some tests. Or even better, get a codebot to do it for you! We will cover that shortly.

When testing web-based systems like the movie application, you will need some browser automation technologies. One of the most popular is Selenium and provides a rich set of programmable utilities. On top of Selenium, you should also consider tools like Cucumber, which provides a nice domain specific language to build more readable tests.

Now for some good news, you can get a codebot to write all of this for you. The bot writes a full-stack software application including all the CRUD tests. As seen in the following screenshot, you can see one of the create tests. The test uses Cucumber to describe the test and then Selenium under the hood to automate the browser for test execution. The bot commits all changed to a git source code repository that you have access to.

Codebots CRUD Test

Further to these bot-written tests, you can also add platform-written tests via a user interface that uses Scratch, the programming language we use to teach children how to code. It is easy to add tests using a drag-n-drop interface as seen in the following screenshot. Alternatively, you can add developer-written tests straight into the source code just like a developer usually does.

Codebots Platform Written Tests


Testing is fundamentally important in building quality software applications. So in this article we have covered 3 steps:

  1. Identify what you are testing
  2. Make a plan
  3. Write some tests

The example used was a simple movie application and a CRUD user interface for managing the data. You have learnt about some cool testing lingo like SUT, positive tests, negative tests, and oracles. Speaking of oracles…

Meme about Oracle CRUD

If you want to dig a little deeper on this topic, we recommend checking out the Codebots platform or you can read about some of the genesis story at IEEE.

Eban Escott

Written by Eban Escott

Founder of Codebots

Dr Eban Escott received his Doctorate from UQ (2013) in Model-Driven Engineering and his Masters from QUT (2004) in Artificial Intelligence. He is an advocate of using models as first class artefacts in software engineering and creating not just technologies, but methodologies that enhance the quality of life for software engineers.