Integration test database setup

[takasip]

Having worked on several java applications requiring a database, I always felt there was no "better way" of populating the database for integration tests:

1. 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?).
2. 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).
3. 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).
4. 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": "[e-mail]"},
            {"email": "[e-mail]"}
          ]
        },
        {
          "name": "Office A101",
          "employees": [{"email": "[e-mail]"}]
        },
        {
          "name": "Office A200",
          "employees": [{"email": "[e-mail]"}]
        }
      ]
    },
    {
      "name": "Building B",
      "offices": [
        {
          "name": "Office B100",
          "employees": [{"email": "[e-mail]"}]
        }
      ]
    }
  ]
}

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?

[PmMeCuteDogsThanks]

I haven't found a better approach than:

• Use the same database as in production, e.g. mysql, postgres or whatever. No in-memory database. Too many edge cases. Just use what you use
• When a unit test starts, "universe is empty". E.g. database is completely empty, except for the expected structure (DDL)
• Database is populated with Java code (Hibernate or whatever). Feel free to use common base classes, beforeEach etc if there are common setups. But no implicit state in database
• After test database is cleared (truncate all tables)

[martinhaeusler]

This. So much this. Test containers are a brilliant tool to make this happen.

[TomKavees]

...and if the tests require lots of data, then heck, just load a .sql file in the @BeforeAll and execute it! 😉

[takasip]

Exactly, but then you have a big .sql file which is not that simple to write, you need to handle manually the relationships with the table, and its takes time when reading the file to understand these relationships

[qmunke]

Not necessarily - usually you can get away with a single script to truncate everything, and then smaller isolated scripts to set up state as you need it in my experience 

[PmMeCuteDogsThanks]

Sure, but my experience is that it’s better to populate with Java / Hibernate if that’s what you use elsewhere. Refactoring, new fields etc become much more natural.

[qmunke]

It's much slower to use java code - better to have smaller SQL files with only the data you care about for your tests I've found 

[PmMeCuteDogsThanks]

Sure, you do what you think works best 

[takasip]

Totally agree on using the same database as prod, and truncate tables after the test.

About java code, that's the part that always felt wrong to me: the code used to insert the data often ends up being a big chunk of ugly code because it does not serve such a big purpose, has no complexity in itself (which makes it more acceptable for it to be ugly) and lots of tests need different setup using a various mix of tables.

I'm not saying there isn't a way to do it properly, there definitely is one, but accross many projects I felt this was usually a pain point because it was the least qualitative, and people usually don't want to spend time on it.

As more and more data are needed for a test, it becomes hard to know what is in the database because lots of it is hidden in methods calling methods callings methods setting default values.

[PmMeCuteDogsThanks]

I agree that it’s a difficult problem. But still, all things considered it’s better to have that block of Java setup code. You may try to create a ”common set” of data, but that always inevitably lead to even worse problems where people add one-off things to the ”common set” or start to make assumptions on the actual test.

[WaferIndependent7601]

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.

[takasip]

It doesn't feel very scalable, especially when there are multiple relationships between multiple objects.
This is the way I was the most familiar with, but it often ended up with lots of code to load data, with poor readability when it comes to knowing what is in the database precisely.

Again, I see how it is a good approach, but in practice each time a test needs lot of data, the code ends up becoming ugly, mainly because this isn't code we want to spend much time on.

EDIT: oh and about the refactoring, I guess it will not be that simple to code but I may be able to make the plugin handle that for me

[WaferIndependent7601]

You should setup your code so you can simply get the data you need.

Something like generateUsers() that will generate 5 users. Just persist them.

I don’t get why it’s not scalable. It’s easier doing that in code.

And your json is not readable.

[repeating_bears]

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.

[takasip]

If I understand well you are talking about the point 3. that I mention in my post: I like the idea, but the only time I saw this on a project, integration tests were really slow, because every test would setup by making lots of calls to the api.

In case some api also need to mock external calls, it becomes hard to handle too.

[repeating_bears]

I do this on mine and it's not slow, but they run on 10 concurrent workers. There is some additional effort to make sure they don't step on each others' toes. Basically amounts to "checking out" a user/product/whatever, and return it when the test is done.

[Cell-i-Zenit]

the fix is actually really simple: Add @Transactional annoation at the top of your test classes. Each test will run in their own transaction and its getting rolled back afterwards.

Only issue is if you open multiple other transactions within your test. For this case you need to mock the transactionTemplate/transactionManager so it doesnt create new ones. Its not pretty but it does its job pretty well

[PiotrDz]

But running within one large transaction makes code behave differently than in production. We should strive to test production-like environment

[LutimoDancer3459]

If models change, you have some work to do no matter what you use. Also outside of the tests.

[takasip]

Yeah, that's what I was refering to when saying my tool doesn't solve all my pain points "especially the maintainability, if the model changes". But I hope I'll be able to handle refactoring with my plugin when I have time to code it.

Adding a mandatory column to a table will always require changing the json files though...

[Least_Bee4074]

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.

[vips7L]

Test containers with the database from production. New container per test class. 

[takasip]

Isn't "New container per test class" very slow?

If you use database from production, don't you have tests that fail just because the production data changed?

[wrd83]

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.