Working with Databases in Go

# CHAPTER 24

Working with Databases in Go #

1. Introduction #

2. Learning Objectives #

• Download and import a database Driver.

• Connect to a MySQL database safely.

• Execute basic CRUD operations (Insert, Update, Delete).

• Query data and map rows into Go Structs.

• Prevent SQL Injection.

3. Installing the Driver #

To connect to MySQL, run this in your terminal:

go get -u github.com/go-sql-driver/mysql

4. Connecting to the Database #

package main

import (
    "database/sql"
    "fmt"
    "log"
    // The underscore imports the driver silently so database/sql can use it!
    _ "github.com/go-sql-driver/mysql" 
)

func main() {
    // Format: username:password@tcp(127.0.0.1:3306)/dbname
    dsn := "root:password123@tcp(127.0.0.1:3306)/school_db"

    // sql.Open establishes the configuration (it doesn't actually connect yet)
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close() // Always clean up!

    // db.Ping() actually tests the connection to the server
    err = db.Ping()
    if err != nil {
        log.Fatal("Cannot connect to Database:", err)
    }

    fmt.Println("Successfully connected to MySQL!")
}

5. Executing Queries (Insert / Update / Delete) #

CRITICAL RULE: Prevent SQL Injection! Never concatenate user input directly into an SQL string ("INSERT INTO users VALUES (" + name + ")"). Hackers will destroy your database. Instead, use ? placeholders. Go will safely sanitize the data.

func createUser(db *sql.DB, name string, age int) {
    // The ? symbols are safe placeholders
    query := "INSERT INTO students (name, age) VALUES (?, ?)"
    
    // Exec safely binds the variables to the ? placeholders
    result, err := db.Exec(query, name, age)
    
    if err != nil {
        log.Println("Error inserting:", err)
        return
    }

    // Get the generated ID
    id, _ := result.LastInsertId()
    fmt.Println("Created new student with ID:", id)
}

6. Reading Data (db.Query) #

type Student struct {
    ID   int
    Name string
    Age  int
}

func getStudents(db *sql.DB) {
    // 1. Execute Query
    rows, err := db.Query("SELECT id, name, age FROM students")
    if err != nil {
        log.Fatal(err)
    }
    // CRITICAL: Prevent memory leaks by closing rows when done
    defer rows.Close()

    var students []Student

    // 2. Iterate through results
    for rows.Next() {
        var s Student
        // 3. Scan the current row's columns into the Struct's memory addresses
        err := rows.Scan(&s.ID, &s.Name, &s.Age)
        if err != nil {
            log.Fatal(err)
        }
        students = append(students, s)
    }

    fmt.Println("Fetched Students:", students)
}

7. Reading a Single Row (db.QueryRow) #

func getSingleStudent(db *sql.DB, id int) {
    var name string
    // QueryRow only expects 1 result. 
    err := db.QueryRow("SELECT name FROM students WHERE id = ?", id).Scan(&name)
    
    // Check if no rows were found
    if err == sql.ErrNoRows {
        fmt.Println("Student not found.")
        return
    }
    
    fmt.Println("Found:", name)
}

8. Common Mistakes #

• Forgetting defer rows.Close(): When you query multiple rows, the database connection is held hostage until you finish iterating. If an error occurs inside your loop and the function returns early without closing the rows, your database will run out of connection limits and crash.

• Forgetting the Underscore Import: If you forget "github.com/go-sql-driver/mysql", the program will panic with sql: unknown driver "mysql".

1. 3. Create a table using db.Exec("CREATE TABLE IF NOT EXISTS test (id INT AUTOINCREMENT PRIMARY KEY, msg VARCHAR(50))").

11. MCQs with Answers & Explanations #

12. Interview Preparation #

1. 1. Explain how Connection Pooling works natively in Go's database/sql package.

1. 2. What is the difference between db.Query and db.Exec?

1. 3. How does Go mitigate SQL injection attacks?

13. Summary #

14. Next Chapter Recommendation #