How to manage test data in database

Question

Background:

• I am setting up an automation framework
• Brand new project (i.e. no scripts)
  (clarification: brand new project as in everything is brand new, new db, new code, new automation, everything - yay new projects!)
• Stack is Java/Javascript/Mongo

I want these tests to be fast and independent. So I will be testing one item at a time. The question comes in when there's something I need existing data for. I cannot restore databases for these tests, so a DB snapshot is out of the question.

Example scripts:

• create user
• create new entry from a newly created user

The Questions:

Should I try to get developers to setup an API method for testing, or create a SQL query to just create the data. My gut reaction is that I don't want to run SQL queries like that to insert and delete data. But I am not sure I can justify asking for those API methods to be created.

What are the pitfalls/benefits of each way? Is there any other way to accomplish it other than running another 'create user' script before 'create new entry from a newly created user'?

Answer 1

Here's how I'd approach this situation (the tl;dr version):

Analyze your options and give an indication of the risks involved. Your risk analysis should show your preferred option as having the lowest risk (expressed as a relative number, e.g. 1 being the highest risk and 25 the lowest) and state a few of the biggest risk factors for each potential solution.

Let the business decide the cost in time and money of each option and choose which one to go with.

More information and my suggestions:

The first thing you absolutely need is a test environment under your control. Even if backup/restore operations aren't possible in this environment, you must have a non-production environment where you can work.

You don't say if this is a long-established application with no automation, or a newer one, and you also don't mention the language it uses. I'm in a similar situation with a massive enterprise-level classic ASP web application, and an API is out of the question. The code is too tightly interwoven with the GUI.

That said, there are ways you can work around the situation, if you can get a controlled test environment in place.

• If the database uses stored procedures, you can call these to set up the data you need and later remove that data to maintain a more-or-less clean data set. For instance, you create a user through the normal interface, and verify that all the user's data lands in the database in the correct table(s) and format(s), then in your next test where you perform an action with a newly-created user you use a stored procedure to create the user, then run the action in the normal interface.
• You can periodically reset the database so that you don't overload it with your test data (where backup/restore is not an option, this is the second-best option)
• After you create your test environment, you can strip it down to just the configurations/customers/options that you are testing with. In situations like mine, where you have thousands of customers all using the same application database, you only need one customer per distinct configuration set, and you may be able to use pairwise analysis to trim things down further, particularly if some of the options aren't mutually exclusive or there are combinations none of your customers use.
• If it's possible to get the programmers to build an API, then do so: it will simplify your automation requirements immensely. Being able to test business logic via API and reserve GUI automation and/or manual testing for end-to-end scenarios where it's not feasible to work with API calls is a huge time-saving both in test development time and in test run time.

Answer 2

Some tests are better run via a test API as they can then test sections of code that may be unreachable or very hard to reach but a test API does not test the database interface so you also need to test with SQL.

The most important thing to remember is Never, Ever, run untested code on a live database - you could potentially do an immense amount of damage or possibly make a subtle change that is hard to isolate and takes years to track down.

I would say that you need to test with the API for robustness and functionality and with both with a small, test, database that you can easily check for changes, introduce "problem" records, etc., and with an offline snapshot of the real database for load tests and to ensure that the real data doesn't contain problems that you haven't thought of.

Answer 3

If you have RW access to the source:

Build your own APIs/Test Harness into a test solution referencing the source code. This will give you a massive amount of benefits including familiarity with the code, additional unit/integration test coverage and ensuring that you're test set up is executing exactly the way that a user's data would be set up.

This will also allow you to build the hooks for a strong automation suite into a testing project that can then just execute the chunks of code and be able to narrow down any issues.

If you don't have RW access

I would avoid raw insert/update statements if at all possible. I would recommend a quick dirty selenium run that sets up the data and then slowly work on that until it is in a maintainable state.

Either way, don't update/insert data. It will become stale and it could potentially cause false positives.

Answer 4

Some pitfalls I encountered by using simple database operations. Some might be applicable for API and SQL.

What is the best order to enter data? E.g. I need to enter customer. So I fill a table of zip codes first.

What kind of data is available? E.g. I need a valid zip code. Only a third of the table is loaded. Ofcourse mine is not in the table, so I need another zip code.

What kind of relationships must be made before testing? E.g. So this customer wants to order. What is the name of his company?

What are the business rules for the test data? E.g. A customer can only work for one company.
Some database operations can bypass checks.

Which test data am I using? E.g. I have an order for red balls. Is this the order I placed yesterday or today? Tip: make test data day and time dependent. E.g. “Red 15-jul-2015 10 am.”

Answer 5

Here is my recommendation in order of preference:

• API
• SQL (Avoid and may be used as a stop-gap-measure until API is available)
• Create user script (takes time and may have false positives)

Most applications these days have such APIs and if your application doesn't have these these it may be worth considering in upcoming sprint/releases.

In case you need to use SQL, goes without saying, you should externalize them from your test scripts so that any change in table/sql doesn't impact your test script.

Answer 6

First, as @Steve Barnes says, you should NEVER run test on production database. If you do, you are asking for problems, and you WILL get them.

Second, part of your test should be test of creating user: if you create user via SQL script and not your app functionality, you are not testing that, and you are creating special program which needs to be maintained. If 2 years from now there is a change in how new user is created, will you remember to (1) go back to your script which creates users and (2) fix it correctly?

Unless creating users adds huge performance penalty, you are better off using integration-level functionality of your application (so you can test them), instead of deep system API. (I assume you are writing integration/acceptance level tests, not unit tests, where you test code unit in separation).

What I did in similar situation was to create new bunch of data (new company, new users with different roles) which were names using naming conventions so you can obviously see (1) they are test entities (2) who belongs where. So in example my code started with generating random tag XYZ, then creating TestCompanyXYZ, AdminXYZ1, RegUserXYZ1, RegUserXYZ2, etc. Then, I run it is test environment, not in PROD.

Unless you are as cool as this guy, of course: http://memegenerator.net/instance/59548834 - then go ahead and test in PROD :-)