Go Database/SQL

In Chapter-8 of our Golang Tutorial, we touched upon ‘Common Utilities in Project Golang’. In this chapter, let’s explore ‘Go-database/SQL’.

In order to use SQL or SQL-like database in Go , we need to use the “database/sql” package. It provides light-weight interface to connect with the databases.

The sql.DB performs the below tasks :

Basically, to access database in Go, we need to use sql.DB. You need to use this to create statements, transactions, execute queries and fetch results. But keep in mind that, sql.DB is not a database connection. According to Go specification, “It’s an abstraction of the interface and existence of a database, which might be as varied as a local file, accessed through a network connection, or in-memory and in-process”.

1. Opening and closing of the connection with the underlying database driver.
2. Managing connection pooling.

Connection pooling is managed like this, the connection will be marked in use when you are doing something otherwise it will be returned to the pool when not in use. One consequence of this is that if you fail to release connections back to pool, you can cause db.SQL to open lots of connections and that will be running out of resources.

After creating sql.DB you can use this query to the database, as well as for creating statements and creating transactions.

Importing Database Driver

To use the database/sql you will need package itself and the specific drivers related to the database. You generally shouldn’t use driver packages directly, although some drivers encourage you to do so. Instead, your code should only refer to types defined in database/sql, if possible. This shall help to avoid making your code dependent on the driver so that you can change the underlying driver (and thus the database you’re accessing) with minimal code changes.

In this, we’ll use the excellent MySQL drivers given by @julienschmidt and @arnehormann.

So now you need to import the package to access db like this,

import (
“database/sql”
_ “github.com/go-sql-driver/mysql”
)

We’ve used _ qualifier before this third party driver, so none of its exported names are visible to our code.

To use this 3rd party driver, download it from GitHub using go get command.

> go get “github.com/go-sql-driver/mysql”

Now, we’re ready to access the database.

As we have already imported the packages, so now you need to create database object sql.DB

To create sql.DB, you use sql.Open() and this will return *sql.DB object.

main.go
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
)
func main(){
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")
if err != nil {
fmt.Println(err)
}else{
fmt.Println("Connection Established")
}
defer db.Close()
}

Now, here we’ll clarify the things :

1. The first parameter in sql.Open() is driver name of the database. This string registers itself with database/sql and is conventionally the same as the package name. There are other drivers like for sqlite3 github.com/mattn/go-sqlite3 and for postgres it is github.com/lib/pq

2. The second argument is driver specific syntax that tells the driver how to access the underlying datastore. In this, we are connecting to the employeedb database in our local database.

3. You should always check and handle errors that are coming from database/sql operations.

4. It is idiomatic to defer db.Close() if the sql.DB should not have a lifetime beyond the scope of the function.

As already said, sql.Open() does not establish any connection to the database, nor does it validate driver parameters. Instead, simply it prepares the database abstraction. The first actual connection to the datastore will be established lazily when it’s needed for the first time. If you want to check that database is available and accessible use db.Ping() and remember to check for errors.

err:=db.Ping()
if err!=nil{
//do something here
}

Even if it’s mandatory to Close() the database object when you’re done with it, sql.DB object is designed for long life with it. Don’t Open() and Close() databases frequently. Instead, create one sql.DB object for each distinct datastore you need to access and keep it until the program is done accessing that datastore. Pass it around as needed, or make it available somehow globally, but keep it open.

Now, after connection opening, we’ll see the operations to retrieve the resultset from datastore.

Fetching Data From Datastore

Go’s database/sql function names are significant. If a function name includes Query, it means for asking the database to return set of rows, even if it’s empty. Statements that don’t return rows should not use Query functions; they should use Exec().

Now let’s look at how to query the database, working with results. We’ll query the user’s table for a user whose id is 11 and print its id and name. We’ll assign results to a variable, a row at a time, with rows.Scan().

main.go
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
"log"
)

