Error installing postgis with testcontainers and prisma orm

Question

I'm creating e2e tests using testcontainers, I have a migration that installs the Postgis extension for Postgres, but I receive the following error:

Command failed: npx prisma migrate dev
    Error: P3006

    Migration `20230524164747_add_postgis` failed to apply cleanly to the shadow database. 
    db error: ERROR: could not open extension control file "/usr/local/share/postgresql/extension/postgis.control": No such file or directory
       0: sql_schema_connector::validate_migrations
               with namespaces=None
                 at schema-engine/connectors/sql-schema-connector/src/lib.rs:301
       1: schema_core::state::DevDiagnostic
                 at schema-engine/core/src/state.rs:266

      24 |
      25 |     // criar as tabelas definidas no prisma no banco de dados
    > 26 |     execSync('npx prisma migrate dev', {
         |             ^
      27 |       env: {
      28 |         ...process.env,
      29 |         DATABASE_URL: urlConnection,

      at Object.<anonymous> (src/test/app.e2e-spec.ts:26:13)

my migration:

-- CreateExtension
CREATE EXTENSION IF NOT EXISTS "postgis";

My test:

describe('test POST /user', () => {
  let prisma: PrismaClient;
  let app: INestApplication;
  let container: StartedPostgreSqlContainer;

  beforeAll(async () => {
    container = await new PostgreSqlContainer().start();

    const urlConnection = `postgresql://${container.getUsername()}:${container.getPassword()}@${container.getHost()}:${container.getPort()}/${container.getDatabase()}?schema=public`;

    process.env.DATABASE_URL = urlConnection;

    execSync('npx prisma migrate dev', {
      env: {
        ...process.env,
        DATABASE_URL: urlConnection,
      },
    });

    const moduleRef = await Test.createTestingModule({
      imports: [UserModule],
    }).compile();

    app = moduleRef.createNestApplication();

    prisma = new PrismaClient({
      datasources: {
        db: {
          url: urlConnection,
        },
      },
    });

    await app.init();
  });
});

The migrations are applied successfully, except for the migration that installs this extension. The container is created successfully! Why can't I install Postgis?

Answer 1

I found a solution, using GenericContainer with a postgis image:

dotenv.config({ path: '.env.test' });
const prismaBinary = './node_modules/.bin/prisma';

describe('[e2e] create user', () => {
  let prisma: PrismaClient;
  let app: INestApplication;
  let container: StartedTestContainer;
  const schema = `test_${randomUUID()}`;

  beforeAll(async () => {
    container = await new GenericContainer('postgis/postgis:16-3.4-alpine')
      .withEnvironment({
        POSTGRES_HOST: dbHost,
        POSTGRES_PASSWORD: dbPass,
        POSTGRES_USER: dbUser,
        POSTGRES_DB: dbName,
      })
      .withExposedPorts({ container: dbPort, host: dbPort })
      .start();

    const urlConnection = `postgresql://${dbUser}:${dbPass}@${dbHost}:${dbPort}/${dbName}?schema=${schema}`;
    process.env.DATABASE_URL = urlConnection;

    execSync(`${prismaBinary} migrate deploy`, {
      env: {
        ...process.env,
        DATABASE_URL: urlConnection,
      },
    });

    const moduleRef = await Test.createTestingModule({
      imports: [AppModule],
    }).compile();
    app = moduleRef.createNestApplication();
    app.useGlobalPipes(new ValidationPipe());

    prisma = new PrismaClient({
      datasources: {
        db: {
          url: urlConnection,
        },
      },
    });

    await app.init();
  });

  afterAll(async () => {
    await container.stop();
  });
});

You may need to increase jest timeOut, it takes a while to install postgis

jest.setTimeout(30000);