PostgreSQL #

PostgreSQL is the strongest open-source database available today — supporting JSON/JSONB, arrays, full-text search, geographic extensions (PostGIS), and many other advanced data types. Go has two main driver options: lib/pq (the older, stable driver) and pgx (the modern driver, far more powerful and performant). For new projects, pgx is highly recommended because it supports PostgreSQL-specific features that can’t be accessed through ordinary database/sql.

Installation #

# pgx — the modern driver, recommended
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/stdlib  # for database/sql compatibility

# lib/pq — the classic driver, still widely used
go get github.com/lib/pq

Connecting to PostgreSQL #

Via `database/sql` + pgx #

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib"
)

func openDB(dsn string) (*sql.DB, error) {
    // Format: postgres://user:password@host:port/dbname?sslmode=disable
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return nil, err
    }

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(1 * time.Minute)

    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("ping: %w", err)
    }
    return db, nil
}

Via pgxpool (Native, Recommended) #

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

func openPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, err
    }

    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = 5 * time.Minute
    config.MaxConnIdleTime = 1 * time.Minute

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, fmt.Errorf("create pool: %w", err)
    }

    if err := pool.Ping(ctx); err != nil {
        return nil, fmt.Errorf("ping: %w", err)
    }
    return pool, nil
}

Placeholders — `$1`, `$2`, … #

PostgreSQL uses $1, $2, … as placeholders:

// Different from MySQL (?) and Oracle (:1)
row := db.QueryRowContext(ctx,
    "SELECT name, price FROM products WHERE id = $1", id)

// Named parameters aren't natively supported in database/sql,
// but pgx supports @name when using pgx directly
rows, err := db.QueryContext(ctx,
    "SELECT id, name FROM products WHERE category = $1 AND price <= $2",
    "electronics", 5_000_000)

Complete CRUD #

type Product struct {
    ID        int
    Name      string
    Price     float64
    Stock     int
    Category  string
    CreatedAt time.Time
}

// INSERT with RETURNING — the PostgreSQL idiom for getting a new ID
func createProduct(ctx context.Context, db *sql.DB, p *Product) (int64, error) {
    var id int64
    err := db.QueryRowContext(ctx, `
        INSERT INTO products (name, price, stock, category, created_at)
        VALUES ($1, $2, $3, $4, NOW())
        RETURNING id
    `, p.Name, p.Price, p.Stock, p.Category).Scan(&id)
    if err != nil {
        return 0, fmt.Errorf("create: %w", err)
    }
    return id, nil
}

// SELECT one row
func getProduct(ctx context.Context, db *sql.DB, id int) (*Product, error) {
    var p Product
    err := db.QueryRowContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE id = $1
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &p, err
}

