I'm currently adding unit testing to my project and came across this question, How can I test my database insertion.
For example, let's say I have a user table, I want to test if I can create a user, and if the default permission are sets correctly. Sure I can simply call the function that create the user, but what if I run my tests 1000 times. It would create 1000 users in the database, all duplicate of each others.
One way I found to fix this would be to run an batch file before each test, that create a new, empty, database with only the base data, run all tests, and then delete the database afterward.
This would probably works, but I wonder if there is a easier way to do this, perhaps with some already made tools.
I'm mostly using Java, but it does not really matter. In my case, i'm building an API with a MySQL database, but it could be any other database system. The question is more theoric.

  • Which level of permissions are you talking about? Whether it is a database-level permissions or application-level permissions?
    – Alexey R.
    Commented Apr 11, 2018 at 13:46
  • @AlexeyR. It's application permission, it was a basic example
    – Nicolas
    Commented Apr 11, 2018 at 13:51
  • 1
    OP: (1) you may want to specify the programming language you use, (2) which mocking frameworks you consider, and pro/con for each , (3) wait with accepting answer for a day or two, to let folks in other timezones to weight in. If you accept an answer early, you may never get better answers from other timezones - people will not to strive to write a better one if something is already accepted. Commented Apr 11, 2018 at 18:31
  • @PeterMasiar that's a good point.
    – Nicolas
    Commented Apr 11, 2018 at 20:38

1 Answer 1


Database shared between tests is a common obstacle.

One way ("orthodox") is to insist that unit test should NOT test database (just exercise the tested library), and mock the DB interface.

Another way ("synthetic") is to use real database, even if such test are not strictly a unit test, but low-level integration tests. Then either:

  • test might interact with each other (and you need to deal with it),
  • test run in multi-user environment as different users, so it does not matter,
  • test are designed to run in a strict sequence (test depend on results of previous tests)
  • you clean up the database before each test

Pick your poison, and welcome to the real world :-)


Mocking gives fastest response (because it bypasses it DB). You need to be careful when some fields in DB change, your mock object needs to change too. Otherwise your unit test might pass, but real DB call will fail.

Some mocking frameworks (like Python Mock) have a "learning mode", where requests goes through to real object, but response is preserved and served next time in "mocking mode". This will generate mocking responses with no extra efforts.

  • It is poison, indeed hehe ! Thanks for the advice, I think the mock approach is still the more efficient one to this day.
    – Nicolas
    Commented Apr 11, 2018 at 13:53
  • @Nicolas - I added some pro/con about mocking for you Commented Apr 11, 2018 at 17:01
  • @PeterMasiar Would it be reasonable to create a standard function call that creates your mocks, and put a test around that to ensure the mock represents what's actually in the database? I actually was just running into that issue when setting up some mocks the other day...
    – corsiKa
    Commented Apr 11, 2018 at 18:11
  • @corsiKa - yes, having a "learning mode" to create canned "valid answers", and then tests those "valid answers" against the real DB will be perfectly valid way to do it. Mocking DB is same as mocking any other system. Lots of work though. And you still need to do integration testing, because such mocking will not mock the timing for DB access. Commented Apr 11, 2018 at 18:36
  • 1
    purists might say that unit tests shouldn't require real DBs, so mocks are the way to go. I think the other alternative might be to use known naming conventions with some randomness - i.e. user names of the form "DELETE_ME_{randomness}", where randomness could be a UUID, timestamp, etc, and then the cleanup could use a LIKE operator.
    – ernie
    Commented Apr 11, 2018 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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