What is the format for the PostgreSQL connection string / URL?

Learn what is the format for the postgresql connection string / url? with practical examples, diagrams, and best practices. Covers postgresql, database-connection development techniques with visual...

Understanding PostgreSQL Connection Strings and URLs

Hero image for What is the format for the PostgreSQL connection string / URL?

Learn the standard formats for connecting to a PostgreSQL database using connection strings and URLs, including common parameters and best practices.

Connecting to a PostgreSQL database is a fundamental task for any application developer or database administrator. PostgreSQL offers flexible ways to specify connection parameters, primarily through connection strings (also known as connection URLs or DSNs - Data Source Names). Understanding the structure and available options is crucial for secure, efficient, and reliable database interactions.

The Standard Connection URL Format

The most common and recommended way to specify PostgreSQL connection parameters is using a URI (Uniform Resource Identifier) format, often referred to as a connection URL. This format is widely supported across various programming languages and tools, providing a consistent way to define connection details. It follows a structure similar to a web URL, making it intuitive to read and parse.

flowchart LR
    A[Scheme] --> B[User:Password]
    B --> C[Host:Port]
    C --> D[DatabaseName]
    D --> E[?Options]

    subgraph Scheme
        S[postgres:// or postgresql://]
    end
    subgraph User:Password
        U[username:password]
    end
    subgraph Host:Port
        H[hostname:port]
    end
    subgraph DatabaseName
        D_N[database]
    end
    subgraph Options
        O[key=value&key2=value2]
    end

    S --> U
    U --> H
    H --> D_N
    D_N --> O

Structure of a PostgreSQL Connection URL

The general format is: postgresql://[user[:password]@][host][:port][/database][?options] or postgres://[user[:password]@][host][:port][/database][?options].

postgresql://user:password@host:port/database?sslmode=require&application_name=my_app

Example of a complete PostgreSQL connection URL

Key Components of the Connection String

Let's break down each part of the connection URL to understand its role and common values.

1. Scheme (postgresql:// or postgres://) This prefix indicates that the URL is for a PostgreSQL connection. Both postgresql:// and postgres:// are commonly accepted, with postgresql:// being the more explicit and generally preferred form.

2. User (user) The username used to authenticate with the PostgreSQL server. This is typically a database role.

3. Password (password) The password for the specified user. It's generally recommended to avoid hardcoding passwords directly in connection strings, especially in source code. Environment variables or secure configuration management systems are better alternatives.

4. Host (host) The hostname or IP address of the PostgreSQL server. This can be localhost for a local connection, an IP address (e.g., 127.0.0.1), or a domain name (e.g., db.example.com).

5. Port (port) The port number on which the PostgreSQL server is listening. The default PostgreSQL port is 5432. If your server is running on the default port, this part can often be omitted.

6. Database (/database) The name of the specific database to connect to. If omitted, PostgreSQL might connect to a database with the same name as the user.

7. Options (?key=value&key2=value2) Optional parameters that modify connection behavior. These are appended after a question mark ? and separated by ampersands &. Common options include:

sslmode: Controls SSL usage (e.g., disable, allow, prefer, require, verify-ca, verify-full). require is often recommended for production.
application_name: A string that identifies the application connecting to the database, useful for logging and monitoring.
connect_timeout: Maximum time in seconds to wait for a connection to be established.
options: Additional command-line options passed to the server upon connection.

💡

For security, always use sslmode=require or stronger (verify-ca, verify-full) in production environments to encrypt data in transit. Never hardcode sensitive credentials directly in your application code; use environment variables or a secure configuration service.

Alternative Key-Value Pair Format

While the URL format is preferred, some older drivers or specific tools might use a key-value pair format, where parameters are listed explicitly. This is less common for new applications but still valid.

host=localhost port=5432 user=myuser password=mypassword dbname=mydb sslmode=require

Example of a PostgreSQL connection string using key-value pairs

This format is essentially a space-separated list of key=value pairs. The keys generally correspond to the components in the URL format (e.g., host, port, user, password, dbname for database, sslmode).

⚠️

When using the key-value pair format, be mindful of spaces and special characters within values. They might need to be quoted or escaped depending on the specific driver or tool parsing the string.

Practical Connection Examples

Here are some practical examples of how connection strings might look in different scenarios.

Basic Local Connection

postgresql://myuser:mypassword@localhost:5432/mydatabase

Cloud Hosted (SSL Required)

postgresql://admin:securepass@my-cloud-db.example.com:5432/productiondb?sslmode=require&application_name=web_app

Unix Socket Connection

postgresql:///mydatabase?host=/var/run/postgresql

Environment Variable Usage

DATABASE_URL=postgresql://user:password@host:port/database