tessl/golang-github-com-shopspring--decimal

Arbitrary-precision fixed-point decimal numbers for Go, avoiding floating-point precision issues with support for arithmetic, rounding, serialization, and database integration.

Overview

Eval results

Files

Database Integration Guide

Name: tessl/golang-github-com-shopspring--decimal
Author: tessl

Complete guide to using Decimal with database/sql and handling NULL values.

Quick Start

Basic Usage

import (
    "database/sql"
    "github.com/shopspring/decimal"
    _ "github.com/lib/pq"  // or your database driver
)

type Product struct {
    ID    int
    Price decimal.Decimal
}

// Query
var p Product
err := db.QueryRow("SELECT id, price FROM products WHERE id = $1", 1).
    Scan(&p.ID, &p.Price)

// Insert
_, err = db.Exec("INSERT INTO products (price) VALUES ($1)", p.Price)

With NULL Values

type Product struct {
    ID       int
    Price    decimal.Decimal
    Discount decimal.NullDecimal  // Can be NULL
}

// Query with NULL handling
var p Product
err := db.QueryRow("SELECT id, price, discount FROM products WHERE id = $1", 1).
    Scan(&p.ID, &p.Price, &p.Discount)

if p.Discount.Valid {
    fmt.Println("Discount:", p.Discount.Decimal)
} else {
    fmt.Println("No discount")
}

// Insert NULL
var discount decimal.NullDecimal  // Valid=false means NULL
_, err = db.Exec("INSERT INTO products (discount) VALUES ($1)", discount)

Storage in Database

Decimal Storage

Decimal values are stored as strings in the database via the driver.Value interface.

Why strings?

Preserves exact precision
Avoids floating-point errors
Works with any database type (DECIMAL, NUMERIC, VARCHAR, TEXT)

Recommended database types:

PostgreSQL: NUMERIC, DECIMAL
MySQL: DECIMAL, NUMERIC
SQLite: TEXT, REAL
SQL Server: DECIMAL, NUMERIC

Schema Examples

-- PostgreSQL / MySQL
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2) NOT NULL,        -- Non-nullable
    discount NUMERIC(10, 2),               -- Nullable
    cost NUMERIC(10, 4)                    -- Higher precision
);

-- SQLite
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price TEXT NOT NULL,                   -- Store as string
    discount TEXT,                         -- Nullable string
    cost TEXT
);

Interface Implementation

Decimal Interfaces

// Scan implements sql.Scanner for reading from database
func (d *Decimal) Scan(value interface{}) error

// Value implements driver.Valuer for writing to database
func (d Decimal) Value() (driver.Value, error)

Accepted types in Scan:

string - Most common, preserves precision
[]byte - String as bytes
int64 - Integer values
float64 - Float values (may lose precision)

Value returns:

string - Decimal formatted as string

NullDecimal Interfaces

type NullDecimal struct {
    Decimal Decimal
    Valid   bool  // true if not NULL
}

// Scan implements sql.Scanner
func (d *NullDecimal) Scan(value interface{}) error

// Value implements driver.Valuer
func (d NullDecimal) Value() (driver.Value, error)

Value returns:

nil - when Valid=false (SQL NULL)
string - when Valid=true (decimal value)

Complete Examples

Single Row Query

