> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Use Rust (sqlx) with ClickHouse Managed Postgres

> Connect a Rust app to ClickHouse Managed Postgres with sqlx and axum, run migrations with sqlx-cli, and query over verified TLS

export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
  if (link) {
    return <a href={link} target="_blank" rel="noopener noreferrer" className="betaBadge" onClick={galaxyTrack && galaxyEvent ? galaxyOnClick(galaxyEvent) : undefined}>
                <span>Beta</span>
            </a>;
  }
  return <a href="https://clickhouse.com/docs/reference/settings/beta-and-experimental-features#beta-features" className="betaBadge">
            <span>Beta feature</span>
        </a>;
};

<BetaBadge link="https://clickhouse.com/cloud/postgres" galaxyTrack={true} galaxyEvent="docs.managed-postgres.guides-rust-beta" />

[sqlx](https://github.com/launchbadge/sqlx) is an async, pure-Rust SQL toolkit with a built-in connection pool and migrations. In this guide, you create a `todos` table with a `sqlx-cli` migration and serve create, read, update, and delete queries from a small [axum](https://github.com/tokio-rs/axum) server. Every connection uses TLS with full certificate verification.

<h2 id="prerequisites">
  Prerequisites
</h2>

* [Rust](https://www.rust-lang.org/tools/install) 1.94 or later. This guide was tested with Rust 1.98.1, `sqlx` 0.9.0, `sqlx-cli` 0.9.0, and `axum` 0.8.9.
* A ClickHouse Cloud account
* [`psql`](https://www.postgresql.org/download/), to create the database. You can also run the `CREATE DATABASE` statement in the [SQL console](/integrations/connectors/sql-clients/sql-console).

<h2 id="create-service">
  Create a ClickHouse Managed Postgres service
</h2>

In the ClickHouse Cloud console, click **New service** and select **Postgres**. The instance is ready in a few minutes. See the [quickstart](/products/managed-postgres/quickstart) for a walkthrough.

<h2 id="connection-details">
  Get your connection details
</h2>

Click **Connect** in the left sidebar of your service. Keep **Directly** selected and turn on **Use SSL**. The connection URL on the **url** tab now ends in `sslmode=verify-full&sslrootcert=<service-name>-ca-certificate.pem`, and the port is `5432`.

Click **Download CA certificate**. You can also download it later from **Settings → CA Certificate**. The certificate is unique to your instance, so the driver can use it to verify that it's talking to your server.

This guide connects **directly** to Postgres on port `5432`, for both the app and `sqlx-cli`. An axum server is a long-lived process, and `PgPool` keeps its own pool of connections, so it doesn't need a second pooler in front of Postgres. Migrations should always use the direct connection. If you run many app instances, see [Use PgBouncer](#pgbouncer).

<h2 id="project-setup">
  Set up the project
</h2>

Create a project and add `sqlx` with the Postgres driver, the Tokio runtime, and the `rustls` TLS backend, plus axum and a few helper crates:

```bash theme={null}
cargo new todo-api && cd todo-api
cargo add sqlx --features postgres,runtime-tokio,tls-rustls,chrono
cargo add axum dotenvy
cargo add tokio --features full
cargo add serde --features derive
cargo add chrono --features serde
```

Install `sqlx-cli` with the same TLS backend:

```bash theme={null}
cargo install sqlx-cli --no-default-features --features rustls,postgres
```

<Warning>
  **Use `rustls`, not `native-tls`, on macOS**

  ClickHouse Managed Postgres accepts only TLS 1.3. On macOS, the `native-tls` backend uses the system's Secure Transport library, which doesn't support TLS 1.3. A plain `cargo install sqlx-cli` uses `native-tls`, and every command then fails with `error occurred while attempting to establish a TLS connection: One or more parameters passed to a function were not valid.` (or `bad protocol version`). The same applies to an app built with the `tls-native-tls` feature. On Linux, `native-tls` uses OpenSSL and connects, but it loads only the first certificate from `ca-certificate.pem`. `rustls` supports TLS 1.3 on every platform and loads every certificate in the file.
</Warning>

<Tip>
  **Adding to an existing app?**

  Skip `cargo new`. In your project, run the `cargo add` and `cargo install` commands above, then continue with [Configure the connection](#configure-connection). If the app uses sqlx with SQLite:

  * Remove `sqlite` from the `sqlx` features in `Cargo.toml`, and replace `SqlitePool` with `PgPool`.
  * Change `?` placeholders to `$1`, `$2`, and so on.
  * Rewrite your migrations in Postgres syntax, for example `bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY` instead of `INTEGER PRIMARY KEY AUTOINCREMENT`.
</Tip>

<h2 id="configure-connection">
  Configure the connection
</h2>

Move the CA certificate you downloaded into the project directory and rename it to `ca-certificate.pem`.

Create a database for the app. Replace `<PASSWORD>` and the host with the values from the **Connect** modal:

```bash theme={null}
psql "postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/postgres?sslmode=verify-full&sslrootcert=ca-certificate.pem" -c "CREATE DATABASE guide_rust;"
```

```text theme={null}
CREATE DATABASE
```

Create a `.env` file in the project root, or update `DATABASE_URL` in your existing one, so that it points at the `guide_rust` database. Both the app and `sqlx-cli` read `DATABASE_URL` from it:

```bash title=".env" theme={null}
DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_rust?sslmode=verify-full&sslrootcert=ca-certificate.pem"
```

sqlx reads `sslmode` and `sslrootcert` from the URL. With `verify-full`, it checks that the server certificate is signed by your instance's CA and that it matches the hostname. The `sslrootcert` path is resolved relative to the directory you run commands from. Keep `.env` out of version control, and if your password contains characters such as `@`, `/`, or `#`, percent-encode them.

<Warning>
  **Always set `sslmode=verify-full`**

  Without `sslmode`, sqlx uses `prefer`: it encrypts the connection but accepts any certificate, even with `sslrootcert` set. With `verify-full`, a missing or wrong CA certificate fails with `UnknownIssuer`, and a host that doesn't match the certificate, for example an IP address, fails with `NotValidForNameContext` (`certificate not valid for name` in `sqlx-cli`). A wrong `sslrootcert` path fails with `No such file or directory`.
</Warning>

<h2 id="migrations">
  Create the table with a migration
</h2>

Create a migration file:

```bash theme={null}
sqlx migrate add create_todos
```

```text theme={null}
Creating migrations/20261001142218_create_todos.sql
```

Open the new file in the `migrations` directory and add the table definition:

```sql title="migrations/<timestamp>_create_todos.sql" theme={null}
CREATE TABLE todos (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      text NOT NULL,
    done       boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now()
);
```

Apply it:

```bash theme={null}
sqlx migrate run
```

```text theme={null}
Applied 20261001142218/migrate create todos (233.971666ms)
```

`sqlx migrate run` records applied migrations in the `_sqlx_migrations` table and only runs new ones on later calls. Commit the `migrations` directory with your code. To apply migrations when the app starts instead, call `sqlx::migrate!().run(&pool).await?` in `main`, as long as the pool uses the direct connection.

<h2 id="query">
  Build the API
</h2>

Replace `src/main.rs` with a small axum server that runs one query per route. In an existing app, create the pool the same way as in `main`, and add the `Todo` struct, the handlers, and the routes to your own router:

```rust title="src/main.rs" theme={null}
use std::str::FromStr;

use axum::{
    Json, Router,
    extract::{Path, State},
    http::StatusCode,
    routing::{get, patch},
};
use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::PgPool;
use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[derive(Serialize, sqlx::FromRow)]
struct Todo {
    id: i64,
    title: String,
    done: bool,
    created_at: DateTime<Utc>,
}

#[derive(Deserialize)]
struct NewTodo {
    title: String,
}

type ApiResult<T> = Result<Json<T>, (StatusCode, String)>;

// Return 404 when no row matches, and 500 for any other database error.
fn db_error(err: sqlx::Error) -> (StatusCode, String) {
    match err {
        sqlx::Error::RowNotFound => (StatusCode::NOT_FOUND, "Not found\n".into()),
        err => {
            eprintln!("Database error: {err}");
            (StatusCode::INTERNAL_SERVER_ERROR, "Database error\n".into())
        }
    }
}

// Read
async fn list_todos(State(pool): State<PgPool>) -> ApiResult<Vec<Todo>> {
    let todos = sqlx::query_as::<_, Todo>("SELECT * FROM todos ORDER BY id")
        .fetch_all(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todos))
}

// Create
async fn create_todo(State(pool): State<PgPool>, Json(new): Json<NewTodo>) -> ApiResult<Todo> {
    let todo = sqlx::query_as::<_, Todo>("INSERT INTO todos (title) VALUES ($1) RETURNING *")
        .bind(new.title)
        .fetch_one(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todo))
}

// Update: toggle the done flag
async fn toggle_todo(State(pool): State<PgPool>, Path(id): Path<i64>) -> ApiResult<Todo> {
    let todo = sqlx::query_as::<_, Todo>("UPDATE todos SET done = NOT done WHERE id = $1 RETURNING *")
        .bind(id)
        .fetch_one(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todo))
}

// Delete
async fn delete_todo(State(pool): State<PgPool>, Path(id): Path<i64>) -> ApiResult<Todo> {
    let todo = sqlx::query_as::<_, Todo>("DELETE FROM todos WHERE id = $1 RETURNING *")
        .bind(id)
        .fetch_one(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todo))
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Load DATABASE_URL from .env, if the variable isn't already set
    dotenvy::dotenv().ok();

    // sslmode and sslrootcert come from the URL. Not sending extra_float_digits
    // lets the same code also connect through PgBouncer, which rejects it.
    let options = PgConnectOptions::from_str(&std::env::var("DATABASE_URL")?)?
        .extra_float_digits(None);
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .connect_with(options)
        .await?;

    // Confirm that the connection is encrypted
    let tls: Option<String> =
        sqlx::query_scalar("SELECT version FROM pg_stat_ssl WHERE pid = pg_backend_pid()")
            .fetch_one(&pool)
            .await?;
    println!("Connected to Postgres, TLS version: {}", tls.as_deref().unwrap_or("none"));

    let app = Router::new()
        .route("/todos", get(list_todos).post(create_todo))
        .route("/todos/{id}", patch(toggle_todo).delete(delete_todo))
        .with_state(pool);

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3117").await?;
    println!("Listening on http://localhost:3117/todos");
    axum::serve(listener, app).await?;
    Ok(())
}
```

Values are always passed with `.bind` as parameters (`$1`), never formatted into the SQL string. Each distinct SQL string is prepared once per connection and kept in a statement cache of 100 statements, which sqlx closes when it evicts them.

<Tip>
  **Compile-time checked queries**

  This guide uses the `query_as` function, which checks the SQL when it runs, so `cargo build` doesn't need a database. The `query!` and `query_as!` macros check the SQL and the column types against the database at compile time instead. They read `DATABASE_URL` from `.env` during the build, so keep it on the direct connection. To build without a database, for example in CI, run `cargo sqlx prepare`, commit the generated `.sqlx` directory, and set `SQLX_OFFLINE=true`.
</Tip>

<h2 id="verify">
  Run and verify
</h2>

Start the server:

```bash theme={null}
cargo run
```

```text theme={null}
Connected to Postgres, TLS version: TLSv1.3
Listening on http://localhost:3117/todos
```

The first line comes from `pg_stat_ssl`, and confirms that the session is encrypted. In a second terminal, create two todos, mark the first one done, and delete the second:

```bash theme={null}
curl -w '\n' -X POST localhost:3117/todos -H 'content-type: application/json' -d '{"title": "Try sqlx"}'
curl -w '\n' -X POST localhost:3117/todos -H 'content-type: application/json' -d '{"title": "Write a migration"}'
curl -w '\n' -X PATCH localhost:3117/todos/1
curl -w '\n' -X DELETE localhost:3117/todos/2
```

Each request returns the affected row. The `-w '\n'` option only adds a line break after each response:

```text theme={null}
{"id":1,"title":"Try sqlx","done":false,"created_at":"2026-10-01T14:22:32.475739Z"}
{"id":2,"title":"Write a migration","done":false,"created_at":"2026-10-01T14:22:32.627680Z"}
{"id":1,"title":"Try sqlx","done":true,"created_at":"2026-10-01T14:22:32.475739Z"}
{"id":2,"title":"Write a migration","done":false,"created_at":"2026-10-01T14:22:32.627680Z"}
```

Open `http://localhost:3117/todos` in your browser to list the remaining todos:

```json theme={null}
[{"id":1,"title":"Try sqlx","done":true,"created_at":"2026-10-01T14:22:32.475739Z"}]
```

To see the data in the console, open **SQL console** in the left sidebar of your service, expand `guide_rust` and then `public`, and click the `todos` table. The `_sqlx_migrations` table next to it holds the migration history. The `todos` table contains the remaining row:

| id | title | done | created\_at |
| - | - | - | - |
| 1 | Try sqlx | true | 2026-10-01 14:22:32.475739+00 |

<h2 id="pgbouncer">
  Use PgBouncer
</h2>

To connect the app through the bundled [PgBouncer](/products/managed-postgres/connection#pgbouncer), select **via PgBouncer** in the **Connect** modal and use port `6432` in the app's `DATABASE_URL`. The same CA certificate works. For example, keep `.env` on the direct connection for `sqlx-cli` and set the variable when you start the app:

```bash theme={null}
DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:6432/guide_rust?sslmode=verify-full&sslrootcert=ca-certificate.pem" cargo run
```

```text theme={null}
Connected to Postgres, TLS version: none
Listening on http://localhost:3117/todos
```

Through PgBouncer, `pg_stat_ssl` reports PgBouncer's own connection to Postgres, so the check prints `none`. Your app's connection to PgBouncer is still verified: a wrong CA certificate fails with `UnknownIssuer` in the same way.

PgBouncer runs in transaction pooling mode, so each transaction can run on a different Postgres connection. With sqlx:

* **Connecting** requires `.extra_float_digits(None)`, as in `main.rs`. By default, sqlx sends `extra_float_digits` as a startup parameter, and PgBouncer rejects the connection with `unsupported startup parameter: extra_float_digits`. For the same reason, `PgConnectOptions::options` fails with `unsupported startup parameter in options`.
* **Prepared statements** work with the default settings. sqlx frees statements that it evicts from its cache with a protocol-level `Close` message, which PgBouncer handles, also inside transactions. Don't set `statement_cache_capacity(0)`: it isn't needed, and with the cache turned off, sqlx still prepares named statements but never closes them.
* **Don't use `.persistent(false)`** on queries that run outside a transaction. sqlx then prepares an unnamed statement and executes it in a second round trip, which PgBouncer can send to a different Postgres connection. The query can fail with `unnamed prepared statement does not exist`, or silently run another client's statement and return wrong results.
* **Session settings** made with `SET`, including in a pool's `after_connect` hook, don't stay on your connection, and can leak to other clients. Use `SET LOCAL` inside a transaction instead.
* **Migrations** must use the direct connection. `sqlx-cli` and the `query!` macros can't connect through PgBouncer, because they always send `extra_float_digits`. `sqlx::migrate!` takes a session-level advisory lock (`pg_advisory_lock`), and through PgBouncer the unlock can reach a different Postgres connection. The lock then stays held on a pooled connection, and later migrations, including over the direct connection, hang until PgBouncer closes that connection.

<h2 id="next-steps">
  Next steps
</h2>

* [Connection](/products/managed-postgres/connection): connection strings, PgBouncer, and TLS
* [Settings](/products/managed-postgres/settings): Postgres and PgBouncer parameters, such as `max_connections`
* [Read replicas](/products/managed-postgres/read-replicas): send read-only queries to a replica with a second `PgPool`
* [Security](/products/managed-postgres/security): IP access lists and private networking