How to create unit tests for Web app with a database

Question

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.

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 :-)

Edit:

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.