3

There were some proposed solutions to the question "How to test SQL statements in an application" -

  • Using RAM memory - I can't change the configuration of staging environment where testing happens.
  • Using H2 - Not very compatible even in PostgreSQL mode
  • Use the same database to run the tests.
  • Using in-memory mode - PostgreSQL doesn't have one.

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.

Improve this question
7
  • 3
    This is a really bad idea - one mistake and you corrupt the real data. Commented Apr 24, 2017 at 10:55
  • Is there anything wrong with using a new database/schema just for testing? You should never be testing on production, live or really debug databases. Commented Apr 24, 2017 at 10:57
  • It's not actually production. The testing happens during CI. Which is not on my local system. Commented Apr 24, 2017 at 10:59
  • Indeed - if you're unable to use containerisation, you should at least be spinning up a dedicated physical database. Commented Apr 24, 2017 at 10:59
  • 1
    You can ALTER ROLE test_role SET search_path = test_schema;, assuming your queries (or function definitions) don't have schema explicitly specified. Commented Apr 24, 2017 at 11:03

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Could you give an example using mybatis? I'm not familiar with Hibernate
I'm not familiar with MyBatis or IBatis, But I found several examples by doing few search in google. I discuss about general concept and I mention hibernate as example.
pawel-malczyk.pl/wordpress/?p=189 , hmkcode.com/mybatis-spring-junit-the-missing-part , myjavaacademy.com/…; these can help you I think, but are not solution !
1

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:

  1. testcontainers will help you provide db.

  2. dbunit - will help you prepare data for your test.

    cons:

    • a lot of work is required to create and maintain schema and data. especially when your project is in a intensive development stage.
    • it's another abstraction layer so if suddenly you want to use some db feature that is unsupported by this tool, it may be difficult to test it

  3. testegration - intents to provide you full, ready to use and extensible lifecycle (disclosure: i'm a creator).

    cons:

    • free only for small projects
    • very young project

you can also fill the gaps on your own. as always it's a trade: time vs money

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.