5

I have a set of functions in my web API app. They perform some operations on the data in the Postgres database.

func CreateUser () {
    db, err := sql.Open("postgres", "user=postgres password=password dbname=api_dev sslmode=disable")
    // Do some db operations here
}

I suppose functions should work with db independently from each other, so now I have sql.Open(...) inside each function. I don't know if it's a correct way to manage db connection.

Should I open it somewhere once the app starts and pass db as an argument to the corresponding functions instead of opening the connection in every function?

Sergei Basharov
  • 51,276
  • 73
  • 200
  • 335

2 Answers2

8

Opening a db connection every time it's needed is a waste of resources and it's slow.

Instead, you should create an sql.DB once, when your application starts (or on first demand), and either pass it where it is needed (e.g. as a function parameter or via some context), or simply make it a global variable and so everyone can access it. It's safe to call from multiple goroutines.

Quoting from the doc of sql.Open():

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

You may use a package init() function to initialize it:

var db *sql.DB

func init() {
    var err error
    db, err = sql.Open("yourdriver", "yourDs")
    if err != nil {
        log.Fatal("Invalid DB config:", err)
    }
}

One thing to note here is that sql.Open() may not create an actual connection to your DB, it may just validate its arguments. To test if you can actually connect to the db, use DB.Ping(), e.g.:

func init() {
    var err error
    db, err = sql.Open("yourdriver", "yourDs")
    if err != nil {
        log.Fatal("Invalid DB config:", err)
    }
    if err = db.Ping(); err != nil {
        log.Fatal("DB unreachable:", err)
    }
}
icza
  • 389,944
  • 63
  • 907
  • 827
  • by definition `It is rarely necessary to close a DB.` so it doesn't have to be closed? and open the connection at all times? – Gujarat Santana Feb 11 '17 at 07:50
  • 2
    @GujaratSantana Yes, it's strongly recommended not to close and reopen db connections all the time. You only have to close a db connection if db changes (e.g. database name, host or authentication info changes). Or when your app exits. – icza Feb 11 '17 at 19:13
0

I will use a postgres example

package main

import necessary packages and don't forget the postgres driver

import (
  "database/sql"
  _ "github.com/lib/pq" //postgres driver
)

initialize your connection in the package scope

var db *sql.DB

have an init function for your connection

func init() {
  var err error
  db, err = sql.open("postgres", "connectionString")
  //connectioString example => 'postgres://username:password@localhost/dbName?sslmode=disable'
  if err != nil {
    panic(err)
  }
  err = db.Ping()
  if err != nil {
    panic(err)
  }
  // note, we haven't deffered db.Close() at the init function since the connection will close after init. you could close it at main or ommit it
}

main function

func main() {
defer db.Close() //optional
//run your db functions
}

checkout this example https://play.golang.org/p/FAiGbqeJG0H

Maxwell Githinji
  • 1
  • 1