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 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 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):
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 Catalogue. 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.
Top comments (0)