Referring to an open database connection inside a function - Golang

Question

My main function opens a database connection:

func main() {
    db, err := sql.Open("sqlite3", "./house.db")
    checkErr(err)

    ...
}

Then, I want to create a function that allows me to add a row to the database based on a passed struct:

func addRow(row Room) error {
    stmt, err := db.Prepare("INSERT INTO Rooms (Name, Size, WindowCount, WallDecorationType, Floor) VALUES(?, ?, ?, ?, ?)")
    _, err = stmt.Exec(row.Name , row.Size , row.WindowCount , row.WallDecorationType , row.Floor)
    return err
}

But obviously I can't do that because the addRow() function has no idea what db is.

How would I make this function work? Should I perhaps, open the database outside of the main function?

Answer 1

Depending on how your application works, you can either

1. keep the db global
2. pass db as a parameter
3. make addRoom a method

What I typically do for API services is create a global db, like this:

var db *sql.DB

func main() {
    var err error
    db, err = sql.Open("sqlite3", "./house.db")
    checkErr(err)
    // create room Room{}
    err = addRoom(room)
    checkErr(err)
}

But you can also pass db as a parameter:

func addRow(db *sql.DB, row Room) error

Or you can create a struct which keeps the connection as an attribute and makes addRow a method:

type dbConn struct {
    db *sql.DB
}

func (conn dbConn) addRow(row Room) error

This book has some nice examples.