flyway embedded postgres

gistfile1.txt

<dependencies>
    <!-- JUnit 5 Dependency for Testing -->
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter-engine</artifactId>
        <version>5.9.3</version> <!-- Use the latest stable version -->
        <scope>test</scope>
    </dependency>

    <!-- Flyway Dependency -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
        <version>9.10.2</version>
    </dependency>

    <!-- PostgreSQL JDBC Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.6.0</version>
        <scope>test</scope>
    </dependency>

    <!-- OpenTable Embedded PostgreSQL -->
    <dependency>
        <groupId>com.opentable.components</groupId>
        <artifactId>otj-pg-embedded</artifactId>
        <version>0.13.1</version>
        <scope>test</scope>
    </dependency>
    
    <dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.18.0</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-simple</artifactId>
    <version>1.7.32</version> <!-- Match with your SLF4J version -->
</dependency>

<dependencies>
    <!-- SLF4J Simple Binding (test scope) -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.32</version> <!-- Match this with your SLF4J version -->
        <scope>test</scope>
    </dependency>
</dependencies>


</dependencies>

gistfile2.txt

--${skip_migration}/*

CREATE OR REPLACE FUNCTION abc_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
   -- Trigger logic here
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ABC_TRG
AFTER INSERT ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION abc_trigger_function();

--${skip_migration}*/

test

import com.opentable.db.postgres.embedded.EmbeddedPostgres;
import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class FlywayMigrationTest {

    private static EmbeddedPostgres embeddedPostgres;
    private static String jdbcUrl;

    @BeforeAll
    public static void setUp() throws IOException, SQLException {
        // Start the embedded PostgreSQL instance
        embeddedPostgres = EmbeddedPostgres.builder().start();
        jdbcUrl = embeddedPostgres.getJdbcUrl("postgres", "postgres");
        // Create a new user without a password
        try (Connection conn = DriverManager.getConnection(jdbcUrl, "postgres", "postgres");
             Statement stmt = conn.createStatement()) {
            String createUserSQL = "CREATE USER test_user";  // No PASSWORD clause
            stmt.execute(createUserSQL);
        }

        // Configure and run Flyway migrations
        Flyway flyway = Flyway.configure()
                .dataSource(jdbcUrl, "postgres", "postgres")
                .locations("classpath:db/migration")
                .placeholders(Map.of("skip_triggers", "-- ")) 
                .load();
        flyway.migrate();
    }

    @AfterAll
    public static void tearDown() throws IOException {
        if (embeddedPostgres != null) {
            embeddedPostgres.close();
        }
    }

    @Test
    public void testDatabaseMigration() throws SQLException {
        try (Connection conn = DriverManager.getConnection(jdbcUrl, "postgres", "postgres")) {
            // Perform test assertions on the migrated database
        }
    }
}

test trigger

import com.opentable.db.postgres.embedded.EmbeddedPostgres;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;

public class PostgresTriggerSupportTest {

    private static EmbeddedPostgres embeddedPostgres;
    private static String jdbcUrl;

    @BeforeAll
    public static void setUp() throws Exception {
        // Start the embedded PostgreSQL instance
        embeddedPostgres = EmbeddedPostgres.builder().start();
        jdbcUrl = embeddedPostgres.getJdbcUrl("postgres", "postgres");

        // Create a sample table for testing trigger creation
        try (Connection conn = DriverManager.getConnection(jdbcUrl, "postgres", "postgres");
             Statement stmt = conn.createStatement()) {
             stmt.execute("CREATE EXTENSION IF NOT EXISTS plpgsql");
            stmt.execute("CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));");
        }
    }

    @AfterAll
    public static void tearDown() throws Exception {
        if (embeddedPostgres != null) {
            embeddedPostgres.close();
        }
    }

    @Test
    public void testTriggerCreation() {
        // Define a trigger function and a trigger on the test table
        String createTriggerFunctionSQL = """
                CREATE OR REPLACE FUNCTION test_trigger_function()
                RETURNS TRIGGER AS $$
                BEGIN
                   NEW.name := UPPER(NEW.name);
                   RETURN NEW;
                END;
                $$ LANGUAGE plpgsql;
                """;

        String createTriggerSQL = """
                CREATE TRIGGER test_trigger
                BEFORE INSERT ON test_table
                FOR EACH ROW
                EXECUTE FUNCTION test_trigger_function();
                """;

        // Execute trigger creation and assert no exceptions are thrown
        assertDoesNotThrow(() -> {
            try (Connection conn = DriverManager.getConnection(jdbcUrl, "postgres", "postgres");
                 Statement stmt = conn.createStatement()) {
                stmt.execute(createTriggerFunctionSQL); // Create the trigger function
                stmt.execute(createTriggerSQL);          // Create the trigger
            }
        }, "Trigger creation should be supported.");
    }
}