Introducing pq, a pure PostgreSQL database driver for Go

In Chapter 4, Simplifying RESTful Services with Popular Go Frameworks, we used a driver package called go-sqlite3 to work with SQLite3. In the same way, pq is a database driver package available for Go. We can install that library system-wide by using the go get command, as follows:

go get github.com/lib/pq

We can also use the dep tool to install this package. We'll use it in this example. Let's look at the steps for installation here:

1. Create a new project directory called basicExample in GOPATH, as follows:

touch -p $GOPATH/src/github.com/git-user/chapter7/basicExample

2. Now, traverse to the basicExample directory and use dep to install the pq package in the directory, like this:

dep init
dep ensure --add github.com/lib/pq

This creates a few configuration files and adds a package to the vendor in the same directory. Now, we can create our program and use that pq package.

3. In order to create a new table, we should create a new database in the PostgreSQL server. To create a new database, enter the psql shell or use pgAdmin 4, as shown in the following command (you only have to do this once):

CREATE DATABASE mydb;

Let's look at  a short example that explains the usage of the pq driver. In the later sections, we'll implement a URL-shortening service. This a pre-step for that service. We'll create a table called web_url for our purpose by following these steps: 

1. Create a directory called helper in the project, as follows:

mkdir $GOPATH/src/github.com/git-user/chapter7/basicExample/helper

This helps in initiating database operations such as creating a table.

2. Now, add a file called models.go. This file is going to have the table creation logic. We use sql.Open to make a connection to PostgreSQL. That function takes a database type and a database string as arguments. We can prepare a SQL statement using the db.Prepare command.
3. Import the necessary packages in the program, like this:

package helper

import (
  "database/sql"
  "fmt"
  "log"

  _ "github.com/lib/pq" // sql behavior modified
)

4. Now, create a few constants that hold database-connection information. A database connection needs a hostname, a port, a username, a password, and a database name, as shown in the following code block:

const (
  host = "127.0.0.1"
  port = 5432
  user = "git-user"
  password = "YOUR_PASSWORD"
  dbname = "mydb"
)

The password should be the one you passed when creating the user.

5. Next, create a function called InitDB. It uses a connection string to open a new database connection to PostgreSQL. After a successful connection, it should prepare a SQL statement to create a table called web_url. The code for the function looks like this:

func InitDB() (*sql.DB, error) {
    var connectionString = fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)

    var err error
    db, err := sql.Open("postgres", connectionString)

    if err != nil {
        return nil, err
    }

    stmt, err := db.Prepare("CREATE TABLE IF NOT EXISTS web_url(ID SERIAL PRIMARY KEY, URL TEXT NOT NULL);")

    if err != nil {
        return nil, err
    }

    _, err = stmt.Exec()

    if err != nil {
        return nil, err
    }

    return db, nil
}

The sql.Open method opens the connection string. It then prepares and executes a CREATE TABLE query to create a web_url table if this does not already exist. InitDB returns an error if any operation fails on the database.

6. Let's create a main.go program to use the helper package, as follows:

mkdir $GOPATH/src/github.com/git-user/chapter7/basicExample/main.go

7. In the main block, we can import an InitDB helper function and use it like this:

package main

import (
    "log"

    "github.com/git-user/chapter7/basicExample/helper"
)

func main() {
    _, err := helper.InitDB()
    if err != nil {
        log.Println(err)
    }

    log.Println("Database tables are successfully initialized.")
}

This program imports the helper package and uses the InitDB function from it. We log a successful initialization message if the table was successfully created; otherwise, we log an error.

8. If you run the program, you will see this message printed:

go run main.go

2020/02/13 22:15:34 Database tables are successfully initialized.

 This creates a web_url table in the mydb database. 

9. We can cross-check that by entering into the psql shell and typing the following:

\c mydb \dt

This connects the user to the mydb database and lists all available tables, as shown in the following code snippet:

You are now connected to database "mydb" as user "postgres".
 List of relations
 Schema | Name | Type | Owner
--------+---------+-------+-------
 public | web_url | table | user
(1 row)

In PostgreSQL, the AUTO_INCREMENT type needs to be replaced by SERIAL while providing a schema for table creation.

As we promised earlier, in the next section, we will try to implement a URL-shortening service. We'll initially lay out the basics required for building such a service. Then, we'll move  on to the implementation. The URL-shortening service will give you a clear understanding of how PostgreSQL can be used to solve problems.