Home

Awesome

Test Lint GoReportCard

pggen - generate type safe Go methods from Postgres SQL queries

pggen generates Go code to provide a typesafe wrapper to run Postgres queries. If Postgres can run the query, pggen can generate code for it. The generated code is strongly-typed with rich mappings between Postgres types and Go types without relying on interface{}. pggen uses prepared queries, so you don't have to worry about SQL injection attacks.

How to use pggen in three steps:

  1. Write arbitrarily complex SQL queries with a name and a :one, :many, or :exec annotation. Declare inputs with pggen.arg('input_name').

    -- name: SearchScreenshots :many
SELECT ss.id, array_agg(bl) AS blocks
FROM screenshots ss
  JOIN blocks bl ON bl.screenshot_id = ss.id
WHERE bl.body LIKE pggen.arg('body') || '%'
GROUP BY ss.id
ORDER BY ss.id
LIMIT pggen.arg('limit') OFFSET pggen.arg('offset');

  2. Run pggen to generate Go code to create type-safe methods for each query.

    pggen gen go \
    --schema-glob schema.sql \
    --query-glob 'screenshots/*.sql' \
    --go-type 'int8=int' \
    --go-type 'text=string'

    That command generates methods and type definitions like below. The full example is in ./example/composite/query.sql.go.

    type SearchScreenshotsParams struct {
    Body   string
    Limit  int
    Offset int
}

type SearchScreenshotsRow struct {
    ID     int      `json:"id"`
    Blocks []Blocks `json:"blocks"`
}

// Blocks represents the Postgres composite type "blocks".
type Blocks struct {
    ID           int    `json:"id"`
    ScreenshotID int    `json:"screenshot_id"`
    Body         string `json:"body"`
}

func (q *DBQuerier) SearchScreenshots(
    ctx context.Context,
    params SearchScreenshotsParams,
) ([]SearchScreenshotsRow, error) {
    /* omitted */
}

  3. Use the generated code.

    var conn *pgx.Conn
q := NewQuerier(conn)
rows, err := q.SearchScreenshots(ctx, SearchScreenshotsParams{
    Body:   "some_prefix",
    Limit:  50,
    Offset: 200,
})

Pitch

Why should you use pggen instead of the myriad of Go SQL bindings?

Anti-pitch

I'd like to try to convince you why you shouldn't use pggen. Often, this is far more revealing than the pitch.

Install

Download precompiled binaries

Precompiled binaries from the latest release. Change ~/bin if you want to install to a different directory. All assets are listed on the releases page.

Make sure pggen works:

pggen gen go --help

Install from source

Requires Go 1.16 because pggen uses go:embed. Installs to $GOPATH/bin.

go install github.com/jschaf/pggen/cmd/pggen@latest

Make sure pggen works:

pggen gen go --help

Usage

Generate code using Docker to create the Postgres database from a schema file:

# --schema-glob runs all matching files on Dockerized Postgres during database 
# creation.
pggen gen go \
    --schema-glob author/schema.sql \
    --query-glob author/query.sql

# Output: author/query.go.sql

# Or with multiple schema files. The schema files run on Postgres
# in the order they appear on the command line.
pggen gen go \
    --schema-glob author/schema.sql \
    --schema-glob book/schema.sql \
    --schema-glob publisher/schema.sql \
    --query-glob author/query.sql

# Output: author/query.sql.go

Generate code using an existing Postgres database (useful for custom setups):

pggen gen go \
    --query-glob author/query.sql \
    --postgres-connection "user=postgres port=5555 dbname=pggen"

# Output: author/query.sql.go

Generate code for multiple query files. All the query files must reside in the same directory. If query files reside in different directories, you can use --output-dir to set a single output directory:

pggen gen go \
    --schema-glob schema.sql \
    --query-glob author/fiction.sql \
    --query-glob author/nonfiction.sql \
    --query-glob author/bestselling.sql

# Output: author/fiction.sql.go
#         author/nonfiction.sql.go
#         author/bestselling.sql.go

# Or, using a glob. Notice quotes around glob pattern to prevent shell 
# expansion.
pggen gen go \
    --schema-glob schema.sql \
    --query-glob 'author/*.sql'

Examples

Examples embedded in the repo:

Features

IDE integration

If your IDE provides SQL autocomplete, you may want to get rid of its warnings by declaring the following DDL schema.

-- Exists solely so editors don't underline every pggen.arg() expression in
-- squiggly red.
CREATE SCHEMA pggen;

-- pggen.arg defines a named parameter that's eventually compiled into a
-- placeholder for a prepared query: $1, $2, etc.
CREATE FUNCTION pggen.arg(param TEXT) RETURNS text AS $$SELECT null$$ LANGUAGE sql;

Tutorial

Let's say we have a database with the following schema in author/schema.sql:

CREATE TABLE author (
  author_id  serial PRIMARY KEY,
  first_name text NOT NULL,
  last_name  text NOT NULL,
  suffix     text NULL
)

First, write a query in the file author/query.sql. The query name is FindAuthors and the query returns :many rows. A query can return :many rows, :one row, or :exec for update, insert, and delete queries.

-- FindAuthors finds authors by first name.
-- name: FindAuthors :many
SELECT * FROM author WHERE first_name = pggen.arg('first_name');

Second, use pggen to generate Go code to author/query.sql.go:

pggen gen go \
    --schema-glob author/schema.sql \
    --query-glob author/query.sql

We'll walk through the generated file author/query.sql.go:

Contributing

See CONTRIBUTING.md and ARCHITECTURE.md.

Acknowledgments

pggen was directly inspired by sqlc. The primary difference between pggen and sqlc is how each tool infers the type and nullability of the input parameters and output columns for SQL queries.

sqlc parses the queries in Go code, using Cgo to call the Postgres parser.c library. After parsing, sqlc infers the types of the query parameters and result columns using custom logic in Go. In contrast, pggen gets the same type information by running the queries on Postgres and then fetching the type information for Postgres catalog tables.

Use sqlc if you don't wish to run Postgres to generate code or if you need better nullability analysis than pggen provides.

Use pggen if you can run Postgres for code generation, and you use complex queries that sqlc is unable to parse. Additionally, use pggen if you have a custom database setup that's difficult to replicate in a schema file. pggen supports running on any database with any extensions.