sqlgen

sqlgen - generate type safe Go methods from Postgres SQL queries

This library is an experimental fork of https://github.com/jschaf/pggen and changes behavior in order to focus on the interactive developer experience. The goal is that it should be possible for a developer who knows sql only to edit, author, execute, and test queries which will subsequently be 'compiled' by sqlgen without modification.

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

A strong design goal of this library is that all the sql you write is valid executable sql which makes it as easy as possible to develop and debug your queries interactively.

How to use sqlgen in three steps:

1. Write arbitrarily complex SQL queries with a name and a :one, :many, or :exec annotation. Declare inputs with sqlgen.arg('input_name', <default value expression>) or sqlgen.required_arg('input_name', <default type expression>).

   -- 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 sqlgen.required_arg('body', null::text) || '%'
   GROUP BY ss.id
   ORDER BY ss.id
   LIMIT sqlgen.arg('limit', 100) OFFSET sqlgen.arg('offset', 0);
   

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

   sqlgen 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.

   ```go
   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 sqlgen instead of the myriad of Go SQL bindings?

• sqlgen generates code by introspecting the database system catalogs, so you can use any database extensions or custom methods, and it will just work. For database types that sqlgen doesn't recognize, you can provide your own type mappings.

• sqlgen scales to Postgres databases of any size and supports incremental adoption. sqlgen is narrowly tailored to only generate code for queries you write in SQL. sqlgen will not create a model for every database object. Instead, sqlgen only generates structs necessary to run the queries you specify.

• sqlgen works with any Postgres database with any extensions. Under the hood, sqlgen runs each query and uses the Postgres catalog tables, pg_type, pg_class, and pg_attribute, to get perfect type information for both the query parameters and result columns.

• sqlgen works with all Postgres queries. If Postgres can run the query, sqlgen can generate Go code for the query.

• sqlgen uses pgx, a faster replacement for lib/pq, the original Go Postgres library that's now in maintenance mode.

• sqlgen provides a batch (aka query pipelining) interface for each generated query with pgx.Batch. Query pipelining is the reason Postgres sits atop the TechEmpower benchmarks. Using a batch enables sending multiple queries in a single network round-trip instead of one network round-trip per query.

Anti-pitch

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

• You want auto-generated models for every table in your database. sqlgen only generates code for each query in a query file. sqlgen requires custom SQL for even the simplest CRUD queries. Use gorm or any of alternatives listed at awesome Go ORMs.

• You use a database other than Postgres. sqlgen only supports Postgres. sqlc, a similar tool which inspired sqlgen, has early support for MySQL.

• You want an active-record pattern where models have methods like find, create, update, and delete. sqlgen only generates code for queries you write. Use gorm.

• You prefer building queries in a Go dialect instead of SQL. I'd recommend investing in really learning SQL; it will payoff. Otherwise, use squirrel, goqu, or go-sqlbuilder

• You don't want to add a Postgres or Docker dependency to your build phase. Use sqlc, though you might still need Docker. sqlc generates code by parsing the schema file and queries in Go without using Postgres.

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.

• MacOS Apple Silicon (arm64)

  mkdir -p ~/bin \
    && curl --silent --show-error --location --fail 'https://github.com/breathe/sqlgen/releases/latest/download/sqlgen-darwin-arm64.tar.xz' \
    | tar -xJf - -C ~/bin/
  

• MacOS Intel (amd64)

  mkdir -p ~/bin \
    && curl --silent --show-error --location --fail 'https://github.com/breathe/sqlgen/releases/latest/download/sqlgen-darwin-amd64.tar.xz' \
    | tar -xJf - -C ~/bin/
  

• Linux (amd64)

  mkdir -p ~/bin \
    && curl --silent --show-error --location --fail 'https://github.com/breathe/sqlgen/releases/latest/download/sqlgen-linux-amd64.tar.xz' \
    | tar -xJf - -C ~/bin/
  

• Windows (amd64)

  mkdir -p ~/bin \
    && curl --silent --show-error --location --fail 'https://github.com/breathe/sqlgen/releases/latest/download/sqlgen-windows-amd64.tar.xz' \
    | tar -xJf - -C ~/bin/
  

Make sure sqlgen works:

sqlgen gen go --help

Install from source

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

go install github.com/breathe/sqlgen/cmd/sqlgen@latest

Make sure sqlgen works:

sqlgen 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.
sqlgen 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.
sqlgen 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):

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

# 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:

sqlgen 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.
sqlgen gen go \
    --schema-glob schema.sql \
    --query-glob 'author/*.sql'

Examples

Examples embedded in the repo:

• ./example/acceptance_test.go - End-to-end examples of how to call sqlgen.
• ./example/author - A single table schema with simple queries.
• ./example/composite - Arrays of composite (aka row or table) types.
• ./example/custom_types - Mapping new Postgres types to Go types.
• ./example/device - Complex queries with a 1:many relationship between a user table and device table.
• ./example/enums - Postgres and Go enums.
• ./example/erp - A few tables with mildly complex queries.
• ./example/go_pointer_types - Mapping to pointer types like *int instead of pgtype.Int8.
• ./example/ltree - Support for the ltree Postgres extension.
• ./example/nested - Complex, nested composite (aka row or table) types.
• ./example/pgcrypto - pgcrypto Postgres extension.
• ./example/syntax - A smoke test of interesting SQL syntax.
• ./example/void - Support for void in select columns.

Features

• JSON struct tags: All <query_name>Row structs include JSON struct tags using the Postgres column name. To change the struct tag, use an SQL column alias.

  -- name: FindAuthors :many
  SELECT first_name, last_name as family_name FROM author;
  

  Generates:

  type FindAuthorsRow struct {
      FirstName   string `json:"first_name"`
      FamilyName  string `json:"family_name"`
  }
  

• Acronyms: Custom acronym support so that author_id renders as AuthorID instead of AuthorId. Supports two formats:

  1. Long form: --acronym <word>=<relacement>: replaces <word> with <replacement> literally. Useful for plural acronyms like author_ids which should render as AuthorIDs, not AuthorIds. For the IDs example, use --acronym ids=IDs.
  2. Short form: --acronym <word>: replaces <word> with uppercase <WORD>. Equivalent to --acronym <word>=<WORD>

  By default, sqlgen includes --acronym id to render id as ID.

• Enums: Postgres enums map to Go string constant enums. The Postgres type:

  CREATE TYPE device_type AS ENUM ('undefined', 'phone', 'ipad');
  

  sqlgen generates the following Go code when used in a query:

  // DeviceType represents the Postgres enum device_type.
  type DeviceType string
  
  const (
      DeviceTypeUndefined DeviceType = "undefined"
      DeviceTypePhone     DeviceType = "phone"
      DeviceTypeIpad      DeviceType = "ipad"
  )
  
  func (d DeviceType) String() string { return string(d) }
  

• Custom types: Use a custom Go type to represent a Postgres type with the --go-type flag. The format is <pg_type>=<qualified_go_type>. For example:

  sqlgen gen go \
      --schema-glob example/custom_types/schema.sql \
      --query-glob example/custom_types/query.sql \
      --go-type 'int8=*int' \
      --go-type 'int4=int' \
      --go-type '_int4=[]int' \
      --go-type 'text=*github.com/breathe/sqlgen/mytype.String' \
      --go-type '_text=[]*github.com/breathe/sqlgen/mytype.String'
  

  pgx must be able to decode the Postgres type using the given Go type. That means the Go type must fulfill at least one of following:

  • The Go type is a wrapper around primitive type, like type AuthorID int. pgx will use decode methods on the underlying primitive type.

  • The Go type implements both pgtype.BinaryDecoder and pgtype.TextDecoder. pgx will use the correct decoder based on the wire format. See the pgtype repo for many example types.

  • The pgx connection executing the query must have registered a data type using the Go type with ConnInfo.RegisterDataType. See the example/custom_types test for an example.

    ci := conn.ConnInfo()
    
    ci.RegisterDataType(pgtype.DataType{
    	Value: new(pgtype.Int2),
    	Name:  "my_int",
    	OID:   myIntOID,
    })
    

  • The Go type implements sql.Scanner.

  • pgx is able to use reflection to build an object to write fields into.

• Nested structs (composite types): sqlgen creates child structs to represent Postgres composite types that appear in output columns.

  -- name: FindCompositeUser :one
  SELECT ROW (15, 'qux')::"user" AS "user";
  

  sqlgen generates the following Go code:

  // User represents the Postgres composite type "user".
  type User struct {
      ID   pgtype.Int8
      Name pgtype.Text
  }
  
  func (q *DBQuerier) FindCompositeUser(ctx context.Context) (User, error) {}
  

IDE integration

Interactive use is easy since queries written for sqlgen are valid sql.

You just need to ensure appropriate definition exists for sqlgen.arg() and sqlgen.required_arg() functions.

CREATE SCHEMA sqlgen;

-- sqlgen.arg defines a named parameter that's eventually compiled into a
-- placeholder for a prepared query: coalesce($1, default_value), coalesce($2, default_value), etc.
-- you can redefine this function with alternative values for parameters whenever you like to easily
CREATE OR REPLACE FUNCTION sqlgen.arg(param text, default_value_expression ANYELEMENT)
  RETURNS anyelement
  LANGUAGE plpgsql
  AS $func$
BEGIN
  IF param = 'name' THEN
    RETURN 'bob';
  ELSIF param = 'age' THEN
    RETURN 12;
  ELSIF param = 'user' THEN
    RETURN ROW(1,
      'bob',
      'lasty')::some_custom_user_type;
  ELSIF param = 'ids' THEN
    RETURN '{1,2}'::integer[];
  ELSIF param = 'sort' THEN
    RETURN 'sort_column_name';
  ELSIF param = 'sort_direction' THEN
    RETURN 'ASC';
  ELSIF param = 'limit' THEN
    RETURN 6;
  ELSE
    <!-- anything we don't override above simply resolves to the <default value expression> as normal -->
    RETURN default_value_expression;
  END IF;
END
$func$;

-- sqlgen.arg defines a named parameter that's eventually compiled into a
-- placeholder for a prepared query: $1, $2, etc.
-- you can easily redefine this function with alternative values for parameters whenever you like
-- note that the default_value_expression is only relevant when the query is executed interactively
-- the compiled form of `sqlgen.required_arg` assumes that a non-null value is always supplied for param
-- and the codegen for the type ensures the same
CREATE OR REPLACE FUNCTION sqlgen.required_arg(param text, default_value_expression ANYELEMENT)
  RETURNS anyelement
  LANGUAGE plpgsql
  AS $func$
BEGIN
  IF param = 'name' THEN
    RETURN 'bob';
  ELSIF param = 'age' THEN
    RETURN 12;
  ELSIF param = 'user' THEN
    RETURN ROW(1,
      'bob',
      'lasty')::some_custom_user_type;
  ELSIF param = 'ids' THEN
    RETURN '{1,2}'::integer[];
  ELSIF param = 'sort' THEN
    RETURN 'sort_column_name';
  ELSIF param = 'sort_direction' THEN
    RETURN 'ASC';
  ELSIF param = 'limit' THEN
    RETURN 6;
  ELSE
    -- anything we don't override above resolves to the <default value expression>
    -- note that the default_value_expression for a required_arg is only ever used when
    -- executing the query interactively
    RETURN default_value_expression;
  END IF;
END
$func$;



# Tutorial

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

```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 = sqlgen.arg('first_name');

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

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

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

