MSSQL #

Microsoft SQL Server (MSSQL) adalah sistem manajemen database relasional yang banyak digunakan di lingkungan enterprise, terutama yang berbasis ekosistem Microsoft. Go mendukung SQL Server melalui driver github.com/microsoft/go-mssqldb — pengganti modern dari denisenkom/go-mssqldb yang sudah tidak dikelola. Seperti semua driver Go, ia bekerja di atas abstraksi database/sql sehingga sebagian besar kode mirip dengan driver lain.

Instalasi #

go get github.com/microsoft/go-mssqldb

Koneksi ke SQL Server #

SQL Server mendukung beberapa metode koneksi — DSN URL style maupun connection string klasik:

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/microsoft/go-mssqldb"
)

func main() {
    // Format URL (direkomendasikan)
    // sqlserver://user:password@host:port?database=dbname&param=value
    dsn := "sqlserver://sa:StrongPass!23@localhost:1433?database=tokoonline"

    // Dengan Windows Authentication (hanya di Windows)
    // dsn := "sqlserver://localhost?database=tokoonline&integrated+security=true"

    // Format connection string klasik
    // dsn := "server=localhost;user id=sa;password=StrongPass!23;database=tokoonline"

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

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

    if err := db.Ping(); err != nil {
        log.Fatal("Ping gagal:", err)
    }
    fmt.Println("Terhubung ke SQL Server!")
}

Parameter Koneksi Penting #

database          → nama database
encrypt           → true/false/disable — enkripsi koneksi (TLS)
trustservercert   → true → percaya sertifikat server (untuk dev)
connection timeout → timeout koneksi (detik)
dial timeout      → timeout TCP dial (detik)
keepalive         → keepalive interval (detik)
app name          → nama aplikasi (tampil di sys.processes)

Placeholder — `@p1` bukan `?` #

SQL Server menggunakan placeholder berbeda dari MySQL/PostgreSQL:

// MySQL/PostgreSQL: gunakan ?
// SQL Server: gunakan @p1, @p2, @p3, ... (positional)
// ATAU named: @paramName

// Positional (urut)
row := db.QueryRowContext(ctx,
    "SELECT id, name FROM products WHERE id = @p1", id)

// Named parameters — lebih ekspresif untuk query panjang
row = db.QueryRowContext(ctx,
    "SELECT id, name FROM products WHERE category = @cat AND price <= @maxPrice",
    sql.Named("cat", "elektronik"),
    sql.Named("maxPrice", 5_000_000),
)

CRUD dengan SQL Server #

Query #

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

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 = @p1
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)

    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, fmt.Errorf("get product: %w", err)
    }
    return &p, nil
}

