Connect Go to PostgreSQL with pgx and sqlc on Ubuntu

An in-memory store is fine for proving that your HTTP layer works, but it forgets everything the moment the process restarts, and it cannot be shared across more than one instance of your API. Sooner or later, a real service needs a real database behind it. This part of the series replaces the MemoryStore built in Part 1 with PostgreSQL, using pgx as the driver and connection pool, and sqlc to generate type-safe Go code straight from SQL.

In Build a REST API in Go with Fiber v3 on Ubuntu, the bookmarkd API grew four endpoints and a store.Store interface with List, Get, Create, and Delete methods, backed by a Go map behind a mutex. That interface is the reason this part is possible without touching a single handler: a new PostgresStore type will implement the same interface, and swapping it in is a one-line change in main.go.

This tutorial is for developers who already have a basic PostgreSQL server running, or are willing to install one, and want to see a Go application connect to it the way a real production service does, with pooled connections, generated queries instead of hand-written Scan calls, and versioned schema migrations. By the end, bookmarkd’s data will survive a restart.

Conceptual Overview

pgx is the most widely used PostgreSQL driver for Go. Unlike the generic database/sql package with a third-party driver plugged underneath it, pgx can also be used directly, which gives you access to PostgreSQL-specific features and, more importantly for this tutorial, pgxpool, a connection pool built specifically for pgx. A pool keeps a set of already-open connections ready to hand out, so a request does not pay the cost of a fresh TCP handshake and authentication round trip every time it needs the database.

sqlc takes a different approach to writing database code than an ORM does. Instead of describing your schema in Go structs and letting a library generate SQL behind the scenes, you write plain SQL queries in .sql files, and sqlc generates Go functions and structs that match them exactly, checked against your real schema at generation time. This means a typo in a column name is caught when you run sqlc generate, not when a query fails in production at 2 AM.

goose is a schema migration tool. Instead of running SQL by hand against the database and hoping every environment stays in sync, you write numbered migration files, and goose tracks which ones have already run in a table inside the database itself, applying only the new ones.

Together, the flow for this part is: write a migration that creates the bookmarks table, apply it with goose, write a .sql file with the four queries the store needs, generate Go code from it with sqlc, and implement store.Store using that generated code and a pgxpool.Pool.

Prerequisites

Before starting, make sure you have:

• The bookmarkd project from Part 1, on the api-01 host at 10.20.0.51.
• A PostgreSQL 16 server reachable from api-01. This guide uses a host named db-01 at 10.20.0.52. If you do not have one yet, follow How to Install and Configure PostgreSQL on Ubuntu first.
• sudo access on api-01.
• Go 1.25 installed (from Part 1).

Step 1: Create a Database and User

On db-01, connect as the postgres role and create a dedicated database and application user for bookmarkd:

sudo -u postgres psql

CREATE DATABASE bookmarkd;
CREATE USER bookmarkd WITH PASSWORD 'change-me-to-a-real-secret';
GRANT ALL PRIVILEGES ON DATABASE bookmarkd TO bookmarkd;
\c bookmarkd
GRANT ALL ON SCHEMA public TO bookmarkd;
\q

Next, allow api-01 to connect. Edit pg_hba.conf (the path varies by installed version, typically /etc/postgresql/16/main/pg_hba.conf) and add a line for the application host:

sudo tee -a /etc/postgresql/16/main/pg_hba.conf > /dev/null <<'EOF'
host    bookmarkd       bookmarkd       10.20.0.51/32           scram-sha-256
EOF

Make sure PostgreSQL is also listening on the network interface, not just localhost, by checking listen_addresses in postgresql.conf, then restart:

sudo systemctl restart postgresql

From api-01, confirm the connection works:

sudo apt install -y postgresql-client
psql "postgresql://bookmarkd:[email protected]:5432/bookmarkd" -c '\conninfo'

Step 2: Install goose and Write a Migration

On api-01, install goose:

go install github.com/pressly/goose/v3/cmd/goose@latest

