((Using "users" as an example to represent one of many record types))

An application with user tracking typically has a User table. Many, if not all, companies mandate that a User record should never be actually deleted. Instead, a discrimating field "isDeleted" should be added. The other possibility is a shadow table that tracks deleted users and its own set of questions which shouldn't be addressed here.

So in general, to retrieve a list of users, I may have a method like so:

public ICollection<User> GetUsers() {
    return Users.Where(x => x.isDeleted == false);

So I have an SDK which wraps a web service which call domain services which depend on data access which hits my database. The eventuallity is something like so:

public void DeleteUser(int id) {
    var user = Users.Where(x => x.id == id).Single();
    user.isDeleted = true;

So what about my Integration Test? I have an Integration Test something like so:

public void TestUserCreation() {
    var integratedUser = Dependency.GetUserBySomething(user.Something);
    Assert.AreEqual(integratedUser, user, "wut?");
    Dependency.DeleteUser(integratedUser.id); //Clean up!

The key above is that my Integration Test thinks it is cleaning up, but in reality, my Production Database has three times more fake test users than actual users. The table is SUPER bloated from running Integration Tests on Deployment in a Continuous Deployment environment.

So how do I let my Integration Tests ACTUALLY Delete a user? I'm not asking for a code answer, I'm asking for a architecturally correct answer.

E.G.: Should I create an endpoint that actually does a delete, but require a key that only the Integration Test knows? That seems messy...

E.G.2: Should I get access directly to the production database and run delete queries against it? (Lol, totally lawl no.)

E.G.3: Nobody else worries about this, so I shouldn't (Non-Refundable!!! I mean.. Not Acceptable!!!!)


Firstly, I'd try to not run tests on true production databases if possible, but I know this cannot always be avoided.

In my tests of this type, I ensure the database is as expected with a range of simple AddEntry, DeleteEntry, AssertEntry and AssertEntryCount routines that either insert, delete, assert the content of, or assert the expected count of rows in tables with specified fields and values.

These have a TableName parameter and then a ParamArray parameter called ColumnsAndValuesAndOptionalConnectString (as well as a couple of straight SQL query overloads) that expects field names, then the same number of values and finally an optional connection string.

In your case, I'd have one (or as few as possible) test that creates a new user and if it succeeds the cleanup does actually drop the user created.

The rest should refer to a single (or again as few as possible) "Integration Testing" user that is normally marked as deleted, but that user is undeleted while tests are performed upon it.

To answer your question: "Should I provide a way to really delete a user?" If you don't want to access the database directly to "run delete queries against it", you have to choose between creating new test users for every test or creating some code path that actually deletes users. If you do go for the second option you can make the user deletion routine only available to your test code by a number of means, and depending upon your development environment, you may be able to avoid showing it on your public API.

Perhaps the most straightforward solution is to identify test users in some explicit way and define a DeleteTestUser public API that is documented to only delete test users, but does so completely.

  • 1
    Assuming the deletion routine was on the other side of an HTTP endpoint normally accessed via an SDK but not necessarily, what are some of your ideas on how to make that endpoint only available to my test code? – Suamere Jun 15 '15 at 13:55
  • Right, that is a harder scenario. My first thought is a conditional build that only includes the endpoint for testing purposes. It could be that the WSDL, etc. only includes the endpoint when retrieved by the testing machine's IP address. – Mark Hurd Jun 15 '15 at 17:47
  • 1
    "I'd try to not run tests on true production databases if possible" - that seems, like quite an understatement, to put it as gently as possible. My response would be more like "Are you bleeding insane?". – sleske Dec 12 '15 at 22:45
  • 1
    @sleske Well, I did start with that point :-) But the question is still valid for someone who would prefer to not pollute a test database with "deleted" users when running lots of tests under similar conditions. – Mark Hurd Dec 13 '15 at 11:59

In cases like described here, I usually set up (or use) a dedicated integration testing environment. In that environment, the test script / test code has full and direct database access, and can do whatever it likes to the database, including creating/deleting stuff as it pleases. Ideally, the DB is an embedded DB, which is spun up explicitly for the test. If that is not enough, you need some type of "real" DB, but with modern virtualization technologies like Docker even that is feasible.

Now, this may sound like "cheating" - a test that directly messes with the DB. However, to be able to test a system thoroughly, you must be able to reproduce arbitrary system state. This includes state that you cannot produce through regular API calls, such as:

  • massive amounts of data that would take forever using the API
  • invalid data, maybe to simulate some DB bug, or a bug in an older version of your software
  • old data - for example, testing expiration of records older than a year
  • and, last not least, an empty DB to have a clean slate for the next test - which is the problem from the question

For thorough testing, the test code needs direct, full DB access - anything else will bite you at some point.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.