How to connect to Postgres

Short answer: Add sqlx with the postgres feature and tokio, build a pool with PgPoolOptions::new().connect(url).await, and expect the trouble to come from the URL, not the pool — a database name that does not exist, a wrong password, or a server that is not listening.

The dependencies

cargo new pg-connect (any name works), then two lines under [dependencies]:

Cargo.toml

[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

sqlx is one crate with one driver per database behind a feature flag. postgres is the one this page needs — without it, PgPool and PgPoolOptions do not exist, and the same crate would talk to MySQL or SQLite instead. sqlx is also runtime-agnostic: its connections are futures, and something has to poll them, so runtime-tokio compiles the tokio driver in. tls-rustls names the TLS backend sqlx insists on — rustls, written in Rust; the alternative, tls-native-tls, delegates to the platform’s (Secure Transport on macOS, OpenSSL on Linux). A Postgres that never leaves the machine uses no TLS at all, and the backend still has to be named.

tokio is there for the same reason as in every async crate: macros provides #[tokio::main], and rt-multi-thread provides the scheduler that attribute starts.

A Postgres to point at

docker run --name hwr-pg -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:16

That is Postgres 16 listening on localhost:5432, user postgres, password dev. The image creates one database for you, named postgres, and the URL on this page points at it — connecting needs no CREATE DATABASE first.

Any Postgres you already have is just as good, as long as the URL matches it: same user, same password, same port. Everything below is written against a URL you can point at a fresh install with no other setup.

The URL, taken apart

postgres://postgres:dev@localhost:5432/postgres
           │       │  │         │    │     └ database
           │       │  │         │    └ port
           │       │  │         └ host
           │       │  └ password
           │       └ user
           └ scheme

Every mistake in the second half of this page is one of these six parts being wrong.

The pool

The thing you keep is a PgPool, not a connection. Handlers, tasks, and request threads all want a database session at the same time, and opening a TCP connection plus a Postgres handshake per use puts that cost on every call. The pool keeps sessions open and lends them out; max_connections is the ceiling, and acquire_timeout is how long a caller waits for one before giving up.

src/main.rs

use std::time::Duration;
use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let url = "postgres://postgres:dev@localhost:5432/postgres";

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .acquire_timeout(Duration::from_secs(3))
        .connect(url)
        .await
        .expect("failed to connect to Postgres");

    println!(
        "{} of {} connections open",
        pool.size(),
        pool.options().get_max_connections()
    );
}

▶Run this in the Rust Playground

Run it with cargo run:

1 of 5 connections open

pool.size() is one because connect is eager: it opens a real connection before it returns, so a wrong URL ends the program here, at startup — the .expect panics — and not later, on the first query. The defaults behind PgPoolOptions::new() are ten connections and a 30-second acquire timeout; both are set here on purpose, and the wrong-URL section shows why the timeout deserves a conscious choice. The two-builder shorthand exists too: sqlx::postgres::PgPool::connect(url).await is PgPoolOptions::new().connect(url).await with every default kept.

The DATABASE_URL you will write wrong

Three wrong URLs cover most of what goes wrong on a first connect. One program tries all three and prints what comes back — each with the three-second timeout, so the slow one takes three seconds:

src/main.rs

use std::time::Duration;
use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let wrong = [
        "postgres://postgres:dev@localhost:5432/doesnotexist",
        "postgres://postgres:wrongpass@localhost:5432/postgres",
        "postgres://postgres:dev@localhost:59999/postgres",
    ];

    for url in wrong {
        let attempt = PgPoolOptions::new()
            .acquire_timeout(Duration::from_secs(3))
            .connect(url)
            .await;

        match attempt {
            Ok(_) => println!("{url}: connected"),
            Err(e) => println!("{url}\n    {e}"),
        }
    }
}

▶Run this in the Rust Playground

postgres://postgres:dev@localhost:5432/doesnotexist
    error returned from database: database "doesnotexist" does not exist at line 1023
postgres://postgres:wrongpass@localhost:5432/postgres
    error returned from database: password authentication failed for user "postgres" at line 334
postgres://postgres:dev@localhost:59999/postgres
    pool timed out while waiting for an open connection

The first URL reaches Postgres and asks for a database that is not there — the server answers, and the answer is no such database. This is the mistake that costs the most head-scratching, because everything about the connection worked; only the name at the end of the URL does not match anything. A database is not created by being connected to: the docker command above gives you a server with one database called postgres, and anything else has to be created first.

The second also reaches Postgres, completes the handshake, and fails the password check. Same advice in mirror image: the connection is fine, the secret in the URL is not.

The third never reaches anything — nothing is listening on port 59999 — and this one is slow on purpose. The pool does not accept “connection refused” and quit; it retries for the whole acquire_timeout before giving up, on the theory that a database that just restarted will come back. Three seconds here, thirty by default. If your program takes thirty seconds to admit the database is down, that is the default acquire timeout, not the network.

The URL from the environment

Hardcoded URLs are for this page. A real program reads DATABASE_URL, so the same binary connects to development, staging, and production without a rebuild:

let url = std::env::var("DATABASE_URL").expect("DATABASE_URL is not set");

Run it without setting the variable and the .expect panics:

thread 'main' (10117357) panicked at src/main.rs:2:45:
DATABASE_URL is not set: NotPresent
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

The thread id and the src/main.rs line number are yours; the message is the point. NotPresent is std::env::VarError — the variable is not set at all. Set-but-empty fails differently: it gets as far as connect and comes back as a URL parse error, error with configuration: relative URL without a base.

Connecting is the whole job of this page: a pool built from a URL that works, and a short list of URLs that do not. What the pool is actually for — running queries and reading rows back — is the next problem.