An Introduction to using SQL Databases in Go

Last updated: September 20th, 2021

In this post we're going to introduce the basic patterns for working with SQL databases in Go, and explain how to use the database/sql package to build a simple but functional database-backed application. We'll cover:

How to connect to your database.
How execute SELECT queries to retrieve data.
How execute SQL statements that don't return data (like INSERT and UPDATE).
What important details and nuances you need to be aware of.

Before we get started you'll first need to go get one of the drivers for the database/sql package.

In this post I'll be using Postgres and the excellent pq driver. But all the code in this tutorial is (nearly) exactly the same for any other driver or database – including MySQL and SQLite. I'll point out the very few Postgres-specific bits as we go.


				$ go get github.com/lib/pq

Basic usage

Let's build a simple Bookstore application, which carries out CRUD operations on a books table.

If you'd like to follow along, you'll need to create a new bookstore database and scaffold it with the following:


				CREATE TABLE books (
	isbn		char(14) NOT NULL,
	title	 varchar(255) NOT NULL,
	author	varchar(255) NOT NULL,
	price	 decimal(5,2) NOT NULL
);

INSERT INTO books (isbn, title, author, price) VALUES
('978-1503261969', 'Emma', 'Jayne Austen', 9.44),
('978-1505255607', 'The Time Machine', 'H. G. Wells', 5.99),
('978-1503379640', 'The Prince', 'Niccolò Machiavelli', 6.99);

ALTER TABLE books ADD PRIMARY KEY (isbn);

Once that's done, head over to your Go workspace and create a new bookstore package directory and a main.go file:


				$ cd $GOPATH/src
$ mkdir bookstore && cd bookstore
$ touch main.go

Let's start with some code that executes a SELECT * FROM books query and then prints the results to stdout.

File: main.go


				package main

import (
	_ "github.com/lib/pq"
	"database/sql"
	"fmt"
	"log"
)

type Book struct {
	isbn	string
	title	string
	author string
	price	float32
}

func main() {
	db, err := sql.Open("postgres", "postgres://user:pass@localhost/bookstore")
	if err != nil {
		log.Fatal(err)
	}

	rows, err := db.Query("SELECT * FROM books")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	bks := make([]*Book, 0)
	for rows.Next() {
		bk := new(Book)
		err := rows.Scan(&bk.isbn, &bk.title, &bk.author, &bk.price)
		if err != nil {
			log.Fatal(err)
		}
		bks = append(bks, bk)
	}
	if err = rows.Err(); err != nil {
		log.Fatal(err)
	}

	for _, bk := range bks {
		fmt.Printf("%s, %s, %s, £%.2f\n", bk.isbn, bk.title, bk.author, bk.price)
	}
}

There's a lot going on here. We'll step through this bit-by-bit.

The first interesting thing is the way that we import the driver. We don't use anything in the pq package directly, which means that the Go compiler will raise an error if we try to import it normally. But we need the pq package's init() function to run so that our driver can register itself with database/sql. We get around this by aliasing the package name to the blank identifier. This means pq.init() still gets executed, but the alias is harmlessly discarded (and our code runs error-free). This approach is standard for most of Go's SQL drivers.

Next we define a Book type – with the struct fields and their types aligning to our books table. For completeness I should point out that we've only been able to use the string and float32 types safely because we set NOT NULL constraints on the columns in our table. If the table contained nullable fields we would need to use the sql.NullString and sql.NullFloat64 types instead – see this gist for a working example. Generally it's easiest to avoid nullable fields altogether if you can, which is what we've done here.