func main(){
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")
if err != nil {
log.Fatal(err)
}else{
fmt.Println("Connection Established")
}
var (
id int
name string
)
rows,err:=db.Query("select id, username from user where id = ?", 1)
if err!=nil{
log.Fatal(err)
}
Defer rows.Close()
for rows.Next(){
err:=rows.Scan(&id,&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(id, name)
}
err = rows.Err()
if err != nil {
log.Fatal(err)
}
defer db.Close()
}

Here we’re using db.Query() to send a query to the database.

1. We defer rows.Close()
2. We iterate over rows with rows.Next()
3. We read the columns in each row into variables with rows.Scan()
4. We check for errors after we’re done iterating over the rows.

Some precautions to take

1. You should always check for an error at the end of for rows.Next() loop.
2. Second, as long as there‘s an open result set(represented by rows), the underlying connection is busy and can’t be used for another query. That means it’s not available in the connection pool. If you iterate over all of the rows with rows.Next(), eventually you’ll read last row and rows.Next() will return an internal EOF error and calls rows.Close() for you. But for some reason you exit that loop – an early return or so on then the rows doesn’t get closed, and the connection remains open. This is an easy way to run out of resources.
3. rows.Close() is a harmless no-op if it’s already closed, so you can call it multiple times. Notice, however, that we check the error first, and only call rows.Close() if there isn’t an error, in order to avoid a runtime panic.
4. You should always defer rows.close(), even if you also call rows.Close(). If there isn’t an error, in order to avoid runtime panic.
5. Don’t defer within the loop.

Preparing Queries

You should always prepare queries to be used multiple times. These prepared statements have parameters that will be passed while executing the statement. This is much better than concatenating strings (Avoiding SQL injection attack).

In MySQL, parameter placeholder is ? and in postgresql it is $N, where N is a number. SQLite accepts either of these. In Oracle, placeholders begin with colon and name like parameter1. Here we’ll use? for MySQL.

main.go
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
"log"
)

func main(){
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")
if err != nil {
log.Fatal(err)
}else{
fmt.Println("Connection Established")
}
var (
id int
name string
)

rows,err:=db.Query("select id, username from user where id = ?", 2)
if err!=nil{
log.Fatal(err)
}

for rows.Next(){
err:=rows.Scan(&id,&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(id, name)
}
}

Here db.Query() prepares, executes and closes prepared statement.

Single Row Queries

If a query is returning at most one row, you can use shortcut around some of the lengthy boilerplate code.

Sample Code ⇒
var name string
err:=db.QueryRow(“select username from user where id=?”)
if err!=nil{
log.Fatal(err)
}
fmt.Println(err)

Errors from the query are deferred until Scan() is called, and then are returned from that. You can also call QueryRow() on a prepared statement:

stmt, err := db.Prepare("select username from user where id = ?")
if err != nil {
log.Fatal(err)
}
var name string
err = stmt.QueryRow(1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)

Modifying Data And Using Transactions

Now here we’ll see how to modify data and to work with the transaction.

Modify Data

Use Exec() with prepared Statement for INSERT, UPDATE, DELETE or another statement that doesn’t return rows.

main.go
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
"log"
)

func main(){
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")
if err != nil {
log.Fatal(err)
}else{
fmt.Println("Connection Established")
}
stmt, err := db.Prepare("INSERT INTO user(id,username) VALUES(?,?)")
if err != nil {
log.Fatal(err)
}
res, err := stmt.Exec(33,"Ray Martin")
if err != nil {
log.Fatal(err)
}
lastId, err := res.LastInsertId()
if err != nil {
log.Fatal(err)
}
rowCnt, err := res.RowsAffected()
if err != nil {
log.Fatal(err)
}
log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}

Hereafter executing a statement, it gives sql.Result that gives access to statement metadata: the last inserted id and no. of rows affected.

If you don’t want to return result just check the case below.

_, err := db.Exec("DELETE FROM users") // GOOD APPROACH
_, err := db.Query("DELETE FROM users") // BAD APPROACH

Here both are not same If we use db.Query() here then it will return sql.Rows and it will keep the connection open till the closing of the connection.

Working With Transaction

In Go, a transaction is an object that reserves a connection to datastore. It guarantees that all the operations related to the same connection will be executed.

Here, you need to begin a transaction by calling db.Begin(), and close it with Commit() or Rollback() method on resultant tx variable. Under the covers, tx gets a connection from pool and reserves it to use only with the transaction.

Prepared statements created in a transaction are bound to the same transaction.

The main things to remember here about the transaction are:

1. The tx object could remain open, reserving connection pool and not returning it.

