Working with SQL databases

The generated codebase will be configured to work with either SQLite3, PostgreSQL, MySQL, or no database, depending on the options you choose. The following database drivers are used:

Connecting to the database

When using a database, you will need to provide a DSN (data source name) – also sometimes referred to as a connection string – as a configuration setting to tell the application how to connect to it.

If you're using command-line flags for configuration, pass your DSN using the --db-dsn flag when starting the application. For example:

$ go run ./cmd/api --db-dsn="user:pass@localhost:port/db"

If you're using environment variables, set it in DB_DSN. For example:

$ export DB_DSN="user:pass@localhost:port/db"

The exact DSN format depends on the database and driver you are using, so please refer to the documentation for the specific drivers linked above. In general, however, you should use the following formats:

Important: If you're using PostgreSQL or MySQL, do not prefix the DSN with postgres:// or mysql://.

Alternatively, you can change the default configuration setting for the DSN in the cmd/api/main.go file.

Configuring the database connection pool

The database connection pool is initialized in the New() function in internal/database/db.go. This returns a DB type which wraps the underlying pool.

By default, the connection pool is configured using the settings below. These are reasonable defaults for most applications, but you can change them if needed in internal/database/db.go.

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxIdleTime(5*time.Minute)
db.SetConnMaxLifetime(2*time.Hour)

Working with the database

The initialized DB type is available in handlers, helpers and middleware as a dependency in the application struct. If you want, you can access the database and carry out queries directly using the regular database/sql methods like so:

func (app *application) exampleHandler(w http.ResponseWriter, r *http.Request) {
    ...

    _, err := app.db.Exec("INSERT INTO people (name, age) VALUES ($1, $2)", "Alice", 28)
    if err != nil {
        app.serverError(w, r, err)
        return
    }
    
    ...
}

Note: If you're using MySQL (rather than SQLite3 or PostgreSQL), your SQL queries should use ? as placeholder parameters instead of $N notation. For example: INSERT INTO people (name, age) VALUES (?, ?).

Generally though, it's recommended to isolate database logic in the internal/database package and extend the DB type with your own methods. For example, you could create a internal/database/people.go file like this:

type Person struct {
	ID   int
	Name string
	Age  int
}

func (db *DB) CreatePerson(person Person) error {
	_, err := db.Exec("INSERT INTO people (name, age) VALUES ($1, $2)", person.Name, person.Age)
	return err
}

You can then call this from your handlers:

func (app *application) exampleHandler(w http.ResponseWriter, r *http.Request) {
	...

	person := Person{
		Name: "Alice",
		Age:  28,
	}

	err := app.db.CreatePerson(person)
	if err != nil {
		app.serverError(w, r, err)
		return
	}

	...
}

Using database timeouts

When running SQL queries, it's good practice to pass a context.Context with a timeout to prevent long-running queries. The above example would be better written like so:

type Person struct {
	ID   int
	Name string
	Age  int
}

func (db *DB) CreatePerson(person Person) error {
    ctx, cancel := context.WithTimeout(context.Background(), defaultTimeout)
	defer cancel()

	_, err := db.ExecContext(ctx, "INSERT INTO people (name, age) VALUES ($1, $2)", person.Name, person.Age)
	return err
}

The defaultTimeout value is defined at the top of internal/database/db.go and is set to 3 seconds. Feel free to change this if you want.

Using sqlx shortcuts

The DB type is configured to support jmoiron/sqlx, so you have access to the full range of sqlx extensions in addition to the standard database/sql methods. For example, you can write queries like this:

type Person struct {
	ID   int    `db:"id"`
	Name string `db:"name"`
	Age  int    `db:"age"`
}

func (db *DB) CreatePerson(person Person) error {
	ctx, cancel := context.WithTimeout(context.Background(), defaultTimeout)
	defer cancel()

	_, err := db.NamedExecContext(ctx, "INSERT INTO people (name, age) VALUES (:name, :age)", person)
	return err
}

func (db *DB) GetPerson(id int) (Person, error) {
	ctx, cancel := context.WithTimeout(context.Background(), defaultTimeout)
	defer cancel()

	var person Person

	err := db.GetContext(ctx, &person, "SELECT id, name, age FROM people WHERE id = $1", id)
	return person, err
}

Please see the official documentation for sqlx for full details about the features it supports.