func getProduct(db *sql.DB, id int) (*Product, error) {
    var p Product
    err := db.QueryRow(`
        SELECT id, name, price, discount
        FROM products
        WHERE id = $1
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Discount)

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

    return &p, nil
}

Multiple Rows Query

func listProducts(db *sql.DB) ([]Product, error) {
    rows, err := db.Query(`
        SELECT id, name, price, discount
        FROM products
        ORDER BY name
    `)
    if err != nil {
        return nil, fmt.Errorf("query 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.Discount); err != nil {
            return nil, fmt.Errorf("scan product: %w", err)
        }
        products = append(products, p)
    }

    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("rows error: %w", err)
    }

    return products, nil
}

Insert

func createProduct(db *sql.DB, p *Product) error {
    result, err := db.Exec(`
        INSERT INTO products (name, price, discount)
        VALUES ($1, $2, $3)
    `, p.Name, p.Price, p.Discount)

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

    id, err := result.LastInsertId()
    if err != nil {
        return fmt.Errorf("get last insert id: %w", err)
    }

    p.ID = int(id)
    return nil
}

Update

func updatePrice(db *sql.DB, id int, newPrice decimal.Decimal) error {
    result, err := db.Exec(`
        UPDATE products
        SET price = $1, updated_at = NOW()
        WHERE id = $2
    `, newPrice, id)

    if err != nil {
        return fmt.Errorf("update price: %w", err)
    }

    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return fmt.Errorf("get rows affected: %w", err)
    }

    if rowsAffected == 0 {
        return fmt.Errorf("product not found")
    }

    return nil
}

Delete

func deleteProduct(db *sql.DB, id int) error {
    _, err := db.Exec(`
        DELETE FROM products WHERE id = $1
    `, id)

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

    return nil
}

NULL Value Handling

Using NullDecimal

type Order struct {
    ID           int
    Total        decimal.Decimal
    Discount     decimal.NullDecimal  // May be NULL
    ShippingCost decimal.NullDecimal  // May be NULL
}

func calculateFinalTotal(o *Order) decimal.Decimal {
    total := o.Total

    // Apply discount if present
    if o.Discount.Valid {
        total = total.Sub(o.Discount.Decimal)
    }

    // Add shipping if present
    if o.ShippingCost.Valid {
        total = total.Add(o.ShippingCost.Decimal)
    }

    return total
}

Creating NullDecimal Values

// Valid value
discount := decimal.NewNullDecimal(decimal.NewFromString("10.00"))
// discount.Valid = true, discount.Decimal = 10.00

// NULL value (zero value)
var nullDiscount decimal.NullDecimal
// nullDiscount.Valid = false (represents NULL)

// NULL value (explicit)
nullDiscount = decimal.NullDecimal{
    Decimal: decimal.Zero,
    Valid:   false,
}

// Conditional NULL
var maybeDiscount decimal.NullDecimal
if hasDiscount {
    maybeDiscount = decimal.NewNullDecimal(discountAmount)
} else {
    maybeDiscount = decimal.NullDecimal{Valid: false}
}

Inserting NULL

// Insert with NULL discount
product := &Product{
    Name:     "Widget",
    Price:    decimal.NewFromString("99.99"),
    Discount: decimal.NullDecimal{Valid: false},  // NULL
}

db.Exec(`
    INSERT INTO products (name, price, discount)
    VALUES ($1, $2, $3)
`, product.Name, product.Price, product.Discount)
// discount column will be NULL

Querying NULL

// Query that may return NULL
var discount decimal.NullDecimal
err := db.QueryRow(`
    SELECT discount FROM products WHERE id = $1
`, id).Scan(&discount)

if err != nil {
    return err
}

if discount.Valid {
    fmt.Printf("Discount: %s\n", discount.Decimal.StringFixed(2))
} else {
    fmt.Println("No discount")
}

Transactions

Basic Transaction

func transferFunds(db *sql.DB, fromID, toID int, amount decimal.Decimal) error {
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("begin transaction: %w", err)
    }
    defer tx.Rollback()  // Rollback if not committed

    // Deduct from source
    _, err = tx.Exec(`
        UPDATE accounts
        SET balance = balance - $1
        WHERE id = $2 AND balance >= $1
    `, amount, fromID)
    if err != nil {
        return fmt.Errorf("deduct from source: %w", err)
    }

    // Add to destination
    _, err = tx.Exec(`
        UPDATE accounts
        SET balance = balance + $1
        WHERE id = $2
    `, amount, toID)
    if err != nil {
        return fmt.Errorf("add to destination: %w", err)
    }

    // Commit transaction
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit transaction: %w", err)
    }

    return nil
}

Transaction with Context

func updatePrices(ctx context.Context, db *sql.DB, updates []PriceUpdate) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    stmt, err := tx.PrepareContext(ctx, `
        UPDATE products SET price = $1 WHERE id = $2
    `)
    if err != nil {
        return err
    }
    defer stmt.Close()

    for _, u := range updates {
        _, err := stmt.ExecContext(ctx, u.Price, u.ID)
        if err != nil {
            return err
        }
    }

    return tx.Commit()
}

Prepared Statements

func batchInsert(db *sql.DB, products []Product) error {
    stmt, err := db.Prepare(`
        INSERT INTO products (name, price, discount)
        VALUES ($1, $2, $3)
    `)
    if err != nil {
        return err
    }
    defer stmt.Close()

    for _, p := range products {
        _, err := stmt.Exec(p.Name, p.Price, p.Discount)
        if err != nil {
            return fmt.Errorf("insert product %s: %w", p.Name, err)
        }
    }

    return nil
}

Aggregations in Database

SUM, AVG, etc.

func getTotalRevenue(db *sql.DB) (decimal.Decimal, error) {
    var total decimal.NullDecimal  // May be NULL if no rows

    err := db.QueryRow(`
        SELECT SUM(price * quantity) FROM orders
    `).Scan(&total)

    if err != nil {
        return decimal.Zero, err
    }

    if !total.Valid {
        return decimal.Zero, nil  // No orders
    }

    return total.Decimal, nil
}

func getAveragePrice(db *sql.DB) (decimal.Decimal, error) {
    var avg decimal.NullDecimal

    err := db.QueryRow(`
        SELECT AVG(price) FROM products
    `).Scan(&avg)

    if err != nil {
        return decimal.Zero, err
    }

    if !avg.Valid {
        return decimal.Zero, nil  // No products
    }

    return avg.Decimal, nil
}

Common Patterns

Price History

type PriceHistory struct {
    ProductID int
    Price     decimal.Decimal
    ChangedAt time.Time
}

func recordPriceChange(db *sql.DB, productID int, newPrice decimal.Decimal) error {
    // Insert into history
    _, err := db.Exec(`
        INSERT INTO price_history (product_id, price, changed_at)
        VALUES ($1, $2, NOW())
    `, productID, newPrice)

    if err != nil {
        return err
    }

    // Update current price
    _, err = db.Exec(`
        UPDATE products SET price = $1 WHERE id = $2
    `, newPrice, productID)

    return err
}

Financial Reporting

type SalesReport struct {
    TotalSales    decimal.Decimal
    TotalDiscount decimal.Decimal
    NetRevenue    decimal.Decimal
    AvgOrderValue decimal.Decimal
}

func generateSalesReport(db *sql.DB, startDate, endDate time.Time) (*SalesReport, error) {
    var report SalesReport

    err := db.QueryRow(`
        SELECT
            COALESCE(SUM(total), 0) as total_sales,
            COALESCE(SUM(discount), 0) as total_discount,
            COALESCE(SUM(total - discount), 0) as net_revenue,
            COALESCE(AVG(total), 0) as avg_order_value
        FROM orders
        WHERE created_at BETWEEN $1 AND $2
    `, startDate, endDate).Scan(
        &report.TotalSales,
        &report.TotalDiscount,
        &report.NetRevenue,
        &report.AvgOrderValue,
    )

    if err != nil {
        return nil, err
    }

    return &report, nil
}

Inventory Valuation

func getInventoryValue(db *sql.DB) (decimal.Decimal, error) {
    var total decimal.Decimal

    rows, err := db.Query(`
        SELECT cost, quantity FROM inventory
    `)
    if err != nil {
        return decimal.Zero, err
    }
    defer rows.Close()

    total = decimal.Zero
    for rows.Next() {
        var cost decimal.Decimal
        var quantity int64

        if err := rows.Scan(&cost, &quantity); err != nil {
            return decimal.Zero, err
        }

        itemValue := cost.Mul(decimal.NewFromInt(quantity))
        total = total.Add(itemValue)
    }

    return total, rows.Err()
}

Database-Specific Notes

PostgreSQL

// Works well with NUMERIC/DECIMAL types
// Stored as string via driver

// Example schema
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2) NOT NULL,
    cost NUMERIC(10, 4)
);

// Usage is identical to examples above

MySQL

// Works with DECIMAL type
// Note: MySQL driver may return different types

// Example schema
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2) NOT NULL,
    cost DECIMAL(10, 4)
);

// Scan handles multiple types automatically

SQLite

// Best stored as TEXT for precision
// Can also use REAL but may lose precision

// Example schema
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    price TEXT NOT NULL,
    cost TEXT
);

// Usage is identical

Error Handling

Scan Errors

var price decimal.Decimal
err := db.QueryRow("SELECT price FROM products WHERE id = $1", id).
    Scan(&price)

if err == sql.ErrNoRows {
    return fmt.Errorf("product not found")
}
if err != nil {
    return fmt.Errorf("scan price: %w", err)
}

Invalid Decimal in Database

// If database contains invalid decimal string
var price decimal.Decimal
err := db.QueryRow("SELECT price FROM products WHERE id = $1", id).
    Scan(&price)

// Scan will return error if value cannot be parsed
if err != nil {
    return fmt.Errorf("invalid price in database: %w", err)
}

Performance Tips

Use prepared statements for batch operations:

stmt, _ := db.Prepare("INSERT INTO products (price) VALUES ($1)")
defer stmt.Close()

for _, price := range prices {
    stmt.Exec(price)
}

Do calculations in Go, not SQL:

// GOOD: Calculate in Go with full precision
var prices []decimal.Decimal
// ... query prices
total := decimal.Zero
for _, p := range prices {
    total = total.Add(p)
}

// AVOID: Database calculations may lose precision
db.QueryRow("SELECT SUM(price) FROM products")

Batch queries when possible:

// Query multiple rows at once
rows, _ := db.Query("SELECT price FROM products WHERE id = ANY($1)", ids)

Complete Serialization API → Complete NullDecimal API →

Install with Tessl CLI

npx tessl i tessl/golang-github-com-shopspring--decimal@1.4.1

docs

api

guides

basic-arithmetic.md

creating-decimals.md

database-integration.md

formatting-output.md

gotchas-and-best-practices.md