Databases

The database/sql package in Go provides a generic interface around SQL (or SQL-like) databases. It allows you to interact with various SQL databases using the same functions, regardless of the specific database backend.

sql.Open

The sql.Open function is used to open a database specified by its driver name and a driver-specific data source name. This function does not generate an error in the event of an incorrect password, driver, etc. Use db.Ping to check if the connection is successful.

sql.Prepare

The db.Prepare function is used to create a prepared statement for later queries or executions. Prepared statements are advantageous when you need to execute the same or similar SQL statements repeatedly with high efficiency.

QueryRow

The QueryRow method is used to execute a query that is expected to return at most one row.

Scan

The Scan method is then used to read the values from the current row into variables. This method expects a list of destination variables as arguments, which it fills with the values from the columns in the result set.

Code example

Example

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
    "github.com/google/uuid"
)

type Product struct {
    ID    string
    Name  string
    Price float64
}

func NewProduct(name string, price float64) *Product {
    return &Product{
        ID:    uuid.New().String(),
        Name:  name,
        Price: price,
    }
}

func insertProduct(db *sql.DB, product Product) error {
    stmt, err := db.Prepare("INSERT INTO products (id, name, price) VALUES (?, ?, ?)")
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec(product.ID, product.Name, product.Price)
    if err != nil {
        return err
    }
    return nil
}

func updateProduct(db *sql.DB, product *Product) error {
    stmt, err := db.Prepare("UPDATE products SET name = ?, price = ? WHERE id = ?")
    if err != nil {
        return err
    }
    defer stmt.Close()
    err = db.Ping()
    if err != nil {
        panic(err.Error())
    }

    _, err = stmt.Exec(product.Name, product.Price, product.ID)
    if err != nil {
        return err
    }
    return nil
}

func selectProduct(db *sql.DB, id string) (*Product, error) {
    stmt, err := db.Prepare("SELECT * FROM products WHERE id = ?")
    if err != nil {
        return nil, err
    }
    defer stmt.Close()
    var product Product
    err = stmt.QueryRow(id).Scan(&product.ID, &product.Name, &product.Price)
    if err != nil {
        return nil, err
    }
    return &product, nil
}

func selectAllProducts(db *sql.DB) ([]Product, error) {
    rows, err := db.Query("SELECT * FROM products")
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var products []Product
    var p Product
    for rows.Next() {
        err = rows.Scan(&p.ID, &p.Name, &p.Price)
        if err != nil {
            return nil, err
        }
        products = append(products, p)
    }
    return products, nil
}

func deleteProduct(db *sql.DB, id string) error {
    stmt, err := db.Prepare("DELETE FROM products WHERE id = ?")
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec(id)
    if err != nil {
        return err
    }
    return nil
}

func main() {

    db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/goexpert")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    product := NewProduct("Car", 7.35)
    err = insertProduct(db, *product)
    if err != nil {
        panic(err)
    }
    product = NewProduct("Notebook", 7.35)
    err = insertProduct(db, *product)
    if err != nil {
        panic(err)
    }
    product.Price = 1900.00
    err = updateProduct(db, product)
    if err != nil {
        panic(err)
    }

    db_product, err := selectProduct(db, product.ID)
    if err != nil {
        panic(err)
    }
    fmt.Println(db_product)
    fmt.Println("********")
    deleteProduct(db, product.ID)
    all_products, err := selectAllProducts(db)
    if err != nil {
        panic(err)
    }
    for _, p := range all_products {
        fmt.Println(p)
    }
}