Creating a database connection pool

Now that the MySQL database is all set up and we’ve got a driver installed, the natural next step is to connect to the database from our web application.

To do this we need Go’s sql.Open() function, which you use a bit like this:

// The sql.Open() function initializes a new sql.DB object, which is essentially a
// pool of database connections.
db, err := sql.Open("mysql", "web:pass@/snippetbox?parseTime=true")
if err != nil {
    ...
}

There are a few things about this code to explain and emphasize:

• The first parameter to sql.Open() is the driver name and the second parameter is the data source name (sometimes also called a connection string or DSN) which describes how to connect to your database.

• The format of the data source name will depend on which database and driver you’re using. Typically, you can find information and examples in the documentation for your specific driver. For the driver we’re using you can find that documentation here.

• The parseTime=true part of the DSN above is a driver-specific parameter which instructs our driver to convert SQL TIME and DATE fields to Go time.Time objects.

• 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 the connections in this pool 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 initialize 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 HTTP handler itself — it would be a waste of memory and network resources.

Using it in our web application

Let’s look at how to use sql.Open() in practice. Open up your main.go file and add the following code:

package main

import (
    "database/sql" // New import
    "flag"
    "log/slog"
    "net/http"
    "os"

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

...

func main() {
    addr := flag.String("addr", ":4000", "HTTP network address")
    // Define a new command-line flag for the MySQL DSN string.
    dsn := flag.String("dsn", "web:pass@/snippetbox?parseTime=true", "MySQL data source name")
    flag.Parse()

    logger := slog.New(slog.NewTextHandler(os.Stdout, nil))

    // To keep the main() function tidy I've put the code for creating a connection
    // pool into the separate openDB() function below. We pass openDB() the DSN
    // from the command-line flag.
    db, err := openDB(*dsn)
    if err != nil {
        logger.Error(err.Error())
        os.Exit(1)
    }

    // We also defer a call to db.Close(), so that the connection pool is closed
    // before the main() function exits.
    defer db.Close()

    app := &application{
        logger: logger,
    }

    logger.Info("starting server", "addr", *addr)

    // Because the err variable is now already declared in the code above, we need
    // to use the assignment operator = here, instead of the := 'declare and assign'
    // operator.
    err = http.ListenAndServe(*addr, app.routes())
    logger.Error(err.Error())
    os.Exit(1)
}

// The openDB() function wraps sql.Open() and returns a sql.DB connection pool
// for a given DSN.
func openDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

    err = db.Ping()
    if err != nil {
        db.Close()
        return nil, err
    }

    return db, nil
}

There’re a few things about this code which are interesting:

• Notice how the import path for our driver is prefixed with an underscore? This is because our main.go file doesn’t actually use anything in the mysql package. So if we try to import it normally the Go compiler will raise an error. However, we need the driver’s init() function to run so that it can register itself with the database/sql package. The trick to getting around this is to alias the package name to the blank identifier, like we are here. This is standard practice for most of Go’s SQL drivers.

• The sql.Open() function doesn’t actually create any connections, all it does is initialize 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. If there is an error, we call db.Close() to close the connection pool and return the error.

• Going back to the main() function, at this moment in time the call to defer db.Close() is a bit superfluous. Our application is only ever terminated by a signal interrupt (i.e. Ctrl+C) or by os.Exit(1). In both of those cases, the program exits immediately and deferred functions are never run. But making sure to always close the connection pool is a good habit to get into, and it could be beneficial in the future if you add a graceful shutdown to your application.

Testing a connection

Make sure that the file is saved, and then try running the application. If everything has gone to plan, the connection pool should be established and the db.Ping() method should be able to create a connection without any errors. All being well, you should see the normal starting server log message like so:

$ go run ./cmd/web
time=2024-03-18T11:29:23.000+00:00 level=INFO msg="starting server" addr=:4000

If the application fails to start and you get an "Access denied..." error message like below, then the problem probably lies with your DSN. Double-check that the username and password are correct, that your database users have the right permissions, and that your MySQL instance is using standard settings.

$ go run ./cmd/web
time=2024-03-18T11:29:23.000+00:00 level=ERROR msg="Error 1045 (28000): Access denied for user 'web'@'localhost' (using password: YES)"
exit status 1

Tidying the go.mod file

Now that our code is actually importing the github.com/go-sql-driver/mysql driver, you can run the go mod tidy command to tidy your go.mod file and remove any unnecessary // indirect annotations.

$ go mod tidy

Once you’ve done that, your go.mod file should now look like below — with github.com/go-sql-driver/mysql listed as a direct dependency and filippo.io/edwards25519 continuing to be an indirect dependency.

module snippetbox.alexedwards.net

go 1.23.0

require github.com/go-sql-driver/mysql v1.8.1

require filippo.io/edwards25519 v1.1.0 // indirect