func listProducts(ctx context.Context, db *sql.DB) ([]*Product, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products
        ORDER BY name
    `)
    if err != nil {
        return nil, fmt.Errorf("list products: %w", err)
    }
    defer rows.Close()

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

INSERT dengan OUTPUT — Ambil ID Tanpa LastInsertId #

SQL Server tidak mendukung LastInsertId() — gunakan klausa OUTPUT untuk mendapatkan ID baru:

func createProduct(ctx context.Context, db *sql.DB, p *Product) (int64, error) {
    var newID int64

    // OUTPUT INSERTED.id mengembalikan nilai kolom setelah INSERT
    err := db.QueryRowContext(ctx, `
        INSERT INTO products (name, price, stock, category, created_at)
        OUTPUT INSERTED.id
        VALUES (@p1, @p2, @p3, @p4, GETDATE())
    `, p.Name, p.Price, p.Stock, p.Category).Scan(&newID)

    if err != nil {
        return 0, fmt.Errorf("create product: %w", err)
    }
    return newID, nil
}

UPDATE dan DELETE #

func updateProduct(ctx context.Context, db *sql.DB, p *Product) error {
    result, err := db.ExecContext(ctx, `
        UPDATE products
        SET name = @p1, price = @p2, stock = @p3, category = @p4
        WHERE id = @p5
    `, p.Name, p.Price, p.Stock, p.Category, p.ID)
    if err != nil {
        return fmt.Errorf("update product: %w", err)
    }
    if n, _ := result.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

func deleteProduct(ctx context.Context, db *sql.DB, id int) error {
    result, err := db.ExecContext(ctx,
        "DELETE FROM products WHERE id = @p1", id)
    if err != nil {
        return fmt.Errorf("delete product: %w", err)
    }
    if n, _ := result.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

Stored Procedure #

SQL Server sangat umum menggunakan stored procedure. Cara memanggilnya:

// Stored procedure tanpa output parameter
func callSP(ctx context.Context, db *sql.DB, categoryID int) error {
    _, err := db.ExecContext(ctx,
        "EXEC sp_archive_products @categoryId = @p1, @olderThanDays = @p2",
        categoryID, 365,
    )
    return err
}

// Stored procedure dengan output parameter
func getSalesTotal(ctx context.Context, db *sql.DB, month, year int) (float64, error) {
    var total float64
    // Output parameter via DECLARE dan SELECT
    err := db.QueryRowContext(ctx, `
        DECLARE @total DECIMAL(18,2);
        EXEC sp_get_sales_total
            @month = @p1,
            @year  = @p2,
            @total = @total OUTPUT;
        SELECT @total;
    `, month, year).Scan(&total)

    if err != nil {
        return 0, fmt.Errorf("get sales total: %w", err)
    }
    return total, nil
}

Pagination dengan OFFSET-FETCH #

SQL Server menggunakan sintaks OFFSET ... FETCH NEXT ... ROWS ONLY (bukan LIMIT):

type PageParams struct {
    Page    int
    PerPage int
}

func listProductsPaged(ctx context.Context, db *sql.DB, params PageParams) ([]*Product, int, error) {
    offset := (params.Page - 1) * params.PerPage

    // Hitung total
    var total int
    if err := db.QueryRowContext(ctx,
        "SELECT COUNT(*) FROM products").Scan(&total); err != nil {
        return nil, 0, err
    }

    // Query dengan pagination
    rows, err := db.QueryContext(ctx, `
        SELECT id, name, price, stock, category
        FROM products
        ORDER BY id
        OFFSET @p1 ROWS
        FETCH NEXT @p2 ROWS ONLY
    `, offset, params.PerPage)
    if err != nil {
        return nil, 0, err
    }
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price,
            &p.Stock, &p.Category); err != nil {
            return nil, 0, err
        }
        products = append(products, &p)
    }
    return products, total, rows.Err()
}

Transaksi dengan Savepoint #

func complexTransaction(ctx context.Context, db *sql.DB) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
    })
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Operasi pertama
    if _, err := tx.ExecContext(ctx,
        "INSERT INTO audit_log (event) VALUES (@p1)", "start"); err != nil {
        return err
    }

    // Savepoint — bisa rollback parsial ke titik ini
    if _, err := tx.ExecContext(ctx, "SAVE TRANSACTION sp1"); err != nil {
        return err
    }

    // Operasi yang mungkin gagal
    _, err = tx.ExecContext(ctx,
        "UPDATE products SET stock = stock - @p1 WHERE id = @p2", 5, 999)
    if err != nil {
        // Rollback hanya ke savepoint, tidak ke awal transaksi
        tx.ExecContext(ctx, "ROLLBACK TRANSACTION sp1")
        // Lanjutkan dengan strategi alternatif
    }

    // Commit keseluruhan transaksi
    return tx.Commit()
}

SQL Server-Specific Features #

Bulk Insert dengan `MERGE` #

// UPSERT (INSERT or UPDATE) dengan MERGE
func upsertProduct(ctx context.Context, db *sql.DB, p *Product) error {
    _, err := db.ExecContext(ctx, `
        MERGE products AS target
        USING (SELECT @p1 AS name, @p2 AS price, @p3 AS stock, @p4 AS category) AS source
            ON target.name = source.name
        WHEN MATCHED THEN
            UPDATE SET price = source.price, stock = source.stock
        WHEN NOT MATCHED THEN
            INSERT (name, price, stock, category, created_at)
            VALUES (source.name, source.price, source.stock, source.category, GETDATE());
    `, p.Name, p.Price, p.Stock, p.Category)
    return err
}

Contoh Program Lengkap #

package main

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

    _ "github.com/microsoft/go-mssqldb"
)

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)
        OUTPUT INSERTED.id
        VALUES (@p1, @p2, @p3, @p4, GETDATE())
    `, 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 = @p1
    `, 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) FindByCategory(ctx context.Context, cat string) ([]*Product, error) {
    rows, err := r.db.QueryContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE category = @p1 ORDER BY name
    `, cat)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

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

