Inconsistent behavior when setting Postgres schema in DB URL after migrating to `prisma-client` with `@prisma/adapter-pg`

[dp-152]

Bug description

When migrating from prisma-client-js to prisma-client using @prisma/adapter-pg, we noticed some inconsistencies on setting a schema in the connection string and how the query engine behaves, that do not occur when using prisma-client-js.

• All queries generated by the ORM (e.g., using client.<model>.findFirst(...)) have the table names prefixed with the schema 'public', despite the schema being set to something different in the connection string (e.g. postgres://user:pass@host:5432/database?schema=myschema). The queries work only if the schema property in the adapter settings (const adapter = new PrismaPg({connectionString}, {schema: "myschema});) is set.
• All raw queries failed with PrismaClientKnownRequestError and code 42P01 (Relation xxx does not exist), even when the schema property is set in the adapter. The only workaround we found to this is to add an option to the connection string explicitly setting the search_path for the connection (postgres://user:pass@host:5432/database?schema=myschema&options=--search-path%3Dmyschema)

We depend on this behavior when running simultaneous integration tests, where each connection has its own test schema within the same database.

Severity

🔹 Minor: Unexpected behavior, but does not block development

Reproduction

https://github.com/dp-152/prisma-postgres-schema-inconsistency

Expected vs. Actual Behavior

All queries should respect the schema option from the connection string.

ORM queries do not respect the schema unless it is explicitly set in the adapter settings.
Raw queries do not respect the schema due to the search path not being set.

Frequency

Consistently reproducible

Does this occur in development or production?

Both development and production

Is this a regression?

Worked without workarounds on Prisma 6.14 using prisma-client-js.
Requires workarounds on Prisma 6.15 using prisma-client and @prisma/adapter-pg.

Workaround

Parsing the connection string and extracting the schema query param, then explicitly setting the schema property in the adapter configs.
Setting the search path for the connection in the connection string query params.

Prisma Schema & Queries

generator client {
  provider               = "prisma-client"
  engineType             = "client"
  output                 = "./client"
  runtime                = "nodejs"
  moduleFormat           = "esm"
  generatedFileExtension = "ts"
  importFileExtension    = "ts"
}

datasource db {
  provider = "postgresql"
  url      = "postgres://<user>:<pass>@localhost:5435/postgres?schema=test_1"
}

model users {
  id    String @id @db.Uuid @default(uuid())
  name  String
  email String
}

const adapter1 = new PrismaPg({
  connectionString,
});

const client1 = new PrismaClient({ adapter: adapter1 });

// fails, table is prefixed with "public" schema
await client1.users
  .create({
    data: {
      email: "user@example.com",
      name: "User",
    }
  })
  .then(() => console.log("client 1 ok"))
  .catch((e) => console.error(e.message))

console.log("\n\n/* -------------------------------------------------------------- */\n\n")

const adapter2 = new PrismaPg({ connectionString }, { schema: new URL(connectionString).searchParams.get("schema")! })

const client2 = new PrismaClient({ adapter: adapter2 });

// succeeds, table is prefixed with correct schema ("test_1")
await client2.users
  .create({
    data: {
      email: "user@example.com",
      name: "User",
    }
  })
  .then(() => console.log("client 2 ok"))
  .catch((e) => console.error(e.message))
debugger;

await client1.$disconnect();
await client2.$disconnect();

Prisma Config

// Add your `prisma.config.ts`

Logs & Debug Info

❯ DEBUG='*' pnpm start:scenario-1                                     

> prisma-postgres-schema-inconsistency@ start:scenario-1 /home/user/personal/prisma-postgres-schema-inconsistency
> node --no-warnings --experimental-strip-types src/scenario-1.ts

Scenario 1: ORM queries are prefixed with 'public' schema when the 'schema' property is not set in the adapter
prisma:client checkPlatformCaching:postinstall undefined +8ms
prisma:client checkPlatformCaching:ciName undefined +0ms
prisma:client dirname /home/user/personal/prisma-postgres-schema-inconsistency/prisma/client +0ms
prisma:client relativePath .. +0ms
prisma:client cwd /home/user/personal/prisma-postgres-schema-inconsistency/prisma +0ms
prisma:client clientVersion 6.16.2 +0ms
prisma:client:clientEngine Using driver adapter: '{\n' +
  '  "provider": "postgres",\n' +
  '  "adapterName": "@prisma/adapter-pg",\n' +
  '  "config": {\n' +
  '    "connectionString": "postgres://user:pass@localhost:5435/postgres?schema=test_1"\n' +
  '  },\n' +
  '  "externalPool": null\n' +
  '}' +2ms
prisma:client Prisma Client call: +2ms
prisma:client prisma.users.create({
  data: {
    email: "user@example.com",
    name: "User"
  }
}) +1ms
prisma:client Generated request: +0ms
prisma:client {
  "modelName": "users",
  "action": "createOne",
  "query": {
    "arguments": {
      "data": {
        "email": "user@example.com",
        "name": "User"
      }
    },
    "selection": {
      "$composites": true,
      "$scalars": true
    }
  }
}
 +0ms
prisma:client:clientEngine sending request +0ms
prisma:client:clientEngine query plan created {
  "type": "dataMap",
  "args": {
    "expr": {
      "type": "let",
      "args": {
        "bindings": [
          {
            "name": "@generated$row0$id",
            "expr": {
              "type": "value",
              "args": {
                "prisma__type": "generatorCall",
                "prisma__value": {
                  "name": "uuid",
                  "args": [
                    4
                  ],
                  "returnType": {
                    "type": "String"
                  }
                }
              }
            }
          }
        ],
        "expr": {
          "type": "unique",
          "args": {
            "type": "query",
            "args": {
              "type": "templateSql",
              "fragments": [
                {
                  "type": "stringChunk",
                  "chunk": "INSERT INTO \"public\".\"users\" (\"id\",\"name\",\"email\") VALUES ("
                },
                {
                  "type": "parameter"
                },
                {
                  "type": "stringChunk",
                  "chunk": ","
                },
                {
                  "type": "parameter"
                },
                {
                  "type": "stringChunk",
                  "chunk": ","
                },
                {
                  "type": "parameter"
                },
                {
                  "type": "stringChunk",
                  "chunk": ") RETURNING \"public\".\"users\".\"id\", \"public\".\"users\".\"name\", \"public\".\"users\".\"email\""
                }
              ],
              "args": [
                {
                  "prisma__type": "param",
                  "prisma__value": {
                    "name": "@generated$row0$id",
                    "type": "String"
                  }
                },
                "User",
                "user@example.com"
              ],
              "argTypes": [
                {
                  "arity": "scalar",
                  "scalarType": "string",
                  "dbType": "UUID"
                },
                {
                  "arity": "scalar",
                  "scalarType": "string",
                  "dbType": "TEXT"
                },
                {
                  "arity": "scalar",
                  "scalarType": "string",
                  "dbType": "TEXT"
                }
              ],
              "placeholderFormat": {
                "prefix": "$",
                "hasNumbering": true
              },
              "chunkable": true
            }
          }
        }
      }
    },
    "structure": {
      "type": "object",
      "serializedName": null,
      "fields": {
        "id": {
          "type": "field",
          "dbName": "id",
          "fieldType": {
            "arity": "required",
            "type": "string"
          }
        },
        "name": {
          "type": "field",
          "dbName": "name",
          "fieldType": {
            "arity": "required",
            "type": "string"
          }
        },
        "email": {
          "type": "field",
          "dbName": "email",
          "fieldType": {
            "arity": "required",
            "type": "string"
          }
        }
      },
      "skipNulls": false
    },
    "enums": {}
  }
} +38ms
prisma:driver-adapter:pg [js::query_raw] '{\n' +
  '  "sql": "INSERT INTO \\"public\\".\\"users\\" (\\"id\\",\\"name\\",\\"email\\") VALUES ($1,$2,$3) RETURNING \\"public\\".\\"users\\".\\"id\\", \\"public\\".\\"users\\".\\"name\\", \\"public\\".\\"users\\".\\"email\\"",\n' +
  '  "args": [\n' +
  '    "2ac7eff7-6039-4b2e-b09b-3403cbdbc38c",\n' +
  '    "User",\n' +
  '    "user@example.com"\n' +
  '  ],\n' +
  '  "argTypes": [\n' +
  '    {\n' +
  '      "arity": "scalar",\n' +
  '      "scalarType": "string",\n' +
  '      "dbType": "UUID"\n' +
  '    },\n' +
  '    {\n' +
  '      "arity": "scalar",\n' +
  '      "scalarType": "string",\n' +
  '      "dbType": "TEXT"\n' +
  '    },\n' +
  '    {\n' +
  '      "arity": "scalar",\n' +
  '      "scalarType": "string",\n' +
  '      "dbType": "TEXT"\n' +
  '    }\n' +
  '  ]\n' +
  '}' +66ms
prisma:driver-adapter:pg Error in performIO: '{\n' +
  '  "length": 111,\n' +
  '  "name": "error",\n' +
  '  "severity": "ERROR",\n' +
  '  "code": "42P01",\n' +
  '  "position": "13",\n' +
  '  "file": "parse_relation.c",\n' +
  '  "line": "1428",\n' +
  '  "routine": "parserOpenTable"\n' +
  '}' +19ms
prisma:client:request_handler {
  "code": "P2021",
  "meta": {
    "driverAdapterError": {
      "name": "DriverAdapterError",
      "cause": {
        "originalCode": "42P01",
        "originalMessage": "relation \"public.users\" does not exist",
        "kind": "TableDoesNotExist",
        "table": "public.users"
      }
    }
  },
  "clientVersion": "6.16.2",
  "name": "PrismaClientKnownRequestError"
} +22ms

Invalid `prisma.users.create()` invocation:


The table `public.users` does not exist in the current database.


/* -------------------------------------------------------------- */


prisma:client checkPlatformCaching:postinstall undefined +2ms
prisma:client checkPlatformCaching:ciName undefined +0ms
prisma:client dirname /home/user/personal/prisma-postgres-schema-inconsistency/prisma/client +0ms
prisma:client relativePath .. +0ms
prisma:client cwd /home/user/personal/prisma-postgres-schema-inconsistency/prisma +0ms
prisma:client clientVersion 6.16.2 +0ms
prisma:client:clientEngine Using driver adapter: '{\n' +
  '  "provider": "postgres",\n' +
  '  "adapterName": "@prisma/adapter-pg",\n' +
  '  "config": {\n' +
  '    "connectionString": "postgres://user:pass@localhost:5435/postgres?schema=test_1"\n' +
  '  },\n' +
  '  "externalPool": null,\n' +
  '  "options": {\n' +
  '    "schema": "test_1"\n' +
  '  }\n' +
  '}' +0ms
prisma:client Prisma Client call: +1ms
prisma:client prisma.users.create({
  data: {
    email: "user@example.com",
    name: "User"
  }
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
  "modelName": "users",
  "action": "createOne",
  "query": {
    "arguments": {
      "data": {
        "email": "user@example.com",
        "name": "User"
      }
    },
    "selection": {
      "$composites": true,
      "$scalars": true
    }
  }
}
 +0ms
prisma:client:clientEngine sending request +0ms
prisma:client:clientEngine query plan created {
  "type": "dataMap",
  "args": {
    "expr": {
      "type": "let",
      "args": {
        "bindings": [
          {
            "name": "@generated$row0$id",
            "expr": {
              "type": "value",
              "args": {
                "prisma__type": "generatorCall",
                "prisma__value": {
                  "name": "uuid",
                  "args": [
                    4
                  ],
                  "returnType": {
                    "type": "String"
                  }
                }
              }
            }
          }
        ],
        "expr": {
          "type": "unique",
          "args": {
            "type": "query",
            "args": {
              "type": "templateSql",
              "fragments": [
                {
                  "type": "stringChunk",
                  "chunk": "INSERT INTO \"test_1\".\"users\" (\"id\",\"name\",\"email\") VALUES ("
                },
                {
                  "type": "parameter"
                },
                {
                  "type": "stringChunk",
                  "chunk": ","
                },
                {
                  "type": "parameter"
                },
                {
                  "type": "stringChunk",
                  "chunk": ","
                },
                {
                  "type": "parameter"
                },
                {
                  "type": "stringChunk",
                  "chunk": ") RETURNING \"test_1\".\"users\".\"id\", \"test_1\".\"users\".\"name\", \"test_1\".\"users\".\"email\""
                }
              ],
              "args": [
                {
                  "prisma__type": "param",
                  "prisma__value": {
                    "name": "@generated$row0$id",
                    "type": "String"
                  }
                },
                "User",
                "user@example.com"
              ],
              "argTypes": [
                {
                  "arity": "scalar",
                  "scalarType": "string",
                  "dbType": "UUID"
                },
                {
                  "arity": "scalar",
                  "scalarType": "string",
                  "dbType": "TEXT"
                },
                {
                  "arity": "scalar",
                  "scalarType": "string",
                  "dbType": "TEXT"
                }
              ],
              "placeholderFormat": {
                "prefix": "$",
                "hasNumbering": true
              },
              "chunkable": true
            }
          }
        }
      }
    },
    "structure": {
      "type": "object",
      "serializedName": null,
      "fields": {
        "id": {
          "type": "field",
          "dbName": "id",
          "fieldType": {
            "arity": "required",
            "type": "string"
          }
        },
        "name": {
          "type": "field",
          "dbName": "name",
          "fieldType": {
            "arity": "required",
            "type": "string"
          }
        },
        "email": {
          "type": "field",
          "dbName": "email",
          "fieldType": {
            "arity": "required",
            "type": "string"
          }
        }
      },
      "skipNulls": false
    },
    "enums": {}
  }
} +1ms
prisma:driver-adapter:pg [js::query_raw] '{\n' +
  '  "sql": "INSERT INTO \\"test_1\\".\\"users\\" (\\"id\\",\\"name\\",\\"email\\") VALUES ($1,$2,$3) RETURNING \\"test_1\\".\\"users\\".\\"id\\", \\"test_1\\".\\"users\\".\\"name\\", \\"test_1\\".\\"users\\".\\"email\\"",\n' +
  '  "args": [\n' +
  '    "a2e01ee3-7ce7-4b05-b7c6-19e2f05d2058",\n' +
  '    "User",\n' +
  '    "user@example.com"\n' +
  '  ],\n' +
  '  "argTypes": [\n' +
  '    {\n' +
  '      "arity": "scalar",\n' +
  '      "scalarType": "string",\n' +
  '      "dbType": "UUID"\n' +
  '    },\n' +
  '    {\n' +
  '      "arity": "scalar",\n' +
  '      "scalarType": "string",\n' +
  '      "dbType": "TEXT"\n' +
  '    },\n' +
  '    {\n' +
  '      "arity": "scalar",\n' +
  '      "scalarType": "string",\n' +
  '      "dbType": "TEXT"\n' +
  '    }\n' +
  '  ]\n' +
  '}' +5ms
prisma:client:clientEngine query plan executed +24ms
client 2 ok

Environment & Setup

• OS:
• Database:
• Node.js version:

Prisma Version

❯ pnpm prisma -v            
Prisma schema loaded from prisma/schema.prisma
prisma                : 6.16.2
@prisma/client        : 6.16.2
Computed binaryTarget : debian-openssl-1.1.x
Operating System      : linux
Architecture          : x64
Node.js               : v23.6.0
TypeScript            : 5.9.2
Query Compiler        : enabled
PSL                   : @prisma/prisma-schema-wasm 6.16.0-7.1c57fdcd7e44b29b9313256c76699e91c3ac3c43
Schema Engine         : schema-engine-cli 1c57fdcd7e44b29b9313256c76699e91c3ac3c43 (at node_modules/.pnpm/@prisma+engines@6.16.2/node_modules/@prisma/engines/schema-engine-debian-openssl-1.1.x)
Default Engines Hash  : 1c57fdcd7e44b29b9313256c76699e91c3ac3c43
Studio                : 0.511.0

[1oglop1]

I can confirm, I have exactly the same problem, after a lot of time spent on debugging the problem.
I intended to dynamically create database schemas for tests.
My script called prisma db push in a loop, and correctly created schemas, but PrismaPg client simply ignored the schema in the env var.
v6.16.3
So far, I've had nothing but problems with DATABASE_URL. I hope Prisma can retire this concept one day.

[yharaskrik]

Also encountering this, it seems to always overwrite to public. I am on 6.17.0, using this configuration:

  provider               = "prisma-client"
  output                 = "./generated"
  previewFeatures        = ["typedSql"]
  engineType             = "client"
  runtime                = "nodejs"
  moduleFormat           = "cjs"
  generatedFileExtension = "ts"
  importFileExtension    = "ts"