> ## 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 Go (pgx) with ClickHouse Managed Postgres

> Connect a Go service to ClickHouse Managed Postgres with pgx and pgxpool over verified TLS, run migrations with golang-migrate, and serve a net/http CRUD API

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-go-beta" />

[pgx](https://github.com/jackc/pgx) is the most widely used PostgreSQL driver for Go, and `pgxpool` is its connection pool. In this guide, you connect a Go service to ClickHouse Managed Postgres over verified TLS, create a `todos` table with a [golang-migrate](https://github.com/golang-migrate/migrate) migration, and serve create, read, update, and delete requests from a `net/http` server.

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

* [Go](https://go.dev/dl/) 1.25 or later. This guide was tested with Go 1.27.1, pgx 5.11.0, and golang-migrate 4.20.1.
* 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 uses port `5432` and ends in `sslmode=verify-full&sslrootcert=<service-name>-ca-certificate.pem`. Click **Download CA certificate**. You can also download it later from **Settings → CA Certificate**. The certificate is unique to your instance, so pgx can use it to verify that it's talking to your server.

This guide connects **directly** to Postgres on port `5432`, both for migrations and for the app. A Go service is a long-lived process, and `pgxpool` already keeps a pool of connections in it, so it doesn't need a second pooler. If you run many instances of the service and need more client connections than Postgres allows, use the bundled PgBouncer (**via PgBouncer**, port `6432`) for the app; see [Use PgBouncer](#pgbouncer), which needs one extra connection setting.

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

Create a Go module, add pgx, and install the `migrate` CLI with the pgx driver:

```bash theme={null}
mkdir todo-api && cd todo-api
go mod init example.com/todo-api
go get github.com/jackc/pgx/v5
go install -tags 'pgx5' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
```

The `-tags 'pgx5'` flag compiles the `migrate` CLI with its pgx v5 driver, which uses the `pgx5://` URL scheme. `go install` puts the binary in `$GOBIN`, or in `$(go env GOPATH)/bin` if `GOBIN` isn't set, so make sure that directory is on your `PATH`.

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

  Skip `mkdir` and `go mod init`. In your module, run `go get github.com/jackc/pgx/v5` and the `go install` command above, then continue with [Configure the connection](#configure-connection). If your code uses `database/sql`, [`stdlib.OpenDBFromPool`](https://pkg.go.dev/github.com/jackc/pgx/v5/stdlib#OpenDBFromPool) wraps the same pool as a `*sql.DB`, so you can move existing queries over gradually; change `?` placeholders to `$1`, `$2`, and so on.
</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_go;"
```

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

Set two environment variables in your shell. They point at the same `guide_go` database and differ only in the URL scheme: the app reads `DATABASE_URL`, and the `migrate` CLI uses `MIGRATE_URL`:

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

pgx parses the URL like `libpq` does. With `sslmode=verify-full`, it loads the CA file from `sslrootcert` and checks both the certificate chain and the hostname, so you don't need any TLS code. The `sslrootcert` path is resolved relative to the directory you run commands from. If your password contains special characters such as `@`, `/`, or `#`, URL-encode them.

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

  Without `sslmode`, pgx uses `prefer`, which doesn't verify the server certificate, even if you set `sslrootcert`. With `sslmode=verify-full` and a wrong CA file, pgx fails with `x509: certificate signed by unknown authority`, and if the file doesn't exist, it fails with `unable to read CA file`. If you connect by IP address instead of the hostname, it fails with `x509: cannot validate certificate for <IP> because it doesn't contain any IP SANs`.
</Warning>

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

Create the migration files:

```bash theme={null}
migrate create -ext sql -dir migrations -seq create_todos
```

```text theme={null}
/Users/you/todo-api/migrations/000001_create_todos.up.sql
/Users/you/todo-api/migrations/000001_create_todos.down.sql
```

Fill in the `up` migration:

```sql title="migrations/000001_create_todos.up.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()
);
```

And the `down` migration, which reverts it:

```sql title="migrations/000001_create_todos.down.sql" theme={null}
DROP TABLE todos;
```

Apply the migration:

```bash theme={null}
migrate -path migrations -database "$MIGRATE_URL" up
```

```text theme={null}
1/u create_todos (480.890916ms)
```

`migrate` records the current version in the `schema_migrations` table and only applies newer migrations on later runs. Commit the `migrations` directory with your code.

<Note>
  Always run `migrate` over the direct connection (port `5432`). It holds a session-level advisory lock while it runs, so that two deployments can't migrate at the same time. Through PgBouncer, the lock and unlock can run on different Postgres connections: the lock stays held, and later `migrate` runs hang or fail with `timeout: can't acquire database lock`.
</Note>

<h2 id="query">
  Query the database
</h2>

Create `db.go`. It creates the pool from `DATABASE_URL` and checks the connection at startup by asking Postgres which TLS version the session uses:

```go title="db.go" theme={null}
package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

// newPool creates a connection pool from DATABASE_URL and verifies that it can connect.
func newPool(ctx context.Context) (*pgxpool.Pool, error) {
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		return nil, err
	}

	var tlsVersion string
	err = pool.QueryRow(ctx,
		"SELECT coalesce(version, 'none') FROM pg_stat_ssl WHERE pid = pg_backend_pid()",
	).Scan(&tlsVersion)
	if err != nil {
		pool.Close()
		return nil, fmt.Errorf("connect to Postgres: %w", err)
	}
	log.Printf("Connected to Postgres (TLS: %s)", tlsVersion)
	return pool, nil
}
```

Create `todos.go` with the HTTP handlers. Each handler runs one parameterized query on the pool, and `pgx.CollectRows` and `pgx.CollectExactlyOneRow` scan the rows into the `Todo` struct by column name:

```go title="todos.go" theme={null}
package main

import (
	"encoding/json"
	"errors"
	"log"
	"net/http"
	"strconv"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type Todo struct {
	ID        int64     `json:"id" db:"id"`
	Title     string    `json:"title" db:"title"`
	Done      bool      `json:"done" db:"done"`
	CreatedAt time.Time `json:"created_at" db:"created_at"`
}

const todoColumns = "id, title, done, created_at"

// registerTodoRoutes adds the CRUD endpoints for todos to mux.
func registerTodoRoutes(mux *http.ServeMux, pool *pgxpool.Pool) {
	// Read
	mux.HandleFunc("GET /todos", func(w http.ResponseWriter, r *http.Request) {
		rows, _ := pool.Query(r.Context(), "SELECT "+todoColumns+" FROM todos ORDER BY id")
		todos, err := pgx.CollectRows(rows, pgx.RowToStructByName[Todo])
		writeJSON(w, http.StatusOK, todos, err)
	})

	// Create
	mux.HandleFunc("POST /todos", func(w http.ResponseWriter, r *http.Request) {
		var in struct{ Title string }
		if err := json.NewDecoder(r.Body).Decode(&in); err != nil || in.Title == "" {
			http.Error(w, "expected a JSON body with a title", http.StatusBadRequest)
			return
		}
		rows, _ := pool.Query(r.Context(),
			"INSERT INTO todos (title) VALUES ($1) RETURNING "+todoColumns, in.Title)
		todo, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByName[Todo])
		writeJSON(w, http.StatusCreated, todo, err)
	})

	// Update: toggle the done flag
	mux.HandleFunc("PATCH /todos/{id}", func(w http.ResponseWriter, r *http.Request) {
		id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
		rows, _ := pool.Query(r.Context(),
			"UPDATE todos SET done = NOT done WHERE id = $1 RETURNING "+todoColumns, id)
		todo, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByName[Todo])
		writeJSON(w, http.StatusOK, todo, err)
	})

	// Delete
	mux.HandleFunc("DELETE /todos/{id}", func(w http.ResponseWriter, r *http.Request) {
		id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
		tag, err := pool.Exec(r.Context(), "DELETE FROM todos WHERE id = $1", id)
		if err == nil && tag.RowsAffected() == 0 {
			err = pgx.ErrNoRows
		}
		writeJSON(w, http.StatusNoContent, nil, err)
	})
}

func writeJSON(w http.ResponseWriter, status int, body any, err error) {
	switch {
	case errors.Is(err, pgx.ErrNoRows):
		http.Error(w, "not found", http.StatusNotFound)
	case err != nil:
		log.Println(err)
		http.Error(w, "database error", http.StatusInternalServerError)
	case status == http.StatusNoContent:
		w.WriteHeader(status)
	default:
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(status)
		enc := json.NewEncoder(w)
		enc.SetIndent("", "  ")
		enc.Encode(body)
	}
}
```

Passing values as arguments (`$1`) rather than formatting them into the SQL string prevents SQL injection. pgx sends them separately from the query text. You don't need to check the error from `pool.Query` separately: `pgx.CollectRows` and `pgx.CollectExactlyOneRow` return it.

Create `main.go`. It creates one pool for the whole process, serves the routes on port `3114`, and closes the pool on <kbd>Ctrl</kbd>+<kbd>C</kbd>. In an existing app, call `newPool` once at startup and `registerTodoRoutes` on your `http.ServeMux` instead:

```go title="main.go" theme={null}
package main

import (
	"context"
	"errors"
	"log"
	"net/http"
	"os"
	"os/signal"
	"syscall"
)

func main() {
	ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
	defer stop()

	pool, err := newPool(ctx)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	mux := http.NewServeMux()
	registerTodoRoutes(mux, pool)
	srv := &http.Server{Addr: ":3114", Handler: mux}

	go func() {
		<-ctx.Done()
		srv.Shutdown(context.Background())
	}()

	log.Println("Listening on http://localhost:3114/todos")
	if err := srv.ListenAndServe(); !errors.Is(err, http.ErrServerClosed) {
		log.Fatal(err)
	}
	log.Println("Shutting down")
}
```

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

Add the remaining dependencies of `pgxpool` to `go.mod` and `go.sum`, then start the server:

```bash theme={null}
go mod tidy
go run .
```

```text theme={null}
2026/10/01 09:44:25 Connected to Postgres (TLS: TLSv1.3)
2026/10/01 09:44:25 Listening on http://localhost:3114/todos
```

`TLSv1.3` comes from `pg_stat_ssl` on the server, so it confirms that the session is encrypted. If you point `sslrootcert` at any other certificate, the server doesn't start and logs `x509: certificate signed by unknown authority`, which shows that the certificate is actually checked.

In a second terminal, create two todos, mark the first one done, and delete the second:

```bash theme={null}
curl -X POST localhost:3114/todos -d '{"title": "Try pgx"}'
curl -X POST localhost:3114/todos -d '{"title": "Write a migration"}'
curl -X PATCH localhost:3114/todos/1
curl -X DELETE localhost:3114/todos/2
```

The `PATCH` request returns the updated row:

```json theme={null}
{
  "id": 1,
  "title": "Try pgx",
  "done": true,
  "created_at": "2026-10-01T09:44:25.896031-05:00"
}
```

Open `http://localhost:3114/todos` in your browser to list the remaining todos. pgx returns `timestamptz` values in your local time zone. The response looks like this:

```json theme={null}
[
  {
    "id": 1,
    "title": "Try pgx",
    "done": true,
    "created_at": "2026-10-01T09:44:25.896031-05:00"
  }
]
```

Stop the server with <kbd>Ctrl</kbd>+<kbd>C</kbd>. It logs `Shutting down` and closes the pool.

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

| id | title | done | created\_at |
| - | - | - | - |
| 1 | Try pgx | true | 2026-10-01 14:44:25.896031+00 |

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

To send the app's queries through the bundled [PgBouncer](/products/managed-postgres/connection#pgbouncer), select **via PgBouncer** in the Connect modal, change the port in `DATABASE_URL` to `6432`, and add `default_query_exec_mode=cache_describe`. Keep `MIGRATE_URL` on port `5432`:

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

pgx reads `default_query_exec_mode` from the URL and doesn't send it to the server. No code changes are needed, and the same CA certificate works. The startup log now shows `TLS: none`, because `pg_stat_ssl` reports PgBouncer's own connection to Postgres. Your app's connection to PgBouncer is still verified: a wrong CA certificate fails in the same way.

<Warning>
  **Don't use pgx's default query mode through PgBouncer**

  By default, pgx prepares each query as a named statement once per connection and reuses the column types it got back. PgBouncer prepares these statements on whichever Postgres connection runs the query and shares them between clients. After a migration changes a column's type, or adds a column to a table queried with `SELECT *`, queries through PgBouncer can keep failing with `cached plan must not change result type`, even after you restart the app, or, with pgx 5.9 and later, return wrong values without an error. For example, a `bigint` column changed to `double precision` comes back as the raw bits of the `float64` value, such as `4631178160564600832` for `42.5`.

  `cache_describe` caches only the parameter and column types, runs every query as an unnamed statement in one round trip, and decodes rows using the column types that the server returns with each result, so it isn't affected. `exec` also works. Don't use `describe_exec` through PgBouncer: it splits each query across two round trips, which PgBouncer can send to different Postgres connections. You don't need `statement_cache_capacity=0`.
</Warning>

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

* Transactions with `pool.Begin` or `pgx.BeginFunc` work as usual.
* Session settings made with `SET` don't stay with your connection, and can leak to other clients. Use `SET LOCAL` inside a transaction instead. PgBouncer also rejects `search_path` in the connection URL with `unsupported startup parameter: search_path`; schema-qualify table names instead.
* SQL-level `PREPARE` and `EXECUTE`, `LISTEN`, and session advisory locks don't work.

<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 `pgxpool.Pool`
* [Security](/products/managed-postgres/security): IP access lists and private networking