While working in a transaction you should care about not to make calls on db variable. Make all of your calls to the tx variable only that you created with db.Begin() because db is not a transaction, tx is the transaction. If you try to make calls on db variable then those calls will not happen inside the transaction

main.go
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"log"
)

func main(){
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")
if err != nil {
log.Fatal(err)
}
tx,_:=db.Begin()
stmt, err := tx.Prepare("INSERT INTO user(id,username) VALUES(?,?)")
res,err:=stmt.Exec(4,"Ricky")
res,err=stmt.Exec(5,"Peter")
if err!=nil{
tx.Rollback()
log.Fatal(err)
}
tx.Commit()
log.Println(res)
}

Using Prepared Statement

Prepared Statement and Connections

A prepared statement is a SQL statement with parameter placeholders which is sent to the database server and prepared for repeated execution. It’s a performance optimization as well as a security measure; it protects against attacks such as SQL injection, where an attacker hijacks unguarded string concatenation to produce malicious queries.

In MySQL, as well as in most databases, you first send the SQL to the server and ask for it to be prepared with placeholders for bind parameters. The server responds with a statement ID. You then send an execute a command to the server, passing it the statement ID and the parameters.

At the database level, prepared statements are bound to a single db connection. The typical flow is like client sends a SQL statement with the placeholders to the server preparation, the server responds with statement ID and then client executes the statement by sending ID and statement.

In Go, connections are not directly exposed to database/sql package. You need to prepare a statement on db or tx object but not directly on a database connection.

1. When a statement is prepared it is prepared on a connection in the pool.
2. The Stmt object remembers which connection used.
3. When you execute Stmt, it tries to use the connection. If not available then it gets another connection from the pool and re-prepares the statement with db.

As here due to re-preparation of statements then there will be high concurrency usage of db, which may keep connection busy.

main.go
 package main
 import (
 _ "github.com/go-sql-driver/mysql"
 "database/sql"
 "log"
 )

 func main(){
 db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")
 if err != nil {
 log.Fatal(err)
 }

 stmt, err := db.Prepare("INSERT INTO user(id,username) VALUES(?,?)")
 res,err:=stmt.Exec(99,"John")
 res,err=stmt.Exec(88,"Martin")
 if err!=nil{
 log.Fatal(err)
 } 
 log.Println(res) 
}

Prepared Statement In Transaction

I think I’ve already said this that prepared statements created in the transaction are bound the same transaction only. So, when we are working on tx object that means our actions are working on one and only connection.

This means prepared statements created inside tx can’t be used separately from it. Also prepared statements created on db can’t be used within transaction because they bound to a different connection.

To use a prepared statement prepared outside the transaction in a tx, you can use tx.Stmt(), which will create a new transaction-specific statement from the one prepared outside the transaction.