Create a migrations directory and the first migration file:

cd ~/bookmarkd
mkdir -p db/migrations
goose -dir db/migrations create create_bookmarks_table sql

This creates a timestamped file like db/migrations/20260811090000_create_bookmarks_table.sql. Replace its contents with:

-- +goose Up
CREATE TABLE bookmarks (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    title      text NOT NULL,
    url        text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_bookmarks_created_at ON bookmarks (created_at DESC);

-- +goose Down
DROP TABLE bookmarks;

The gen_random_uuid() default requires the pgcrypto extension on PostgreSQL versions before 13, but PostgreSQL 16 ships it built in through the pgcrypto extension already available; enable it once if gen_random_uuid() errors out:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Apply the migration:

export DATABASE_URL="postgresql://bookmarkd:[email protected]:5432/bookmarkd?sslmode=disable"
goose -dir db/migrations postgres "$DATABASE_URL" up

OK    20260811090000_create_bookmarks_table.sql

Goose creates its own goose_db_version table to track what has run, so this same command is safe to run again on a fresh environment or after adding new migration files later in the series.

Step 3: Write Queries and Generate Code with sqlc

Install the sqlc CLI:

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

Create sqlc.yaml in the project root:

version: "2"
sql:
  - engine: "postgresql"
    queries: "db/queries"
    schema: "db/migrations"
    gen:
      go:
        package: "sqlcgen"
        out: "internal/store/sqlcgen"
        sql_package: "pgx/v5"
        emit_json_tags: true

The sql_package: pgx/v5 setting tells sqlc to generate code that uses pgx directly rather than database/sql, which matters because it lets the generated functions accept a pgxpool.Pool and use pgx’s native types.

Create db/queries/bookmarks.sql:

-- name: ListBookmarks :many
SELECT id, title, url, created_at
FROM bookmarks
ORDER BY created_at DESC;

-- name: GetBookmark :one
SELECT id, title, url, created_at
FROM bookmarks
WHERE id = $1;

-- name: CreateBookmark :one
INSERT INTO bookmarks (title, url)
VALUES ($1, $2)
RETURNING id, title, url, created_at;

-- name: DeleteBookmark :execrows
DELETE FROM bookmarks
WHERE id = $1;

The comment above each query is not decoration; sqlc parses it to decide the generated function’s name and return shape. :many returns a slice, :one returns a single row (or an error if none is found), and :execrows returns the number of affected rows, which is exactly what is needed to tell a delete of a nonexistent ID apart from a successful one.

Generate the code:

sqlc generate

This creates internal/store/sqlcgen/ with a Queries type exposing ListBookmarks, GetBookmark, CreateBookmark, and DeleteBookmark as real Go methods, along with a generated Bookmark struct matching the table’s columns.

Step 4: Implement store.Store Against PostgreSQL

Add pgx to the module:

go get github.com/jackc/pgx/v5/pgxpool

Create internal/store/postgres.go:

package store

import (
	"context"
	"errors"

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

	"github.com/facsiaginsa/bookmarkd/internal/store/sqlcgen"
)

type PostgresStore struct {
	pool    *pgxpool.Pool
	queries *sqlcgen.Queries
}

func NewPostgresStore(pool *pgxpool.Pool) *PostgresStore {
	return &PostgresStore{
		pool:    pool,
		queries: sqlcgen.New(pool),
	}
}

func (s *PostgresStore) List(ctx context.Context) ([]Bookmark, error) {
	rows, err := s.queries.ListBookmarks(ctx)
	if err != nil {
		return nil, err
	}

	out := make([]Bookmark, 0, len(rows))
	for _, r := range rows {
		out = append(out, Bookmark{
			ID:        r.ID.String(),
			Title:     r.Title,
			URL:       r.Url,
			CreatedAt: r.CreatedAt.Time,
		})
	}
	return out, nil
}

func (s *PostgresStore) Get(ctx context.Context, id string) (Bookmark, error) {
	uid, err := parseUUID(id)
	if err != nil {
		return Bookmark{}, ErrNotFound
	}

	r, err := s.queries.GetBookmark(ctx, uid)
	if errors.Is(err, pgx.ErrNoRows) {
		return Bookmark{}, ErrNotFound
	}
	if err != nil {
		return Bookmark{}, err
	}

	return Bookmark{
		ID:        r.ID.String(),
		Title:     r.Title,
		URL:       r.Url,
		CreatedAt: r.CreatedAt.Time,
	}, nil
}

func (s *PostgresStore) Create(ctx context.Context, b Bookmark) (Bookmark, error) {
	r, err := s.queries.CreateBookmark(ctx, sqlcgen.CreateBookmarkParams{
		Title: b.Title,
		Url:   b.URL,
	})
	if err != nil {
		return Bookmark{}, err
	}

	return Bookmark{
		ID:        r.ID.String(),
		Title:     r.Title,
		URL:       r.Url,
		CreatedAt: r.CreatedAt.Time,
	}, nil
}

func (s *PostgresStore) Delete(ctx context.Context, id string) error {
	uid, err := parseUUID(id)
	if err != nil {
		return ErrNotFound
	}

	affected, err := s.queries.DeleteBookmark(ctx, uid)
	if err != nil {
		return err
	}
	if affected == 0 {
		return ErrNotFound
	}
	return nil
}

Add a small helper for parsing the ID from the URL into pgx’s UUID type, internal/store/uuid.go:

package store

import "github.com/jackc/pgx/v5/pgtype"

func parseUUID(id string) (pgtype.UUID, error) {
	var u pgtype.UUID
	err := u.Scan(id)
	return u, err
}

Note the errors.Is(err, pgx.ErrNoRows) check in Get. This is the exact same pattern the in-memory store used with ErrNotFound, just translated from pgx’s own not-found error into the store package’s own ErrNotFound, so the handlers written in Part 1 keep working without any changes; they only ever check for store.ErrNotFound, never for a pgx-specific type.

Step 5: Configure the Pool and Wire It Into main.go

Update cmd/api/main.go to build a pgxpool.Pool and pass it to NewPostgresStore instead of NewMemoryStore:

package main

import (
	"context"
	"log"
	"os"
	"time"

	"github.com/gofiber/fiber/v3"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/facsiaginsa/bookmarkd/internal/handler"
	"github.com/facsiaginsa/bookmarkd/internal/store"
)

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	dbURL := os.Getenv("DATABASE_URL")
	if dbURL == "" {
		log.Fatal("DATABASE_URL is required")
	}

	poolConfig, err := pgxpool.ParseConfig(dbURL)
	if err != nil {
		log.Fatalf("invalid DATABASE_URL: %v", err)
	}
	poolConfig.MaxConns = 10
	poolConfig.MinConns = 2

	pool, err := pgxpool.NewWithConfig(ctx, poolConfig)
	if err != nil {
		log.Fatalf("failed to create connection pool: %v", err)
	}
	defer pool.Close()

	if err := pool.Ping(ctx); err != nil {
		log.Fatalf("failed to reach database: %v", err)
	}

	app := fiber.New(fiber.Config{
		ErrorHandler: func(c fiber.Ctx, err error) error {
			code := fiber.StatusInternalServerError
			message := "internal server error"

			if fe, ok := err.(*fiber.Error); ok {
				code = fe.Code
				message = fe.Message
			}

			return c.Status(code).JSON(fiber.Map{"error": message})
		},
	})

	pgStore := store.NewPostgresStore(pool)
	bookmarkHandler := &handler.BookmarkHandler{Store: pgStore}

	v1 := app.Group("/v1")
	bookmarks := v1.Group("/bookmarks")
	bookmarks.Get("/", bookmarkHandler.List)
	bookmarks.Get("/:id", bookmarkHandler.Get)
	bookmarks.Post("/", bookmarkHandler.Create)
	bookmarks.Delete("/:id", bookmarkHandler.Delete)

	log.Fatal(app.Listen(":8080"))
}

