Embedded PostgreSQL for Java JUnit tests

Question

Is there an embedded PostgreSql so that we could unit test our PostgreSql driven application?

Since PostgreSql has some dialects, it's better to use embedded PostgreSql itself than other embedded databases.

Embedded does not necessarily mean it must be embedded in the JVM process. It also does not necessarily need to use in-memory persistence. It should be loaded automatically by the dependency management (Maven, Gradle), so that Unit tests can run on every machine without having to install and configure a local PostgreSQL server.

Tanana

Answer 1

No, there is no embedded PostgreSQL, in the sense of an in-process-loadable database-as-a-library. PostgreSQL is process oriented; each backend has one thread, and it spawns multiple processes to do work. It doesn' make sense as a library.

The H2 database supports a limited subset of the PostgreSQL SQL dialect and the use of the PgJDBC driver.

What you can do is initdb a new temporary database, start it with pg_ctl on a randomized port so it doesn't conflict with other instances, run your tests, then use pg_ctl to stop it and finally delete the temporary database.

I strongly recommend that you run the temporary postgres on a non-default port so you don't risk colliding with any locally installed PostgreSQL on the machine running the tests.

(There is "embedded PostgreSQL in the sense of ecpg, essentially a PostgreSQL client embedded in C source code as preprocessor based C language extensions. It still requires a running server and it's a bit nasty to use, not really recommended. It mostly exists to make porting from various other databases easier.)

Tungus

That's what I'd do as well. A batch file/shell script to setup Postgres without any installer is about 5 lines of code. Although doing an initdb on every unit test-run is probably going to be too slow. – Galenism

@a_horse_with_no_name Yeah, you can always unzip a pre-initdb'd database or copy from a clean template that you initdb if it's missing during the build process. Painful when you upgrade Pg though. Personally I find initdb plenty fast enough, but I don't use spinning disks anymore. As for the batch file - the main challenge is making sure you use a non-conflicting port (especially if running concurrent tests) and making really sure you terminate the server when you're done. Especially on Windows, where you can't delete the datadir until the server is stopped. – Tungus

You say "it doesn't make sense for a process oriented piece to be packaged as a library". Why not? You can automate IE nowadays using a Java library. Sure, processes will be spawned behind the scenes, but you can still talk comfortably to a Java library to achieve it. The same should be possible with PostgreSQL. You can get close to that goal by embedding a PostgreSQL installer, but there's a lot of boilerplate just to set it up like that. – Gavrila

@MihaiDanila The key difference is that PostgreSQL is writing to shared, critical data files. Process management must be exactly right. What you describe could be achieved with a tool that automates pg_ctl from Java, and that'd certainly be useful, but there's no need or benefit to bringing the PostgreSQL server its self in-process. – Tungus

Can PostgreSQL be started on the fly whether or not it has been previously installed on the machine? Perhaps that's all that the OP needs. I know that's what I would need. I would not want for my unit tests to depend on PostgreSQL being pre-installed, because this would mean depending on the correct functioning of processes in a different team, specifically, the team that maintains the TeamCity agents. Plus, I would expect the fresh process approach to allow full server configurability, something generally difficult in a pre-existing installation (probably as root). – Gavrila

@MihaiDanila Absolutely, and that's what I would recommend. Just bundle the .zip of the Pg binaries, and launch Pg using pg_ctl on demand. You can edit the configs after initdb and with initdb arguments, control things like ports via environment variables or args to pg_ctl, etc. – Tungus

Cool. And if all of the details of starting pg_ctl and initdb could be hidden behind a connection string URL, similar to the (now retired) MySQL MXJ connector library, that would be ideal. – Gavrila

This is not true: github.com/yandex-qatools/postgresql-embedded – Unaccountedfor

Answer 2

There is an "embedded" PostgresSQL server that has been designed for unit testing from Java:

https://github.com/yandex-qatools/postgresql-embedded

Embedded postgresql will provide a platform neutral way for running postgres binary in unit tests. Much of the code has been crafted from Flapdoodle OSS's embed process

As an aside, there also exists similar projects for Mongo, Redis, Memcached and nodejs.

Aleksandrovsk

why isn't this marked as the correct answer? is there a downside to this lib? does it have all the features of postgre? Can it be embelished with modules etc? – Goeger

I can testify that it works well. Standard Postgres. Example usage: github.com/icgc-dcc/dcc-submission/blob/develop/… github.com/icgc-dcc/dcc-submission/blob/… – Aleksandrovsk

what 'embedded' means is actually quite unclear, but to me it is running in memory just like H2. and yandex-qatools/postgresql-embedded does not conform to this definition. in fact it downloads the specified postgres version, unpacks it to temporary dir and launches the server, all this leads to disk overhead, which is hardly acceptable in non enterprise environment. i'm judging of this sample project github.com/scottmf/postgresql-integration-test , which is still a good example. correct me if i'm wrong – Yarkand

A few members of my team implemented github.com/opentable/otj-pg-embedded, and it seems to be serving them well. It has cleaner interface when working with Spring configs. – Oriel

This solution has issues if you try to use it out of its default configuration (download package from different place etc.) – Pacifically

Note if running on a mac you may need to define the following environment variables, such that they are available both to your build and IDE:export LC_ALL="en_US.UTF-8" export LC_CTYPE="en_US.UTF-8" – Contemplate

@RaduSimionescu Because it's not, IMO, embedded PostgreSQL. It's a heavily modified/hacked up server based on PostgreSQL that may or may not work for a given purpose. – Tungus

Answer 3

I tried the project suggested by @btiernay (yandex-qatools). I spent a good few days with this and without any offence it's over engineered solution which doesn't work in my case as I wanted to download the binaries from internal repository rather than going to public internet. In theory it supports it but in fact it doesn't.

OpenTable Embedded PostgreSQL Component

I ended up using otj-pg-embedded and it works like a charm. It was mentioned in comments so I thought I'll mention it here as well.

I used it as standalone DB and not via rule for both unit tests and local development.

Dependency:

<dependency>
    <groupId>com.opentable.components</groupId>
    <artifactId>otj-pg-embedded</artifactId>
    <version>0.7.1</version>
</dependency>

Code:

@Bean
public DataSource dataSource(PgBinaryResolver pgBinaryResolver) throws IOException {
    EmbeddedPostgres pg = EmbeddedPostgres.builder()
        .setPgBinaryResolver(pgBinaryResolver)
        .start();


    // It doesn't not matter which databse it will be after all. We just use the default.
    return pg.getPostgresDatabase();
}

@Bean
public PgBinaryResolver nexusPgBinaryResolver() {
    return (system, machineHardware) -> {
        String url = getArtifactUrl(postgrePackage, system + SEPARATOR + machineHardware);
        log.info("Will download embedded Postgre package from: {}", url);

        return new URL(url).openConnection().getInputStream();
    };
}

private static String getArtifactUrl(PostgrePackage postgrePackage, String classifier) {
    // Your internal repo URL logic
}

Pacifically

Is it possible to create schema from .sql file in otj-pg-embedded. I am not using spring. I did not find any example without Rule. – Lynnell

See dzone.com article: Using Embedded PostgreSQL Databases For Testing – Croesus

Been using otj-pg-embedded with great success for years. Works like a charm – Sachi

Answer 4

I am using the container instance of PostgreSQL in the tests. https://www.testcontainers.org/#about https://www.testcontainers.org/modules/databases/jdbc/

dependencies:

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.7.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-params</artifactId>
            <version>5.7.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>5.7.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <version>1.15.3</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>1.15.3</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>1.15.3</version>
            <scope>test</scope>
        </dependency>

And do the tests:

@SpringBootTest
@ActiveProfiles({"test"})
@Testcontainers
class ApplicationTest {
    @Container
    static PostgreSQLContainer<?> postgreSQL = new PostgreSQLContainer<>("postgres:12.7");

    @DynamicPropertySource
    static void postgreSQLProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.username", postgreSQL::getUsername);
        registry.add("spring.datasource.password", postgreSQL::getPassword);
    }


    @Test
    void someTests() {

    }

in application-test.yml:

source:
  datasource:
    url: jdbc:tc:postgresql:12.7:///databasename

Tabard

Answer 5

You can use a container instance of PostgreSQL.

Since spinning a container is a matter of seconds, this should be good enough for unittests. Moreover, in case you need to persist the data, e.g. for investigation, you don't need to save the entire container, only the data files, which can be mapped outside of the container.

One of example of how to do this can be found here.

Godson

Answer 6

If you are looking to run an in-process version of postgres from an Integration (or similar) test suite, the postgresql-embedded worked fine for me.

I wrote a small maven plugin that can be used as a maven wrapper around a forked version of postgresql-embedded.

Lanham

Again, be warned this solution has issues if you step outside of the default configuration – Pacifically

Fair enough. But, in the maven plugin, I specifically added a configuration option for setting the download URL for binaries to a location other than the default. – Lanham

Please checkout github.com/aramcodz/embedded-postgres-maven-plugin and look for the "downloadUrl" plugin configuration parameter. – Lanham

Did you try that? Because the underlying library has bugs. No plugin can fix this - the library itself has the bug. Try to clean local cache of postgre binaries, downloadnfrom local url (file:///...) while being disconnected from internet. I bet it won't start up (at least it was failing for me and I debugged it to a point where I clearly saw a bug which ignores any settings passed to initDb command) – Pacifically

@JanZyka, Yes, I just tried it and found that the download from local succeeded. I cleaned the local cache of binaries (moved to another local folder (away from ~/.embedpostgresql), and used the downloadUrl of file:////my_local_folder/dev/tmp/ in the maven config. Here is a snippet of the output: 'Download Version{9.2.4-1}:OS_X:B64 START Download Version{9.2.4-1}:OS_X:B64 DownloadSize: 64139929 Download Version{9.2.4-1}:OS_X:B64 0% 1%...94% 95% 96% 97% 98% 99% 100% Download Version{9.2.4-1}:OS_X:B64 downloaded with 62636kb/s Download Version{9.2.4-1}:OS_X:B64 DONE' – Lanham

That's interesting (really). I should have said my problem was that the process in fact run 3 processes: Postgres and this one runs 2 subprocesses as part of the beforeExecute() and afterExecute(): initDb and createDb. In my case the first download succeeds but the other two don't inherit the download config. Key to reproduce the bug is also changing the temp binary folder to other place. But well ... if it works for you I might have done some mistake. Not convinced yet though :) – Pacifically

Answer 7

I use Zonky Embedded Postgres. It does not require Docker, instead it downloads PostgreSQL into a temporary directory (if it wasn’t found there from a previous test run). Just add the Maven dependency and do something like this JUnit 5 example (JUnit 4 instructions are in the Readme):

package org.example.test;

import io.zonky.test.db.postgres.junit5.EmbeddedPostgresExtension;
import io.zonky.test.db.postgres.junit5.SingleInstancePostgresExtension;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import static org.junit.jupiter.api.Assertions.*;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.RegisterExtension;

public final class EmbeddedPostgreSQLTest {

    @RegisterExtension
    public SingleInstancePostgresExtension pg = EmbeddedPostgresExtension.singleInstance();

    @Test
    void demo() throws SQLException {
        final DataSource dataSource 
                = pg.getEmbeddedPostgres().getDatabase("postgres", "postgres");
        try (final Connection connection = dataSource.getConnection();
             final Statement statement = connection.createStatement();) {
            final ResultSet resultSet = statement.executeQuery("SELECT 3 * 7");
            assertTrue(resultSet.next());
            final int result = resultSet.getInt(1);
            assertEquals(21, result);
        }
    }
}

Augustineaugustinian