Learning GO: Obtaining a unique sequence from #PostgresSQL with #golang

At some point I discovered GO trying to do very simple and fast code for specific tasks without having to load lots of libraries, deploy application servers or set up complicated frameworks. If you want to build fast very small native micro-services that can be deployed in a docker instance < 10MB Go is the king.

Due to the huge number of standard libraries and libraries from go community in go everything is really concise and with limited number of code lines.
The following is an example on how to connect to a PostgreSQL database and obtain a unique sequence number. This is very useful when you need to assign unique ids to some entry.

STEP 1: Prerequisites
We assume that we already have a PostgreSQL setup on our system. The easy way to do it just pull it out of the Docker repo.
In a docker-compose.yml just define a db service as:

version: '2'

services:
  db:
    image: postgres:9.6.2-alpine
    volumes:
      - database:/var/lib/postgresql/data/
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: mydb
      POSTGRES_PASSWORD: mydb
     
...

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

volumes:
database:

After the database container is created and started just connect to it:

docker -it db_1 /bin/bash

Start the PostgreSQL cli:

psql -u postgres

Connect to our defined db mydb:

\c mydb

Simply execute the following query to create the sequence table:

CREATE TABLE booksequence (
  id SERIAL PRIMARY KEY,
  data_key TEXT,
  user_key INT,
  creation_date TEXT
);

STEP 2: Go import section, register the database driver
Next step is to simply create a package for our db functions and import some basic packages

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

What is important here is the import:
_ “github.com/lib/pq”

Note the _ before the import. In the case of github.com/lib/pq, the underscore import is used for the side-effect of registering the pq driver as a database driver in the init() function, without importing any other functions.

STEP 3: Define the sequence function

We can now define a sequence function that uses the above define db table to store unique ids. For example unique ids for each book of a user.

func getNextUserBookSeq(bookKey string, userKey int) (string, error) {
...
}

STEP 4: Build connection string and open a connection to the database
To open a connection to the database is very easy with the following code block:

	psqlInfo := fmt.Sprintf("host=%s port=%s user=%s "+
		"password=%s dbname=%s sslmode=disable",
		DbHost, DbPort, DbUser, DbPassword, DbName)
	// open connection
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		return "", err
	}
     defer db.Close()

Note that we use some global variables we defined in the init() function of the package from some environment variables (DbHost, DbPort, DbUser, DbPassword, DbName). It is always better and more secure to get them as global parameters.

Note how simple we make sure we close the connection: defer db.Close(). By using the “defer” keyword we instruct go to execute some code line at the moment when we exit the function. This is extremely useful for a programmer as the majority of resource leaks are caused by forgotten resources that are not properly close. By being able to close a socket connection, a stream or a buffer right after you opened it is very helpful in resource management.

STEP 5: Create the sequence date field
Here I want to record the date when the entry was added. I record just the month and day.

current_time := time.Now().Local()
	creationDateMonth := strconv.Itoa(int(current_time.Month()))
	if len(creationDateMonth) == 1 {
		creationDateMonth = "0" + creationDateMonth
	}
	creationDateDay := strconv.Itoa(current_time.Day())
	if len(creationDateDay) == 1 {
		creationDateDay = "0" + creationDateDay
	}
creationDate := creationDateDay + creationDateMonth

STEP 6: Insert a new entry and return the sequence:
now simply insert a new entry and concatenate the input params to the returned unique id:

// insert record and retrieve the next invoice number
	sqlStatement := `
	INSERT INTO booksequence(bookKey, userKey, creationDate )
	VALUES ($1, $2, $3)
	RETURNING id `

	id := 0
	err = db.QueryRow(sqlStatement, bookKey, userKey, creationDate ).Scan(&id)
	if err != nil {
		return "", err
	}
	fmt.Println("New record ID is:", id)

return bookKey +"-"+strconv.Itoa(userKey)+"-"+ strconv.Itoa(id), nil

STEP 7: Putting all together

package book
import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
	"strconv"
	"time"
)

func getNextUserBookSeq(bookKey string, userKey int) (string, error) {
psqlInfo := fmt.Sprintf("host=%s port=%s user=%s "+"password=%s dbname=%s sslmode=disable",
		DbHost, DbPort, DbUser, DbPassword, DbName)
// open connection
db, err := sql.Open("postgres", psqlInfo)
if err != nil {
	return "", err
}
defer db.Close()
creationDateMonth := strconv.Itoa(int(current_time.Month()))
if len(creationDateMonth) == 1 {
	creationDateMonth = "0" + creationDateMonth
}
creationDateDay := strconv.Itoa(current_time.Day())
if len(creationDateDay) == 1 {
	creationDateDay = "0" + creationDateDay
}
creationDate := creationDateDay + creationDateMonth
sqlStatement := `
INSERT INTO booksequence(bookKey, userKey, creationDate )
VALUES ($1, $2, $3)
RETURNING id `

id := 0
err = db.QueryRow(sqlStatement, bookKey, userKey, creationDate ).Scan(&id)
if err != nil {
	return "", err
}
fmt.Println("New record ID is:", id)

return bookKey +"-"+strconv.Itoa(userKey)+"-"+ strconv.Itoa(id), nil
}

STEP 8: Use the sequence:

To use the sequence in some other go code just import the package if we are not in the same package and call the function.

import "book"
...
bookSeq, err:= getNextUserBookSeq("ScienceBook", user_id)
... 

The returned sequence is something like: ScienceBook-42-1001

where user_id is some unique id identifying the user connected to the system.

This is by far the best way to create a sequence or the best choice of parameters. I only wanted to show how easy is to interact from go with a database.