Golang & MySQL - Managing Connections

Question

I'm having trouble handling connections in a golang web app that uses MySQL.

In the tutorials I have seen the database interaction is all handled once, in the main function.

However, in the real world each http request will interact with the database - where should I keep sql.Open() and defer sql.Close()? Here is my code.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "html/template"
    "net/http"
)

var db *sql.DB

var pageTemplate = template.Must(template.ParseFiles("index.html"))

type Items []string

func main() {
    db, err := sql.Open("mysql",
        "username:passwordl@tcp(127.0.0.1:3306)/databasename")
    checkErr(err)
    defer db.Close()
    _, err = db.Exec(
        `
    CREATE TABLE IF NOT EXISTS items (
      item_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      item TEXT
    );
    `)
    checkErr(err)
    http.HandleFunc("/", mainHandler)
    http.ListenAndServe(":3000", nil)
}

func mainHandler(w http.ResponseWriter, r *http.Request) {
    var item string
    var items Items
    stmt, err := db.Prepare("select item from items")
    checkErr(err)
    defer stmt.Close()
    rows, err := stmt.Query()
    checkErr(err)
    defer rows.Close()
    for rows.Next() {
        err := rows.Scan(&item)
        checkErr(err)
        items = append(items, item)
    }
    if err = rows.Err(); err != nil {
        checkErr(err)
    }
    pageTemplate.Execute(w, items)
}

func checkErr(err error) {
    if err != nil {
        fmt.Println(err)
    }
}

I get a runtime error on the line:

stmt, err := db.Prepare("select item from items")

Persumably because the db isn't recognised outside of the main function. Should I instead have sql.Open() on each url Handler?

Apologies if the question is ambiguous.

sql.Open is really opening a connection pool. the resulting db object is thread safe and can be used by your http handlers concurrently. you generally will only call db.Close() before exiting your application — David Budworth
– David Budworth, Commented Jan 25, 2016 at 16:10

Riscie · Accepted Answer · 2016-01-25 15:49:04Z

4

The problem is, you are redeclaring db inside your main function.

change:

db, err := sql.Open("mysql",

to:

var err error
db, err = sql.Open("mysql",

Also consider reading about diffrent organizing methods when it comes to db access in your app. This is a great read: http://www.alexedwards.net/blog/organising-database-access

answered Jan 25, 2016 at 15:49

Riscie

4,9291 gold badge29 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Kane Over a year ago

Aha! Thanks a lot for that and for the reading material. Very much appreciated.

kostix · Accepted Answer · 2016-01-25 16:23:10Z

2

In the

db, err := ...

statement the operator := will define an new variable and hidde the global one with the same name.

edited Jan 25, 2016 at 16:23

kostix

56.2k14 gold badges104 silver badges188 bronze badges

answered Jan 25, 2016 at 16:17

Antman

212 bronze badges

Collectives™ on Stack Overflow

Golang & MySQL - Managing Connections

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related