Let’s Go, Everything you need to know about creating a RESTful Api in Go — Part IV

8 min readMay 1, 2019

Detailed overview on integrating a PostgresSQL database.

PART IV

Now we have implemented the RESTful API interface in previous articles, it’s time to dive in and understand the entire life cycle of a go application. Whether it is a REST API written in java, C++ or nodejs, there is a higher chance that you would require a database integration to store information at some point. This article is about integrating a PostgresSQL database with our go application.

For starters let’s assume that there is a PostgresSQL database in our local machine. Here, let’s consider the database asgo_dband the table account.

Let’s create our database and account table

//create database
postgres=# CREATE DATABASE go_db;//use database
postgres=# \c go_db;//create table
go_db=# CREATE TABLE account ( 
   id         int8,
   user_name  CHARACTER VARYING(24), 
   first_name CHARACTER VARYING(24), 
   last_name  CHARACTER VARYING(24)
);//populate account table with data
go_db=# INSERT INTO account VALUES (1, 'james123', 'james', 'root');//verify data
go_db=# SELECT * FROM account; //will return
id  | user_name | first_name | last_name 
----+-----------+------------+-----------
  1 | james123  | james      | root

Now that we have a database, a table and set of records, let’s see how we can use go and previously exposed APIs to integrate with the data source layer.

Sql package

Package database/sql provides a generic interface to work with SQL and SQL-like databases. It is required to use this package in conjunction with the database driver. For this tutorial purpose, will be using a PostgresSQL database driver, since we will be connecting to a PostgresSQL database. Read more on sql package — source.

PostgresSQL database driver

For Postgres will be using a package known as pq. Before importing the package, we should install it and import the dependency.

go get github.com/lib/pq

Once installed, driver package can be imported along with the sql package.

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

Normally it is not advisable to use the driver package with the sql package to avoid tight coupling with driver implementation. If we are to change the driver somewhere down the line, it will be difficult if we have used direct driver related implementations inside our code base. In this case we are loading the driver anonymously by aliasing its package qualifier to _ and this way we avoid tight coupling our code base with the driver package.

DB struct

With our driver in place, we are ready to connect to the database. But first it is important to know sql package basics.

Accessing database in go is done via a DB struct. This struct is used to create connections, handle connection pool configurations, prepare statements, execute queries, fetch results and handle transactions.

There are multiple important tasks take place behind the scene.

The struct uses the driver to establish a connection to the underlying database.
Manage pool of connections.
Manage concurrent access to the underlying database.

type DB struct {   waitDuration int64   connector driver.Connector   numClosed uint64   mu           sync.Mutex
   freeConn     []*driverConn
   connRequests map[uint64]chan connRequest
   nextRequest  uint64 
   numOpen      int 
   
   openerCh          chan struct{}
   resetterCh        chan *driverConn
   closed            bool
   dep               map[finalCloser]depSet
   lastPut           map[*driverConn]string 
   maxIdle           int                    
   maxOpen           int                    
   maxLifetime       time.Duration          
   cleanerCh         chan struct{}
   waitCount         int64 
   maxIdleClosed     int64 
   maxLifetimeClosed int64    stop func() 
}

waitDuration: Total time waited for new connections.
connector: A Connector can be passed to sql.OpenDB, to allow drivers to implement their own sql.DB constructors.
numClosed: atomic counter for total number of closed connections.
mu: This struct represents a mutual exclusion lock.
freeConn: This struct wraps a driver.Conn with a mutex.
connRequests: connRequest map where single connRequest represents one request for a new connection. When no idle connections available, DB.conn will create a new connRequest.
nextRequest: Next key to use in connRequests map
numOpen: number of opened and pending open connections
openerCh: Used to signal the need for new connections.
dep: A map where the key is finalCloser and the value depSet.
lastPut: stacktrace of last conn’s put, for debugging.
maxIdle: zero means defaultMaxIdleConns; negative means 0.
maxOpen: <= 0 means unlimited.
maxLifetime: maximum amount of time a connection may be reused.
waitCount: Total number of connections waited for
maxIdleClosed: Total number of connections closed due to idle
maxLifetimeClosed: Total number of connections closed due to max free limit

About sql.Open

This function is responsible for opening a database using a database driver name and provided data source information. Down the line, this function might just validate it arguments without actually creating a connection to the database. Therefore db.Ping() is required for further verification. This function should be called only once and rarely required to be close.

func Open(driverName, dataSourceName string) (*DB, error)

Let’s use the above function to open a database in our code base.

//datasource
connStr := "dbname=orders user=postgres password=password host=localhost port=5432 sslmode=disable"//passing arguments to Open function.
db, err := sql.Open("postgres", connStr)

Since we are using a PostgresSQL database and a Postgres database driver, the driver name should be postgres. Data source string is defined as a separate string connStr and passed in to the function. It includes the connection parameters such as database name:dbname, database user: user, database password: password, database host: host, database port: port (default port is 5432) and sslmode: sslmode, more info on connection parameters — source

//function
func Open(driverName, dataSourceName string) (*DB, error)//equivalent usage in our code base
db, err := sql.Open("postgres", connStr)

As you can see, the function returns an error and a DB struct. It is required to check and handle errors returned from any sql package related operations, thus we should do the same for this one as well.

connStr := "dbname=orders user=postgres password=password host=localhost port=5432 sslmode=disable"db, err := sql.Open("postgres", connStr)
if err != nil {
   log.Fatal(err)
}
defer db.Close()

If there is an error, we log it and if sql.DB shouldn’t have a lifetime beyond the scope we should defer db.Close() the connection.

Refresher: defer

