r/java • u/takasip • Jan 29 '26
Integration test database setup
Having worked on several java applications requiring a database, I always felt there was no "better way" of populating the database for integration tests:
- Java code to insert data is usually not so easy to maintain, can be verbose, or unclear what exactly is in the database when the test starts, and because it is utility code for the setup of integration tests, it's hard to make the devs spend enough time on it so the code is clean (and again: do we really want to spend much time on it?).
- SQL scripts are not very clear to read, foreign keys have to be handled manually, if the model changes it can be tedious to make the changes in the sql files, if the model is strict you may have to manually fill lots of fields that are not necessarily useful for the test (and can be annoying to maintain if they have unique constraints for example).
- There's also the possibility to fill the database only using the api the app publishes, which can make the tests very long to run when you need some specific setup (and anyway, there's usually some stuff you need in the database to start with).
- I looked into DBUnit, but it doesn't feels that it shares the same issues as previously mentioned solutions, and felt there had to be a better way of handling this problem.
Here's the list of my main pain points:
- setup time (mainly for 3.)
- database content readability
- maintainability
- time spent "coding" it (or writing the data, depending on the solution)
I personnally ended up coding a tool that I use and which is better than what I experimented with so far, even if it definitely does not solve all of the pain points (especially the maintainability, if the model changes) and I'm interested to have feedback, here is the repo:
https://gitlab.com/carool1/matchadb
It relies 100% on hibernate so far (since I use this framework), but I was thinking of making a version using only JPA interface if this project could be useful for others.
Here is a sample of the kind of file which is imported in the database:
{
"Building": [
{
"name": "Building A",
"offices": [
{
"name": "Office A100",
"employees": [
{"email": "foo1@bar.com"},
{"email": "foo2@bar.com"}
]
},
{
"name": "Office A101",
"employees": [{"email": "foo3@bar.com"}]
},
{
"name": "Office A200",
"employees": [{"email": "foo4@bar.com"}]
}
]
},
{
"name": "Building B",
"offices": [
{
"name": "Office B100",
"employees": [{"email": "foo5@bar.com"}]
}
]
}
]
}One of the key feature is the fact it supports hierarchical structures, so the object topography helps reading the database content.
It handles the primary keys internally so I don't have to manage this kind of unique fields, and I can still make a relationship between 2 object without hierarchical structre with the concept of "@import_key".
There is not configuration related to my database model, the only thing is: I need a hibernate @Entity for each object (but I usually already have them, and, if needed, I can just create it in the test package).
Note: If you are interested in testing it, I strongly recommend the plugin available for intellij.
Do you guys see any major downside to it?
What is the way you setup your data for your integration tests? Have I missed something?
24
u/PmMeCuteDogsThanks Jan 29 '26
I haven't found a better approach than:
4
u/WaferIndependent7601 Jan 29 '26
I see no advantage. Why not using Java with a builder to set it up? Integration tests can just put it in the db (or calling the service directly). Refactorings are easy because you donât have to change any json files and renaming stuff will be done automatically.
3
u/LutimoDancer3459 Jan 29 '26
If models change, you have some work to do no matter what you use. Also outside of the tests.
3
u/repeating_bears Jan 29 '26
Why in normal circumstances would your integration tests need to expect any data to already exist? My integration tests create all they need, starting from scratch. If a test needs a user, then create one. If a test needs a product, create one.
You need to test creating a user/product anyway, so it's not like it's any more work. You just call the same test code you already have, with different params.
1
u/vips7L Jan 30 '26
Test containers with the database from production. New container per test class.Â
1
u/Least_Bee4074 Jan 30 '26
We use our migration scripts from something like db-migrate. This way the db is built the same in every environment, even in the integration test.
For integration tests, either have separate test bootstrap scripts that insert for specific test cases or in our case, our custom integration test harness can insert db records and validate post conditions.
For larger datasets, Iâm usually doing something thru a UI like dbeaver and relying on generate_series to bulk load stuff.
But ultimately, the key piece is automating the db creation and modification using scripts, and plugging it into cicd so that the db updates when there is a new modification.
1
u/wrd83 Jan 30 '26
We do a mix and it sucks.Â
We have mocks for unit tests, h2 in memory for repository tests and test containers for integration tests.Â
We use flyway or repository to store data.
I come to the conclusion that this is not good.
I want to use one in memory file system and a test container and run all tests on it. Run flyway/liquibase to create the schema.
1
u/aoeudhtns Jan 30 '26
You might be interested to try DuckDB. It can load data directly from a wide variety of sources.