How to Work With SQL in Go (2024)

Know how to query SQL databases in Go. The right way

sql.DB

To access databases in Go, you use a sql.DB. You use this type to create statements and transactions, execute queries, and fetch results.

The first thing you should know is that sql.DB isn’t a database connection. It also doesn’t map to any particular database software’s notion of a “database” or “schema.” It’s an abstraction of a database, which might be as varied as a local file, accessed through a network connection, or in-memory and in-process.

sql.DB performs some important tasks for you behind the scenes:

It opens and closes connections to the actual underlying database, via the driver.
It manages a pool of connections as needed, which may be a variety of things as mentioned.

The sql.DB abstraction is designed to keep you from worrying about how to manage concurrent access to the underlying datastore. It’s safe for concurrent use by multiple goroutines.

A connection is marked in-use when you use it to perform a task, and then returned to the available pool when it’s not in use anymore. One consequence of this is that if you fail to release connections back to the pool, you can cause sql.DB to open a lot of connections, potentially running out of resources (too many connections, too many open file handles, lack of available network ports, etc). We’ll discuss more about this later.

Database driver

To use database/sql you’ll need the package itself, as well as a driver for the specific database you want to use.

You generally shouldn’t use driver packages directly, although some drivers encourage you to do so. (In our opinion, it’s usually a bad idea.) Instead, your code should only refer to types defined in database/sql, if possible. This helps 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. It also forces you to use the Go idioms instead of ad-hoc idioms that a particular driver author may have provided.

No database drivers are included in the Go standard library. But there are plenty of them implemented as a third-party, see https://golang.org/s/sqldrivers.

For demo purposes, we’ll use the excellent MySQL drivers.

Add the following to the top of your Go source file:

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

Notice that we’re loading the driver anonymously, aliasing its package qualifier to _ so none of its exported names are visible to our code. Under the hood, the driver registers itself as being available to the database/sql package, but in general nothing else happens with the exception that the init function is run.

Now you’re ready to access a database.

Now that you’ve loaded the driver package, you’re ready to create a database object, a sql.DB.

To create a sql.DB, you use sql.Open(). This returns a *sql.DB:

func main() {
db, err := sql.Open("mysql",
"user:password@tcp(127.0.0.1:3306)/hello")
if err != nil {
log.Fatal(err)
}
defer db.Close()
}

In the example shown, we’re illustrating several things:

The first argument to sql.Open is the driver name. This is the string that the driver used to register itself with database/sql, and is conventionally the same as the package name to avoid confusion. For example, it’s mysql for github.com/go-sql-driver/mysql. Some drivers do not follow the convention and use the database name, e.g. sqlite3 for github.com/mattn/go-sqlite3 and postgres for github.com/lib/pq.
The second argument is a driver-specific syntax that tells the driver how to access the underlying datastore. In this example, we’re connecting to the “hello” database inside a local MySQL server instance.
You should always check and handle errors returned from all 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.

Perhaps counter-intuitively, sql.Open() does not establish any connections to the database, nor does it validate driver connection parameters. Instead, it simply prepares the database abstraction for later use. The first actual connection to the underlying datastore will be established lazily, when it’s needed for the first time. If you want to check right away that the database is available and accessible (for example, check that you can establish a network connection and log in), use db.Ping() to do that, and remember to check for errors:

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

Although it’s idiomatic to Close() the database when you’re finished with it, the sql.DB object is designed to be long-lived. 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. And don’t Open() and Close() from a short-lived function. Instead, pass the sql.DB into that short-lived function as an argument.

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

Fetching Data from the Database

Let’s take a look at an example of how to query the database, working with results. We’ll query the users table for a user whose id is 1, and print out the user’s id and name. We will assign results to variables, a row at a time, with rows.Scan().

Here’s what’s happening in the above code:

We’re using db.Query() to send the query to the database. We check the error, as usual.
We defer rows.Close(). This is very important.
We iterate over the 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.

This is pretty much the only way to do it in Go. You can’t get a row as a map, for example. That’s because everything is strongly typed. You need to create variables of the correct type and pass pointers to them, as shown.

A couple parts of this are easy to get wrong, and can have bad consequences.

rows.Next() indicates whether the next row from result set is available, and will return true until either result set is exhausted or an error has occurred during fetching the data. For this reason you should always check for an error at the end of the for rows.Next() loop (this is done calling rows.Err()). If there’s an error during the loop, you need to know about it. Don’t just assume that the loop iterates until you’ve processed all the rows.
Second, as long as there’s an open result set (represented by rows), the underlying connection is busy and can’t be used for any other query until rows.Close() is called. That means it’s not available in the connection pool. The nice thing about database/sql is that it will implicitly call rows.Close() for you, when rows.Next() returns false, but if you exit the loop prematurely, it is your responsibility to close the rows, otherwise the connection will be left busy and unavailable to other operations, leading to a connection leak. Thus, as a rule of thumb, you should always defer rows.Close(), to avoid connection leak and running out of resources.
rows.Close() is a harmless no-op if it’s already closed, so it’s OK to call it multiple times. Notice, however, that we check the error from db.Query() first, and only defer rows.Close() if there isn’t an error, in order to avoid a runtime panic (e.g. when error is returned from db.Query() method, the rows object will be nil).

How Scan() Works

When you iterate over rows and scan them into destination variables, Go performs data type conversions work for you, behind the scenes. It is based on the type of the destination variable. Being aware of this can clean up your code and help avoid repetitive work.

For example, suppose you select some rows from a table that is defined with string columns, such as VARCHAR(45) or similar. You happen to know, however, that the table always contains numbers. If you pass a pointer to a string, Go will copy the bytes into the string. Now you can use strconv.ParseInt() or similar to convert the value to a number. You’ll have to check for errors in the SQL operations, as well as errors parsing the integer. This is messy and tedious.

Or, you can just pass Scan() a pointer to an integer. Go will detect that and call strconv.ParseInt() for you. If there’s an error in conversion, the call to Scan() will return it. Your code is neater and smaller now. This is the recommended way to use database/sql.

Single-Row Queries

If a query returns at most one row, you can use a shortcut around some of the lengthy boilerplate code:

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

Use Exec(), to accomplish an INSERT, UPDATE, DELETE, or another statement (probably database-specific) that doesn’t return rows. The following example shows how to insert a row and inspect metadata about the operation:

Executing the statement produces a sql.Result that gives access to statement metadata: the last inserted ID and the number of rows affected.

What if you don’t care about the result? What if you just want to execute a statement and check if there were any errors, but ignore the result? Wouldn’t the following two statements do the same thing?

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

The answer is no. They do not do the same thing, and you should never use Query() like this. The Query() will return a sql.Rows, which reserves a database connection until the sql.Rows is closed. Since there might be unread data (e.g. more data rows), the connection can not be used. In the example above, the connection will never be released again. This anti-pattern is therefore a good way to run out of resources (too many connections, for example).

We explored idiomatic ways to work with SQL databases in Go programming language using standard database/sql package. Advanced concepts of Transactions and Prepared Statements were left aside for brevity.

If you require more details on how to work with Transactions and Prepared statements in database/sql, I encourage you to check out the excellent golang SQL tutorial: http://go-database-sql.org/index.html.