Go and PostgreSQL with pgx and squirrel libraries
Tags: go, postgres
Published: 2021-02-26 17:48:14

---
title: Go and PostgreSQL with pgx and squirrel libraries
published: true
date: 2021-02-26 00:00:00 UTC
tags: golang, postgresql
canonical_url: https://aiven.io/blog/aiven-for-postgresql-for-your-go-application
---

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e0wsvzqxax4rqmokmx2o.png)

Applications and databases go together like milk and cookies; on the menu today is a PostgreSQL-flavored cookie with a splash of refreshing Go milk. I've picked two of my favorite Go libraries to show how to connect to and work with your Aiven PostgreSQL service. The libraries are [pgx](https://github.com/jackc/pgx) which is a nice PostgreSQL-specific alternative to `database/sql` and [Squirrel](https://github.com/Masterminds/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](https://console.aiven.io/signup) 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 recent 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):

```go

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](https://help.aiven.io/en/articles/964730-postgresql-connection-pooling) 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 Catalogue](https://exoplanets.nasa.gov/discovery/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:

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

```go
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](https://twitter.com/aiven_io) if you have questions. We are always pleased to chat.

* [Getting Started with Aiven PostgreSQL](https://help.aiven.io/en/articles/489573-getting-started-with-aiven-postgresql)
* [Importing Exoplanets into your PostgreSQL](#add this when it publishes)
* [Code examples for Aiven connections](https://github.com/aiven/aiven-examples)
* [Postgres13 is Available](#add this if true by publication date)