Golang Database/SQL

Index of GoLang Resources

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’.

we need to use the “database/sql” package, we need to use the “database/sql” package. It provides a light-weight interface to connect with the databases.

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”.

The sql.DB performs the below tasks :

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,

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.

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 employee database in our local database.

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

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 the 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 to a 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().

Here we’re using db.Query() to send a query to the database.
We defer rows.Close()
We iterate over rows with rows.Next()
We read the columns in each row into variables with rows.Scan()
We check for errors after we’re done iterating over the rows.

Some precautions to take

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.

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 ⇒

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.

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.

Here both are not the same If we use db.Query() here then it will return to sql.Rows and it will keep the connection open until 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 operation 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:

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

Using a 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 the 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.

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.

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 the iterating resultset

Consider the following code:

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.

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:

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.

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.

But there are some limitations and reasons to avoid nullable columns

There’s no sql.NullUint64 or sql.Null your favorite type. 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 :

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.

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

Index of GoLang Resources

Related Articles