MSSQL #

Microsoft SQL Server (MSSQL) is a relational database management system widely used in enterprise environments, especially those built on the Microsoft ecosystem. Go supports SQL Server through the github.com/microsoft/go-mssqldb driver — the modern replacement for denisenkom/go-mssqldb, which is no longer maintained. Like all Go drivers, it works on top of the database/sql abstraction, so most of the code is similar to other drivers.

Installation #

go get github.com/microsoft/go-mssqldb

Connecting to SQL Server #

SQL Server supports several connection methods — URL-style DSNs and classic connection strings:

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

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

    // With Windows Authentication (Windows only)
    // dsn := "sqlserver://localhost?database=onlinestore&integrated+security=true"

    // Classic connection string format
    // dsn := "server=localhost;user id=sa;password=StrongPass!23;database=onlinestore"

    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 failed:", err)
    }
    fmt.Println("Connected to SQL Server!")
}

The TDS (Tabular Data Stream) Protocol Handshake Cycle #

The go-mssqldb driver communicates with Microsoft SQL Server using the TDS protocol at the application layer on top of TCP:

flowchart TD
    Client["Go App (go-mssqldb)"] -->|"1. TCP Connect (Port 1433)"| Server["SQL Server Listening"]
    Client -->|"2. TDS Pre-Login Packet (TLS Negotiation)"| Server
    Server -->|"3. TDS Pre-Login Response"| Client
    Client -->|"4. TDS Login7 Packet (User/Password & DB)"| Server
    Server -->|"5. TDS Login Response (Confirmation)"| Client
    Client -.->|"6. Send SQL Query (TDS Packet)"| Server

Important Connection Parameters #

database          → the database name
encrypt           → true/false/disable — connection encryption (TLS)
trustservercert   → true → trust the server certificate (for development)
connection timeout → connection timeout (seconds)
dial timeout      → TCP dial timeout (seconds)
keepalive         → keepalive interval (seconds)
app name          → the application name (shows in sys.processes)

Placeholders — `@p1` instead of `?` #

SQL Server uses different placeholders from MySQL/PostgreSQL:

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

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

// Named parameters — more expressive for long queries
row = db.QueryRowContext(ctx,
    "SELECT id, name FROM products WHERE category = @cat AND price <= @maxPrice",
    sql.Named("cat", "electronics"),
    sql.Named("maxPrice", 5_000_000),
)

CRUD with 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 with OUTPUT — Getting the ID Without LastInsertId #

SQL Server doesn’t support LastInsertId() — use the OUTPUT clause to get the new ID:

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

    // OUTPUT INSERTED.id returns the column value after the 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 and 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 Procedures #

SQL Server very commonly uses stored procedures. How to call them:

// Stored procedure without output parameters
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 with an output parameter
func getSalesTotal(ctx context.Context, db *sql.DB, month, year int) (float64, error) {
    var total float64
    // Output parameter via DECLARE and 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 with OFFSET-FETCH #

SQL Server uses the OFFSET ... FETCH NEXT ... ROWS ONLY syntax (not 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

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

    // Query with 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()
}

Transactions with Savepoints #

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

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

    // Savepoint — allows a partial rollback to this point
    if _, err := tx.ExecContext(ctx, "SAVE TRANSACTION sp1"); err != nil {
        return err
    }

    // An operation that might fail
    _, err = tx.ExecContext(ctx,
        "UPDATE products SET stock = stock - @p1 WHERE id = @p2", 5, 999)
    if err != nil {
        // Roll back only to the savepoint, not the whole transaction
        tx.ExecContext(ctx, "ROLLBACK TRANSACTION sp1")
        // Continue with an alternative strategy
    }

    // Commit the entire transaction
    return tx.Commit()
}

SQL Server-Specific Features #

Bulk Inserts with `MERGE` #

// UPSERT (INSERT or UPDATE) with 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
}

Complete Example Program #

package main

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

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

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)
        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=onlinestore&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("Connection failed:", err)
    }
    fmt.Println("✓ Connected to 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: "electronics"},
        {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("Failed to 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 (stock: %d)\n", p.Name, p.Price, p.Stock)
        }
    }

    // FindByCategory
    fmt.Println("\n=== FindByCategory: electronics ===")
    electronics, _ := repo.FindByCategory(ctx, "electronics")
    for _, p := range electronics {
        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: "electronics",
        })
        if err != nil {
            log.Println(err)
        } else {
            fmt.Println("  Update successful")
        }
    }

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

Summary #

The github.com/microsoft/go-mssqldb driver — the modern replacement for denisenkom/go-mssqldb.
Placeholders @p1, @p2, … or named sql.Named("name", val) — not ? or :1.
OUTPUT INSERTED.id to get the ID after an INSERT — there’s no LastInsertId().
OFFSET ... FETCH NEXT ... ROWS ONLY for pagination — not LIMIT/OFFSET.
Stored procedures are called with EXEC sp_name @param = @p1.
MERGE for powerful UPSERTs — insert or update based on a condition.
SAVE TRANSACTION for savepoints — partial rollback within a transaction.
defer tx.Rollback() + tx.Commit() — the safe transaction pattern.
defer rows.Close() is mandatory after QueryContext — returns the connection to the pool.
sql.ErrNoRows detects a missing row.

← Previous: MySQL Next: Oracle →