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.
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.
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.