MaxConns and MinConns bound how many connections the pool keeps open. Ten is a reasonable starting point for a single small API instance talking to a database on the same private network; raising it without also raising PostgreSQL’s own max_connections just moves the bottleneck from your application to the database server. If this API ever needs to scale to many instances, look at Connection Pooling for PostgreSQL with PgBouncer on Ubuntu, which sits a shared external pooler between every instance and PostgreSQL so the total connection count stays under control regardless of how many API processes are running.

Restart the service with the connection string set:

sudo systemctl stop bookmarkd
DATABASE_URL="postgresql://bookmarkd:[email protected]:5432/bookmarkd?sslmode=disable" go run ./cmd/api

Step 6: Verify Data Survives a Restart

Create a bookmark, then check it is really in PostgreSQL, not just in the running process:

curl -s -X POST http://localhost:8080/v1/bookmarks \
  -H "Content-Type: application/json" \
  -d '{"title": "sqlc Docs", "url": "https://docs.sqlc.dev"}'

Stop the process with Ctrl+C, start it again with the same DATABASE_URL, and list bookmarks:

curl -s http://localhost:8080/v1/bookmarks

The bookmark created before the restart is still there, because it now lives in the bookmarks table on db-01 instead of a map that vanished with the process.

Common Mistakes and Troubleshooting

