Accessing database from the connection handler

[alexs (Alex)]

I’m working on a simple server which accepts data from multiple devices. Server listens for incoming connections and process them with goroutines like this

listener, err := net.Listen("tcp", addr)
if err != nil {
	return err
}
defer listener.Close()

for {
    conn, err := listener.Accept()
    if err != nil {
        continue
    }
    go handleConnection(conn)
}

I want to store received data in the database, so I defined global db variable and wrote some helper functions to save data, something like this

var db *sql.DB

func InitDB(dataSourceName string) {
    db, err = sql.Open("mssql", dataSourceName)
    if err != nil {
        log.Panic(err)
    }
    if err = db.Ping(); err != nil {
        log.Panic(err)
    }
}

func SaveData() (MyData, error) {
    tx, err := db.Begin()
    if err != nil {
	log.Fatal(err)
    }
    defer tx.Rollback()
    stmt, err := tx.Prepare("INSERT INTO mytable VALUES (?)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()
    _, err = stmt.Exec(MyData.Data)
    if err != nil {
        log.Fatal(err)
    }
    err = tx.Commit()
    if err != nil {
        log.Fatal(err)
    }
}

In the connection handler I simply call helper functions to work with database. Is this correct way to do things? I have found this blog post which describes similar approach, so I suppose it is fine. But one thing is still not clear to me, how in this case correctly close database?

[mathew]

You close the sql.DB database connection pool when your application quits. So typically your main() function will open sql.DB and do a deferred close, like for the TCP listener.

That said, the DB will obviously get closed anyway when your application quits. It’s just a question of whether you want to catch and log any errors which occur during the DB close. It’s a good idea to do so, you can catch problems such as unclosed statements that way if the underlying database is picky enough.

[acim (Boban Acimovic)]

This is not testable code, otherwise it is OK if does what you want Also try to avoid globals.