• The Querier interface defines the interface with methods for each SQL query. Each SQL query compiles into three methods, one method for to run the query by itself, and two methods to support batching a query with pgx.Batch.

  // Querier is a typesafe Go interface backed by SQL queries.
  //
  // Methods ending with Batch enqueue a query to run later in a pgx.Batch. After
  // calling SendBatch on pgx.Conn, pgxpool.Pool, or pgx.Tx, use the Scan methods
  // to parse the results.
  type Querier interface {
      // FindAuthors finds authors by first name.
      FindAuthors(ctx context.Context, firstName string) ([]FindAuthorsRow, error)
      // FindAuthorsBatch enqueues a FindAuthors query into batch to be executed
      // later by the batch.
      FindAuthorsBatch(batch *pgx.Batch, firstName string)
      // FindAuthorsScan scans the result of an executed FindAuthorsBatch query.
      FindAuthorsScan(results pgx.BatchResults) ([]FindAuthorsRow, error)
  }
  

  To use the batch interface, create a *pgx.Batch, call the <query_name>Batch methods, send the batch, and finally get the results with the <query_name>Scan methods. See example/author/query.sql_test.go for complete example.

  q := NewQuerier(conn)
  batch := &pgx.Batch{}
  q.FindAuthorsBatch(batch, "alice")
  q.FindAuthorsBatch(batch, "bob")
  results := conn.SendBatch(context.Background(), batch)
  aliceAuthors, err := q.FindAuthorsScan(results)
  bobAuthors, err := q.FindAuthorsScan(results)
  

