Data Access

While it's certainly a challenging and rewarding endeavor to write a database driver from scratch, it's not practical for the complexity.

We will use pgx to talk to a PostgresQL database.

Install pgx

If you haven't initialized Go modules, do so with:

$ go mod init wdgfs

Next, install pgx v5:

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

Take a look at the dependencies:

$ cat go.mod
module wdgfs

go 1.23.1

require (
        github.com/jackc/pgpassfile v1.0.0 // indirect
        github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
        github.com/jackc/pgx/v5 v5.7.1 // indirect
        github.com/jackc/puddle/v2 v2.2.2 // indirect
        golang.org/x/crypto v0.27.0 // indirect
        golang.org/x/sync v0.8.0 // indirect
        golang.org/x/text v0.18.0 // indirect
)

Setup PostgresQL

$ docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=my_password -d postgres

Create Database

$ docker exec -it postgres createdb -U postgres mydb

Create Table

Add schema.sql

DROP TABLE IF EXISTS album;
CREATE TABLE album (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(128) NOT NULL,
  artist     VARCHAR(255) NOT NULL,
  price      NUMERIC(5,2) NOT NULL
);

INSERT INTO album
  (title, artist, price)
VALUES
  ('Blue Train', 'John Coltrane', 56.99),
  ('Giant Steps', 'John Coltrane', 63.99),
  ('Jeru', 'Gerry Mulligan', 17.99),
  ('Sarah Vaughan', 'Sarah Vaughan', 34.98);

Execute the sql file:

$ docker exec -i postgres psql -U postgres mydb < schema.sql

Query PostgresQL

Update server.go with following content:

package main

import (
	"context"
	"fmt"
	"os"

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

type Album struct {
	ID     int64
	Title  string
	Artist string
	Price  float32
}

func main() {
	dbpool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
		os.Exit(1)
	}
	defer dbpool.Close()

	rows, err := dbpool.Query(context.Background(), "select * from album limit 2")
	if err != nil {
		fmt.Fprintf(os.Stderr, "Query failed: %v\n", err)
		os.Exit(1)
	}

	albums, err := pgx.CollectRows(rows, pgx.RowToStructByName[Album])
	if err != nil {
		fmt.Fprintf(os.Stderr, "Failed to collect rows: %v\n", err)
		os.Exit(1)
	}
	fmt.Println(albums)
}

$ export DATABASE_URL=postgresql://postgres:my_password@localhost:5432/mydb
$ go run server.go
[{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}]