Go: pgx

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DATABASE_URL=postgres://app:${DB_PASSWORD}@old-postgres:5432/app
DATABASE_URL=postgres://app:${DB_PASSWORD}@machsql-host:5432/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Go 1.27
• pgx v5.11.0

Run

go mod tidy
export DATABASE_URL=postgres://app:${DB_PASSWORD}@machsql-host:5432/app
go run .

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.go

// pgx: the native PostgreSQL driver for Go.
package main

import (
  "context"
  "fmt"
  "os"

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

func main() {
  ctx := context.Background()
  conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
  if err != nil {
    fail(err)
  }
  defer conn.Close(ctx)

  if _, err := conn.Exec(ctx, "CREATE TABLE IF NOT EXISTS todo (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)"); err != nil {
    fail(err)
  }
  var id int64
  if err := conn.QueryRow(ctx, "INSERT INTO todo (title) VALUES ($1) RETURNING id", "write the examples").Scan(&id); err != nil {
    fail(err)
  }
  var open int64
  if err := conn.QueryRow(ctx, "SELECT count(*) FROM todo WHERE NOT done").Scan(&open); err != nil {
    fail(err)
  }
  fmt.Printf("inserted todo %d, %d open\n", id, open)
}

func fail(err error) {
  fmt.Fprintln(os.Stderr, err)
  os.Exit(1)
}

go.mod

module example.com/machsql/go-pgx

go 1.27

require github.com/jackc/pgx/v5 v5.11.0