Aiven for PostgreSQL® for your Go application

Applications and databases go together like milk and cookies; on the menu today, a PostgreSQL® cookie with a splash of refreshing Go milk. Bon appetit!

Subscribe to RSS

The libraries I picked for this post are pgx which is a nice PostgreSQL-specific alternative to database/sql and Squirrel because I love the name (it also has some quite cool features).

Start with a database

It would make sense to already have a PostgreSQL® database handy, ideally with some data in it. If you don't already have an Aiven account, then sign up and start a PostgreSQL service. If you already have some data to use, great! I'm using some open data from the Kepler space mission, you can follow along with the Discover exoplanets with PostgreSQL® sample data blog post to set this up yourself.

In the Aiven Console, copy the connection string for Postgres - or if you are using a different database, copy the postgres://.... connection string.

Connecting to PostgreSQL from Go

Go has built-in database support in its database/sql library, but it's quite generic since it has to be able to cope with so many different database platforms. For applications that are specifically connecting to PostgreSQL, it make sense to use a specialist library.

For this example I chose jackc/pgx which is PostgreSQL-specific and has some nice features around understanding PostgreSQL data types in addition to improved performance. The overall pattern isn't radically different from other applications using database/sql, which makes it feel quite familiar.

Set the connection string you copied earlier as the DATABASE_URL environment variable. Then initialise your go application with go mod init pgfun; pgx uses go modules.

When you have everything set up, try the code example below to connect to a database and run one query (my database has the exoplanets data in it):

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v4/pgxpool"
)

func main() {
	dbpool, dberr := pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if dberr != nil {
		panic("Unable to connect to database")
	}
	defer dbpool.Close()

	sql := "SELECT kepler_name, koi_score " +
        "FROM cumulative " +
        "WHERE kepler_name IS NOT NULL AND koi_pdisposition = 'CANDIDATE' " +
        "ORDER BY koi_score LIMIT 5"
	rows, sqlerr := dbpool.Query(context.Background(), sql)
	if sqlerr != nil {
		panic(fmt.Sprintf("QueryRow failed: %v", sqlerr))
	}

	for rows.Next() {
		var planet_name string
		var score float64
		rows.Scan(&planet_name, &score)
		fmt.Printf("%s\t%.2f\n", planet_name, score)
	}
}

In the main() function, first the code connects to the database using the connection pool features of pgx. As a general rule I consider that pooling connections for PostgreSQL is a wise move which is why it's used here.

Then a simple SQL statement that shows the exoplanets that the Kepler mission assigned a "CANDIDATE" status, and how confident they were that it was a real planet. The statement has a row limit (the dataset has ~2k rows without the limit) so you only see the five lowest-confidence-scoring planets in the final section that iterates over the rows and reads the data in as variables.

I love that the names are here - if you're interested you can even look up the planets in the Exoplanet Catalog. I spent way too much time browsing that, science is ace!

So to recap, we have one PostgreSQL connection string, one Go database library and a hardcoded SQL query. What's next?

Fluid Interfaces with Squirrel

While the pgx library is a PostgreSQL-specific alternative to database/sql, it also has a compatible interface. This is ideal if you want to switch an existing application over, but it also means that other tools designed to play nicely with database/sql can work with pgx too. That's good news for me because I want to find something more elegant than my hardcoded SQL string.

Squirrel!

No, I didn't get distracted, it's an SQL library. Although, it is a bit of a shiny toy. It's a fluid interface for building SQL queries, and SQL lends itself well to being thought about in this way.

Rebuilding our existing SQL query in this interface produces something like this:

	planets := psql.Select("kepler_name", "koi_score").
		From("cumulative").
		Where(sq.NotEq{"kepler_name": nil}).
		Where(sq.Eq{"koi_pdisposition": "CANDIDATE"}).
		OrderBy("koi_score").
		Limit(5)

It's a bit more manageable and I can imagine building this in a real application much more easily than concatenating bits of SQL string together. It's still possible to inspect the SQL that it built as a string by calling planets.ToSql(), which is a nice touch.

Switching pgx over to the "pretend to be database/sql" mode needed a little refactoring. I also need a spoonful of secret sauce to make Squirrel play nicely with PostgreSQL, so here's the whole runnable example at once. It expects the database connection string to be in DATABASE_URL as for the previous example:

package main

import (
	"database/sql"
	"fmt"
	"os"

	sq "github.com/Masterminds/squirrel"
	_ "github.com/jackc/pgx/v4/stdlib"
)

func main() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		panic("Unable to connect to database")
	}
	defer db.Close()

    // a little magic to tell squirrel it's postgres
	psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

	planets := psql.Select("kepler_name", "koi_score").
		From("cumulative").
		Where(sq.NotEq{"kepler_name": nil}).
		Where(sq.Eq{"koi_pdisposition": "CANDIDATE"}).
		OrderBy("koi_score").
		Limit(5)

	rows, sqlerr := planets.RunWith(db).Query()
	if sqlerr != nil {
		panic(fmt.Sprintf("QueryRow failed: %v", sqlerr))
	}

	for rows.Next() {
		var planet_name string
		var score float64
		rows.Scan(&planet_name, &score)
		fmt.Printf("%s\t%.2f\n", planet_name, score)
	}
}

Note that the pgx import is different this time to get the database/sql compatible library that Squirrel expects in its RunWith() method. The connection step changes a little as well so don't try to amend the previous example; this one is different.

The psql variable holds a reconfigured squirrel which is needed because PostgreSQL handles placeholders slightly differently. Skipping this causes the code to tell you there is a syntax error near the LIMIT clause. Hopefully now I've written this down my valued readers can avoid this problem, and I may even remember this next time too!

Fun with Go and PostgreSQL

This was a lightweight introduction to show you a couple of my favourite libraries for Go and PostgreSQL applications - and of course with an open data set to play with, the fun is multiplied :)

Here are some related links and further reading, reach out to us on Twitter if you have questions. We are always pleased to chat.

Wrapping up

Not using Aiven services yet? Sign up now for your free trial at https://console.aiven.io/signup!

In the meantime, make sure you follow our changelog and RSS feeds or our LinkedIn and Twitter accounts to stay up-to-date with product and feature-related news.