Adding a database to a Go web application 🗄

By Soham Kamani

This post will go through how to add a postgres database into your Go application.

It is not enough to just add a driver and query the database in your code if you want to make your application production ready. There are a few things that you have to take care of:

1. How would you write tests for your application?
2. How will you ensure that everyone else who wants to run the application (including other servers and VMs) are using the same database structure as the one you developed?
3. How do you most effectively make use of your computing resources?

First, lets start with adding the database to an existing application.

Our application

I have written another post on setting up a web application in Go. You can read it here, if you want to go into detail. In short, we have an encyclopedia of birds, that we have turned into a web application.

• A user can make a POST call to create a new bird entry
• Each entry consists of the “species” and “description” of the bird.
• A user can get existing entries by fetching them through a GET call.

The only issue with this existing application, is that the storage is all in memory (stored in data structures within the application code itself) this means that if we restarted the application, all the data would disappear.

Additionally, since all the data is stored in memory, it’s very likely that we would run out of space quickly, since RAM is a much more limited resource as compared to disk space.

Adding a database, would help solve these issues.

Creating our database tables

First, create a database on postgres, and connect to it :

CREATE DATABASE bird_encyclopedia;
\c bird_encyclopedia

Based on our application, we can determine the column names and types for our birds table:

• “species” which is expected to be a short string of text
• “description” which is expected to be a longer string of text
• “id”, which will be an autogenerated integer to keep track of our entries.

Based on this, create a new table using the postgres command line :

CREATE TABLE birds (
  id SERIAL PRIMARY KEY,
  bird VARCHAR(256),
  description VARCHAR(1024)
);

Connecting to the database in Go

We are going to structure our application in such a way, that the database will be modeled as a “store” interface within our application.

Creating the store interface, and implementation

Add the file store.go to the existing application :

There are lots of benefits to creating the store as an interface

• We can change its implementation at any time without affecting the components that use it
• It can be mocked in unit tests that use its implementation.

Testing the store

Before we can use the store in our application, we will have to write tests for it. These will be more like integration tests, since they will test our interaction with the database in the process.

We will also be using a test suite, instead of the usual test functions. This is to make it easier for us to perform one-time setups before some of our tests run. This setup, as is seen in the code mainly entails making and storing the actual database connection, and cleaning up the database before the tests run.

One point to note while writing tests that involve the use of a database (or, for that matter, any persistent store), is to always use the most direct mode of access when observing results.
In the tests above, when testing the CreateBird method, we queried the database directly to get the count of entires, instead of using the GetBirds method and seeing the length of the resulting slice of birds.
Similarly, when testing GetBirds, we used the INSERT query instead of using the CreateBird metgod to insert an entry into our table.

This is so that we can isolate the errors in each of our methods, and prevent false positives from occuring, should both the methods fail to run the way we expect them to.

Adding the store to our application

Now that we have created and tested our database store, we can add it to our application. If you have not read the previous post on creating the handlers, you can see the code here to know about the earlier implementation. There are very few changes that we actually have to make to our code to implement the store :

Mocking the store

Using the store in the request handlers was easy, as we just saw. The tricky part comes when you need to test the handlers. It would be unwise use an actual database connection for this :

• We only want to test that the handler actually called the stores GetBirds and CreateBird methods with the correct arguments.
• By using an actual database connection, our tests will not be unit tests since they would be implicitly testing the store as well, which would be out of its domain.

One solution to this problem is to use a mock store. The mock store will serve two purposes:

1. It will pretend to be the actual store. By this I mean that it will accept the same arguments, and return the same type of results as the actual store implementation, without actually interacting with the database
2. It will allow us to inspect its method calls. This is important when you want to verify that a method was called, and had the correct arguments.

The mock store is defined in a new file store_mock.go:

Now that we have defined the mock store, we can use it in our tests:

We can visulaize the mock store, and its interaction with the rest of our code :

To verify the results, run the tests with go test, and they should all run successfully.

Finishing touches

Now that we have tested that our store is working, and that our handlers are calling the store correctly, the only thing left to do is add the code for initializing the store on application start up. For this , we will edit the main.go file's main function :

The source code for this post can be found here