Spring test @Sql throws "Unterminated dollar quote started ... Expected terminating $$"

Question

I am testing my Spring Boot API with a test class (FoodE2eTest) and I have a separate SQL script file (setup-test-schema.sql) that I want to run before the tests start to set up schema, tables, and data.

I am using PostgreSQL.

The problem

When I run the file by itself then it works fine but when I run the test class then I get the following error:

Unterminated dollar quote started at position 3 in SQL
DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public') THEN DROP SCHEMA public CASCADE.
Expected terminating $$

Configuration file

...
spring.datasource.url=jdbc:postgresql://localhost:5432/testdb
spring.datasource.username=testuser
spring.datasource.password=testpass
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
...

Test class

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@TestPropertySource(locations = "/application-test.properties")
@AutoConfigureMockMvc
@Sql(scripts = {"/migration/setup-test-schema.sql"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
public class FoodE2eTest {
...
}

SQL file

-- drop everything
DO
$$
    BEGIN
        IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public') THEN
            DROP SCHEMA public CASCADE;
        END IF;
    END
$$;

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO testuser;

-- create tables
CREATE TABLE public.user(
...

---

The problem persisted

1. With no changes when I specified the language.

DO
$$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public') THEN
        DROP SCHEMA public CASCADE;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

2. When I moved the @Sql annotation to method level.

Answer 1

Found a solution but I don't know precisely why it works. I guess the Spring framework test environment doesn't support double dollar signs.

Fixed with this change

-- drop everything
DO
'
BEGIN
    IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = ''public'') THEN
        DROP SCHEMA public CASCADE;
    END IF;
END;
' LANGUAGE PLPGSQL;

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO testuser;


-- create tables
CREATE TABLE public.user
(