Six Tips for Using PostgreSQL in Functional Tests

In 2018, working at Akvelon Inc. I interviewed one person. Before the interview, they gave me his test task for verification: a small web application like a notebook or todo list - React \ TypeScript, C # on the back end and MS SQL Server as a persistent storage. The application was fashionable: with an abundance of unit-tests on mocks, packed in a docker-image - it is clear that the person tried. And this solution had only one drawback - it did not work. Absolutely. Failed while trying to save a new row to the database.

I remembered this case very well, as it highlighted several common problems at once.

The first of these is false confidence from unit tests. Even 100% coverage of the code with tests does not guarantee that there are no errors in it.

– . , . : , production’. , Oracle, H2\HSQLDB, , , production (boolean, group by ).

PostgreSQL 10- 11-. ( ) , .

, : Embedded PostgreSQL Yandex QATools. . :

• TestContainers,
• otj-pg-embedded,
• Zonky.

TestContainers , Docker’ .

otj-pg-embedded. , , HeadHunter DZone. Yandex QATools , otj-pg-embedded Windows MacOS , - . Liquibase Flyway « »:

abstract class DatabaseAwareTestBase {
    @RegisterExtension
    static final PreparedDbExtension embeddedPostgres =
            EmbeddedPostgresExtension.preparedDatabase(
                    LiquibasePreparer.forClasspathLocation("changelogs/changelog.xml"));
}

GitHub.

. PostgreSQL : , .

, , , , , . , .

. , delete from <table> . truncate. , . truncate , cascade:

truncate table tableA;
truncate table tableB cascade;
truncate table tableE;

, : , :

truncate table tableA, tableB, tableC, tableD, tableE;

cascade, ; . , .

, , , . , 11- Postgres ( 11- , 12-). otj-pg-embedded Zonky, , :

testImplementation enforcedPlatform('io.zonky.test.postgres:embedded-postgres-binaries-bom:11.6.0')
testImplementation 'io.zonky.test:embedded-postgres:1.2.6'

, 100% , .

    @Test
    void checkPostgresVersion() {
        final String pgVersion = jdbcTemplate.queryForObject("select version();", String.class);
        assertThat(pgVersion, startsWith("PostgreSQL 11.6"));
    }

, – . Java-, Linux, . , (, ), CI- , .

SQL

: plain SQL . xml, yml , , . , - \ . xml- psql\pgAdmin – . C plain SQL .

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
    <changeSet id="orders_table_create_2020-05-09" author="ivan.vakhrushev">
        <createTable tableName="orders">
            <column name="id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="shop_id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="buyer_id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)">
            <column name="creation_time" type="TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="update_time" type="TIMESTAMP"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

--liquibase formatted sql
--changeset ivan.vakhrushev:orders_table_create_2020-05-09
create table if not exists orders
(
    id bigint not null primary key,
    shop_id bigint not null,
    buyer_id bigint not null,
    status varchar(20),
    creation_time timestamp not null,
    update_time timestamp
);

. Java 8 Java 11 . Instant\LocalDateTime. PostgreSQL Timestamp , «» . Java . , «» . , - :

Timestamp.valueOf(localDateTime.truncatedTo(ChronoUnit.MICROS));
Timestamp.from(instant.truncatedTo(ChronoUnit.MICROS));

. , - .
.