• The DBQuerier struct implements the Querier interface with concrete implementations of each query method.

  type DBQuerier struct {
      conn genericConn
  }
  

• Create DBQuerier with NewQuerier. The genericConn parameter is an interface over the different pgx connection transports so that DBQuerier doesn't force you to use a specific connection transport. *pgx.Conn, pgx.Tx, and *pgxpool.Pool all implement genericConn.

  // NewQuerier creates a DBQuerier that implements Querier. conn is typically
  // *pgx.Conn, pgx.Tx, or *pgxpool.Pool.
  func NewQuerier(conn genericConn) *DBQuerier {
      return &DBQuerier{
          conn: conn,
      }
  }
  

• sqlgen embeds the SQL query formatted for a Postgres PREPARE statement with parameters indicated by $1, $2, etc. instead of sqlgen.arg('first_name').

  const findAuthorsSQL = `SELECT * FROM author WHERE first_name = $1;`
  

• sqlgen generates a row struct for each query named <query_name>Row. sqlgen transforms the output column names into struct field names from lower_snake_case to UpperCamelCase in internal/casing/casing.go. sqlgen derives JSON struct tags from the Postgres column names. To change the JSON struct name, change the column name in the query.

  type FindAuthorsRow struct {
      AuthorID  int32       `json:"author_id"`
      FirstName string      `json:"first_name"`
      LastName  string      `json:"last_name"`
      Suffix    pgtype.Text `json:"suffix"`
  }
  

  As a convenience, if a query only generates a single column, sqlgen skips creating the <query_name>Row struct and returns the type directly. For example, the generated query for SELECT author_id from author returns int32, not a <query_name>Row struct.

  sqlgen infers struct field types by preparing the query. When Postgres prepares a query, Postgres returns the parameter and column types as OIDs. sqlgen finds the type name from the returned OIDs in internal/codegen/golang/gotype/types.go.

  Choosing an appropriate type is more difficult than might seem at first glance due to null. When Postgres reports that a column has a type text, that column can have both text and null values. So, the Postgres text represented in Go can be either a string or nil. pgtype provides nullable types for all built-in Postgres types. sqlgen tries to infer if a column is nullable or non-nullable. If a column is nullable, sqlgen uses a pgtype Go type like pgtype.Text. If a column is non-nullable, sqlgen uses a more ergonomic type like string. sqlgen's nullability inference implemented in internal/pginfer/nullability.go is rudimentary; a proper approach requires a full explain-plan with some control flow analysis.

