Exercise 4: Database Interaction in Go (PostgreSQL/MySQL)

Why Use a Database?

Databases allow you to store and manage information persistently, unlike in-memory variables.

PostgreSQL/MySQL Setup

Make sure you have PostgreSQL or MySQL installed and configured.

For PostgreSQL, use:

sudo apt update && sudo apt install postgresql

For MySQL, use:

sudo apt update && sudo apt install mysql-server

Connecting to a Database in Go

We use the `database/sql` package and a driver like `pq` for PostgreSQL or `mysql` for MySQL.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq" // PostgreSQL driver
)

const dsn = "user=postgres password=secret dbname=mydb sslmode=disable"

func main() {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()
    fmt.Println("Successfully connected to the database!")
}

Executing an SQL Query

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

func main() {
    db, _ := sql.Open("postgres", "user=postgres password=secret dbname=mydb sslmode=disable")
    defer db.Close()
    
    _, err := db.Exec("CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT)")
    if err != nil {
        panic(err)
    }
    fmt.Println("Table created successfully")
}

Exercise Instructions

• Create a file named database.go.
• Establish a connection to PostgreSQL or MySQL.
• Create a `users` table with `id` and `name`.
• Insert some users and retrieve their data.

Exercise Solution

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

func main() {
    db, err := sql.Open("postgres", "user=postgres password=secret dbname=mydb sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    
    db.Exec("CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT)")
    db.Exec("INSERT INTO users (name) VALUES ($1)", "Alice")
    
    rows, _ := db.Query("SELECT id, name FROM users")
    defer rows.Close()
    
    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        fmt.Printf("ID: %d, Name: %s\n", id, name)
    }
}

Best Practices & Common Errors

• ✅ Always close the connection with `defer db.Close()`.
• ✅ Use `sql.Prepare` to prevent SQL injection.
• ✅ Check errors after every query.
• ⚠️ Never expose connection credentials in your code.