Dev Skills
Go·Project 1 — URL shortener

Step 5 — Swap memory for Postgres

The same API, now with data that survives a restart. The service and handlers do not change.

Topic 63 of 98 · 10 min to read · 50 min to build
After this topic you can
  • Write a Postgres store with the same methods as the memory store.
  • Open and configure a connection pool and apply the schema.
  • Swap stores in main.go without changing the service or handlers.

Every command for this step

terminal — run these in order
# 1. a database
docker run -d --name short-pg \
  -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=shortener \
  -p 5432:5432 postgres:16
# 2. the driver
go get github.com/jackc/pgx/v5/stdlib
# 3. new files
mkdir -p migrations
touch internal/store/db.go internal/store/postgres.go
touch migrations/001_links.up.sql migrations/001_links.down.sql
# 4. apply the schema (no migration tool needed for one file)
docker exec -i short-pg psql -U postgres -d shortener < migrations/001_links.up.sql
# 5. run it
export DATABASE_URL='postgres://postgres:secret@localhost:5432/shortener?sslmode=disable'
go run ./cmd/api
# 6. prove the data survives a restart
curl -s -X POST localhost:8080/links -d '{"url":"https://go.dev"}' | jq -r .code
# Ctrl-C, then go run ./cmd/api again, then fetch that code

Why this step is small

Everything above the store talks to it through the Store interface: Save, ByCode, Incr. So switching to a database means writing one new type with those three methods and changing how main.go builds the store.

That is the payoff for building against an interface in step 2. If the handler had written SQL directly, this step would touch every handler.

Start a database and add the driver

terminal
docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=shortener \
  -p 5432:5432 postgres:16
go get github.com/jackc/pgx/v5/stdlib
database/sql is the standard library interface; pgx is the driver that speaks the Postgres protocol. One dependency, not a framework.

File 1 — the schema

migrations/001_links.up.sql — create this file
CREATE TABLE links (
  id         BIGSERIAL PRIMARY KEY,
  code       TEXT UNIQUE NOT NULL,      -- UNIQUE does the clash check for you
  long_url   TEXT NOT NULL,
  clicks     BIGINT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON links (created_at DESC);   -- for the list endpoint
The UNIQUE constraint on code is what actually stops two links getting the same code. Checking in Go first would still leave a gap between the check and the insert.

File 2 — opening the pool

internal/store/db.go — create this file
package store
import (
	"database/sql"
	"time"
	_ "github.com/jackc/pgx/v5/stdlib"    // blank import: registers the driver
)
func Open(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)       // does NOT connect yet
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(25)                // never leave this unlimited
	db.SetMaxIdleConns(25)
	db.SetConnMaxLifetime(5 * time.Minute)
	return db, db.Ping()                  // Ping is what actually connects
}
sql.DB is a pool, not a connection. Create one for the whole program and share it; it is safe to use from many goroutines.

File 3 — the SQL store

internal/store/postgres.go — create this file
package store
import (
	"context"
	"database/sql"
	"errors"
)
type Postgres struct{ db *sql.DB }
func NewPostgres(db *sql.DB) *Postgres { return &Postgres{db: db} }
func (s *Postgres) Save(ctx context.Context, l *Link) error {
	err := s.db.QueryRowContext(ctx,
		`INSERT INTO links (code, long_url) VALUES ($1, $2)
		 RETURNING id, clicks, created_at`,
		l.Code, l.LongURL,                   // $1 and $2 — never string formatting
	).Scan(&l.ID, &l.Clicks, &l.CreatedAt)
	if isUniqueViolation(err) {
		return ErrCodeTaken                  // same error the memory store returns
	}
	return err
}
func (s *Postgres) ByCode(ctx context.Context, code string) (*Link, error) {
	var l Link
	err := s.db.QueryRowContext(ctx,
		`SELECT code, long_url, clicks, created_at FROM links WHERE code = $1`,
		code,
	).Scan(&l.Code, &l.LongURL, &l.Clicks, &l.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound              // translate the driver's error to ours
	}
	if err != nil {
		return nil, err                      // never hand back a half-filled link
	}
	return &l, nil
}
func (s *Postgres) Incr(ctx context.Context, code string) error {
	_, err := s.db.ExecContext(ctx,`UPDATE links SET clicks = clicks + 1 WHERE code = $1`, code)
	return err
}
clicks = clicks + 1 is done by the database, so two redirects at the same moment cannot lose a count. Reading the value into Go and adding one would. Every method takes ctx and uses the ...Context variant, so a cancelled request stops its query instead of leaving it running.

Change how main builds the store

cmd/api/main.go
// before
st := store.NewMemory()
// after
db, err := store.Open(os.Getenv("DATABASE_URL"))
if err != nil {
	return err
}
defer db.Close()
st := store.NewPostgres(db)      // everything below is unchanged
The service and the handler do not change at all. That is what the interface bought you.
Watch out: Never put values into SQL with fmt.Sprintf or +. Pass every value as a $1, $2 placeholder, even values you think you generated yourself. Placeholders are your main defence against SQL injection. Table and column names cannot be placeholders, so if one has to vary, pick it from a fixed list in your code.
The short version
  • Building against an interface means changing storage touches the store package and main.go, nothing else.
  • sql.DB is a pool. Create one, share it, and always set SetMaxOpenConns.
  • Translate sql.ErrNoRows into your own ErrNotFound at the storage boundary.
  • Let the database do clicks = clicks + 1, and let the UNIQUE constraint catch clashes.
Remember: Placeholders always. A query built by joining strings is a security bug waiting for the right input.
Build this before moving on
Run the migration, write the three files, change how main builds the store, and confirm every endpoint behaves exactly as it did with the memory store. Then restart the server and check your links are still there.
Then try these in your own editor
  1. 1Stop Postgres and hit the API. You should get a clean 500 and a useful log line, not a panic.
  2. 2Run EXPLAIN ANALYZE on the redirect lookup and confirm it uses the unique index on code.
  3. 3Insert a duplicate code by hand in psql and watch the constraint reject it.
  4. 4Try rewriting one query with fmt.Sprintf and then shorten a URL containing a quote. Then put it back.
Official documentation
Check yourselfTake the Project 1 — URL shortener test