Choosing a low-level PostgreSQL driver for Go
When working with PostgreSQL in Go without an ORM, developers have several solid options. The standard library's database/sql interface remains the baseline, but specialized drivers and wrapper packages offer different trade-offs in verbosity, performance, and PostgreSQL-specific feature support. This guide walks through the most common approaches using a sample schema for an online course platform.
The data model involves three tables: users, courses, and projects. A join table links users to courses (many-to-many), and projects belong to a single course (one-to-one). One column worth noting is hashtags text[], which deliberately uses PostgreSQL's array type to demonstrate how custom types are handled across the different Go packages.
create table if not exists courses (
id bigserial primary key,
created_at timestamp(0) with time zone not null default now(),
title text not null,
hashtags text[]
);
create table if not exists projects (
id bigserial primary key,
name text not null,
content text not null,
course_id bigint not null references courses (id) on delete cascade
);
create table if not exists users (
id bigserial primary key,
name text not null
);
create table if not exists course_user (
course_id bigint not null references courses (id) on delete cascade,
user_id bigint not null references users (id) on delete cascade,
constraint course_user_key primary key (course_id, user_id)
);
database/sql with the pq driver
The most traditional route is using database/sql with the pq driver. Setup follows the familiar pattern: blank-import the driver so it self-registers, then open a connection with sql.Open("postgres", ...). The connection string can be supplied via environment variable, for example pointing to a database named testmooc with user testuser.
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/lib/pq"
)
// Check is a helper that terminates the program with err.Error() logged in
// case err is not nil.
func Check(err error) {
if err != nil {
log.Fatal(err)
}
}
func main() {
db, err := sql.Open("postgres", os.Getenv("MOOCDSN"))
Check(err)
defer db.Close()
// ... use db here
}
Go structs for the tables mirror the schema, but relationships between tables are not represented. A course type doesn't contain a slice of projects; that association must be built manually through queries. PostgreSQL arrays are mapped to Go slices with the help of pq.Array:
type course struct {
Id int64
CreatedAt time.Time
Title string
Hashtags []string
}
type user struct {
Id int64
Name string
}
type project struct {
Id int64
Name string
Content string
}
Querying involves the standard database/sql scanning loop. For example, to fetch all courses a user has signed up for, you join the courses and join tables, then scan each row into a struct:
func dbAllCoursesForUser(db *sql.DB, userId int64) ([]course, error) {
rows, err := db.Query(`
select courses.id, courses.created_at, courses.title, courses.hashtags
from courses
inner join course_user on courses.id = course_user.course_id
where course_user.user_id = $1`, userId)
if err != nil {
return nil, err
}
defer rows.Close()
var courses []course
for rows.Next() {
var c course
err = rows.Scan(&c.Id, &c.CreatedAt, &c.Title, pq.Array(&c.Hashtags))
if err != nil {
return nil, err
}
courses = append(courses, c)
}
if err := rows.Err(); err != nil {
return nil, err
}
return courses, nil
}
More complex queries follow the same pattern. Fetching all projects assigned to a user across multiple courses requires a three-table join, but the Go code stays nearly identical in structure.
func dbAllProjectsForUser(db *sql.DB, userId int64) ([]project, error) {
rows, err := db.Query(`
select projects.id, projects.name, projects.content
from courses
inner join course_user on courses.id = course_user.course_id
inner join projects on courses.id = projects.course_id
where course_user.user_id = $1`, userId)
if err != nil {
return nil, err
}
defer rows.Close()
var projects []project
for rows.Next() {
var p project
err = rows.Scan(&p.Id, &p.Name, &p.Content)
if err != nil {
return nil, err
}
projects = append(projects, p)
}
if err := rows.Err(); err != nil {
return nil, err
}
return projects, nil
}
pgx: maintenance-mode alternative
The pq driver is effectively in maintenance mode. Its own README recommends pgx, which is actively maintained and offers two modes of operation: it can serve as a drop-in database/sql driver, or as a direct PostgreSQL interface that bypasses the standard library's constraints.
Using pgx in database/sql mode requires only swapping the import and changing the driver name in sql.Open:
_ "github.com/jackc/pgx/v4/stdlib"
db, err := sql.Open("pgx", os.Getenv("MOOCDSN"))
All existing code continues to work unchanged. The direct mode, however, requires adjustments. Instead of sql.Open, you call pgx.Connect. The struct types stay the same, but querying code becomes slightly different — notably, PostgreSQL arrays map directly to Go slices without needing a pq.Array wrapper:
ctx := context.Background()
conn, err := pgx.Connect(ctx, os.Getenv("MOOCDSN"))
Check(err)
defer conn.Close(ctx)
func dbAllCoursesForUser(ctx context.Context, conn *pgx.Conn, userId int64) ([]course, error) {
rows, err := conn.Query(ctx, `
select courses.id, courses.created_at, courses.title, courses.hashtags
from courses
inner join course_user on courses.id = course_user.course_id
where course_user.user_id = $1`, userId)
if err != nil {
return nil, err
}
defer rows.Close()
var courses []course
for rows.Next() {
var c course
err = rows.Scan(&c.Id, &c.CreatedAt, &c.Title, &c.Hashtags)
if err != nil {
return nil, err
}
courses = append(courses, c)
}
if err := rows.Err(); err != nil {
return nil, err
}
return courses, nil
}
The direct mode's advantages include native support for custom PostgreSQL types and JSON, an advanced connection pool, and use of PostgreSQL's binary protocol for faster marshaling. pgx's benchmarks show considerable performance gains in some cases.
sqlx: cutting the scanning boilerplate
One recurring complaint about database/sql is verbosity. Every query requires a manual row-by-row scan loop with explicit field assignment. The reflection-based sqlx package automates that process: you pass a slice of structs, and it maps columns to fields automatically.
Setup resembles vanilla database/sql; sqlx.Open wraps sql.Open and returns a sqlx.DB type that extends sql.DB with convenient methods. Querying becomes a single call:
import (
"fmt"
"log"
"os"
"github.com/jmoiron/sqlx"
_ "github.com/lib/pq"
)
func Check(err error) {
if err != nil {
log.Fatal(err)
}
}
func main() {
db, err := sqlx.Open("postgres", os.Getenv("MOOCDSN"))
Check(err)
defer db.Close()
// ... use db here
}
func dbAllCoursesForUser(db *sqlx.DB, userId int64) ([]course, error) {
var courses []course
err := db.Select(&courses, `
select courses.id, courses.created_at, courses.title, courses.hashtags
from courses
inner join course_user on courses.id = course_user.course_id
where course_user.user_id = $1`, userId)
if err != nil {
return nil, err
}
return courses, nil
}
There is one trade-off: sqlx uses reflection and sometimes needs hints. The created_at column won't map to a CreatedAt field automatically, requiring an explicit field tag:
type course struct {
Id int64
CreatedAt time.Time `db:"created_at"`
Title string
Hashtags pq.StringArray
}
sqlx works on top of a database/sql driver — either pq or pgx's stdlib mode. It does not support pgx's native driver. For that, the scany package supports both native and stdlib pgx drivers with a similar scanning API.
The middle ground
Weighing sqlx or scany against raw database/sql comes down to code volume versus dependency risk. A typical query function shrinks by roughly 14 lines with sqlx. For 50 queries, that's about 700 lines of routine scanning code removed — but that saving is rarely critical.
The real advantage is the focused scope. Unlike ORMs, which introduce significant abstraction and behavior, packages like sqlx do one thing: map query results to structs. The standard library already takes this approach with encoding/json, so the pattern is proven. And because these utilities are narrow, they're relatively easy to remove and replace if needed. This puts them at a sensible middle point between raw SQL access and a full ORM — with correspondingly moderate benefits and trade-offs.
