how to fetch data from db in go lang

Question

package main

import (
    "encoding/json"
    "gorilla/mux"
    "log"
    "net/http"
    "database/sql"
    _ "go-sql-driver/mysql"
    )

func GetPeople(w http.ResponseWriter, r *http.Request, db *sql.DB) {
    json.NewEncoder(w).Encode(db.QueryRow("SELECT * from test.animal"))
}

func main() {

db, err := sql.Open("mysql", "root:mariadb@tcp(127.0.0.1:3306)/test")
if err != nil {
    panic(err.Error())
}
defer db.Close()
router := mux.NewRouter()

router.HandleFunc("/people", GetPeople).Methods("GET")

log.Fatal(http.ListenAndServe(":8000", router))
}

I need to make my db recognized inside the GetPeople function but giving the function a third parameter (db *sql.DB) is impossible.

How can I solve it? Thank you

"How can I solve it?" Globals or closures.

Volker
– Volker

2017-09-28 08:49:53 +00:00
Commented Sep 28, 2017 at 8:49 — Volker
– Volker, Commented Sep 28, 2017 at 8:49

abhink · Accepted Answer · 2017-09-28 09:04:13Z

5

Another way to do this is using closures, this prevents global use of variables:

func GetPeopleHandler(db *sql.DB) func (w http.ResponseWriter, r *http.Request) {
    return func (w http.ResponseWriter, r *http.Request) {
        json.NewEncoder(w).Encode(db.QueryRow("SELECT * from test.animal"))
    }
}

func main() {
    var err error
    db, err = sql.Open("mysql", "root:mariadb@tcp(127.0.0.1:3306)/test")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
    router := mux.NewRouter()

    router.HandleFunc("/people", GetPeopleHandler(db)).Methods("GET")

    log.Fatal(http.ListenAndServe(":8000", router))
}

answered Sep 28, 2017 at 9:04

abhink

9,1961 gold badge41 silver badges52 bronze badges

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

1 Comment

Omzig Over a year ago

I get an error when i use this: struct type 'database/sql.Row' doesn't have any exported fields, nor custom marshaling (SA9005)go-staticcheck The error happens on the db.QueryRow('') section, i am also having to use the github.com/denisenkom/go-mssqldb driver

icza · Accepted Answer · 2017-09-28 14:00:31Z

1

Simply make your db variable global, not local in main():

var db *sql.DB

func GetPeople(w http.ResponseWriter, r *http.Request) {
    json.NewEncoder(w).Encode(db.QueryRow("SELECT * from test.animal"))
}

func main() {
    var err error
    db, err = sql.Open("mysql", "root:mariadb@tcp(127.0.0.1:3306)/test")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    router := mux.NewRouter()
    router.HandleFunc("/people", GetPeople).Methods("GET")
    log.Fatal(http.ListenAndServe(":8000", router))
}

See related questions:

How to use global var across files in a package?

Performance difference between global database connection and opening connection everytime on Golang

edited Sep 28, 2017 at 14:00

answered Sep 28, 2017 at 8:49

icza

423k78 gold badges1k silver badges905 bronze badges

Collectives™ on Stack Overflow

how to fetch data from db in go lang

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related