Postgresql don't allow to search for functions in the public schema.

[julia-dizhak]

Hi guys

thanks a lot for this tutorial, we use it for running our backend test and it's really cool and helpful. However, we faced a problem that postgres don't apply extensions for the public schema.

a bit background
We use prisma as ORM which creates a schema for DB.
During models declaration for the ID fields prisma adjust field to dbgenerated("uuid_generate_v4()")

id    String    @id @default(dbgenerated("uuid_generate_v4()")) @db.Uuid

uuid_generate_v4() is a part of Postgres extension uuid-ossp that is installed in our database for the public schema.

In our createTestContext we dynamically generate schema before test and further uuid_generate_v4() couldn’t be found inside schema for the test. That causes backend tests to fail with uuid_generate_v4() not being found.

So far we don’t have a way to set search_path for to our_dynamic_schema,public to allow postgresql to search for functions also in the public schema.

https://github.com/graphql-nexus/tutorial/blob/master/tests/__helpers.ts#L78

      execSync(`${prismaBinary} db push`, {
        env: {
          ...process.env,
          DATABASE_URL: databaseUrl,
        },
      });

The way I fixed is next, but I am not sure if it's the best solution

     const client = new Client({
        connectionString: databaseUrl,
      });

      await client.connect();

      await client.query(`CREATE SCHEMA IF NOT EXISTS "${schema}"`);
      const extension = await client.query(
        "select * from pg_extension where extname = 'uuid-ossp' and extnamespace = (SELECT to_regnamespace('pg_catalog')::oid)"
      );

      if (extension.rows.length === 0) {
        // https://stackoverflow.com/questions/12986368/installing-postgresql-extension-to-all-schemas
        await client.query(
          'CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA pg_catalog'
        );
        await client.query('ALTER EXTENSION "uuid-ossp" SET SCHEMA pg_catalog');
      }

      await client.end();