Chapter 13: SQL and Databases

Introduksyon

Ang Go database/sql package kay generic interface para sa SQL databases. Inubanan ug driver (SQLite, PostgreSQL, MySQL, bisag unsa), pwede ka mag-create, magbasa, mag-update, ug mag-delete ug data. Anhi nga chapter, atong gamiton ang SQLite as example. Simple lang, practical.

The Database

Ang relational databases kay organized into tables naay rows ug columns. Mura rag Excel pero mas professional. Ang Go database/sql interface mo-work sa daghang backends. Ang examples nato kay mogamit ug SQLite via github.com/mattn/go-sqlite3 driver.

Database API and Drivers

Ang database/sql package nagdefine ug interface. Ang drivers nag-implementar para specific databases. I-register ang driver pinaagi sa blank identifier import:

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"  // SQLite driver
    _ "github.com/lib/pq"            // PostgreSQL driver
    _ "github.com/go-sql-driver/mysql"  // MySQL driver
)

Connecting to Databases

Gamit ug sql.Open para maghimo ug database handle (dili pa ni actual connection ha, promise). Gamit ug db.Ping para i-verify kung buhi ba ang connection:

db, err := sql.Open("sqlite3", "./data.db")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

err = db.Ping()
if err != nil {
    log.Fatal("cannot connect:", err)
}
fmt.Println("connected to database")

Creating Tables

I-execute ang DDL statements (CREATE TABLE, etc.) gamit db.Exec:

createTable := `
CREATE TABLE IF NOT EXISTS users (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    age   INTEGER
);`

_, err := db.Exec(createTable)
if err != nil {
    log.Fatal(err)
}

Inserting Data

Gamit ug parameterized queries (dili jud string interpolation!) para maiwasan ang SQL injection. Dili ta ganahan ma-hack:

result, err := db.Exec(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    "Alice", "alice@example.com", 30,
)
if err != nil {
    log.Fatal(err)
}
id, _ := result.LastInsertId()
fmt.Println("inserted id:", id)

Exercise 13.01: Creating a Table with Numbers

Goal: Paghimo ug numbers table naay id ug value column, dayon i-insert ang numbers 1 hangtod 10.

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()

    db.Exec(`CREATE TABLE numbers (id INTEGER PRIMARY KEY, value INTEGER)`)

    for i := 1; i <= 10; i++ {
        db.Exec("INSERT INTO numbers (value) VALUES (?)", i)
    }

    fmt.Println("Inserted 10 numbers")
}

Retrieving Data

Mag-query ug multiple rows gamit db.Query or single row gamit db.QueryRow:

// Multiple rows
rows, err := db.Query("SELECT id, name, age FROM users WHERE age > ?", 25)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var id, age int
    var name string
    rows.Scan(&id, &name, &age)
    fmt.Println(id, name, age)
}

// Single row
var name string
var age int
err = db.QueryRow("SELECT name, age FROM users WHERE id = ?", 1).Scan(&name, &age)

Updating Existing Data

Gamit ug db.Exec para UPDATE statements — i-update ang existing data:

result, err := db.Exec(
    "UPDATE users SET age = ? WHERE name = ?",
    31, "Alice",
)
if err != nil {
    log.Fatal(err)
}
affected, _ := result.RowsAffected()
fmt.Println("updated", affected, "rows")

Deleting Data

Delete rows gamit parameterized DELETE queries. Ayawg gamit ug DELETE without WHERE kung dili ka sure:

result, err := db.Exec("DELETE FROM users WHERE id = ?", 5)
if err != nil {
    log.Fatal(err)
}
affected, _ := result.RowsAffected()
fmt.Println("deleted", affected, "rows")

Exercise 13.02: Holding Prime Numbers in a Database

Goal: I-store ang prime numbers nga ubos sa 50 sa database, dayon i-retrieve ug i-print. Math + database = powerful combo.

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/mattn/go-sqlite3"
)

func isPrime(n int) bool {
    if n < 2 { return false }
    for i := 2; i*i <= n; i++ {
        if n%i == 0 { return false }
    }
    return true
}

func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()

    db.Exec("CREATE TABLE primes (value INTEGER PRIMARY KEY)")

    for i := 2; i < 50; i++ {
        if isPrime(i) {
            db.Exec("INSERT INTO primes (value) VALUES (?)", i)
        }
    }

    rows, _ := db.Query("SELECT value FROM primes ORDER BY value")
    defer rows.Close()

    fmt.Print("Primes: ")
    for rows.Next() {
        var v int
        rows.Scan(&v)
        fmt.Printf("%d ", v)
    }
    fmt.Println()
}

Truncating and Deleting Table

Remove all rows with DELETE (without WHERE) or ang tibuok table gamit DROP:

// Remove all rows
db.Exec("DELETE FROM users")

// Remove the table entirely
db.Exec("DROP TABLE users")

Activity 13.01: Holding User Data in a Table

Goal: Paghimo ug users table, i-insert ug 3 users, dayon query ug i-print ang tanang users nga sobra 25 ang edad.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()

    db.Exec(`CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT, age INTEGER
    )`)

    db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "Alice", 30)
    db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "Bob", 22)
    db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "Carol", 28)

    rows, _ := db.Query("SELECT name, age FROM users WHERE age > 25")
    defer rows.Close()

    for rows.Next() {
        var name string
        var age int
        rows.Scan(&name, &age)
        fmt.Printf("%s is %d\n", name, age)
    }
}

Activity 13.02: Finding Messages of Specific Users

Goal: Paghimo ug messages ug users tables, i-join para makit-an ang messages sa specific user. Relational database in action.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()

    db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
    db.Exec(`CREATE TABLE messages (
        id INTEGER PRIMARY KEY, user_id INTEGER, content TEXT,
        FOREIGN KEY(user_id) REFERENCES users(id)
    )`)

    db.Exec("INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob')")
    db.Exec("INSERT INTO messages VALUES (1, 1, 'Hello!'), (2, 2, 'Hi!'), (3, 1, 'How are you?')")

    rows, _ := db.Query(`
        SELECT u.name, m.content FROM messages m
        JOIN users u ON m.user_id = u.id
        WHERE u.name = ?`, "Alice")
    defer rows.Close()

    for rows.Next() {
        var name, content string
        rows.Scan(&name, &content)
        fmt.Printf("%s: %s\n", name, content)
    }
}

Summary