Super simple connecting to postgres with Go. 
Published: 2021-08-06 22:58:01

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. 

```sh
docker pull postgres

# I used
docker pull postgres:10.8
```

To run this postgres image in a container you can use the following. 
```sh
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: 

```sh
.
├── 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! 

```go
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: 

```go
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.