Is it a good practice to hit actual database for Integrations test(Not prod but pre prod DB) for Jenkins Night builds? The reason I am asking this is because our DAO uses procedures to perform simple CRUD operations so it is almost impossible to test operation like INSERTS/UPDATES/DELETE even against in memory H2 database.


Some integration and system tests do, indeed, rely on a database in order to run.

This being said:

  • Be aware that tests that do rely on a database are slower than unit tests, by a factor of hundreds of thousands. If you have a lot of those tests, it can become very problematic.

    The fact that you mention nightly builds, as opposed to continuous integration where builds run after each commit might indicate that the performance of the tests is already an issue. If this is the case, reconsider how you test, and what you test.

  • Do you really need integration tests to test CRUD operations? Do you understand the difference between unit tests and integration/system tests?

  • If you have a lot of CRUD operations, use a library/framework/tool which would either write code for you or do those operations without the need for custom source code. There are ORMs for that, available for most programming languages and ecosystems. This way, you will not need to test those CRUD operations at all.

  • If you are still convinced that you need a database to be involved in your tests, search for the common practices for your specific database in order to find how to restore the original state after every test efficiently. In some cases, for instance, one would use an in-memory temporary database which would be created before each test, and then will be destroyed at the end of it. Others would use database snapshots, which are often amazingly fast.

  • Performance of tests is not an issue .They run within seconds.I have tons of microservices .About your question Do you need to test your CRUD operation. If you don't test your CRUD operation (against in-memory or actual DB) then how would you determine if your INSERT is working fine since table columns have checks and constraints.And how would if test TRANSACTIONS. But I do agree we should use ORM for simple CRUD operations which are easier to tests against in-memory databases Mar 4 at 6:24

Although Arseni Mourzenko's answer covers a lot of the points, it's important to make a distinction between different databases.

Tests, especially those that manipulate data, should probably be run against a database that is instantiated for those tests. Assuming that your pre-prod environment is also used by people to perform manual testing or demonstrations, you don't know what the state of the database is. If you ensure that the database starts in a known state, you can make stronger assertions about what the end state will be. If the database isn't in a known start at the start of the test, the test could erroneously fail or succeed based on the state of existing data or the assertions will need to account for unknown data in the database.

I do think that it makes sense to run tests against a real database on a regular basis, especially if there are stored procedures in the database that need to be tested in conjunction with the application code. However, this should be a database that you have good control over so you can assert a state at the start and end of tests.

  • 1
    I'd never dare to "test" against a real(!) database. Rather, an exact image of it. When changes such as stored-procedures are applied against the one, they must likewise be applied against the other. (P.S.: In making this comment, I understand – and refer to – the context that you've stated in this post.) Mar 4 at 2:22
  • @MikeRobinson Testing against an image is OK, too. I prefer to start with an empty database and seed it for sets of tests. Without seeing the tests and how they are structured, I'm not sure if an image or seeding or a blank database is the best starting point - it could also vary by test set.
    – Thomas Owens
    Mar 4 at 10:36
  • @ThomasOwens i am using Oracle database.How do i create an empty database ?Does empty database means no objects are present i.e not even procedures that have INSERT/UPDATE logic.Or you mean tables with no data?I am thinking implementing your way of calling real database(in my case PRE-PROD DB which is used by testing teams too) Mar 4 at 16:34
  • @PaleBlueDot By "empty database", I mean stored procedures, triggers, and empty tables. Your tests would populate the data tables as necessary. I'm not familiar with Oracle database, so I can't provide detailed guidance, but I would be against using a database used for other purposes as part of the test framework. Your automated tests should have their own database that can be created, populated, and cleared as necessary during testing.
    – Thomas Owens
    Mar 4 at 17:22
  • @PaleBlueDot: I assume your application is restricted to one DB Schema / DB User (which in Oracle means the same), and the schema name / user name is hopefully configurable. Then you can provide a schema as a template, including test data, and clone it for your tests.
    – Doc Brown
    Mar 5 at 11:00

Yes. That is the point of Integration tests.

An integration test is there to prove that you program, and program X play nicely together. This cannot be verified without program X really being there. Of course make sure its not the production program X, just another instance of it.

For actual unit tests though. Avoid at all costs and mock the database out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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