Super simple connecting to postgres with Go.

Hi again,

I recently wrote a small article on making a really easy connection to mongoDB with Go. This article intends to cover the same ground but for postgres because not everyone wants to go with a document model engine.

Let's map it out

We want to achieve a couple of things here:

• separate the DB code into a separate package.
• make clear that this is demo quality and I'll be taking the easy route with usernames and passwords and so on. ie. Do not repeat these shortcuts in production code.
• clearly mark the bits that are whiffy for prod code.

Let's get a Dockerised postgres instance

We want to grab a docker image, my particular one was 10.8 because it matched a requirement I had. You can take a later version without any hassles.

docker pull postgres

# I used
docker pull postgres:10.8

To run this postgres image in a container you can use the following.

docker run --name postgres-local -p 5432:5432 -v ~/.postgres-data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=STRONGPASSWORD -d postgres:10.8

This connects you to the default user. Therefore to setup your database you can psql into the postgres shell and create a database CREATE DATABASE databasename and a role/user.

If you don't like the CLI for this sprt of thing you can use postico, or pgadmin apps to have a graphical tool for the ops here.

Lets go

Ideally you want your project to resemble the following structure:

.
├── go.mod
├── go.sum
├── models
│   └── models.go
└── main.go

We'l do most of the database work in the models.go file and it will look like this. First you'll have to go get the driver package called pq. This can be achieved with go get github.com/lib/pq. Easy!

package models

import (
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
)

const (
    host     = "127.0.0.1"
    port     = 5432
    user     = "your_username_here"
    password = "make_it_a_good_on"
    dbname   = "your_database_name_here"
)

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
    }

        // example of an operation we can take.
    stmt, err := db.Prepare("CREATE TABLE IF NOT EXISTS your_table_name(ID SERIAL PRIMARY KEY, ATTR TEXT NOT NULL);")
    if err != nil {
        return nil, err
    }

    _, err = stmt.Exec()
    if err != nil {
        return nil, err
    }

    return db, nil
}

and to be able to run the demo we have a trivial main.go, it's as simple as:

package main

import (
    "log"

    models "github.com/USERNAME/postgres-go-driver/models"
)

func main() {
    _, err := models.InitDB()
    if err != nil {
        log.Println(err)
    } else {
        log.Println("Database schema successfully initialized")
    }
}

Short, hopefully sweet, and a little bit of reference boilerplate for you, (or for me a few months from now) and remember the takeaway about it being demo code, this means you might consider:

• making username and password environment variables
• Not using a magic string to hardcode SQL in your code. You could separate this to a package, or packages for DML and DDL respectively.
• remember to not use admin & password.

Best wishes.