H2 and Postgres array compatibility

Question

Can I get h2 to support Postgres array syntax

CREATE TABLE artists
(
release_id integer,
artist_name text,
roles text[]
)

I use h2 to mimic Postgres in my unit tests, but it doesn't like the above DDL because of the definition of roles (if I comment out that column it works). H2 does have an ARRAY datatype is there a way I can write so that my code would work with either h2 or postgres

In postgres you can use these syntaxes f.ex. `text[]`, `text[3]`, `text ARRAY`, `text ARRAY[3]`. But in H2 I can only see the `ARRAY` keyword alone in their docs http://www.h2database.com/html/datatypes.html#array_type — pozs, May 30 '14 at 08:12
ah okay so I can define using ARRAY but access using same Java code whether it was created as ARRAY or [] thx. — Paul Taylor, Jun 03 '14 at 14:02
mmh no. In H2 var ARRAY, in Postgres var TEXT ARRAY, not ARRAY alone. Not compatibles it seems — Whimusical, Sep 14 '16 at 17:19

yazabara · Answer 1 · 2021-08-14T21:03:57.293

In fact, you can define integration tests with real postgres DB instead of h2. It will be more usefull.

The main idea is to run docker instances with dependencies(postgres DB) before integration tests and shut down after.

Here is an example with maven:

First define rules:

                <plugin>
                    <!-- define Integration tests -->
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-failsafe-plugin</artifactId>
                    <version>3.0.0-M3</version>
                    <configuration>
                        <systemPropertiesFile>${ports.env.file}</systemPropertiesFile>
                        <includes>
                            <include>**/*IT.*</include>
                        </includes>
                        <additionalClasspathElements>
                            <additionalClasspathElement>resources</additionalClasspathElement>
                        </additionalClasspathElements>
                        <systemPropertiesFile>${it.ports.env.file}</systemPropertiesFile>
                    </configuration>
                    <executions>
                        <execution>
                            <goals>
                                <goal>integration-test</goal>
                                <goal>verify</goal>
                            </goals>
                        </execution>
                    </executions>
                </plugin>

Then need to get free ports for your dependencies (for example postgres DB)

                <plugin>
                    <groupId>org.codehaus.mojo</groupId>
                    <artifactId>build-helper-maven-plugin</artifactId>
                    <version>3.1.0</version>
                    <executions>
                        <execution>
                            <id>reserve-network-port</id>
                            <goals>
                                <goal>reserve-network-port</goal>
                            </goals>
                            <phase>generate-resources</phase>
                            <configuration>
                                <portNames>
                                    <portName>DB_PORT</portName>
                                </portNames>
                                <outputFile>${it.ports.env.file}</outputFile>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>

Then you should run and stop docker containers with dependency-services (postgres):

               <plugin>
                    <groupId>com.dkanejs.maven.plugins</groupId>
                    <artifactId>docker-compose-maven-plugin</artifactId>
                    <version>4.0.0</version>
                    <configuration>
                        <envFile>${it.ports.env.file}</envFile>
                        <envVars>
                            <COMPOSE_HTTP_TIMEOUT>120</COMPOSE_HTTP_TIMEOUT>
                        </envVars>
                        <services>
                            <service>db-postgres-test</service>
                        </services>
                        <composeFiles>
                            <composeFile>${session.executionRootDirectory}/docker-compose.db-only.yml
                            </composeFile>
                        </composeFiles>
                        <detachedMode>true</detachedMode>
                    </configuration>
                    <executions>
                        <execution>
                            <id>up</id>
                            <phase>pre-integration-test</phase>
                            <goals>
                                <goal>up</goal>
                            </goals>
                        </execution>
                        <execution>
                            <id>down</id>
                            <phase>post-integration-test</phase>
                            <goals>
                                <goal>down</goal>
                            </goals>
                            <configuration>
                                <removeVolumes>true</removeVolumes>
                                <removeOrphans>true</removeOrphans>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>

This solution helps me with the same problem earlier. I hope, it will help you.

There is modern solution: https://www.testcontainers.org/ – yazabara Apr 08 '22 at 12:40 — yazabara, Apr 08 '22 at 12:40

H2 and Postgres array compatibility

1 Answers1