How to simulate a DB for testing (Java)?

Java comes with Java DB.

That said, I would advise against using a different type of DB than what you use in production unless you go through an ORM layer. Otherwise, your SQL might not be as cross-platform as you think.

Also check out DbUnit

new answer to old question (but things have moved forward a bit):

How to simulate a DB for testing (Java)?

you don't simulate it. you mock your repositiories and you don't test them or you use the same db in your tests and you test your sqls. All the in-memory dbs are not fully compatible so they won't give you full coverage and reliability. and never ever try to mock/simulate the deep db objects like connection, result set etc. it gives you no value at all and is a nightmare to develop and maintain

to have a personal testing DB is pretty impossible. You have to use a "public" DB, which is accessible for everyone

unfortunately a lot of companies still use that model but now we have docker and there are images for almost every db. commercial products have some limitations (like up to a few gb of data) that are non-important for tests. also you need your schema and structure to be created on this local db

"These tests sure ain't fast..." - DB tests tend to be slower than usual tests. It's really not ideal to have slow tests.

yes, db tests are slower but they are not that slow. I did some simple measurements and a typical test took 5-50ms. what takes time is the application startup. there are plenty of ways to speed this up:

• first DI frameworks (like spring) offers a way run only some part of your application. if you write your application with a good separation of db and non-db related logic, then in your test you can start only the db part
• each db have plenty of tuning options that makes it less durable and much faster. that's perfect for testing. postgres example

• you can also put the entire db into tmpfs

• another helpful strategy is to have groups of tests and keep db tests turned off by default (if they really slows your build). this way if someone is actually working on db, he needs to pass additional flag in the cmd line or use IDE (testng groups and custom test selectors are perfect for this)

For each case a certain amount of insert/update queries should be made, which is annoying and takes time

'takes time' part was discussed above. is it annoying? I've seen two ways:

• prepare one dataset for your all test cases. then you have to maintain it and reason about it. usually it's separated from code. it has kilobytes or megabytes. it's to big to see on one screen, to comprehend and to reason about. it introduces coupling between tests. because when you need more rows for test A, your count(*) in test B fails. it only grows because even when you delete some tests, you don't know which rows were used only by this one test
• each tests prepares its data. this way each test is completely independent, readable and easy to reason about. is it annoying? imo, not at all! it let you write new tests very quickly and saves you a lot of work in future

how do you know there are 542 rows in that table?" - One of the main principles in testing, is to be able to test the functionality in a way different from that of your tested-code

uhm... not really. the main principle is to check if your software generates desired output in response to specific input. so if you call dao.insert 542 times and then your dao.count returns 542, it means your software works as specified. if you want, you can call commit/drop cache in between. Of course, sometimes you want to test your implementation instead of the contract and then you check if your dao changed the state of the db. but you always test sql A using sql B (insert vs select, sequence next_val vs returned value etc). yes, you'll always have the problem 'who will test my tests', and the answer is: no one, so keep them simple!

other tools that may help you:

1. testcontainers will help you provide real db.

2. dbunit - will help you clean the data between tests

   cons:

   • a lot of work is required to create and maintain schema and data. especially when your project is in a intensive development stage.
   • it's another abstraction layer so if suddenly you want to use some db feature that is unsupported by this tool, it may be difficult to test it

3. testegration - intents to provide you full, ready to use and extensible lifecycle (disclosure: i'm a creator).

   cons:

   • free only for small projects
   • very young project

4. flyway or liquibase - db migration tools. they help you easily create schema and all the structures on your local db for tests.

There are lots of points of view on how to test integration points such as the Database connection via SQL. My personal set of rules that has worked well for me is as follows:

1) Separate out the Database accessing logic and functions from general business logic and hide it behind an interface.Reason: In order to test the grand majority of logic in the system it is best to use a dummy/stub in place of the actual database as its simpler.Reason 2: It is dramatically faster

2) Treat tests for the database as integration tests that are separated from the main body of unit tests and need to run on a setup databaseReason: Speed and quality of tests

3) Every developer will need their own distinct database. They will need an automated way to update its structure based on changes from their team mates and introduce data. See points 4 and 5.

4) Use a tool like http://www.liquibase.org to manage upgrades in your databases structure.Reason: Gives you agility in the ability to change the existing structure and move forward in versions

5) Use a tool like http://dbunit.sourceforge.net/ to manage the data. Set up scenario files (xml or XLS) for particular test cases and base data and only clear down what is needed for any one test case.Reason: Much better than manually inserting and deleting dataReason 2: Easier for testers to understand how to adjust scenariosReason 3: Its quicker to execute this

6) You need functional tests which also have DBUnit like scenario data, but this are far larger sets of data and execute the entire system. This completes the step of combining the knowledge thata) The unit tests run and hence the logic is soundb) That the integration tests to the database run and SQL is correctresulting in "and the system as a whole works together as a top to bottom stack"

This combination has served me well so far for achieving a high quality of testing and product as well as maintaining speed of unit test development and agility to change.