func (r *ProductRepo) Update(ctx context.Context, p *Product) error {
    res, err := r.db.ExecContext(ctx, `
        UPDATE products SET name=@p1, price=@p2, stock=@p3, category=@p4
        WHERE id=@p5
    `, 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
}

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

const ddl = `
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='products' AND xtype='U')
CREATE TABLE products (
    id         INT IDENTITY(1,1) PRIMARY KEY,
    name       NVARCHAR(200) NOT NULL,
    price      DECIMAL(15,2) NOT NULL DEFAULT 0,
    stock      INT NOT NULL DEFAULT 0,
    category   NVARCHAR(100) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT GETDATE()
);`

func main() {
    dsn := "sqlserver://sa:StrongPass!23@localhost:1433?database=tokoonline&trustservercert=true"

    db, err := sql.Open("sqlserver", 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 gagal:", err)
    }
    fmt.Println("✓ Terhubung ke SQL Server")

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

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

    // Insert
    fmt.Println("\n=== Insert ===")
    seeds := []Product{
        {Name: "Surface Pro", Price: 20_000_000, Stock: 5, Category: "elektronik"},
        {Name: "Xbox Controller", Price: 1_200_000, Stock: 30, Category: "gaming"},
        {Name: "Azure Dev License", Price: 500_000, Stock: 999, Category: "software"},
    }
    var ids []int64
    for _, p := range seeds {
        p := p
        id, err := repo.Create(ctx, &p)
        if err != nil {
            log.Printf("Gagal insert %s: %v", p.Name, err)
            continue
        }
        ids = append(ids, id)
        fmt.Printf("  [%d] %s\n", id, p.Name)
    }

    // FindByID
    if len(ids) > 0 {
        fmt.Println("\n=== FindByID ===")
        p, err := repo.FindByID(ctx, int(ids[0]))
        if err != nil {
            log.Println(err)
        } else {
            fmt.Printf("  %s — Rp%.0f (stok: %d)\n", p.Name, p.Price, p.Stock)
        }
    }

    // FindByCategory
    fmt.Println("\n=== FindByCategory: elektronik ===")
    elekt, _ := repo.FindByCategory(ctx, "elektronik")
    for _, p := range elekt {
        fmt.Printf("  [%d] %s — Rp%.0f\n", p.ID, p.Name, p.Price)
    }

    // Update
    if len(ids) > 0 {
        fmt.Println("\n=== Update ===")
        err := repo.Update(ctx, &Product{
            ID: int(ids[0]), Name: "Surface Pro 11",
            Price: 22_000_000, Stock: 3, Category: "elektronik",
        })
        if err != nil {
            log.Println(err)
        } else {
            fmt.Println("  Update berhasil")
        }
    }

    // Pagination
    fmt.Println("\n=== Pagination (halaman 1, 2 per halaman) ===")
    products, total, err := listProductsPaged(ctx, db, PageParams{Page: 1, PerPage: 2})
    if err != nil {
        log.Println(err)
    } else {
        fmt.Printf("  Total: %d produk\n", total)
        for _, p := range products {
            fmt.Printf("  [%d] %s\n", p.ID, p.Name)
        }
    }
}

Ringkasan #

Driver github.com/microsoft/go-mssqldb — pengganti modern dari denisenkom/go-mssqldb.
Placeholder @p1, @p2, … atau named sql.Named("name", val) — bukan ? atau :1.
OUTPUT INSERTED.id untuk mendapatkan ID setelah INSERT — tidak ada LastInsertId().
OFFSET ... FETCH NEXT ... ROWS ONLY untuk pagination — bukan LIMIT/OFFSET.
Stored procedure dipanggil dengan EXEC sp_name @param = @p1.
MERGE untuk UPSERT yang powerful — insert atau update berdasarkan kondisi.
SAVE TRANSACTION untuk savepoint — rollback parsial dalam transaksi.
defer tx.Rollback() + tx.Commit() — pola transaksi yang aman.
defer rows.Close() wajib setelah QueryContext — kembalikan koneksi ke pool.
sql.ErrNoRows untuk mendeteksi baris tidak ditemukan.

← Sebelumnya: MySQL Berikutnya: Oracle →