The pgx Files 01: Connecting

14 Aug 2016

Welcome to part one of The pgx Files, where we test Jack Christensen's fantastic pgx golang library for interacting with PostgreSQL.

My aim is to do stuff in a psql session and then replicate the effort with pgx. In other words, The pgx Files will cater to people who already know SQL (and PostgreSQL) and who want all that power exposed in golang as directly as possible.

First things first: we need to get connected.

As PostgreSQL fans will know, the easiest way to connect to PostgreSQL if you are on the same machine as the database cluster is to do the following, because on a default installation, the postgres user and database are guaranteed to exist:

$ psql -U postgres -d postgres

Of course, once in the psql session, any psql fan will have customized a few things through .psqlrc. Most of these customizations are not necessary when connecting to PostgreSQL through an application, of course, but one setting is particularly useful, and that's application_name.

I have customized mine, and it shows up in pg_stat_activity like so:

# select datname, usename, application_name from pg_stat_activity;
┌──────────┬──────────┬──────────────────┐
│ datname  │ usename  │ application_name │
├──────────┼──────────┼──────────────────┤
│ postgres │ postgres │ manni_desktop    │
└──────────┴──────────┴──────────────────┘
(1 row)

I can set it to anything I want:

# set application_name to 'foo bar';

# select datname, usename, application_name from pg_stat_activity; rollback;
┌──────────┬──────────┬──────────────────┐
│ datname  │ usename  │ application_name │
├──────────┼──────────┼──────────────────┤
│ postgres │ postgres │ foo bar          │
└──────────┴──────────┴──────────────────┘
(1 row)

pgx allows you to also set the application_name as soon as you connect, which is fantastic.

Here's how to set up one connection to PostgreSQL using pgx, including setting the application_name:

package main

import (
	"fmt"
	"os"

	"github.com/jackc/pgx"
)

func main() {
	conn := Connect("connect test")
	defer conn.Close()
	fmt.Printf("Connection worked!\n")
}

func Connect(applicationName string) (conn *pgx.Conn) {
	var runtimeParams map[string]string
	runtimeParams = make(map[string]string)
	runtimeParams["application_name"] = applicationName
	connConfig := pgx.ConnConfig{
		User:              "postgres",
		Password:          "postgres",
		Host:              "localhost",
		Port:              5432,
		Database:          "postgres",
		TLSConfig:         nil,
		UseFallbackTLS:    false,
		FallbackTLSConfig: nil,
		RuntimeParams:     runtimeParams,
	}
	conn, err := pgx.Connect(connConfig)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to establish connection: %v\n", err)
		os.Exit(1)
	}
	return conn
}

I'm going to re-use this simple connection for the rest of my exploration of pgx, so I may as well turn it into a little library that I can call from the rest of my examples:

package util

import (
	"fmt"
	"os"

	"github.com/jackc/pgx"
)

func Connect(applicationName string) (conn *pgx.Conn) {
	var runtimeParams map[string]string
	runtimeParams = make(map[string]string)
	runtimeParams["application_name"] = applicationName
	connConfig := pgx.ConnConfig{
		User:              "postgres",
		Password:          "postgres",
		Host:              "localhost",
		Port:              5432,
		Database:          "postgres",
		TLSConfig:         nil,
		UseFallbackTLS:    false,
		FallbackTLSConfig: nil,
		RuntimeParams:     runtimeParams,
	}
	conn, err := pgx.Connect(connConfig)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to establish connection: %v\n", err)
		os.Exit(1)
	}
	return conn
}

Here is my directory layout:

~/go/src/github.com/manniwood/playground/pgxfiles
					    ├── cmd
					    │   ├── connect
					    │   │   └── main.go
					    └── util.go

And now my simple connection program looks like this:

package main

import (
	"fmt"

	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	conn := util.Connect("connect test")
	defer conn.Close()
	fmt.Printf("Connection worked!\n")
}

Next, on The pgx Files: creating a table!