# How to Work with SQL Databases in Go
SQL, or Structured Query Language, is the standard for managing and manipulating
relational databases. In Go,
[the built-in database/sql](https://pkg.go.dev/database/sql) package provides a
convenient way to interact with SQL databases, allowing you to easily perform
tasks such as inserting, updating, and querying data.
The article will discuss the use of the database/sql package in Go to connect to
a database, execute SQL statements, and handle results. It will also
cover topics like prepared statements, transactions, and connection
pooling. The goal of the article is to provide a solid understanding of how to
work with SQL databases in Go by the end of it.
## Prerequisites
To follow along with this article, ensure that you have the latest version of Go
installed on your machine. If you are missing Go, you can
[find the installation instructions here](https://go.dev/doc/install).
You'll also need the latest version of PostgreSQL installed locally. Ensure to
[follow the instructions](https://www.postgresql.org/download/) specific to your
operating system.
This article also assumes that you have a sufficient understanding of SQL to
follow along. Regardless, we have prepared a primer on databases and SQL for
your reference.
[summary]
## Side note: Track database issues without staring at terminal output
Once your Go app starts talking to PostgreSQL via `database/sql`, you can ship its logs to [Better Stack](https://betterstack.com/logs) to quickly spot connection issues, failed queries, and timeouts without re running commands over and over.
[/summary]
## Setting up the demo project
Once you've installed PostgreSQL on your machine, create a new database called
`gda` through the `psql` interface. You can
[refer to this resource for how to accomplish that](https://www.tutorialspoint.com/postgresql/postgresql_create_database.htm).
When you type `\l` into the `psql` terminal, you should observe an entry for
`gda` as shown below:
Afterward, clone the
[demo repository](https://github.com/betterstack-community/go-database-access)
to access the code that demonstrates the concepts introduced in this article.
```command
git clone https://github.com/betterstack-community/go-database-access.git
```
```command
cd go-database-access/
```
A simple CLI is provided to access the examples shown in this article. To gain
access to this CLI, build the project using the command below. Afterward, you'll
observe that a `gda` binary is now present at the root of the project.
```command
go build
```
Next, let's configure the [connection string](#connecting-to-the-database) to
access the `gda` database created earlier. Rename the `.env.example` file
provided in the root of the repository to `.env` and change the `CONN_STR`
environment variable to your intended connection string.
```text
[label .env]
[highlight]
CONN_STR="postgres://:@:/gda?sslmode=disable"
[/highlight]
```
For example, if your username and password is `postgres`, and your postgres
instance is running at localhost:5432 (the default), you can use the following
connection string:
```text
postgres://postgres:postgres@localhost:5432/gda?sslmode=disable
```
Once you've updated the PostgreSQL connection string in the `.env` file, enter
the following command to automatically set up and populate the `gda` database:
```command
./gda setup
```
The above command populates the `gda` database with the following sample data
(note that the `id`s will be different on your instance of the database as they
are randomly generated):
**Customer table**:
| id | name | allergy |
| ------------------------------------ | ----------- | ------- |
| b1098ec4-6a1f-4847-9af0-fa1d67809cf8 | John Doe | |
| 0bc82f83-3cb2-496e-b0a0-149ccd8ec740 | Mary Anne | Cheese |
| d3f63bfa-e215-4d4d-bf67-6fb15fb95ef4 | Jason Borne | |
**Order table**:
| id | food | quantity | timestamp | customer_id |
| ------------------------------------ | --------------- | -------- | -------------------------- | ------------------------------------ |
| 671db26b-57ed-4778-8d9c-1d2be229f284 | Pie | 2 | 2023-01-12 20:17:12.253902 | b1098ec4-6a1f-4847-9af0-fa1d67809cf8 |
| dfd8b36f-8b6a-4ac8-83ac-d0ffefa0c223 | Soup of the Day | 1 | 2023-01-12 20:17:12.253902 | b1098ec4-6a1f-4847-9af0-fa1d67809cf8 |
| 2c5ad469-e581-4723-b855-f6201bef87f2 | Pudding | 2 | 2023-01-12 20:17:12.253902 | b1098ec4-6a1f-4847-9af0-fa1d67809cf8 |
| 4645160d-d7fb-4dfc-94b9-d6039149df51 | Fish and Chips | 1 | 2023-01-12 20:17:12.256816 | 0bc82f83-3cb2-496e-b0a0-149ccd8ec740 |
| e29ea776-077a-41bb-8c8c-8a6851e2d34d | Soup of the Day | 1 | 2023-01-12 20:17:12.256816 | 0bc82f83-3cb2-496e-b0a0-149ccd8ec740 |
| 6ef32bdf-a134-4a7d-bed0-dc2fe1a0020a | Pie | 3 | 2023-01-12 20:17:12.257334 | d3f63bfa-e215-4d4d-bf67-6fb15fb95ef4 |
| 16fef766-ff68-4f73-8a92-27f34fa4e09b | Pudding | 3 | 2023-01-12 20:17:12.257334 | d3f63bfa-e215-4d4d-bf67-6fb15fb95ef4 |
The following is the basic
[Entity-Relationship Diagram (ERD)](https://www.smartdraw.com/entity-relationship-diagram/)
that is used for the examples in this article.
## A brief primer on databases
According to [Oracle](https://www.oracle.com/sg/database/what-is-database/), a
database is an organized collection of data stored electronically in a computer
system. There are two primary types of databases: relational and non-relational.
Relational databases are structured so the data that enters and resides in
tables, rows, and columns so that the relationship between each data point is
clearly defined. Non-relational databases, on the other hand, do not have such
rigid structures.
This article will explore relational databases only and focus on the most common
interfacing language used to write and query data in such databases (SQL).
### What is SQL?
Structured Query Language (SQL) is a domain-specific language used to work with
relational databases. There are many dialects of SQL, but for this article, we
are focusing on [PostgreSQL](https://www.postgresql.org), a widely used
open-source dialect.
### Common queries
| Query | Usage | Example |
| -------------- | ------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| `CREATE TABLE` | Creates a new database table | `CREATE TABLE order (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), food TEXT NOT NULL, quantity INTEGER NOT NULL timestamp DATE DEFAULT now());` |
| `SELECT` | Selects rows of data from a given table following additional constraints by `WHERE` and `ORDER BY` | `SELECT * FROM order WHERE food = 'Pie' ORDER BY timestamp;` |
| `INSERT` | Inserts a new row of data into a given table | `INSERT INTO order (food, quantity) VALUES ('Pie', 15);` |
| `UPDATE` | Updates a row/set of rows of a given table that match a given constraint, specified by `WHERE` | `UPDATE order SET quantity = 16 WHERE food = 'Pie';` |
| `DELETE` | Deletes a row/set of rows of a given table that match a given constraint, specified by `WHERE` | `DELETE FROM order WHERE food = 'Pie';` |
| `WHERE` | Specifies a constraint; commonly used with `SELECT`, `UPDATE` and `DELETE` | See above |
| `ORDER BY` | Orders the results of a `SELECT` by a given column/set of columns in ascending (default) or descending order | See above |
| `RETURNING` | Returns the results of a query that might not typically return results like `INSERT`, `UPDATE`, and `DELETE` | `INSERT INTO order (food, quantity) VALUES ('Chicken Soup', 1) RETURNING *;` |
This list is non-exhaustive. For more information, visit the
[PostgreSQL tutorial for a detailed guide on PostgreSQL](https://www.postgresqltutorial.com).
You can also refer to the
[official PostgreSQL documentation](https://www.postgresql.org/docs/15/index.html)
for more information about PostgreSQL’s syntax. To understand database design,
refer to
[Microsoft’s guide to database design basics](https://support.microsoft.com/en-us/office/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5).
### Transactions
Due to the concurrent nature of reads and writes in databases, a series of
related queries should be executed as a batch (a.k.a. transaction). Transactions
minimize data inconsistencies caused by external reads or writes, and they
preserve the integrity of the database when executing a series of related
queries.
A transaction is committed if, and only if, every query within the transaction
is successful. If any queries fail, the entire transaction is aborted to avoid
creating inconsistencies. Formally, this process is referred to as "rolling back
a transaction". For more information about transactions, refer
to [Linode’s primer on transactions](https://www.linode.com/docs/guides/a-primer-on-sql-transactions/).
With the basics out of the way, let's start learning about working with SQL in
Go programs.
## Database access with `database/sql`
Go provides a built-in database access package (`database/sql`) which provides
the necessary tools for working with SQL databases. It is the foundation for
many other database access tools that we will be discussing later on in
tutorial. Therefore, a firm understanding of how the `database/sql` package
works is a must!
The `database/sql` package exposes a `sql.DB` type that provides a layer of
abstraction over the SQL database. However, it does not directly interact with
the underlying database; that is the role of the database drivers, which we will
discuss shortly.
Through `database/sql`, uniform database access is achieved, regardless of the
underlying database, which makes it easy to switch from one SQL dialect to
another if necessary.
The following is an illustration of how `database/sql` works.
For more information about how `database/sql` works, please
[refer to its documentation](https://pkg.go.dev/database/sql).
## Database drivers in Go
Before you can access whatever database you're using in your Go program, a
corresponding database driver must be installed. This driver is the concrete
link between your Go code and the database.
A list of database drivers for various SQL dialects can be found on the
[official Go GitHub Wiki](https://github.com/golang/go/wiki/SQLDrivers). To
install a driver in your project, use the following command:
```command
go get
```
If you're working with the demo project, you don't need to run the command able
as it already includes [pgx](https://github.com/jackc/pgx), one of the most
popular PostgreSQL drivers. Since `pgx` is already installed, you can proceed
with connecting to the `gda` database in the next section.
## Connecting to the database
Every database, regardless of language or platform, has a standard format for
its connection string. For PostgreSQL, the connection string format is shown
below:
```text
postgresql://:@:/
```
For instance, the connection string to the `gda` database that you created
earlier would be:
```text
postgresql://postgres:root@localhost:5432/gda?sslmode=disable
```
It is considered a best practice to store this connection string in the
execution environment to avoid leaking your database credentials in the code.
Note that our connection string includes an additional query parameter:
`sslmode=disable`. It is used to disable SSL when connecting to the database
since we are accessing it locally, but you should definitely enable it in
production to ensure that your database connections are secure.
Our demo project provides a utility function for retrieving the connection
string from the environment found in the `internal/utility/utility.go` file:
```go
[label internal/utility/utility.go]
func ConnectionString() string {
connStr, status := os.LookupEnv("CONN_STR")
if !status {
log.Fatalln("Missing environment variable CONN_STR")
}
return connStr
}
```
To connect to the `gda` database in your Go code, the `sql.Open()` function
should be used as shown below:
```go
[label example/connecting.go]
package example
import (
"database/sql"
[highlight]
_ "github.com/jackc/pgx/v5/stdlib"
[/highlight]
"woojiahao.com/gda/internal/utility"
)
func Connect() {
[highlight]
connStr := utility.ConnectionString()
db, err := sql.Open("pgx", connStr)
[/highlight]
if err != nil {
log.Fatalf("Unable to connect to database because %s", err)
}
[highlight]
if err = db.Ping(); err != nil {
log.Fatalf("Cannot ping database because %s", err)
}
[/highlight]
log.Println("Successfully connected to database and pinged it")
}
```
The `pgx` driver is imported and prefixed with a blank identifier solely for its
side effect of registering the driver without importing any other functions.
This allows us to connect to the database. Do note that this step is only
required once per package.
Once the driver is registered, the `sql.Open()` method is subsequently used to
connect to the database, and the `Ping()` method is used to verify that the
database connection is alive.
Notice that we did not have to use `defer` to close the database connection.
This is intentional as according to the
[official documentation of the `Open()` function](https://golang.org/pkg/database/sql/#Open),
the returned `*sql.DB` is safe for concurrent use and it does not need to be
closed manually.
This example can be tested by running the following command:
```command
./gda example connect
```
```text
[output]
2023/01/10 12:24:29 Successfully connected to database and pinged it
```
Now that you've established a successful database connection, you can now start
working with the database using the methods provided on the `sql.DB` type as
you'll see in the following sections.
## Querying for data
One of the most common tasks for any program interacting with a database is to
query for data that is already present in the database. In Go, you can query for
data through the `QueryRowContext()` and `QueryContext()` methods on the
`sql.DB` type in conjunction with the `SELECT` SQL query.
### Single row queries
The `QueryRowContext()` method retrieves a single row from the database. This is
useful when only a single result is needed, such as when you are query for user
record through their unique user id.
```go
[label example/querying.go]
func SingleRowQuery() {
// Connecting to database...
[highlight]
var johnDoeId string
row := db.QueryRowContext(context.TODO(), `SELECT id FROM customer WHERE name = 'John Doe';`)
err = row.Scan(&johnDoeId)
switch {
case err == sql.ErrNoRows:
log.Fatalf("Unable to retrieve anyone called 'John Doe'")
case err != nil:
log.Fatalf("Database query failed because %s", err)
default:
log.Printf("John Doe has an ID of %s", johnDoeId)
}
[/highlight]
}
```
The `QueryRowContext()` method returns a `*sql.Row` type, which has a `Scan()`
method to map the values returned from the database to variables. In our case,
we are mapping the result of the `SELECT` statement to the `johnDoeId` variable
since we expect a string result.
When we successfully scan the row returned from the database, the variable
`johnDoeId` will hold the `id` returned from the database. Notice that we passed
a pointer reference of `johnDoeId` to `Scan()`. If a pointer reference is
unused, there will be an error.
Run the command below to see the code in action:
```command
./gda example single
```
```text
[output]
2022/12/22 11:56:58 John Doe has an ID of c9d36f13-c508-4cd0-a849-5022b94a9c86
```
There is a special type of error that is returned when `Scan()` is called but
the database returns no rows: `sql.ErrNoRows`. The suggested `switch` structure
is a typical pattern employed when using `QueryRowContext()` to handle the
aforementioned error properly .
You can see this in action by changing the SQL statement from "John Doe" to "Joe
Doe". When you build the binary and execute the `single` command once more, you
will observe the following result:
```command
go build && ./gda example single
```
```text
[output]
2022/12/22 12:16:37 Unable to retrieve anyone called 'Joe Doe'
```
### Multiple rows queries
Multiple rows of data can be retrieved from the database through the
`QueryContext()` method. This is useful when your `SELECT` query might return
multiple rows that match a given condition.
```go
[label example/querying.go]
func MultiRowQuery() {
// Connecting to database...
orderQuantities := make(map[string]int)
[highlight]
rows, err := db.QueryContext(context.TODO(), `SELECT food, sum(quantity) FROM "order" GROUP BY food;`)
[/highlight]
if err != nil {
log.Fatalf("Database query failed because %s", err)
}
[highlight]
defer rows.Close()
for rows.Next() {
var food string
var totalQuantity int
err = rows.Scan(&food, &totalQuantity)
if err != nil {
log.Fatalf("Failed to retrieve row because %s", err)
}
orderQuantities[food] = totalQuantity
}
if err := rows.Err(); err != nil {
log.Fatalf("Error encountered while iterating over rows: %s", err)
}
[/highlight]
log.Printf("Total order quantity per food %v", orderQuantities)
}
```
Unlike thee `QueryRowContext()` method, `QueryContext()` returns both
`*sql.Rows` and the `error` that occurred during the query (if any). After
ascertaining that no error occurred, you can proceed to access the rows of data
through the pattern prescribed in the example above. The `Scan()` method is
still available, and is used to map every row to a given set of values, as seen
in the example.
```command
./gda example multi
```
```text
[output]
2022/12/22 12:27:18 Total order quantity per food map[Fish and Chips:1 Pie:5 Pudding:5 Soup of the Day:2]
```
### Specifying contexts
Aside from the `QueryRowContext()` and the `QueryContext()` methods, we also
have the `QueryRow()` and `Query()` methods which perform exactly the same
functions except that the latter two do not allow you to specify a context.
In Go, contexts carry deadline and cancellation signals (among others) across
API boundaries and between processes so that you can control how long a task is
allowed to take.
When working with databases, this context can be used to inform the database
service to cancel a query if too much time has elapsed to prevent performance
degradation.
Imagine the following: you are building an API, and an endpoint requires access
to the database. Unfortunately, the query for the database takes an abnormally
long time to return any results. Due to this delay, the client has to wait for
an equally abnormal amount of time for the API’s response. With contexts,
however, you can instruct the database to cancel a query if too much time has
elapsed to avoid forcing the client to wait.
Therefore, you should eschew the usage of the `QueryRow()` and `Query()` methods
in favor of their context-aware alternatives. In the examples above, the
`context.TODO()` method is used, which returns an empty context that allows the
query to run for as long as it needs. However, we will demonstrate query
timeouts using contexts later on.
### Parameterized queries
A common practice and defense against SQL injection is using parameterized
queries where the parameters of such a query are replaced with placeholders. The
necessary arguments are subsequently injected into these placeholders at
execution time.
The `database/sql` package supports parameterized queries as part of its core
API. We can modify our example for `QueryRowContext()` to use a parameterized
query instead of hardcoding "John Doe" as the target. This way, we can design a
function that is flexible with finding a user with any given name.
```go
[label example/querying.go]
func ParameterisedQuery(target string) {
// Connecting to database...
var id string
[highlight]
row := db.QueryRowContext(context.TODO(), `SELECT id FROM customer WHERE name = $1;`, target)
[/highlight]
err = row.Scan(&id)
switch {
case err == sql.ErrNoRows:
log.Fatalf("Unable to retrieve anyone called %s", target)
case err != nil:
log.Fatalf("Database query failed because %s", err)
default:
log.Printf("%s has an ID of %s", target, id)
}
}
```
The arguments of a parameterized query are supplied after the query string in
the exact order specified in the query.
```command
./gda example parameterised
```
```text
[output]
2022/12/22 13:45:32 Mary Anne has an ID of 587208e9-5287-4b23-9ec4-6ea1b8c82144
```
Note that the syntax for placeholders differs between SQL dialects. For
PostgreSQL, placeholders are specified as: `$1`, `$2`, etc, but others might use
`?` to signify placeholders. Ensure to use the appropriate placeholder syntax
for your SQL dialect of choice.
### Handling nullable types
Some columns in your database might allow `NULL` values. To properly scan and
assign such values in your code, you can use the null types provided by the
`database/sql` package.
```go
[label example/querying.go]
func NullTypeQuery() {
// Connecting to database...
[highlight]
var allergies []sql.NullString
[/highlight]
rows, err := db.QueryContext(context.TODO(), `SELECT allergy FROM customer;`)
if err != nil {
log.Fatalf("Unable to retrieve customer allergies because %s", err)
}
for rows.Next() {
[highlight]
var allergy sql.NullString
[/highlight]
err = rows.Scan(&allergy)
if err != nil {
log.Fatalf("Failed to scan for row because %s", err)
}
allergies = append(allergies, allergy)
}
log.Printf("Customer allergies are %v", allergies)
}
```
In this case, as the `allergy` column allows `NULL` values, we use the
`sql.NullString` type. `database/sql` intelligently assigns the column's value
to the variable if it is not `NULL`.
```command
./gda example null
```
```text
[output]
2022/12/22 14:03:08 Customer allergies are [{ false} {Cheese true} { false}]
```
The list of null types in `database/sql` can be found in the
[official documentation](https://pkg.go.dev/database/sql#NullBool).
## Modifying data
The three primary statements used to change the data in an SQL database are
`INSERT`, `UPDATE`, and `DELETE`. In all three scenarios, the `ExecContext()`
method on the `sql.DB` type should be used to execute the queries.
```go
[label example/changing.go]
func InsertQuery() {
// Connecting to database...
[highlight]
_, err = db.ExecContext(context.TODO(), `INSERT INTO customer(name, allergy) VALUES('John Adams', 'Seafood');`)
[/highlight]
if err != nil {
log.Fatalf("Unable to insert new customer because %s", err)
}
ParameterisedQuery("John Adams")
}
```
The `ExecContext()` method returns the query's `sql.Result` and an error (if
any). Notice that no `*sql.Rows` or `*sql.Row` is returned as `ExecContext()` is
meant to be used for queries that do not produce any values. This is why
`ExecContext()` should not be used with `SELECT` queries.
```command
./gda example insert
```
```text
[output]
2022/12/22 14:34:20 John Adams has an ID of 85b14220-10a6-42d1-8587-db41ee2f94a7
```
## Using SQL transactions
Transactions execute a series of related queries so that the entire series of
changes is undone if one of the queries fails. This mechanism exists to minimize
inconsistencies in the database.
To use transactions in `database/sql`, you need to start the transaction first,
then execute the queries within the transaction, and finally, commit the
transaction to confirm the changes. The transaction rolls back if an error
occurs at any point within the transaction block.
```go
[label example/transactions.go]
func Transaction() {
// Connecting to database...
[highlight]
tx, err := db.BeginTx(context.TODO(), nil)
[/highlight]
if err != nil {
log.Fatalf("Unable to begin transaction because %s", err)
}
[highlight]
defer tx.Rollback()
[/highlight]
var johnDoeId string
err = tx.QueryRowContext(context.TODO(), `SELECT id FROM customer WHERE name = 'John Doe';`).Scan(&johnDoeId)
if err != nil {
log.Fatalf("Unable to retrieve John Doe because %s", err)
}
_, err = tx.ExecContext(
context.TODO(),
`INSERT INTO "order"(food, quantity, customer_id) VALUES('Mac and Cheese', 3, $1)`,
johnDoeId,
)
if err != nil {
log.Fatalf("John Doe was not able to order because %s", err)
}
var macAndCheeseQuantity int
err = tx.QueryRowContext(
context.TODO(),
`SELECT sum(quantity) FROM "order" WHERE food = 'Mac and Cheese';`,
).Scan(&macAndCheeseQuantity)
if err != nil {
log.Fatalf("Failed to retrieve any Mac and Cheese orders because %s", err)
}
log.Printf("There are %d Mac and Cheese orders", macAndCheeseQuantity)
[highlight]
tx.Commit()
[/highlight]
}
```
The `BeginTx()` method is used to begin a transaction. You can call the scope of
the `Transaction()` function as the transaction block where we can execute the
same methods mentioned before (but using `tx` instead of `db`).
Notice that `tx.Rollback()` is deferred, ensuring that if the function returns
early due to an error, the transaction rolls back without saving any of the
changes. At the end of the function, we call `tx.Commit()`, which commits the
changes of the transaction block. Note that the deferred `tx.Rollback()` does
not have any effect when this occurs as the commit occurs first and saves the
changes, leaving nothing to rollback. It is a typical pattern when working with
transactions in `database/sql`.
This example is accessible through the following command. Feel free to play
around with the transaction queries to test how `database/sql` handles invalid
queries.
```command
./gda example transaction
```
```text
[output]
2022/12/22 15:01:55 There are 3 Mac and Cheese orders
```
## Common database access patterns in Go
There are some commonly occurring patterns when working with SQL databases in
Go. This is not an exhaustive list, but this will cover some of the core
patterns that you can employ to improve your workflow.
### Storing database results as structs
You can assign the values of the returned results of a database to a struct
which is useful when working with the database results as logical units, rather
than discrete fields.
For instance, if you want to work with the customers from the database as an
entire entity, we can create a `customer` struct and assign the values of each
field when scanning through a row of results.
```go
[label example/structs.go]
[highlight]
type customer struct {
id string
name string
allergy sql.NullString
}
[highlight]
func Struct() {
// Connecting to database...
[highlight]
var customers []customer
[/highlight]
rows, err := db.QueryContext(context.TODO(), `SELECT * FROM customer;`)
if err != nil {
log.Fatalf("Unable to retrieve customers because %s", err)
}
for rows.Next() {
[highlight]
var c customer
err = rows.Scan(&c.id, &c.name, &c.allergy)
[/highlight]
if err != nil {
log.Fatalf("Unable to scan row for customer because %s", err)
}
customers = append(customers, c)
}
log.Printf("Customers in the system are %v", customers)
}
```
Notice that the field types are the same as the types of the individual
variables in previous sections.
```command
./gda example struct
```
```text
[output]
2022/12/22 16:02:06 Customers in the system are [{6ed77f7b-57c8-4933-9a52-f7657b6bd2d1 John Doe { false}} {76ad552d-942b-4f1f-bb15-6068b11f8fe4 Mary Anne {Cheese true}} {c1714c8a-5d29-4318-bbea-54eec810ffec Jason Borne { false}} {85b14220-10a6-42d1-8587-db41ee2f94a7 John Adams {Seafood true}}]
```
### Returning results when changing data
When building an API with database access, you might want to return the updated
row of data after changing it. In SQL, `RETURNING *` is used to achieve this.
Since such queries change the data while returning values, `QueryRowContext()`
and `QueryContext()` should be used instead of `ExecContext()` as they will
still execute the changes while returning the data. This pattern allows you to
avoid calling `SELECT` again after changing the data.
```go
[label example/returning.go]
func Returning() {
// Connecting to database...
var allergy sql.NullString
[highlight]
err = db.QueryRowContext(
context.TODO(),
`INSERT INTO customer(name, allergy) VALUES('Megan', 'Seafood') RETURNING allergy;`,
).Scan(&allergy)
[/highlight]
if err != nil {
log.Fatalf("Failed to insert new customer Megan because %s", err)
}
if a, err := allergy.Value(); err != nil {
log.Fatalf("Cannot read Megan's allergy because %s", err)
} else {
log.Printf("Newly add customer Megan has a %s allergy", a)
}
}
```
Note that the choice between `QueryRowContext()` and `QueryContext()` still
applies. When inserting a single row of data, `QueryRowContext()` might be
ideal. When updating a set of rows that satisfy some condition, `QueryContext()`
is more appropriate.
```command
./gda example return
```
```text
[output]
2022/12/22 16:23:16 Newly add customer Megan has a Seafood allergy
```
### Prepared statements
There are times when you may wish to reuse a query, and this is where prepared
statements shine. They are beneficial when you aim to reuse a parameterized
query with different parameters but the same query structure.
The methods used earlier apply to these statements.
```go
[label example/prepared.go]
func Prepared() {
// Connecting to database...
[highlight]
stmt, err := db.PrepareContext(context.TODO(), `SELECT id FROM customer WHERE name = $1;`)
[/highlight]
if err != nil {
log.Fatalf("Unable to prepare statement because %s", err)
}
[highlight]
defer stmt.Close()
[/highlight]
var johnDoeId string
[highlight]
err = stmt.QueryRowContext(context.TODO(), "John Doe").Scan(&johnDoeId)
[/highlight]
if err != nil {
log.Fatalf("Failed to retrieve John Doe's ID because %s", err)
}
var maryAnneId string
[highlight]
err = stmt.QueryRowContext(context.TODO(), "Mary Anne").Scan(&maryAnneId)
[/highlight]
if err != nil {
log.Fatalf("Failed to retrieve Mary Anne's ID because %s", err)
}
log.Printf("John Doe's id is %s and Mary Anne's id is %s", johnDoeId, maryAnneId)
}
```
Note that the statement created must be closed manually through a `defer`.
```command
./gda example prepared
```
```text
[output]
2022/12/22 16:38:58 John Doe's id is 6ed77f7b-57c8-4933-9a52-f7657b6bd2d1 and Mary Anne's id is 76ad552d-942b-4f1f-bb15-6068b11f8fe4
```
### Enforcing query timeouts with contexts
As mentioned earlier, when querying a database, it is a best practice to enforce
a time limit on each query to avoid a situation where the query hangs forever.
You can enforce time constraints for queries using contexts as shown below:
```go
[label example/timeout.go]
func Timeout() {
// Connecting to database...
[highlight]
ctx, cancel := context.WithTimeout(context.TODO(), 5*time.Second)
defer cancel()
res, err := db.ExecContext(ctx, `SELECT pg_sleep(10);`)
if err != nil {
log.Fatalf("Failed to execute command because %s", err)
}
log.Printf("Result is %v", res)
[/highlight]
}
```
Providing the timed context (`ctx`) as an argument to the `ExecContext()` method
ensures that the query is canceled if it takes longer than the stipulated time
(5 seconds). In such cases, an error is returned from the `ExecContext()`
method. In the above example, the `pg_sleep(10)` function mimics a slow-running
query by sleeping for 10 seconds so that the query is ultimately canceled after
the 5-second limit:
```command
./gda example timeout
```
```text
[output]
2023/01/12 08:21:36 Failed to execute command because timeout: context deadline exceeded
```
### Configuring max/idle connections
The `database/sql` package aims to minimize the creation of a brand new
connection to the database by reusing existing connections when they are not in
use. These are referred to as idle connections. Idle connections, together with
connections that are actively performing queries, constitute the open connection
pool.
By default, `database/sql` manages the open connection pool internally. However,
you can configure this connection pool to optimize your use case. There are four
ways to configure the connection pool:
#### The SetMaxOpenConns method
By default, `database/sql` does not restrict the number of open connections that
can exist. This is the number of open connections (active + idle) that are
allowed to exist at once.
However, if you have a limited amount of resources available for the database,
it might be wise to set a fixed threshold using `SetMaxOpenConns()` as an
infinitely large open connection pool size can result in performance penalties.
In the example below, we set the maximum number of open connections to be 15.
```go
[label examples/configuration.go]
func MaxOpenConns() {
// Connecting to database
[highlight]
db.SetMaxOpenConns(15)
[/highlight]
}
```
#### The SetMaxIdleConns method
The second configuration you can make is to modify the maximum number of idle
connections. As mentioned, the total number of idle connections is equals to
`max open conns - current active conns`. By default, this value is set to 2 but
you can set this to a higher value using `SetMaxIdleConns()`.
Theoretically, if you increase the maximum number of idle connections allowed,
it can decrease the waiting time for an idle connection which improves the
performance. However, as with the previously discussed configuration, more is
not always better and you should always configure the maximum number of idle
connections with your use case in mind. In the example below, we've set the
maximum number of idle connections to be 5.
```go
[label example/configuration.go]
func MaxIdleConns() {
// Connecting to database
[highlight]
db.SetMaxIdleConns(5)
[/highlight]
}
```
#### The SetConnMaxLifetime and SetConnMaxIdleTime methods
You can also configure the maximum number of time a connection may be reused
using `SetConnMaxLifetime()` and the maximum amount of time a connection can
remain in the idle pool before being closed using `SetConnMaxIdleTime()`.
For both configurations, values that are set too low might result in inefficient
connection pools as connections will be repeatedly created from scratch as they
would timeout too quickly or be destroyed after too little reuses. In the
example below, we set the maximum idle time to be 5 minutes and the maximum time
it can be reused to be 100 milliseconds. Note that we are using the `time`
package to compute the duration.
```go
[label example/configuration.go]
package example
import (
// Other imports
"time"
)
func Lifecycle() {
// Connecting to database
db.SetConnMaxLifetime(100 * time.Millisecond)
db.SetConnMaxIdleTime(5 * time.Minute)
}
```
As a general rule of thumb, more is not always better and in fact, less can be
more. As such, do not go overboard with trying to maximize the connection pool.
Instead, you should test your values thoroughly through benchmarking and [logging](https://betterstack.com/community/guides/logging/zerolog/) and optimize as needed.
[summary]
## Side note: Catch slow queries and timeouts before users complain
Once you start enforcing query deadlines with Go contexts, [Better Stack](https://betterstack.com/error-tracking) helps you surface timeouts and database errors as grouped issues, so you can prioritize what’s breaking in production instead of chasing one off logs.
[/summary]
## Database tools
Building your entire SQL database integration using `database/sql` alone can be
time-consuming and unideal for rapid prototyping. As such, a slew of database
tools built on top of `database/sql` exists to improve the overall quality of
life when working with SQL databases in Go.
There are three categories of such tools, with each one providing a different
level of abstraction for database access.
### 1. Database migration
Database migration migrates data from one source database to another target
database. It also can migrate data from one schema to another schema (of the
same database) while preserving the original data from the initial schema. It is
useful when adding fields to an existing table of the database. Without a proper
database migration system, such changes entail interacting with the data stored
in the database, creating a point of failure.
Database schema migration tools exist to ease this process. Examples
include [Goose](https://github.com/pressly/goose),
[migrate](https://github.com/golang-migrate/migrate), [darwin](https://github.com/GuiaBolso/darwin),
and others.
Beyond database migrations, these tools do not help with the other aspects of
working with SQL databases so you still have to manage other aspects of working
with the database. However, having database migration handled automatically for
you is an incentive to consider using such tools.
### 2. Query builders
Query builders generate functions for querying the data based on a set of SQL
queries written by the developer. They remove the boilerplate for writing
database access queries. For instance, with error handling generated, you will
not miss an error handling block by accident, causing silent failures.
However, as these query builders often follow a fixed generation pattern, the
generated functions might be less than optimal for complex queries. Thus, while
the boilerplate has been taken care of for you, you would still need to manually
check the generated functions to ensure that they are optimal and abide by the
intended behavior.
Examples of query builders
include [sqlc](https://sqlc.dev/), and [jet](https://github.com/go-jet/jet).
### 3. ORMs
Object-Relational Mappers or ORMs are libraries that allow you to query and
manipulate the data from a database using an object-oriented paradigm. In Go,
structs represent the data from a database in most ORMs.
ORMs provide a hands-off experience when working with databases. Through the
many utility methods provided by the ORM, you can write little to no SQL.
However, like query builders, this automatic handling of querying comes with the
drawback of a lack of optimization for complex queries. As ORMs employ a fixed
generation patterns, the queries it uses might not be optimal. Thus, if you
observe significant performance issues when using an ORM, it is advisable that
you write the query separately and execute it.
Examples of ORMs
include [GORM](https://github.com/go-gorm/gorm), and [reform](https://github.com/go-reform/reform).
The usage of ORMs is a
[highly debated topic](https://old.reddit.com/r/golang/comments/t08oox/are_orms_considered_an_antipattern_in_go/)
in the Go community, and they should not be the first tool you reach for when
working with SQL databases in Go. We propose a general framework for deciding
which tool to use for database access, depending on your project and the amount
of control you require.
### How to choose the right tool
The framework we are proposing aims to provide some guidance on the tools you
should consider based on the scope of your project.
If the project requires a lot of complex queries that are performance sensitive,
it would be a good decision to avoid ORMs and query builders. ORMs are not a
good choice for such projects as the generated queries are not entirely
transparent from the get-go, and as such, debugging which queries are the most
time-consuming can be a tedious process.
Query builders remove some of this abstraction, but it is important to note that
work still needs to go into revising and double-checking the generated queries
to ensure they are optimal. In such a case, you should build a lightweight
wrapper around the `database/sql` package and write the queries manually. This
option provides you with the most amount of control over the queries that you
use.
If the project has a lot of relatively simple queries, and you do not wish to
use an ORM, query builders might be a good middle ground as they handle as much
of the boilerplate.
If the project is relatively small scale and does not require many complex
queries, then ORMs and query builders are good options as they eliminate much of
the boilerplate for working with databases.
Regardless of the project, it is always advisable to use database schema
migration tools as they alleviate a pain point when working with databases in
Go. They also help to provide ease of mind when migrating the data across
different databases or even when changing the database schema.
Note that this framework is not definitive. You should exercise some personal
judgment when deciding the type of tools to use to access databases in Go.
[summary]
## Side note: Monitor your Go app endpoints after you ship to production
Once your Go service is live and serving database backed requests, [Better Stack Uptime](https://betterstack.com/uptime) helps you catch downtime instantly with reliable checks and fast alerts, so you know something broke before your users do.
[/summary]
## Final thoughts
The built-in `database/sql` package in Go provides a good starting point for
database access in Go. Many of the features of database access are abstracted
away from developers. As such, you can focus on working with the data from the
database. It also does not shy away from providing developers with sufficient
control over the database they are working with, such as configuring the
connection pool.
However, while `database/sql` is very powerful, there are times when this power
is unnecessary. Thus, database tools like query builders or ORMs are viable
alternatives as they remove the need to write boilerplate code to access the
database.
As `database/sql` is relatively barebones in terms of helping you to optimize
your database access performance, a lot of the optimizations still lie in your
ability to write efficient SQL queries.
Thanks for reading, and happy coding!
_This article was contributed by guest author
[Woo Jia Hao](https://twitter.com/@woojiahao_), a Software Developer from
Singapore! He is an avid learner who loves solving and talking about complex and
interesting problems. Lately, he has been working with Go and Elixir!_