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

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 migration, and serve create, read, update, and delete requests from a net/http server.

Prerequisites

• Go 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, to create the database. You can also run the CREATE DATABASE statement in the SQL console.

Create a ClickHouse Managed Postgres service

In the ClickHouse Cloud console, click New service and select Postgres. The instance is ready in a few minutes. See the quickstart for a walkthrough.

Get your connection details

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, which needs one extra connection setting.

Set up the project

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

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.

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. If your code uses database/sql, 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.

Configure the connection

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:

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

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:

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.

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.

Create the schema with a migration

Create the migration files:

migrate create -ext sql -dir migrations -seq create_todos

/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:

migrations/000001_create_todos.up.sql

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:

migrations/000001_create_todos.down.sql

DROP TABLE todos;

Apply the migration:

migrate -path migrations -database "$MIGRATE_URL" up

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.

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.

Query the database

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:

db.go

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:

todos.go

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 Ctrl+C. In an existing app, call newPool once at startup and registerTodoRoutes on your http.ServeMux instead:

main.go

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")
}

Run and verify

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

go mod tidy
go run .

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:

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:

{
  "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:

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

Stop the server with Ctrl+C. 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

Use PgBouncer

To send the app’s queries through the bundled 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:

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.

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.

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.

Next steps

• Connection: connection strings, PgBouncer, and TLS
• Settings: Postgres and PgBouncer parameters, such as max_connections
• Read replicas: send read-only queries to a replica with a second pgxpool.Pool
• Security: IP access lists and private networking