Databases

Database management in Go

Opening DB connection

How to open database connection in Go:

// The openDB() function wraps sql.Open() and returns a sql.DB connection pool
func openDB(dsn string) (*sql.DB, error) { 
  db, err := sql.Open("mysql", dsn)
  if err != nil {
    return nil, err 
  }
  
  // Set the maximum number of concurrently open (idle + in-use) connections. Setting this 
  // to less than or equal to 0 will mean there is no maximum limit. If the maximum
  // number of open connections is reached and all are in-use when a new connection is
  // needed, Go will wait until one of the connections is freed and becomes idle. From a 
  // user perspective, this means their HTTP request will hang until a connection
  // is freed.
  db.SetMaxOpenConns(100)
  // Set the maximum number of idle connections in the pool. Setting this
  // to less than or equal to 0 will mean that no idle connections are retained. 
  db.SetMaxIdleConns(5)
  
  // Create a context with a 5-second timeout deadline.
  ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) 
  defer cancel()
    
  // Use PingContext() to establish a new connection to the database, passing in the // context we created above as a parameter. If the connection couldn't be
  // established successfully within the 5 second deadline, then this will return an // error.
  err = db.PingContext(ctx)
  if err != nil { 
    return nil, err
  }
  return db, nil 
}

• The sql.Open() function returns a sql.DB object. This isn’t a database connection — it’s a pool of many connections. This is an important difference to understand. Go manages these connections as needed, automatically opening and closing connections to the database via the driver.

• The connection pool is safe for concurrent access, so you can use it from web application handlers safely.

• The connection pool is intended to be long-lived. In a web application it’s normal to initialise the connection pool in your main() function and then pass the pool to your handlers. You shouldn’t call sql.Open() in a short-lived handler itself — it would be a waste of memory and network resources.

• The sql.Open() function doesn’t actually create any connections, all it does is initialise the pool for future use. Actual connections to the database are established lazily, as and when needed for the first time. So to verify that everything is set up correctly we need to use the db.Ping() method to create a connection and check for any errors.

Configuring DB pool

The sql.DB connection pool is made up of connections which are either idle or in-use. By default, there is no limit on the maximum number of open connections (idle + in-use) at one time, but the default maximum number of idle connections in the pool is 2. Уour database itself probably has a hard limit on the maximum number of connections.

• The SetMaxOpenConns() method allows you to set an upper MaxOpenConns limit on the number of ‘open’ connections (in-use + idle connections) in the pool. By default, the number of open connections is unlimited.

• The SetMaxIdleConns() method sets an upper MaxIdleConns limit on the number of idle connections in the pool. By default, the maximum number of idle connections is 2.

• The SetConnMaxLifetime() method sets the ConnMaxLifetime limit — the maximum length of time that a connection can be reused for. By default, there’s no maximum lifetime and connections will be reused forever.

• The SetConnMaxIdleTime() method sets the ConnMaxIdleTime limit. This works in a very similar way to ConnMaxLifetime, except it sets the maximum length of time that a connection can be idle for before it is marked as expired. By default there’s no limit.

Let’s summarise all the above into some actionable points:

1. As a rule of thumb, you should explicitly set a MaxOpenConns value. This should be comfortably below any hard limits on the number of connections imposed by your database and infrastructure, and you may also want to consider keeping it fairly low to act as a rudimentary throttle.

2. In general, higher MaxOpenConns and MaxIdleConns values will lead to better performance. But the returns are diminishing, and you should be aware that having a too-large idle connection pool (with connections that are not frequently re-used) can actually lead to reduced performance and unnecessary resource consumption.

3. To mitigate the risk from point 2 above, you should generally set a ConnMaxIdleTime value to remove idle connections that haven’t been used for a long time.

4. It’s probably OK to leave ConnMaxLifetime as unlimited, unless your database imposes a hard limit on connection lifetime, or you need it specifically to facilitate something like gracefully swapping databases.

Database Migrations

Install golang-migrate tool:

# create migrations
migrate create -seq -ext=.sql -dir=./migrations create_movies_table

# executing migrations
migrate -path=./migrations -database=$GREENLIGHT_DB_DSN up

# migrate to specific version
migrate -path=./migrations -database=$EXAMPLE_DSN version
migrate -path=./migrations -database=$EXAMPLE_DSN goto 1
migrate -path=./migrations -database =$EXAMPLE_DSN down 1

# force specific version
migrate -path=./migrations -database=$EXAMPLE_DSN force 1

If the migration file which failed contained multiple SQL statements, then it’s possible that the migration file was partially applied before the error was encountered. In turn, this means that the database is in an unknown state as far as the migrate tool is concerned.

Accordingly, the version field in the schema_migrations field will contain the number for the failed migration and the dirty field will be set to true.

Once that’s done, then you must also ‘force’ the version number in the schema_migrations table to the correct value.