It does this by taking an existing prepared statement, setting the connection to that of the transaction and re-preparing all statements every time they are executed.

 
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
defer tx.Rollback()
stmt, err := tx.Prepare("INSERT INTO user VALUES (?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close() // danger!
for i := 0; i < 10; i++ {
_, err = stmt.Exec(i)
if err != nil {
log.Fatal(err)
}
}
err = tx.Commit()
if err != nil {
log.Fatal(err)
}
// stmt.Close() runs here!

Below are the parameter placeholder syntaxes which are database specific. Consider comparison MySQL, PostgreSQL, Oracle.

MySQL PostgreSQL Oracle ===== ========= ======= WHERE col=? WHERE col=$1 WHERE col=:col VALUES(?,?,?) VALUES($1,$2,$3) VALUES(:val1,:val2,:val3)

Handling Errors

Almost all the operations in database/SQL types return an error as last value. You should always check the error, never ignore them. Some special error behaviors are there that you might know

Error From Iterating Resultset

Consider the following code:

for rows.Next() {
// ...
}
if err = rows.Err(); err != nil {
// handle the error here
}

Errors From Closing Resultsets

You should always close sql.Rows explicitly, if you exit the loop prematurely. It’s auto closed if the loop exits normally or through error, but you might accidentally do this.

for rows.Next() {
// ...
break; // whoops, rows is not closed! memory leak…
}
// do the usual "if err = rows.Err()" [omitted here]...
// it's always safe to [re?]close here:
if err = rows.Close(); err != nil {
// but what should we do if there's an error?
log.Println(err)
}

The error returned by rows.Close() is the only exception to general rule that its best to capture and check for errors in all db operations. If rows.Close() returns an error, it’s unclear what you should do.

Errors From QueryRow

Consider the following code to fetch a single row:

var name string
err = db.QueryRow("select username from user where id = ?", 1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)

What if there was no user with id = 1? Then there would be no row in the result, and.Scan() would not scan a value into the name. What happens then? Go defines special error constant sql.ErrNoRows which is returned from QueryRow() when the result is empty. This needs to be handled. An empty result is not considered an error by application code, and if you didn’t check whether an error is a special constant then, you’ll cause application code errors.

Errors from a query are deferred until Scan() is called, and then returned from that.

var name string
err = db.QueryRow("select username from user where id = ?", 1).Scan(&name)
if err != nil {
if err == sql.ErrNoRows {
// there were no rows, but otherwise no error occurred
} else {
log.Fatal(err)
}
}
fmt.Println(name)

Working With NULLS

Nullable columns lead to a lot of ugly code. If you can, avoid them. If not then you need to use special types from database/sql package to handle them or to define your own.

There are types of nullable booleans, strings, integers and floats. Here’s how to use them.

for rows.Next() {
var s sql.NullString
err := rows.Scan(&s)
// check err
if s.Valid {
// use s.String
} else {
// NULL value
}
}

But there are some limitations and reasons to avoid nullable columns

1. There’s no sql.NullUint64 or sql.NullYourFavouriteType. You need to define your own for this.
2. Nullability can be tricky and not future-proof. If you think something won’t be null, but you’re wrong, your program will crash.
3. One of the nice things about Go is having a useful default zero-value for every variable. This isn’t way nullable things work.

The Connection Pool

The connection pooling is provided by database/SQL package. Connection pooling is the mechanism of maintaining a pool of connections and reusing those connections. It is used in enhancing the performance of executing commands on the database. It facilitates reuse of the same connection object to serve a number of client requests.

Every time a client request is received, the pool is searched for an available connection and it’s highly likely that it gets a free connection. Otherwise, either the incoming requests are queued or a new connection is created and added to the pool (depending on how many connections are already there in the pool). As soon as a request finishes using a connection, it is given back to the pool from where it’s assigned.

Some useful things to know about connection pooling here :

1. Connection pooling means that executing two consecutive statements on a single database might open two connections and execute them separately. For example, LOCK TABLES followed by an INSERT can block because the INSERT is a connection that does not hold the table lock.
2. Connections are created when needed and there isn’t a free connection in the pool.
3. By default, there’s no limit on the number of connections. If you try to do a lot of things at once, you can create an arbitrary number of connections. This can cause the database to return an error such as “too many connections.”
4. In Go 1.1 or newer, you can use db.SetMaxIdleConns(N) to limit the number of idle connections in the pool. This doesn’t limit the pool size, though.
5. In Go 1.2.1 or newer, you can use db.SetMaxOpenConns(N) to limit the number of total open connections to the database. Unfortunately, a deadlock bug (fix) prevents db.SetMaxOpenConns(N) from safely being used in 1.2.
6. Connections are recycled rather fast.
7. Keeping a connection idle for a long time can cause problems. Try db.SetMaxIdleConns(0) if you get connection timeouts because a connection is idle for too long.

main.go
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"log"
)

func main(){
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/employeedb")

// Connection Pooling methods

db.SetConnMaxLifetime(500)
db.SetMaxIdleConns(50)
db.SetMaxOpenConns(10)
db.Stats()

if err != nil {
log.Fatal(err)
}
tx,_:=db.Begin()
stmt, err := tx.Prepare("INSERT INTO user(id,username) VALUES(?,?)")
res,err:=stmt.Exec(4,"Abhijit")
res,err=stmt.Exec(5,"Yogesh")
if err!=nil{
tx.Rollback()
log.Fatal(err)
}
tx.Commit()
log.Println(res)

}

Here are some external sources of information we’ve found to be helpful.

• http://golang.org/pkg/database/sql/database
• http://jmoiron.net/blog/gos-database-sql/
• http://jmoiron.net/blog/built-in-interfaces/