In the main() function we initialise a new sql.DB object by calling sql.Open(). We pass in the name of our driver (in this case "postgres") and the connection string (you'll need to check your driver documentation for the correct format). It's worth emphasising that the sql.DB object it returns is not a database connection – it's an abstraction representing a pool of underlying connections. You can change the maximum number of open and idle connections in the pool with the db.SetMaxOpenConns() and db.SetMaxIdleConns() methods respectively. A final thing to note is that sql.DB is safe for concurrent access, which is very convenient if you're using it in a web application (like we will shortly).

From there we follow a standard pattern that you'll see often:

We fetch a resultset from the books table using the DB.Query() method and assign it to a rows variable. Then we defer rows.Close() to ensure the resultset is properly closed before the parent function returns. Closing a resultset properly is really important. As long as a resultset is open it will keep the underlying database connection open – which in turn means the connection is not available to the pool. So if something goes wrong and the resultset isn't closed it can rapidly lead to all the connections in your pool being used up. Another gotcha (which caught me out when I first began) is that the defer statement should come after you check for an error from DB.Query. Otherwise, if DB.Query() returns an error, you'll get a panic trying to close a nil resultset.
We then use rows.Next() to iterate through the rows in the resultset. This preps the first (and then each subsequent) row to be acted on by the rows.Scan() method. Note that if iteration over all of the rows completes then the resultset automatically closes itself and frees-up the connection.
We use the rows.Scan() method to copy the values from each field in the row to a new Book object that we created. We then check for any errors that occurred during Scan, and add the new Book to the bks slice we created earlier.
When our rows.Next() loop has finished we call rows.Err(). This returns any error that was encountered during the interation. It's important to call this – don't just assume that we completed a successful iteration over the whole resultset.

If our bks slice has been filled successfully, we loop through it and print the information about each book to stdout.

If you run the code you should get the following output:


				$ go run main.go
978-1503261969, Emma, Jayne Austen, £9.44
978-1505255607, The Time Machine, H. G. Wells, £5.99
978-1503379640, The Prince, Niccolò Machiavelli, £6.99

Using in a web application

Let's start to morph our code into a RESTful-ish web application with 3 routes:

GET /books – List all books in the store
GET /books/show – Show a specific book by its ISBN
POST /books/create – Add a new book to the store

We've just written all the core logic we need for the GET /books route. Let's adapt it into a booksIndex() HTTP handler for our web application.

File: main.go


				package main

import (
	_ "github.com/lib/pq"
	"database/sql"
	"fmt"
	"log"
	"net/http"
)

type Book struct {
	isbn	 string
	title	string
	author string
	price	float32
}

var db *sql.DB

func init() {
	var err error
	db, err = sql.Open("postgres", "postgres://user:pass@localhost/bookstore")
	if err != nil {
		log.Fatal(err)
	}

	if err = db.Ping(); err != nil {
		log.Fatal(err)
	}
}

func main() {
	http.HandleFunc("/books", booksIndex)
	http.ListenAndServe(":3000", nil)
}

func booksIndex(w http.ResponseWriter, r *http.Request) {
	if r.Method != "GET" {
		http.Error(w, http.StatusText(405), 405)
		return
	}

	rows, err := db.Query("SELECT * FROM books")
	if err != nil {
		http.Error(w, http.StatusText(500), 500)
		return
	}
	defer rows.Close()

	bks := make([]*Book, 0)
	for rows.Next() {
		bk := new(Book)
		err := rows.Scan(&bk.isbn, &bk.title, &bk.author, &bk.price)
		if err != nil {
			http.Error(w, http.StatusText(500), 500)
			return
		}
		bks = append(bks, bk)
	}
	if err = rows.Err(); err != nil {
		http.Error(w, http.StatusText(500), 500)
		return
	}

	for _, bk := range bks {
		fmt.Fprintf(w, "%s, %s, %s, £%.2f\n", bk.isbn, bk.title, bk.author, bk.price)
	}
}

So how is this different?

We use the init() function to set up our connection pool and assign it to the global variable db. We're using a global variable to store the connection pool because it's an easy way of making it available to our HTTP handlers – but it's by no means the only way. Because sql.Open() doesn't actually check a connection, we also call DB.Ping() to make sure that everything works OK on startup.
In the booksIndex hander we return a 405 Method Not Allowed response for any non-GET request. Then we have our data access logic. This is exactly the same as the earlier example, except that we're now returning proper HTTP errors instead of exiting the program. Lastly we write the books' details as plain text to the http.ResponseWriter.

Run the application and then make a request:


				$ curl -i localhost:3000/books
HTTP/1.1 200 OK
Content-Length: 205
Content-Type: text/plain; charset=utf-8

978-1503261969, Emma, Jayne Austen, £9.44
978-1505255607, The Time Machine, H. G. Wells, £5.99
978-1503379640, The Prince, Niccolò Machiavelli, £6.99

Querying a single row

For the GET /books/show route we want to retrieve single book based on its ISBN, with the ISBN being passed in the query string like /books/show?isbn=978-1505255607.

We'll create a new bookShow() handler for this:

File: main.go


				...

func main() {
	http.HandleFunc("/books", booksIndex)
	http.HandleFunc("/books/show", booksShow)
	http.ListenAndServe(":3000", nil)
}

...

func booksShow(w http.ResponseWriter, r *http.Request) {
	if r.Method != "GET" {
		http.Error(w, http.StatusText(405), 405)
		return
	}

	isbn := r.FormValue("isbn")
	if isbn == "" {
		http.Error(w, http.StatusText(400), 400)
		return
	}

	row := db.QueryRow("SELECT * FROM books WHERE isbn = $1", isbn)

	bk := new(Book)
	err := row.Scan(&bk.isbn, &bk.title, &bk.author, &bk.price)
	if err == sql.ErrNoRows {
		http.NotFound(w, r)
		return
	} else if err != nil {
		http.Error(w, http.StatusText(500), 500)
		return
	}

	fmt.Fprintf(w, "%s, %s, %s, £%.2f\n", bk.isbn, bk.title, bk.author, bk.price)
}

Once again the handler starts again by checking that it's dealing with a GET request.

We then use the Request.FormValue() method to fetch the ISBN value from the request query string. This returns an empty string if there's no parameter found, so we check for that and issue a 400 Bad Request response if it's missing.

Now we get to the interesting bit: DB.QueryRow(). This method is similar to DB.Query, except that it fetches a single row instead of multiple rows.

Because we need to include untrusted input (the isbn variable) in our SQL query we take advantage of placeholder parameters, passing in the value of our placeholder as the second argument to DB.QueryRow() like so:

db.QueryRow("SELECT * FROM books WHERE isbn = $1", isbn)

Behind the scenes, db.QueryRow (and also db.Query() and db.Exec()) work by creating a new prepared statement on the database, and subsequently execute that prepared statement using the placeholder parameters provided. This means that all three methods are safe from SQL injection when used correctly . From Wikipedia:

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, injection cannot occur.

The placeholder parameter syntax differs depending on your database. Postgres uses the $N notation, but MySQL, SQL Server and others use the ? character as a placeholder.

OK, let's get back to our code.

After we've got a row from DB.QueryRow() we use row.Scan() to copy the values into a new Book object. Note how any errors from DB.QueryRow() are deferred and not surfaced until we call row.Scan().

If our query returned no rows, our call to row.Scan() will return an error of the type sql.ErrNoRows. We check for that error type specifically and return a 404 Not Found response if that's the case. We then handle all other errors by returning a 500 Internal Server Error.

If everything went OK, we write the book details to the http.ResponseWriter.

Give it a try:


								$ curl -i "localhost:3000/books/show?isbn=978-1505255607"
HTTP/1.1 200 OK
Content-Length: 54
Content-Type: text/plain; charset=utf-8

978-1505255607, The Time Machine, H. G. Wells, £5.99

If you play around with the ISBN value, or issue a malformed request you should see that you get the appropriate error responses.

Executing a statement

For our final POST /books/create route we'll make a new booksCreate() handler and use DB.Exec() to execute a INSERT statement. You can take the same approach for an UPDATE, DELETE, or any other action that doesn't return rows.

Here's the code:

File: main.go


								...

import (
	_ "github.com/lib/pq"
	"database/sql"
	"fmt"
	"log"
	"net/http"
	"strconv"
)
...

func main() {
	http.HandleFunc("/books", booksIndex)
	http.HandleFunc("/books/show", booksShow)
	http.HandleFunc("/books/create", booksCreate)
	http.ListenAndServe(":3000", nil)
}
...

func booksCreate(w http.ResponseWriter, r *http.Request) {
	if r.Method != "POST" {
		http.Error(w, http.StatusText(405), 405)
		return
	}

	isbn := r.FormValue("isbn")
	title := r.FormValue("title")
	author := r.FormValue("author")
	if isbn == "" || title == "" || author == "" {
		http.Error(w, http.StatusText(400), 400)
		return
	}
	price, err := strconv.ParseFloat(r.FormValue("price"), 32)
	if err != nil {
		http.Error(w, http.StatusText(400), 400)
		return
	}

	result, err := db.Exec("INSERT INTO books VALUES($1, $2, $3, $4)", isbn, title, author, price)
	if err != nil {
		http.Error(w, http.StatusText(500), 500)
		return
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		http.Error(w, http.StatusText(500), 500)
		return
	}

	fmt.Fprintf(w, "Book %s created successfully (%d row affected)\n", isbn, rowsAffected)
}

Hopefully this is starting to feel familiar now.

In the booksCreate() handler we check we're dealing with a POST request, and then fetch the request parameters using request.FormValue(). We verify that all the necessary parameters exist, and in the case of price use the strconv.ParseFloat() to convert the parameter from a string into a float.

We then carry out the insert using db.Exec(), passing our new book details as parameters just like we did in the previous example. Note that DB.Exec(), like DB.Query() and DB.QueryRow(), is a variadic function, which means you can pass in as many parameters as you need.

The db.Exec() method returns an object satisfying the sql.Result interface, which you can either use (like we are here) or discard with the blank identifier.

The sql.Result() interface guarantees two methods: LastInsertId() – which is often used to return the value of an new auto increment id, and RowsAffected() – which contains the number of rows that the statement affected. In this code we're picking up the latter, and then using it in our plain text confirmation message.

It's worth noting that not all drivers support the LastInsertId() and RowsAffected() methods, and calling them may return an error. For example, pq doesn't support LastInsertId() – if you need that functionality you'll have to take an approach like this one.

Let's try out the /books/create route, passing our parameters in the POST body:


								$ curl -i -X POST -d "isbn=978-1470184841&title=Metamorphosis&author=Franz Kafka&price=5.90" localhost:3000/books/create
HTTP/1.1 200 OK
Content-Length: 58
Content-Type: text/plain; charset=utf-8

Book 978-1470184841 created successfully (1 row affected)

Refactoring

At the moment all our database access logic is mixed in with our HTTP handlers. It's probably a good idea to refactor this for easier maintainability and DRYness as our application grows.

But this tutorial is already pretty long, so I'll explore some of the options for refactoring our code in my next post – Organising Database Access in Go

Additional Tools

The jmoiron/sqlx package provides some additions to the standard database/sql functionality, including support for named placeholder parameters and automatic marshalling of rows into structs.

The guregu/null package by can help make managing nullable values easier, if that's something you need to do a lot of.

Lastly, I found the tutorials at go-database-sql.org to be clear and helpful. Especially worth reading is the surprises and limitations section.

If you enjoyed this article, you might like to check out my recommended tutorials list or check out my books Let's Go and Let's Go Further, which teach you everything you need to know about how to build professional production-ready web applications and APIs with Go.

Filed under:golang tutorial