PostgreSQL #

PostgreSQL adalah database open-source terkuat yang tersedia saat ini — mendukung JSON/JSONB, array, full-text search, extension geografis (PostGIS), dan banyak tipe data canggih lainnya. Go punya dua pilihan driver utama: lib/pq (driver lama, stabil) dan pgx (driver modern yang jauh lebih powerful dan performant). Untuk proyek baru, pgx sangat direkomendasikan karena mendukung fitur PostgreSQL-spesifik yang tidak bisa diakses via database/sql biasa.

Instalasi #

# pgx — driver modern, direkomendasikan
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/stdlib  # untuk kompatibilitas database/sql

# lib/pq — driver klasik, masih banyak dipakai
go get github.com/lib/pq

Koneksi ke 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, Direkomendasikan) #

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("buat pool: %w", err)
    }

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

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

PostgreSQL menggunakan $1, $2, … sebagai placeholder:

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

// Named parameter tidak didukung natively di database/sql
// tapi pgx mendukung @name jika menggunakan pgx secara langsung
rows, err := db.QueryContext(ctx,
    "SELECT id, name FROM products WHERE category = $1 AND price <= $2",
    "elektronik", 5_000_000)

CRUD Lengkap #

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

// INSERT dengan RETURNING — PostgreSQL idiom untuk mendapat ID baru
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 satu baris
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 banyak baris
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 punya sintaks ON CONFLICT yang sangat ekspresif:

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 jika belum ada
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
}

Fitur PostgreSQL-Spesifik #

Array #

import "github.com/lib/pq"

// Kolom tags TEXT[]
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),  // konversi []string ke 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 ke []string
    return &a, err
}

// Query dengan 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{}

// Simpan 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
}

// Baca 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 field
rows, _ := db.QueryContext(ctx, `
    SELECT name, metadata->>'color' AS color
    FROM products
    WHERE metadata @> '{"brand": "Apple"}'::jsonb
`)

COPY — Bulk Insert Tercepat #

COPY adalah cara tercepat memasukkan data besar ke PostgreSQL — jauh lebih cepat dari INSERT biasa:

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

func bulkInsert(ctx context.Context, pool *pgxpool.Pool, products []Product) error {
    // pgx CopyFrom — sangat cepat untuk bulk insert
    _, 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 mendukung pub/sub built-in 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 ke channel
    if _, err := conn.Exec(ctx, "LISTEN product_changes"); err != nil {
        log.Fatal(err)
    }

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

// Trigger untuk otomatis NOTIFY saat ada perubahan:
// 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();

Transaksi dengan Isolation Level #

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

    // SELECT FOR UPDATE — lock baris agar tidak ada concurrent update
    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("stok tidak mencukupi")
    }

    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()
}

Contoh Program Lengkap #

package main

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

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

var ErrNotFound = errors.New("data tidak ditemukan")

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:password@localhost:5432/tokoonline?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("Koneksi PostgreSQL gagal:", err)
    }
    fmt.Println("✓ Terhubung ke PostgreSQL")

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

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

    // Upsert — insert atau update jika sudah ada
    fmt.Println("\n=== Upsert Produk ===")
    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("Gagal upsert %s: %v", p.Name, err)
        } else {
            fmt.Printf("  Upsert: %s\n", p.Name)
        }
    }

    // Create
    fmt.Println("\n=== Insert Baru ===")
    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("  Dibuat dengan ID: %d\n", id)
    }

    // List
    fmt.Println("\n=== Semua Produk ===")
    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("  Ditemukan: %s (dibuat: %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 berhasil dihapus\n", id)
        }
    }
}

Ringkasan #

pgx adalah driver PostgreSQL terbaik untuk Go — lebih performant dari lib/pq dan mendukung fitur PostgreSQL-spesifik.
Placeholder $1, $2, … — berbeda dari MySQL (?) dan Oracle (:1).
RETURNING id setelah INSERT untuk mendapatkan ID baru — lebih idiomatik dari LastInsertId().
ON CONFLICT untuk UPSERT yang ekspresif — DO UPDATE SET atau DO NOTHING.
pq.Array() untuk kolom PostgreSQL array (TEXT[], INT[], dll).
JSONB untuk data semi-terstruktur — query dengan operator @>, ->, ->>
COPY via pgxpool.CopyFrom untuk bulk insert — 10-100x lebih cepat dari INSERT loop.
LISTEN/NOTIFY untuk real-time events dari database — alternatif message broker yang sederhana.
SELECT FOR UPDATE untuk lock baris dalam transaksi — mencegah concurrent update.
BIGSERIAL untuk auto-increment ID; TIMESTAMPTZ untuk timestamp dengan timezone.

← Sebelumnya: Oracle Berikutnya: GORM →