How to do a query in golang, fiber, using database/sql and "github.com/lib/pq" as the postgresql driver

Question

This is my controller in fiber

func Register(c *fiber.Ctx) error {
    user := new(User)

    if err := c.BodyParser(user); err != nil {
        return c.Status(400).JSON(fiber.Map{
            "error": err.Error(),
        })
    }

    if user.Username == "" || user.Password == "" {
        return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{
            "error":   "Bad request.",
            "message": "Please include both username and password.",
        })
    }

    query := `SELECT username FROM user WHERE username = $1`

    res, err := db.Query(query, user.Username)

    if err != nil {
        return err
    }
    defer res.Close()

    return c.JSON("hello")
}

But when i hit the endpoint i got

panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x1 addr=0x20 pc=0x865db4]

goroutine 22 [running]:
database/sql.(*DB).conn(0x0, {0xbee8e0, 0xeee440}, 0x1)
        C:/Program Files/Go/src/database/sql/sql.go:1282 +0x54
database/sql.(*DB).query(0x9?, {0xbee8e0, 0xeee440}, {0xb670d2, 0x2d}, {0xc00028fb08, 0x1, 0x1}, 0xc0?)
        C:/Program Files/Go/src/database/sql/sql.go:1721 +0x57
database/sql.(*DB).QueryContext.func1(0x0?)
        C:/Program Files/Go/src/database/sql/sql.go:1704 +0x4f
database/sql.(*DB).retry(0xc00002c440?, 0xc00028f9f0)
database/sql.(*DB).QueryContext(0xc00000ad18?, {0xbee8e0?, 0xeee440?}, {0xb670d2?, 0xc00000ac50?}, {0xc00028fb08?, 0xc0000a3af8?, 0x7cbfaf?})
        C:/Program Files/Go/src/database/sql/sql.go:1703 +0xc5
database/sql.(*DB).Query(...)
        C:/Program Files/Go/src/database/sql/sql.go:1717
example/backend/user.Register(0xc000004600)
        C:/Users/me/Documents/hello/backend/user/user.go:35 +0x1e5
github.com/gofiber/fiber/v2.(*App).next(0xc00000c900, 0xc000004600)
        C:/Users/me/go/pkg/mod/github.com/gofiber/fiber/v2@v2.48.0/router.go:144 +0x1b2
github.com/gofiber/fiber/v2.(*App).handler(0xc00000c900, 0x81622f?)
        C:/Users/me/go/pkg/mod/github.com/gofiber/fiber/v2@v2.48.0/router.go:171 +0x78
github.com/valyala/fasthttp.(*Server).serveConn(0xc000160000, {0xbeff80?, 0xc00008e0c0})
        C:/Users/me/go/pkg/mod/github.com/valyala/fasthttp@v1.48.0/server.go:2363 +0x11d4
github.com/valyala/fasthttp.(*workerPool).workerFunc(0xc0000d0140, 0xc000096320)
        C:/Users/me/go/pkg/mod/github.com/valyala/fasthttp@v1.48.0/workerpool.go:224 +0xa4
github.com/valyala/fasthttp.(*workerPool).getCh.func1()
        C:/Users/me/go/pkg/mod/github.com/valyala/fasthttp@v1.48.0/workerpool.go:196 +0x32
created by github.com/valyala/fasthttp.(*workerPool).getCh in goroutine 1
        C:/Users/me/go/pkg/mod/github.com/valyala/fasthttp@v1.48.0/workerpool.go:195 +0x1ab

I have another endpoint that is not doing any query and it is working fine, so i don't think there is anything wrong with my connection to the db itself because at this point i'v ealready established a connection to it.

Here is my other endpoint

app.Get("/", func(c *fiber.Ctx) error {
        return c.Status(fiber.StatusOK).JSON(fiber.Map{
            "status":  "OK.",
            "message": "Service is up and running.",
        })
    })

Here is how i connect to postgre, any help would be appreciated

package config

import (
    "database/sql"
    "fmt"
    "os"

    "github.com/joho/godotenv"

    _ "github.com/lib/pq"
)

var db *sql.DB

