Opening a database handle - The Go Programming Language
Why Go arrow_drop_down
Case Studies
Common problems companies solve with Go
Use Cases
Stories about how and why companies use Go
Security Policy
How Go can help keep you secure by default
Learn
Docs arrow_drop_down
Effective Go
Tips for writing clear, performant, and idiomatic Go code
Go User Manual
A complete introduction to building software with Go
Standard library
Reference documentation for Go's standard library
Release Notes
Learn what's new in each Go release
Packages
Community arrow_drop_down
Recorded Talks
Videos from prior events
Meetups
open_in_new
Meet other local Go developers
Conferences
open_in_new
Learn and network with Go developers from around the world
Go blog
The Go project's official blog.
Go project
Get help and stay informed from Go
Get connected
Why Go navigate_next
navigate_beforeWhy Go
Case Studies
Use Cases
Security Policy
Learn
Docs navigate_next
navigate_beforeDocs
Effective Go
Go User Manual
Standard library
Release Notes
Packages
Community navigate_next
navigate_beforeCommunity
Recorded Talks
Meetups
open_in_new
Conferences
open_in_new
Go blog
Go project
Get connected
Opening a database handle
The database/sql package simplifies
database access by reducing the need
for you to manage connections. Unlike many data access APIs, with
database/sql you don’t explicitly open a connection, do work, then close
the connection. Instead, your code opens a database handle that represents
a connection pool, then executes data access operations with the handle,
calling a Close method only when needed to free resources, such as those
held by retrieved rows or a prepared statement.
In other words, it’s the database handle, represented by an
sql.DB, that
handles connections, opening and closing them on your code’s behalf. As your
code uses the handle to execute database operations, those operations have
concurrent access to the database. For more, see
Managing connections.
Note: You can also reserve a database connection. For more
information, see
Using dedicated connections.
In addition to the APIs available in the database/sql package, the Go
community has developed drivers for all of the most common (and many uncommon)
database management systems (DBMSes).
When opening a database handle, you follow these high-level steps:
Locate a driver.
A driver translates requests and responses between your Go code and the
database. For more, see Locating and importing a database driver.
Open a database handle.
After you’ve imported the driver, you can open a handle for a specific
database. For more, see Opening a database handle.
Confirm a connection.
Once you’ve opened a database handle, your code can check that a
connection is available. For more, see Confirming a connection.
Your code typically won’t explicitly open or close database connections – that’s
done by the database handle. However, your code should free resources it
obtains along the way, such as an sql.Rows containing query results. For
more, see Freeing resources.
Locating and importing a database driver
You’ll need a database driver that supports the DBMS you’re using. To locate
a driver for your database, see SQLDrivers.
To make the driver available to your code, you import it as you would
another Go package. Here’s an example:
import "github.com/go-sql-driver/mysql"
Note that if you’re not calling any functions directly from the driver
package –- such as when it’s being used implicitly by the sql package –
you’ll need to use a blank import, which prefixes the import path with an
underscore:
import _ "github.com/go-sql-driver/mysql"
Note: As a best practice, avoid using the database driver’s own API
for database operations. Instead, use functions in the database/sql
package. This will help keep your code loosely coupled with the DBMS,
making it easier to switch to a different DBMS if you need to.
Opening a database handle
An sql.DB database handle provides the ability to read from and write to a
database, either individually or in a transaction.
You can get a database handle by calling either sql.Open (which takes a
connection string) or sql.OpenDB (which takes a driver.Connector). Both
return a pointer to an sql.DB.
Note: Be sure to keep your database credentials out of your Go source.
For more, see Storing database credentials.
Opening with a connection string
Use the sql.Open function when you
want to connect using a connection string. The format for the string will vary
depending on the driver you’re using.
Here’s an example for MySQL:
db, err = sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/jazzrecords")
if err != nil {
log.Fatal(err)
However, you’ll likely find that capturing connection properties in a more
structured way gives you code that’s more readable. The details will vary by
driver.
For example, you could replace the preceding example with the following, which
uses the MySQL driver’s Config
to specify properties and its
FormatDSN method
to build a connection string.
// Specify connection properties.
cfg := mysql.Config{
User: username,
Passwd: password,
Net: "tcp",
Addr: "127.0.0.1:3306",
DBName: "jazzrecords",
// Get a database handle.
db, err = sql.Open("mysql", cfg.FormatDSN())
if err != nil {
log.Fatal(err)
Opening with a Connector
Use the sql.OpenDB function when
you want to take advantage of driver-specific connection features that aren’t
available in a connection string. Each driver supports its own set of
connection properties, often providing ways to customize the connection request
specific to the DBMS.
Adapting the preceding sql.Open example to use sql.OpenDB, you could
create a handle with code such as the following:
// Specify connection properties.
cfg := mysql.Config{
User: username,
Passwd: password,
Net: "tcp",
Addr: "127.0.0.1:3306",
DBName: "jazzrecords",
// Get a driver-specific connector.
connector, err := mysql.NewConnector(&cfg)
if err != nil {
log.Fatal(err)
// Get a database handle.
db = sql.OpenDB(connector)
Handling errors
Your code should check for an error from attempting to create a handle, such
as with sql.Open. This won’t be a connection error. Instead, you’ll get an
error if sql.Open was unable to initialize the handle. This could happen,
for example, if it’s unable to parse the DSN you specified.
Confirming a connection
When you open a database handle, the sql package may not create a new
database connection itself right away. Instead, it may create the connection
when your code needs it. If you won’t be using the database right away and
want to confirm that a connection could be established, call
Ping or
PingContext.
Code in the following example pings the database to confirm a connection.
db, err = sql.Open("mysql", connString)
// Confirm a successful connection.
if err := db.Ping(); err != nil {
log.Fatal(err)
Storing database credentials
Avoid storing database credentials in your Go source, which could expose the
contents of your database to others. Instead, find a way to store them in a
location outside your code but available to it. For example, consider a
secret keeper app that stores credentials and provides an API your code can
use to retrieve credentials for authenticating with your DBMS.
One popular approach is to store the secrets in the environment before the
program starts, perhaps loaded from a secret manager, and then your Go program
can read them using os.Getenv:
username := os.Getenv("DB_USER")
password := os.Getenv("DB_PASS")
This approach also lets you set the environment variables yourself for local
testing.
Freeing resources
Although you don’t manage or close connections explicitly with the
database/sql package, your code should free resources it has obtained when
they’re no longer needed. Those can include resources held by an sql.Rows
representing data returned from a query or an sql.Stmt representing a
prepared statement.
Typically, you close resources by deferring a call to a Close function so
that resources are released before the enclosing function exits.
Code in the following example defers Close to free the resource held by
sql.Rows.
rows, err := db.Query("SELECT * FROM album WHERE artist = ?", artist)
if err != nil {
log.Fatal(err)
defer rows.Close()
// Loop through returned rows.
Why Go
Use Cases
Case Studies
Get Started
Playground
Tour
Stack Overflow
Help
Packages
Standard Library
About
Download
Blog
Issue Tracker
Release Notes
Brand Guidelines
Code of Conduct
Connect
Twitter
GitHub
Slack
r/golang
Meetup
Golang Weekly
Copyright
Terms of Service
Privacy Policy
Report an Issue