[PostgreSQL] - setting a schema clobbers the postgresql `search_path` instead of prepending to `search_path`

[copiousfreetime]

Bug description

When

• postgresql extensions are always installed in a specific namespace (for instance heroku_ext -- see Changes to PostgreSQL extension schema management)
• and that namespace is added to the global postgresql search_path
• and the prisma datasource url includes a schema=myschema parameter
• and a migration uses a type from an extension e.g. citext

then the migration fails because the type from the extension is not found.

Migration name: 20220324172504_create_users
Database error code: 42704
Database error:
ERROR: type "citext" does not exist
Position:
  0
  1 -- CreateTable
  2 CREATE TABLE "users" (
  3     "id" UUID NOT NULL DEFAULT gen_random_uuid(),
  4     "email" CITEXT NOT NULL,
DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42704), message: "type \"citext\" does not exist", detail: None, hint: None, position: Some(Original(101)), where_: None, schema: None, table: None, column: None, datatype:

How to reproduce

I've created a github repo with instructions on how to duplicate this:

https://github.com/dojo4/prisma-extension-namespace-issue

Expected behavior

I expect a user table with an email column of type citext to exist.

Prisma information

• https://github.com/dojo4/prisma-extension-namespace-issue/blob/main/db/schema.prisma

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node.js version: v16.15.1
• Hosting: Heroku

Prisma Version

prisma                  : 4.1.1
@prisma/client          : 3.15.2
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at ../../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at ../../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at ../../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at ../../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash    : 8d8414deb360336e4698a65aa45a1fbaf1ce13d8
Studio                  : 0.469.0
Preview Features        : interactiveTransactions

[copiousfreetime]

As a follow on to this - I have investigated the issue and it looks like it boils down to quaint setting the search_path instead of prepending to the search path:

See: https://github.com/prisma/quaint/blob/385682fd0211c76f1187c10708c49da05e65bdcd/src/connector/postgres.rs#L604-L617

// A SetSearchPath statement (Display-impl) for connection initialization.
struct SetSearchPath<'a>(Option<&'a str>);


impl Display for SetSearchPath<'_> {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        if let Some(schema) = self.0 {
            f.write_str("SET search_path = \"")?;
            f.write_str(schema)?;
            f.write_str("\";\n")?;
        }


        Ok(())
    }
}

And : https://github.com/prisma/quaint/blob/385682fd0211c76f1187c10708c49da05e65bdcd/src/connector/postgres.rs#L529-L535

        let session_variables = format!(
            r##"
            {set_search_path}
            SET NAMES 'UTF8';
            "##,
            set_search_path = SetSearchPath(url.query_params.schema.as_deref())
        );

I believe the proper solution is to prepend the schema to the search path instead of clobbering the search path.

[copiousfreetime]

As a note to anyone else having this problem. For the moment, we are mitigating this in our systems by adding a line similar to the following to the top of our generated migration.sql files. This is specific to our heroku deployment, and is an example others can use:

set search_path = myschema, "$user", public, heroku_ext;

Not all the migrations are getting this, just those that reference anything provided by an extension.

I don't know how this bug affects prisma db push.

[tomhoule]

@copiousfreetime would, instead of mutating the search path, letting you set multiple schemas in the search path from the connection string, work? I am not aware of other tools that mutate the search path instead of setting it altogether.

[copiousfreetime]

@tomhoule I think that would be perfectly fine. I just suggested mutating search_path as it looked like that's what the code would support most easily. Are you thinking about doing something like:

postgresql://user:pass@host/mydb?search_path=mobile,"$user",public,heroku_ext

That would probably work well, and would probably need to be coordinated with #1122 depending on how the that is resolved so that we shouldn't end up doing mydb?schema=mobile&search_path=mobile,"$user",public,heroku_ext and make things even more difficult.

Also - would it be worth adding a search_path parameter to the datasource? Something like:

datasource db {
    //...
    search_path = [ "mobile", '"$user"', "public", "heroku_ext" ]
}

Generally it looks like prisma keeps connection parameter's out of the data source - so that might not be feasible, but also - search_path isn't really a connection parameter, its a session parameter.

I'm open to whatever works best, and assists with relatively straightforward fixes to the future problems we haven't encountered yet 😁

[tomhoule]

Thanks for the details, they really help. We are currently working very actively on https://github.com/prisma/prisma/issues/1122 so this is relevant. I'll try to come back to this issue as soon as there is movement on that front, I agree there is room for more flexibility.

[copiousfreetime]

One other thought I just had, since in our deployment (heroku) we don't manage the DATABASE_URL parameter, that is set by heroku automatically, we have to tack on the url parameters via expanding environment variables.

Although we didn't put in a top level .env, we added a <schema folder>/.env.

It is probably worth noting this use case in the heroku deployment documentation. -- I guess I could edit that page on github. Maybe I will 😄 .

[janpio]

Internal note: Next step is a writeup what this is and what it means.

[Druue]

Hey, I took a look at this using the postgresqlExtensions preview feature and it's definitely better ~~but still has issues~~ Edit: I spoke to a teammate, this is working as intended using the flow you defined above.

For comparison: schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [citext(schema: "extensions_only")]
}

model User {
  id    String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email String? @unique @db.Citext

  @@map("users")
}

package.json

...
"dependencies": {
    "@prisma/client": "^4.15.0"
  },
"devDependencies": {
    "prisma": "^4.15.0"
  }
...

cli output Note how this no longer complains about not knowing what citext is / not finding a definition.

❯ npx prisma migrate dev                                                                        
Prisma schema loaded from db/schema.prisma
Datasource "db": PostgreSQL database "extension_test", schema "myschema" at "localhost:5432"

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[+] Added extensions
  - citext

? We need to reset the "myschema" schema at "localhost:5432"
Do you want to continue? All data will be lost. › (y/N)

Prisma Version

❯ npx prisma -v         
prisma                  : 4.15.0
@prisma/client          : 4.15.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 8fbc245156db7124f997f4cecdd8d1219e360944 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 8fbc245156db7124f997f4cecdd8d1219e360944 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.15.0-28.8fbc245156db7124f997f4cecdd8d1219e360944
Default Engines Hash    : 8fbc245156db7124f997f4cecdd8d1219e360944
Studio                  : 0.484.0
Preview Features        : postgresqlExtensions

For reference, the code snippets mentioned in the OP can now be found at per quaint's move: engines/quaint/connector/postgres.rs/750 engines/quaint/connector/postgres.rs/630

[Druue]

Okay, so, I can replicate the initial error that you showed still using the postgresqlExtensions (and multiSchema) preview feature.

Add to schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "multiSchema"]
}

datasource db {
  provider   = "postgresql"
  url        = "postgres://postgres:prisma@localhost:5433"
  extensions = [citext(schema: "extensions_only")]
  schemas    = ["public", "extensions_only"]
}

model User {
  id    String  @id
  email String? @unique @db.Citext

  @@map("users")
  @@schema("public")
}

run prisma migrate dev

[oswaldoacauan]

any updates? Not cool to have to be calling SET search_path on every single CRUD operation.

[leppaott]

We set the search_path for the user given on the connection string with: ALTER ROLE user" SET search_path = ... Even though the connection string then only contains one main schema it would be nice if it used the whole search_path - but maybe here it makes sense to allow multiple comma delimited schemas. This would allow appending public for the duration of migrations since now most migration files need to modify it on top like @copiousfreetime .