There were some proposed solutions to the question "How to test SQL statements in an application" -
The third one was viable and I looked into Test Containers which is actually a beautiful solution but a relatively new one. As a result, our company is sceptical of adopting it.
We use Mybatis to access PostgreSQL.
Another way would be to recreate entire schema and populate required tables before tests. Here is the problem, I could create and delete schema with tables with the same name. To avoid name collision I'd have to change schema's name, as a result, even queries should be renamed which is not at all preferred. Is there a way to do this without changing queries but pointing them to the dummy schema.
You should NOT change your queries. In tests you should only change the connection url your application will use. The problem is, how to get that url working.
To have full test coverage you need the same db (as you noticed, h2 and other in-memory db are not very compatible). postgres doesn't have in-memory mode so you have to manage the lifecycle yourself. there is a few decisions you have to make. some of them:
where will you get the db from: require all the devs to provide postgres (installation / docker / vagrant) or automate the setup?
how to prepare db for tests: manual schema setup and cleanup?
how to reset db between tests: restart? always rollback? predefined and separately defined content? some kind of reverse operations?
if and how to make those tests fast?
there are some tools that can help you solve some of the problems:
testcontainers will help you provide db.
dbunit - will help you prepare data for your test.
cons:
testegration - intents to provide you full, ready to use and extensible lifecycle (disclosure: i'm a creator).
cons:
you can also fill the gaps on your own. as always it's a trade: time vs money
you can define database configuration for test purpose and connect to your real database base for execute tests. you should access to test database configuration in test classes.
for example, if you use spring and hibernate to connect to the database, you can define a test hibernate configuration xml file where it connect to test database. then in your test classes, use this configuration file as follow:
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguratiion({testHibernate.xml, testSpring.xml , .... })
@TestExecutionListeners({...})
public class TestClass {
....
@Test
public void test1(){
...
}
}
so, you can access your test hibernate session factory to execute your queries.