A defer statement delays the execution of a function until other functions complete its executions. Once all the surrounding functions return, defer functions will execute. The following scenario will describe the function behavior.

//function
func main() {
   defer fmt.Println("end 3")
   defer fmt.Println("end 2")   fmt.Println("hello")
   for i := 0; i < 10; i++ {
      fmt.Print(i, " ")
   }
   fmt.Println("world")   defer fmt.Println("end 1")
}//output
hello
0 1 2 3 4 5 6 7 8 9 world
end 1
end 2 
end 3//function stack for defer statements, just an illustration.
| defer "end 1"|
| defer "end 2"|
| defer "end 3"|
----------------

defer statements are evaluated immediately hence will stacked up first than the other functions. Even if there are multiple defer statements, the last statement will be in the top of the stack (last in first out).

So what defer db.Close() does is, once all the other functions are done executing, close the database connection. And even if there are multiple defer statements after this, should add the close statement as the first defer statement of the lot, since closing should happen at the end.

Although here we close the connection, it is not advisable to open() and close() database connections frequently. only one sql.DB struct should be created for each datastore separately and should keep it open and pass around into short-lived function as an argument. sql.DB should be treated as a long-lived object else poor usage of reusable connections could lead to running out of available network resources.

Now we have open a connection to the database, let’s see how we can query and perform crud operations.

About db.Query()

Function query() should be used for scenarios which required to return a set of rows (SELECT), even if the result set is empty. On the other hand if no row set is required to return from the underlying operation (INSERT, DELETE) exec() should be used instead of query().

Earlier we populate account table, now let’s use query() to fetch the results.

connStr := "dbname=go_db user=postgres password=password host=localhost port=5432 sslmode=disable"db, err := sql.Open("postgres", connStr)
if err != nil {
   log.Fatal(err)
}var (
   id        int
   userName  string
)rows, err := db.Query("select id, user_name from account")if err != nil {
   log.Fatal(err)
}defer rows.Close()for rows.Next() {
   err := rows.Scan(&id, &userName)
   if err != nil {
      log.Fatal(err)
   }   log.Println(id, userName)
}//output
2019/04/20 20:18:40 1 james123

Here, we are using the query() to send the query to the database. This is what query function looks like,

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Query executes a query that return rows, typically a SELECT. The args are for any placeholder parameters in the query. We should check errors for any sql package function as mentioned earlier in this article. Thus, should do the same for query() as well. Statement defer rows.Close() is important, it closes the Rows, preventing further enumeration.

About rows.Next() & rows.Scan()

func (rs *Rows) Next() bool

Function next() provides a way to iterate over the rows in the result set. If there is no next result row or an error, next() will return false.

Next provide a way to iterate over each row, scan() is used to iterate over each column in the current row and get column fields specified in the parameters. In the query() we’ve asked to fetch two fields in the SELECT statement, id and userName. These two fields ( &id, &userName points to the locations of the variables) need to be specified in the scan function as parameters.

func (rs *Rows) Scan(dest ...interface{}) error

This is the way to access a result set in go. No way to get a row as a map since everything is strongly typed. Scan require variables with the correct type and pass pointers to the function as parameters as mentioned above.

CRUD operations

Will use our account table to perform db crud operations. Earlier section described Read via SELECT using query, in this section will see how to perform rest of the Create, Update and Delete operations.

Although we used query to fetch data for a SELECT statement, it is recommended to use exec for INSERT, UPDATE and DELETE statements which doesn’t require returning rows.

Create

Let’s insert data into our account table.

insertStmt, err := db.Prepare("INSERT INTO account(id, user_name,
                   first_name, last_name) VALUES($1, $2, $3, $4)")
if err != nil {
 log.Fatal(err)
}res, err := insertStmt.Exec(2, "jon1234", "jon", "snow")
if err != nil {
 log.Fatal(err)
}rowCnt, err := res.RowsAffected()
if err != nil {
 log.Fatal(err)
}log.Printf("Successfully added an account, affected count = %d\n", rowCnt)

Here, db.Prepare() prepares the INSERT statement and we pass the values as arguments using $1, $2, $3, $4. This parameter arguments are specific to PostgresSQL driver. Next, insertStmt.Exec() executes the prepared statement by passing arguments of the record which needs to be inserted. RowsAffected() returns the number of rows been inserted. As you can see we have handled all the errors as mentioned earlier.

This will insert an account into our database. Let’s look at how to update an existing record in the database.

Update

Let’s update an existing record in our account table,

updateStmt, err := db.Prepare("UPDATE account SET user_name = $1
                   WHERE id = $2")
if err != nil {
 log.Fatal(err)
}updateRes, err := updateStmt.Exec("jonSnow", 2)
if err != nil {
 log.Fatal(err)
}updateRowCnt, err := updateRes.RowsAffected()
if err != nil {
 log.Fatal(err)
}log.Printf("Successfully updated an account, affected count = %d\n", updateRowCnt)

Similar to INSERT, we use the same structure but prepare statement query and the parameter passed in exec is the only difference.

Delete

After successfully inserting and updating, it’s time to remove a record in the database. For DELETE also we can use a similar approach as INSERT and UPDATE. Will be using a prepare statement and pass relevant arguments via exec function.

So it is the end of another article. We have integrated a database with our REST API endpoints. Now we know how to connect to a database in go and work with data.

Next will see how to integrate a third party REST API with our go application.

check my personal blog to view more — icodeforpizza

Prev Section — Part III

Next Section — Part V

Let’s Go, Everything you need to know about creating a RESTful Api in Go — Part IV

CRUD operations

Written by Supun Muthutantrige