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

Answer 1

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.