why does this can't be work when I specified the schema and set the sslmode=no-verify

[BertramYe]

Question

for my pgsql db, I have make the ssl enable, and then I found it always make error, so I have found the solution like this one
here below to disable the ssl verification:

#28803

but the issue is when I make the connection string into like this one:

DATABASE_URL="postgresql://USER:DATABASE_PASSWORD@HOST:PORT/DATABASE?schema=my_web_schema&sslmode=no-verify"

as my schema of the my_web_schema indeed existed in my db and the table of the User have been created as well, but why does it will always use the default schema of the public like below?

but with the same string , when I run the commend of the npx prisma migrate, with the same string, it indeed can create the table of the User in the specified schema of the my_web_schema, but why ?

also here are what I use of the prisma version:

prisma: 7.2.0
postgresql: 18

[JackyST0]

The schema parameter in the connection string is for Prisma Migrate, not for runtime queries. That's why your migrations work but queries still use public.

Solution:

1. For Prisma 5.x+, use schemas in your schema.prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["my_web_schema"]
}

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

model User {
  id   Int    @id
  name String

  @@schema("my_web_schema")
}

2. Update your connection string (remove the schema param):

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?sslmode=no-verify"

3. Regenerate the client:

npx prisma generate
npx prisma db push

Alternative: Use search_path in connection string:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?sslmode=no-verify&options=-c%20search_path%3Dmy_web_schema"

This sets the default schema for all queries.

Docs: https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema

[BertramYe]

hello @JackyST0 ,thanks, your solution should work , but I prefer to config the schema in the connection_string and with the solution like this one:

const connectionString = env.DATABASE_URL;
const schema = new URL(connectionString).searchParams.get('schema');
const adapter = new PrismaPg(
	{
		connectionString,
	},
	{
		schema,
	},
);
const prisma = new PrismaClient({ adapter });

and by the way, for the prisma 7.x its provide should be as provider = "prisma-client", the provider = "prisma-client-js" will help thow the error, but any way, thanks for your help, which did do a great favour for me

[nurul3101]

@BertramYe 👋

You seem to be running into this issue, can you try out the solution mentioned here and confirm if it fixes the issue for you?

#28128 (comment)

[BertramYe]

hello @nurul3101 yes, it works when I use the config here below, thanks,

const connectionString = env.DATABASE_URL;
const schema = new URL(connectionString).searchParams.get('schema');
const adapter = new PrismaPg(
	{
		connectionString,
	},
	{
		schema,
	},
);
const prisma = new PrismaClient({ adapter });

but how about the ssl certs config? I've reference to the docs here: https://www.prisma.io/docs/orm/overview/databases/postgresql#configuring-an-ssl-connection,
and tried with its internal certs like: /etc/ssl/private/ssl-cert-snakeoil.key and /etc/ssl/certs/ssl-cert-snakeoil.pem with the the commend below to create the certs, which come from its postgresql.conf, but it seems it dosen't work, but do you know how can I get and config the client-key.pem and client-cert.pem in the pgsql 18 correctly for the other cloud like AWS or DigitalOcean, what if I host the domain in the CloudFlare, or the certs comes from the Let's Encrypt, is there any more docs to show me how to do or get them correctly ? thanks

# the docs commend
$ openssl pkcs12 -export -out client-identity.p12 -inkey client-key.pem -in client-cert.pem

# and what I tried and used is in the server to generated the client-identity.p12 here below:
$ sudo openssl pkcs12 -export -out client-identity.p12 -inkey /etc/ssl/private/ssl-cert-snakeoil.key -in /etc/ssl/certs/ssl-cert-snakeoil.pem

# and then tried with the connection with the string here below in local 
# (have download the generated file and certs file in local)

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?sslidentity=client-identity.p12&sslpassword=mypassword&sslcert=ssl-cert-snakeoil.pem

but it seems still get the error like:

[logusivam]

This is a well-known Prisma + PostgreSQL schema routing issue, and the behavior you're describing — migrate respects the schema, but queries fall back to public — has a specific, documented cause.

---

Why this happens — two separate mechanisms control schema routing

npx prisma migrate and Prisma Client use the schema parameter differently:

