Server-Side Scripting/SQL Databases/Go

routes/lesson9.go

// This program creates and displays a temperature database
// with options to insert, update, and delete records.
//
// References:
//  https://golang.org/doc/
//  https://astaxie.gitbooks.io/build-web-application-with-golang/content/en/05.3.html
//  https://pkg.go.dev/database/sql

package routes

import (
    "database/sql"
    "html/template"
    "log"
    "net/http"
    "path/filepath"
    "strconv"
    _ "github.com/mattn/go-sqlite3"
)

func Lesson9(response http.ResponseWriter, request *http.Request) {
	response.Header().Set("Content-Type", "text/html; charset=utf-8")

	type Data struct {
		Table  template.HTML
	}
	
	result := ""

    switch request.Method {
        case "GET":
            result = processGet9()
        case "POST":
            result = processPost9(request)
        default:
            result = "Unexpected request method: " + request.Method
    }

	data := Data{template.HTML(result)}
	path := filepath.Join("templates", "lesson9.html")
	parsed, _ := template.ParseFiles(path)
	parsed.Execute(response, data)
}

func processGet9() string {
    checkDatabase()
    result := getData()
    return result
}

func processPost9(request *http.Request) string {
    country := request.FormValue("country")
    temperature := request.FormValue("temperature")

    if !countryExists(country) {
        insertCountry(country, temperature)
    } else if (temperature != "") {
        updateCountry(country, temperature)
    } else {
        deleteCountry(country)
    }

    result := getData()
    return result
}

func checkDatabase() {
    query := `
        SELECT COUNT(*) AS Count FROM sqlite_master
        WHERE name = 'Countries';`
    rows := sqliteQuery(query)
    var count int
    rows.Next()
    rows.Scan(&count)
    rows.Close()

    if count == 1 {
        return
    }

    query = `
        CREATE TABLE Countries(
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        Country TEXT UNIQUE NOT NULL,
        Temperature REAL NOT NULL);`
    sqliteExec(query)
}

func getData() string {
    query := "SELECT ID, Country, Temperature FROM Countries;"
    rows := sqliteQuery(query)

    result := "<table><tr><th>ID</th>"
    result += "<th>Country</th>"
    result += "<th>Temperature</th></tr>"

    for rows.Next() {
        var id int
        var country string
        var temperature float64
        err := rows.Scan(&id, &country, &temperature)
        if err != nil {
            log.Fatal(err)
        }

        result += "<tr><td>" + strconv.Itoa(id) + "</td>"
        result += "<td>" + country + "</td>"
        result += "<td>"+ strconv.FormatFloat(temperature, 'f', 1, 64) + "</td></tr>"
    }

    result += "</table>"
    rows.Close()
    return result;
}

func countryExists(country string) bool {
    query := `
        SELECT EXISTS(
        SELECT * FROM Countries
        WHERE Country = ?) AS Count;`

    rows := sqliteQuery(query, country)
    var count int
    rows.Next()
    rows.Scan(&count)
    rows.Close()
    
    return count == 1
}

func insertCountry(country string, temperature string) {
    query := `
        INSERT INTO Countries (Country, Temperature)
        VALUES(?, ?);`
    sqliteExec(query, country, temperature);
}

func updateCountry(country string, temperature string) {
    query := `
        UPDATE Countries
        SET Temperature = ?
        WHERE Country = ?;`
    sqliteExec(query, temperature, country);
}

func deleteCountry(country string) {
    query := `
        DELETE FROM Countries
        WHERE Country = ?;`
    sqliteExec(query, country);
}

func sqliteQuery(query string, parameters ...interface{}) *sql.Rows {
    db, err := sql.Open("sqlite3", "./temperature.db")
    if err != nil {
		log.Fatal(err)
	}
    defer db.Close()

    var result *sql.Rows
    if parameters == nil {
        result, err = db.Query(query)
    } else {
        result, err = db.Query(query, parameters...)
    }
    if err != nil {
        log.Fatal(err)
    }

    return result
}

func sqliteExec(query string, parameters ...interface{}) sql.Result {
    db, err := sql.Open("sqlite3", "./temperature.db")
    if err != nil {
		log.Fatal(err)
	}
    defer db.Close()

    var result sql.Result
    if parameters == nil {
        result, err = db.Exec(query)
    } else {
        result, err = db.Exec(query, parameters...)
    }
    if err != nil {
        log.Fatal(err)
    }

    return result
}

templates/lesson9.html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Lesson 9</title>
    <link rel="stylesheet" href="styles.css">
</head>

<body>
    <h1>Temperature Data Entry</h1>
    <p>Enter country and temperature. Enter again to update.</p>
    <p>Enter country without temperature to delete.</p>
    <form method="POST">
        <p><label for="country">Country:</label>
            <input type="text" id="country" name="country" required>
        </p>
        <p><label for="stop">Temperature:</label>
            <input type="text" id="temperature" name="temperature">
        </p>
        <p><input type="submit" name="submit" value="Submit"></p>
    </form>
    <hr>
    {{.Table}}
</body>
</html>

Try It

See Server-Side Scripting/Routes and Templates/Go to create a test environment.