“password authentication failed for user” from psql or the app. Double-check the password in DATABASE_URL matches what was set with CREATE USER, and that pg_hba.conf uses scram-sha-256 (or md5 on older servers) rather than trust or peer, which do not accept a password at all.

Connection refused from api-01 to db-01. Confirm listen_addresses in postgresql.conf includes the server’s private IP or *, not just localhost, and that a firewall is not blocking port 5432 between the two hosts.

sqlc generate fails complaining about an unknown table. This means sqlc could not find the migration that creates the table referenced in your query. Confirm the schema path in sqlc.yaml points at the same db/migrations directory goose uses, and that the migration file was saved before running sqlc.

pgx.ErrNoRows leaks through as a 500 instead of a 404. Check that every :one query result is checked with errors.Is(err, pgx.ErrNoRows) before checking for a generic error, since a plain if err != nil check earlier in the function will catch it first and never reach the more specific check.

Pool exhausted under load, requests hang. This usually means MaxConns is set too low for the actual concurrency, or a connection is being held open somewhere instead of released. Since pgxpool connections are returned automatically when a query finishes, this is more often caused by a slow query holding a connection for too long than a leak; check pg_stat_activity on db-01 for long-running queries.

Best Practices

• Never commit real credentials. The DATABASE_URL values in this tutorial are for a private lab network; in a real deployment, load them from environment variables or a secrets manager, never from a file checked into version control.
• Keep migrations forward-only in production. Running goose down against a production database is rarely what you want; write a new forward migration to fix a mistake instead of rolling back and losing any data written since.
• Size the pool to the database, not the other way around. MaxConns across every instance of your API should stay comfortably under PostgreSQL’s max_connections, leaving headroom for administrative connections and other services.
• Let sqlc regenerate on every schema change. Treat internal/store/sqlcgen as generated output, not code you hand-edit; regenerate it every time a migration changes the schema so it never silently drifts out of sync.
• Set a context timeout on every database call from an HTTP handler. A slow or hung query should not be able to hold a request open indefinitely; propagating the request’s context down to pgx, as this store already does, ensures a canceled request also cancels its database work.

Conclusion

The bookmarkd API now persists real data in PostgreSQL, with a schema tracked by versioned goose migrations and queries generated by sqlc instead of hand-written and easy to typo. Because everything was built behind the store.Store interface from Part 1, this was a change confined entirely to the data layer: the handlers, routes, and error handling did not need to change at all.

Right now, though, anyone can create or delete anyone else’s bookmarks, because there is no concept of a user yet. Part 3 adds a users table, password hashing, and JWT-based authentication, so bookmarks belong to the account that created them.