• Lastly, sqlgen generates the implementation for each query.

  As a convenience, if a there are only one or two query parameters, sqlgen inlines the parameters into the method definition, as with firstName below. If there are three or more parameters, sqlgen creates a struct named <query_name>Params to pass the parameters to the query method.

  // FindAuthors implements Querier.FindAuthors.
  func (q *DBQuerier) FindAuthors(ctx context.Context, firstName string) ([]FindAuthorsRow, error) {
      rows, err := q.conn.Query(ctx, findAuthorsSQL, firstName)
      if rows != nil {
          defer rows.Close()
      }
      if err != nil {
          return nil, fmt.Errorf("query FindAuthors: %w", err)
      }
      items := []FindAuthorsRow{}
      for rows.Next() {
          var item FindAuthorsRow
          if err := rows.Scan(&item.AuthorID, &item.FirstName, &item.LastName, &item.Suffix); err != nil {
              return nil, fmt.Errorf("scan FindAuthors row: %w", err)
          }
          items = append(items, item)
      }
      if err := rows.Err(); err != nil {
          return nil, err
      }
      return items, err
  }
  

Contributing

See CONTRIBUTING.md and ARCHITECTURE.md.

Acknowledgments

This library began as a direct fork of https://github.com/jschaf/pggen. This library primarily explores alternative api's in order to focus on the interactive developer experience -- meaning particularly, finding ways to ensure that the sql is always directly executable without first going through the tool's compilation process and being called from an external program.