Operation	How schema is set	Result
prisma migrate	Reads ?schema=my_web_schema from DATABASE_URL and sets search_path on the migration connection	Correctly creates tables in my_web_schema
Prisma Client (runtime queries)	Also reads ?schema= from DATABASE_URL, but only sets search_path if the connection string is parsed correctly	Falls back to public if parsing fails or the parameter is dropped

The core issue is that when you have multiple query parameters in a PostgreSQL connection string, certain parameter combinations cause the URL parser to silently drop or misread one of them. In your case:

?schema=my_web_schema&sslmode=no-verify

The sslmode=no-verify parameter appended after schema is the likely culprit. Prisma's connection URL parser processes schema as a Prisma-specific parameter (not a native PostgreSQL parameter), strips it from the URL before passing the rest to the underlying driver, and reconstructs the connection. When sslmode=no-verify follows it, the reconstruction can lose the schema value in certain Prisma versions — the migration CLI path handles this correctly but the runtime Client path does not.

---

The fix — set search_path explicitly using options parameter

Instead of relying on ?schema= alone for runtime query routing, use the PostgreSQL options parameter to set search_path directly at the driver level. This bypasses Prisma's schema parameter parsing entirely and works reliably for both migrations and runtime:

DATABASE_URL="postgresql://USER:DATABASE_PASSWORD@HOST:PORT/DATABASE?schema=my_web_schema&sslmode=no-verify&options=-c%20search_path%3Dmy_web_schema"

The options=-c%20search_path%3Dmy_web_schema part URL-encodes to options=-c search_path=my_web_schema — this is a native PostgreSQL connection option that sets search_path at the session level the moment the connection is established, independently of anything Prisma does with the schema parameter.

---

Cleaner alternative — use Datasource schemas in prisma.schema

For Prisma 7.x specifically, the most reliable approach is to declare the schema explicitly in your schema.prisma datasource block rather than depending solely on the URL parameter:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["my_web_schema"]
}

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

And simplify your DATABASE_URL to keep ?schema= as the sole routing parameter, separate from SSL settings:

DATABASE_URL="postgresql://USER:DATABASE_PASSWORD@HOST:PORT/DATABASE?schema=my_web_schema&sslmode=no-verify"

With multiSchema preview feature enabled and schemas = ["my_web_schema"] declared in the datasource, Prisma Client sets search_path correctly at the connection level for all runtime queries — not just migrations.

Then annotate your models with @@schema("my_web_schema") to explicitly bind them:

model User {
  id    String @id @default(uuid())
  email String @unique

  @@schema("my_web_schema")
}

---

Why migrate works but runtime queries don't — the deeper reason

prisma migrate opens a single administrative connection, sets search_path=my_web_schema before running DDL, and closes it. It never goes through Prisma Client's connection pool — it uses a direct connection with full control over session parameters.

Prisma Client uses a connection pool (via @prisma/driver-adapter or the query engine). When a pooled connection is checked out, search_path defaults to whatever PostgreSQL has configured at the database or role level — which is public unless overridden. The ?schema= parameter in DATABASE_URL is supposed to override this, but the bug you're hitting is that the override is silently lost when the URL contains additional parameters after schema= in certain Prisma 7.x builds.

---

Verify the fix worked

After applying either fix above, confirm Prisma Client is querying the correct schema:

// Run a raw query to check the active search_path at runtime
const result = await prisma.$queryRaw<[{ search_path: string }]>`SHOW search_path`;
console.log(result); // Should show: my_web_schema, not public

If this returns my_web_schema, your runtime queries are routing correctly. If it still shows public or "$user", public, the search_path is still not being set at connection time — in which case the options=-c%20search_path%3D approach is the guaranteed fix since it operates at the PostgreSQL driver level with no Prisma parsing involved.

---

Summary

Approach	Fixes migrate?	Fixes runtime queries?	Reliability
?schema=my_web_schema alone	✅	⚠️ Sometimes lost with additional params	Unreliable with multiple query params
?schema= + options=-c search_path=	✅	✅	Reliable — driver-level
schemas = ["my_web_schema"] in datasource + multiSchema	✅	✅	Recommended for Prisma 7.x
@@schema("my_web_schema") on models	✅	✅	Required alongside multiSchema

Use the multiSchema datasource declaration approach — it is the correct, idiomatic Prisma 7.x solution for non-public schema routing and resolves both the migration and runtime query issues permanently.

Hope this helps — if it answers your question, would you mind clicking "Mark as answer" so others searching for the same thing can find it quickly?