func PostgreInit() error {
    // load env
    if envErr := godotenv.Load(".env"); envErr != nil {
        fmt.Println("Could not load .env")
        os.Exit(1)
    }

    var (
        DB_NAME     = os.Getenv("DB_NAME")
        DB_USER     = os.Getenv("DB_USER")
        DB_PASSWORD = os.Getenv("DB_PASSWORD")
        DB_HOST     = os.Getenv("DB_HOST")
        DB_PORT     = os.Getenv("DB_PORT")
    )

    // connect to db
    var err error
    connStr := fmt.Sprintf(
        "postgres://%v:%v@%v:%v/%v?sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)

    db, err = sql.Open("postgres", connStr)
    if err != nil {
        return err
    }

    if err = db.Ping(); err != nil {
        return err
    }

    return nil
}

Is the db var actually populated? Have you called `PostgreInit`? — Aditya, Aug 24 '23 at 04:20
While you do show the function that initializes `db` in package `config`, you do not show how `db` is initialized inside the the `user` package. Note that the two variables are completely separate, initializing a variable inside one package will not automagically initialize a completely separate variable in a separate package. — mkopriva, Aug 24 '23 at 04:20
i have called the PostgreInit inside my main function like this `func main() { app := fiber.New() // db init if err := config.PostgreInit(); err != nil { log.Fatal(err) } app.Post("/register", user.Register) log.Fatal(app.Listen("localhost:8080")) }` — poo, Aug 24 '23 at 04:32
@poo the variables `config.db` and `user.db` are two separate variables. Initializing one will not initialize the other. — mkopriva, Aug 24 '23 at 04:45
@mkopriva how do i initialize it once and make it available everywhere? — poo, Aug 24 '23 at 04:50
@poo to make it available everywhere, the simplest approach would be to export the config's variable and then have any code that needs to use that variable import the config package and then use `config.DB`. — mkopriva, Aug 24 '23 at 04:57
@mkopriva i have tried doing `var DB *sql.DB` in config then `var db = config.DB` in user but output is still the same, have tried to `fmt.Println(db)` inside of user and it returns `` — poo, Aug 24 '23 at 05:00
You should use `config.DB` inside your functions instead of the local `db`. Doing `var db = config.DB` doesn't work because this top-level declaration is executed before the call to `PostgresInit` is executed, therefore `var db = config.DB` just assigns `nil` to the variable `db`. — mkopriva, Aug 24 '23 at 05:03
Yeah i was thinking about that too and now i put `var db = config.DB` inside of the `Register()` func and it is woking, thank you so much! — poo, Aug 24 '23 at 05:10

score -2 · Answer 1 · answered Aug 24 '23 at 12:49

The error you're encountering is a result of a nil pointer dereference. In your code, the db variable is defined globally but it is not initialized anywhere. To fix this issue, you need to initialize the db variable in the PostgreInit() function.

see this code:

package config

import (
    "database/sql"
    "fmt"
    "os"

    "github.com/joho/godotenv"

    _ "github.com/lib/pq"
)

var db *sql.DB

func PostgreInit() error {
    // load env
    if envErr := godotenv.Load(".env"); envErr != nil {
        fmt.Println("Could not load .env")
        os.Exit(1)
    }

    var (
        DB_NAME     = os.Getenv("DB_NAME")
        DB_USER     = os.Getenv("DB_USER")
        DB_PASSWORD = os.Getenv("DB_PASSWORD")
        DB_HOST     = os.Getenv("DB_HOST")
        DB_PORT     = os.Getenv("DB_PORT")
    )

    // connect to db
    var err error
    connStr := fmt.Sprintf(
        "postgres://%v:%v@%v:%v/%v?sslmode=disable",
        DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)

    db, err = sql.Open("postgres", connStr)
    if err != nil {
        return err
    }

    return nil
}

func CloseDB() {
    if db != nil {
        db.Close()
    }
}

With this update, the db variable will be initialized with the database connection in the PostgreInit() function. Remember to call PostgreInit() before using the database in your controller. I've added a CloseDB() function to close the database connection if it exists. You can call this function when your application exits or when you no longer need to use the database connection.

This is not a good answer. All you did was to remove the ping statement and add a CloseDB function, none of which will fix the problem OP _was having_. Note that Go does not have support for "global" variables. The `db` variable, being declared at the "top level", is scoped to the package, this means that it's "package variable", not a "global variable". — mkopriva, Aug 24 '23 at 13:57
And if you look at the stack-trace you'll notice that the nil-pointer-dereference panic occurs in package `user` and not in package `config`, therefore no matter how well you initialize `db` inside `config`, the `db` inside `user` will remain `nil` unless it itself is properly initialized. — mkopriva, Aug 24 '23 at 13:57
Your point is correct, but what do you suggest to improve this answer in general in Go language and not just this framework? — hadirezaei, Aug 24 '23 at 14:37
Just like using global variables in other languages, using package level variables in Go is generally not recommended, especially when it comes to external dependencies like a db, or 3rd party API client, etc. But, as already suggested in the comments to the question itself, one easy way would be to export the variable in the `config` package, e.g. `var DB *sql.DB`, and then have the function in the `user` package use that exported config variable directly, e.g. `config.DB`. — mkopriva, Aug 24 '23 at 15:07

How to do a query in golang, fiber, using database/sql and "github.com/lib/pq" as the postgresql driver

1 Answers1