PostgreSQL Connection URL Explained, Piece by Piece

Almost every Node.js backend that talks to PostgreSQL configures the connection with a single string, usually stored in an environment variable named DATABASE_URL. It looks dense at first, but it is just a URL with a fixed set of parts:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public

This article breaks that string into its parts, explains what each one controls, and covers the details that cause the most confusion: encoding a password with special characters, choosing a port, picking an sslmode, and keeping the value out of your logs and repository.

The scheme

postgresql://

The scheme identifies the URL as a PostgreSQL connection string. PostgreSQL's own client library, libpq, accepts both postgresql:// and postgres:// as equivalent — see the PostgreSQL connection string documentation. Prisma and most Node.js PostgreSQL drivers follow the same convention. Pick one and stay consistent within a project; mixing both across .env files makes the codebase harder to search.

User and password

USER:PASSWORD@

This is the userspec part of the URI: a username, an optional colon, and a password, followed by @. A few rules matter here:

• If the password is omitted, the client may still prompt for one or fall back to other authentication methods, depending on the driver.
• Characters that have special meaning in a URL — @, :, /, ?, #, %, and others — must be percent-encoded if they appear in the username or password. An unencoded @ inside a password, for example, will be parsed as the separator between the credentials and the host, which points the client at the wrong host entirely.

A password like p@$$w0rd must become:

p%40%24%24w0rd

Prisma's own documentation states this directly: "you must percentage-encode special characters in any part of your connection URL — including passwords" (see the Prisma connection URLs reference). Do this with your language's URL-encoding function (encodeURIComponent in JavaScript) rather than by hand — it's easy to miss a character or double-encode one.

Never solve a percent-encoding problem by choosing a simpler, weaker password. Encode it correctly instead.

Host

HOST

The host is the hostname or IP address where PostgreSQL is listening. What counts as "correct" here depends entirely on where the connecting process runs, not on the database itself:

libpq also accepts multiple comma-separated host:port pairs in one URL, trying each in order until one connects — useful for HA/failover setups, but not something most single-instance projects need.

For IPv6 addresses, wrap the host in square brackets: postgresql://[2001:db8::1234]/mydb.

Port

PORT

PostgreSQL's default port is 5432. You'll see a different port when:

• Multiple PostgreSQL instances share one machine and were configured on different ports.
• A managed provider exposes a connection pooler on a separate port from the direct connection (see the next section).
• The port is forwarded or proxied by a container platform, tunnel, or reverse proxy.

If the port is omitted from the URL, libpq uses the build-time default (normally 5432).

Database name

DATABASE

The database name comes after the final /. If it's omitted, libpq defaults to using the username as the database name — which is rarely what you want in an application, so treat this as required in practice.

Query parameters

?schema=public&sslmode=require

Everything after ? is name=value pairs joined with &. A few show up constantly:

• schema — Prisma-specific. Sets the default PostgreSQL schema for the connection (commonly public). This has no meaning to libpq or psql directly; it's read by Prisma's client.
• sslmode — controls how strictly the client verifies the server's TLS certificate. Covered below.
• connection_limit and pool_timeout — Prisma-specific parameters for its built-in connection pool: how many connections Prisma opens, and how long a query waits for a free one before timing out.
• connect_timeout, application_name, and others — standard libpq parameters; see the full list in the PostgreSQL connection parameter keywords.

Any literal = or space inside a parameter value must itself be percent-encoded (%3D and %20 respectively), per the PostgreSQL documentation linked above.

Direct vs. pooled connections

Many managed PostgreSQL providers offer two different connection endpoints:

• A direct connection to the database instance itself.
• A pooled connection, routed through a connection pooler (such as PgBouncer or a provider-managed equivalent), usually on a different port or hostname.

These are not interchangeable, and the difference matters for two separate reasons:

1. Connection limits. PostgreSQL has a hard cap on concurrent connections. Serverless or edge functions can spin up many short-lived processes, each opening its own connection — a pooler absorbs that fan-out so the database itself doesn't run out of connections.
2. Migration behavior. Schema-changing commands sometimes need session-level features (like advisory locks or prepared statements) that a transaction-mode pooler doesn't support well. Providers typically document a separate URL, or a query parameter, for running migrations versus normal application traffic.

Copy the exact connection string your provider labels for the purpose you're using — direct for migrations, pooled for application runtime — rather than assuming you can convert one into the other by changing the port number.

SSL modes

The sslmode parameter controls how the client negotiates and verifies TLS. From strictest to loosest, per the PostgreSQL libpq documentation:

For a production application talking to a database over the public internet, require is a reasonable minimum — it stops plaintext traffic. verify-full is stronger because it also protects against a server impersonating the expected host, but it requires the client to trust the right CA certificate, which some managed providers handle automatically and others require you to configure. Check your specific provider's documentation for which modes it supports before assuming verify-full will work out of the box.

Putting it together safely

A complete, structurally valid URL looks like this — this example uses placeholder values, not a real credential:

postgresql://app_user:REPLACE_ME@db.internal.example.com:5432/orders?schema=public&sslmode=require

A few practices keep this safe in a real project:

• Store it only in environment variables (.env.local, your hosting provider's secret manager), never committed to version control.
• Keep a .env.example file with the variable names but empty or placeholder values, so the repository documents what's required without exposing anything.
• Avoid printing the full URL in logs, error messages, or CI output. If you need to confirm a variable is set without exposing its value, check only for presence:

test -n "$DATABASE_URL" && echo "DATABASE_URL is set" || echo "DATABASE_URL is missing"

• If a real connection string is ever committed or pasted somewhere public, rotate the database password. Removing it from a later commit or edited screenshot does not undo the exposure.

You can build and inspect a correctly encoded URL without hand-typing percent-encoding using this site's PostgreSQL Connection URL Builder — it runs entirely in your browser and never transmits what you enter.

How this connects to Prisma errors

Prisma reads this same URL format (with its schema, connection_limit, and pool_timeout extensions) from DATABASE_URL by default. A malformed URL — wrong host, unencoded password, wrong port — is one of the first things to rule out when Prisma reports it can't reach the database. If you're debugging that specific failure, see Fix Prisma P1001: Can't Reach Database Server, which walks through isolating whether the problem is the URL itself, DNS, the network, or the database service.

Verification checklist

• The URL uses postgresql:// (or postgres://) as the scheme.
• The username and password are percent-encoded wherever they contain reserved characters.
• The host and port match the network the connecting process actually runs in (not just your laptop).
• The database name is present and correct.
• sslmode (or the provider's equivalent SSL parameter) is set intentionally, not left to default.
• The URL is stored in an environment variable, not hardcoded or committed.
• No real connection string appears in this article, in logs, or in committed screenshots.

Claims to manually verify before publishing

• Claim: sslmode=prefer is libpq's default when sslmode is omitted.

  • Why verification is needed: Defaults can change between PostgreSQL client versions.
  • Suggested evidence: The sslmode section of the PostgreSQL documentation matching the client version actually in use.

• Claim: Your provider offers separate direct and pooled connection strings with the behavior described here.

  • Why verification is needed: This is general guidance about how managed PostgreSQL providers commonly work, not a description of one specific provider's product.
  • Suggested evidence: The provider's current connection documentation and a sanitized copy of both connection strings it issues.

• Claim: verify-full is supported by your database provider out of the box.

  • Why verification is needed: CA trust configuration for verify-full varies by provider and hosting environment.
  • Suggested evidence: A successful sanitized connection log using verify-full against your actual database.