// SELECT many rows
func listProducts(ctx context.Context, db *sql.DB, category string) ([]*Product, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE category = $1 ORDER BY name
    `, category)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)
        products = append(products, &p)
    }
    return products, rows.Err()
}

// UPDATE
func updateProduct(ctx context.Context, db *sql.DB, p *Product) error {
    res, err := db.ExecContext(ctx, `
        UPDATE products SET name=$1, price=$2, stock=$3, category=$4
        WHERE id=$5
    `, p.Name, p.Price, p.Stock, p.Category, p.ID)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

// DELETE
func deleteProduct(ctx context.Context, db *sql.DB, id int) error {
    res, err := db.ExecContext(ctx,
        "DELETE FROM products WHERE id = $1", id)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

UPSERT — INSERT OR UPDATE #

PostgreSQL has a very expressive ON CONFLICT syntax:

func upsertProduct(ctx context.Context, db *sql.DB, p *Product) error {
    _, err := db.ExecContext(ctx, `
        INSERT INTO products (name, price, stock, category)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (name)
        DO UPDATE SET
            price = EXCLUDED.price,
            stock  = EXCLUDED.stock,
            category = EXCLUDED.category
    `, p.Name, p.Price, p.Stock, p.Category)
    return err
}

// ON CONFLICT DO NOTHING — insert only if it doesn't exist yet
func insertIfNotExists(ctx context.Context, db *sql.DB, p *Product) error {
    _, err := db.ExecContext(ctx, `
        INSERT INTO products (name, price, stock, category)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (name) DO NOTHING
    `, p.Name, p.Price, p.Stock, p.Category)
    return err
}

PostgreSQL-Specific Features #

Arrays #

import "github.com/lib/pq"

// A tags TEXT[] column
type Article struct {
    ID    int
    Title string
    Tags  []string
}

func createArticle(ctx context.Context, db *sql.DB, a *Article) error {
    _, err := db.ExecContext(ctx,
        "INSERT INTO articles (title, tags) VALUES ($1, $2)",
        a.Title,
        pq.Array(a.Tags),  // convert []string to a PostgreSQL array
    )
    return err
}

func getArticle(ctx context.Context, db *sql.DB, id int) (*Article, error) {
    var a Article
    err := db.QueryRowContext(ctx,
        "SELECT id, title, tags FROM articles WHERE id = $1", id,
    ).Scan(&a.ID, &a.Title, pq.Array(&a.Tags))  // scan into []string
    return &a, err
}

// Query with array overlap
rows, _ := db.QueryContext(ctx,
    "SELECT * FROM articles WHERE tags && $1",
    pq.Array([]string{"go", "backend"}))

JSONB #

import "encoding/json"

type Metadata map[string]interface{}

// Store JSONB
func saveWithMetadata(ctx context.Context, db *sql.DB, name string, meta Metadata) error {
    metaJSON, err := json.Marshal(meta)
    if err != nil {
        return err
    }
    _, err = db.ExecContext(ctx,
        "INSERT INTO products (name, metadata) VALUES ($1, $2)",
        name, metaJSON)
    return err
}

// Read JSONB
func getMetadata(ctx context.Context, db *sql.DB, id int) (Metadata, error) {
    var raw []byte
    err := db.QueryRowContext(ctx,
        "SELECT metadata FROM products WHERE id = $1", id,
    ).Scan(&raw)
    if err != nil {
        return nil, err
    }

    var meta Metadata
    return meta, json.Unmarshal(raw, &meta)
}

// Query JSON fields
rows, _ := db.QueryContext(ctx, `
    SELECT name, metadata->>'color' AS color
    FROM products
    WHERE metadata @> '{"brand": "Apple"}'::jsonb
`)

COPY — The Fastest Bulk Insert #

COPY is the fastest way to insert large amounts of data into PostgreSQL — far faster than ordinary INSERT:

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

func bulkInsert(ctx context.Context, pool *pgxpool.Pool, products []Product) error {
    // pgx CopyFrom — very fast for bulk inserts
    _, err := pool.CopyFrom(
        ctx,
        pgx.Identifier{"products"},
        []string{"name", "price", "stock", "category"},
        pgx.CopyFromRows(func() [][]interface{} {
            rows := make([][]interface{}, len(products))
            for i, p := range products {
                rows[i] = []interface{}{p.Name, p.Price, p.Stock, p.Category}
            }
            return rows
        }()),
    )
    return err
}

LISTEN/NOTIFY — Real-Time Events #

PostgreSQL supports built-in pub/sub via LISTEN/NOTIFY:

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

func listenForChanges(ctx context.Context, connStr string) {
    conn, err := pgx.Connect(ctx, connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close(ctx)

    // Subscribe to a channel
    if _, err := conn.Exec(ctx, "LISTEN product_changes"); err != nil {
        log.Fatal(err)
    }

    log.Println("Listening for 'product_changes' notifications...")
    for {
        notification, err := conn.WaitForNotification(ctx)
        if err != nil {
            log.Println("Error:", err)
            return
        }
        log.Printf("Notification from channel '%s': %s\n",
            notification.Channel, notification.Payload)
    }
}

// Trigger to automatically NOTIFY on changes:
// CREATE OR REPLACE FUNCTION notify_product_change() RETURNS TRIGGER AS $$
// BEGIN
//   PERFORM pg_notify('product_changes',
//     json_build_object('op', TG_OP, 'id', NEW.id)::text);
//   RETURN NEW;
// END;
// $$ LANGUAGE plpgsql;
//
// CREATE TRIGGER product_change_trigger
// AFTER INSERT OR UPDATE OR DELETE ON products
// FOR EACH ROW EXECUTE FUNCTION notify_product_change();

The Database Transaction Workflow (Tx) #

To prevent inconsistent data, all query operations within a Go transaction must be handled correctly using tx.Commit() on success or tx.Rollback() on failure:

flowchart TD
    Start["db.BeginTx(ctx, &options)"] --> Tx["Transaction Opened (tx)"]
    Tx --> Exec["Execute SQL via tx"]
    Exec --> Check{"Error Occurred?"}
    
    Check -->|"Yes"| Rollback["tx.Rollback()<br/>(Cancel all changes)"]
    Check -->|"No"| More{"More Queries?"}
    
    More -->|"Yes"| Exec
    More -->|"No"| Commit["tx.Commit()<br/>(Save permanently to DB)"]
    
    Rollback --> Done["Transaction Finished"]
    Commit --> Done

Transactions with Isolation Levels #

func transferWithIsolation(ctx context.Context, db *sql.DB, fromID, toID, qty int) error {
    // PostgreSQL supports all standard SQL isolation levels
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,  // the strictest isolation
        ReadOnly:  false,
    })
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // SELECT FOR UPDATE — lock rows to prevent concurrent updates
    var fromStock int
    err = tx.QueryRowContext(ctx,
        "SELECT stock FROM products WHERE id = $1 FOR UPDATE",
        fromID).Scan(&fromStock)
    if err != nil {
        return err
    }
    if fromStock < qty {
        return errors.New("insufficient stock")
    }

    if _, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock - $1 WHERE id = $2",
        qty, fromID); err != nil {
        return err
    }

    if _, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock + $1 WHERE id = $2",
        qty, toID); err != nil {
        return err
    }

    return tx.Commit()
}

Complete Example Program #

package main

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "log"
    "time"

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

var ErrNotFound = errors.New("data not found")

type Product struct {
    ID        int
    Name      string
    Price     float64
    Stock     int
    Category  string
    CreatedAt time.Time
}

type ProductRepo struct{ db *sql.DB }

func NewProductRepo(db *sql.DB) *ProductRepo { return &ProductRepo{db} }

func (r *ProductRepo) Create(ctx context.Context, p *Product) (int64, error) {
    var id int64
    err := r.db.QueryRowContext(ctx, `
        INSERT INTO products (name, price, stock, category, created_at)
        VALUES ($1, $2, $3, $4, NOW())
        RETURNING id
    `, p.Name, p.Price, p.Stock, p.Category).Scan(&id)
    return id, err
}

func (r *ProductRepo) FindByID(ctx context.Context, id int) (*Product, error) {
    var p Product
    err := r.db.QueryRowContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE id = $1
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &p, err
}

func (r *ProductRepo) List(ctx context.Context) ([]*Product, error) {
    rows, err := r.db.QueryContext(ctx,
        "SELECT id, name, price, stock, category, created_at FROM products ORDER BY id")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var list []*Product
    for rows.Next() {
        var p Product
        rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)
        list = append(list, &p)
    }
    return list, rows.Err()
}

func (r *ProductRepo) Upsert(ctx context.Context, p *Product) error {
    _, err := r.db.ExecContext(ctx, `
        INSERT INTO products (name, price, stock, category)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (name)
        DO UPDATE SET price=EXCLUDED.price, stock=EXCLUDED.stock
    `, p.Name, p.Price, p.Stock, p.Category)
    return err
}

func (r *ProductRepo) Delete(ctx context.Context, id int) error {
    res, err := r.db.ExecContext(ctx,
        "DELETE FROM products WHERE id = $1", id)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

const ddl = `
CREATE TABLE IF NOT EXISTS products (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(200) NOT NULL UNIQUE,
    price      NUMERIC(15,2) NOT NULL DEFAULT 0,
    stock      INTEGER NOT NULL DEFAULT 0,
    category   VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);`

func main() {
    dsn := "postgres://postgres:secret@localhost:5432/onlinestore?sslmode=disable"

    db, err := sql.Open("pgx", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

    if err := db.Ping(); err != nil {
        log.Fatal("PostgreSQL connection failed:", err)
    }
    fmt.Println("✓ Connected to PostgreSQL")

    if _, err := db.Exec(ddl); err != nil {
        log.Fatal("DDL:", err)
    }

    ctx := context.Background()
    repo := NewProductRepo(db)

    // Upsert — insert or update if it already exists
    fmt.Println("\n=== Upserting Products ===")
    seeds := []Product{
        {Name: "PostgreSQL Pro License", Price: 0, Stock: 999, Category: "software"},
        {Name: "TimescaleDB Extension", Price: 500_000, Stock: 100, Category: "software"},
        {Name: "PostGIS Extension", Price: 0, Stock: 999, Category: "extension"},
    }
    for _, p := range seeds {
        p := p
        if err := repo.Upsert(ctx, &p); err != nil {
            log.Printf("Failed to upsert %s: %v", p.Name, err)
        } else {
            fmt.Printf("  Upserted: %s\n", p.Name)
        }
    }

    // Create
    fmt.Println("\n=== Inserting New ===")
    newProd := &Product{
        Name: "pgvector Extension", Price: 0, Stock: 999, Category: "extension",
    }
    id, err := repo.Create(ctx, newProd)
    if err != nil {
        log.Println("Create:", err)
    } else {
        fmt.Printf("  Created with ID: %d\n", id)
    }

    // List
    fmt.Println("\n=== All Products ===")
    list, _ := repo.List(ctx)
    for _, p := range list {
        fmt.Printf("  [%d] %-30s %-12s Rp%.0f\n",
            p.ID, p.Name, p.Category, p.Price)
    }

    // FindByID
    if id > 0 {
        fmt.Println("\n=== FindByID ===")
        p, err := repo.FindByID(ctx, int(id))
        if err != nil {
            log.Println(err)
        } else {
            fmt.Printf("  Found: %s (created: %s)\n",
                p.Name, p.CreatedAt.Format("2006-01-02 15:04:05"))
        }

        // Delete
        fmt.Println("\n=== Delete ===")
        if err := repo.Delete(ctx, int(id)); err != nil {
            log.Println(err)
        } else {
            fmt.Printf("  ID %d deleted successfully\n", id)
        }
    }
}

Summary #

pgx is the best PostgreSQL driver for Go — more performant than lib/pq and supports PostgreSQL-specific features.
Placeholders $1, $2, … — different from MySQL (?) and Oracle (:1).
RETURNING id after INSERT to get the new ID — more idiomatic than LastInsertId().
ON CONFLICT for expressive UPSERTs — DO UPDATE SET or DO NOTHING.
pq.Array() for PostgreSQL array columns (TEXT[], INT[], etc.).
JSONB for semi-structured data — query with the @>, ->, ->> operators.
COPY via pgxpool.CopyFrom for bulk inserts — 10-100x faster than an INSERT loop.
LISTEN/NOTIFY for real-time database events — a simple alternative to a message broker.
SELECT FOR UPDATE to lock rows within a transaction — prevents concurrent updates.
BIGSERIAL for auto-increment IDs; TIMESTAMPTZ for timezone-aware timestamps.

← Previous: Oracle Next: GORM →