Go + pgx

Backend: go-pgx | Library: pgx v5 | Engine: PostgreSQL

SQL input

-- @name GetUser
-- @returns :one
SELECT id, name, email, created_at FROM users WHERE id = $1;

-- @name ListUsers
-- @returns :many
SELECT id, name FROM users ORDER BY name LIMIT $1;

-- @name CreateUser
-- @returns :exec
INSERT INTO users (name, email) VALUES ($1, $2);

Schema:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Generated code

Every generated file opens with a provenance header, then a “Code generated by scythe” marker and package/import block (integration_tests/go-pgx/generated/queries.go:1-12):

// scythe:provenance v=0.15.0 backend=go-pgx engine=postgresql schema=sch1:2e813606acee8b51 queries=q1:9c4e1f77a0b3d582
// Code generated by scythe. DO NOT EDIT.
// Run `goimports -w .` to fix imports.
package queries

The connection type is *pgxpool.Pool, not *pgx.Conn. Generated function parameters use the column’s PascalCase field name directly (to_pascal_case(&p.field_name), crates/scythe-codegen/src/backends/go_pgx.rs) – not idiomatic Go’s lowerCamelCase, and not the ID initialism convention: id becomes Id, matching the struct field name exactly (integration_tests/go-pgx/generated/queries.go:33).

Struct with json tags and nullable pointers

type GetUserRow struct {
  Id        int32     `json:"id"`
  Name      string    `json:"name"`
  Email     *string   `json:"email"`
  CreatedAt time.Time `json:"created_at"`
}

Nullable columns use *T pointers. All field names are PascalCase; json tags preserve the SQL column name.

:one – QueryRow + Scan, zero value on no rows

// Returns the zero value of the struct if no row is found.
// Use pgx.ErrNoRows to distinguish not-found from other errors.
func GetUser(ctx context.Context, db *pgxpool.Pool, Id int32) (GetUserRow, error) {
  row := db.QueryRow(ctx, "SELECT id, name, email, created_at FROM users WHERE id = $1", Id)
  var r GetUserRow
  err := row.Scan(&r.Id, &r.Name, &r.Email, &r.CreatedAt)
  return r, err
}

context.Context is always the first parameter. There is no if err != nil branch here – callers check errors.Is(err, pgx.ErrNoRows) to distinguish “no row” from other errors.

:many

type ListUsersRow struct {
  Id   int32  `json:"id"`
  Name string `json:"name"`
}

func ListUsers(ctx context.Context, db *pgxpool.Pool, Limit int64) ([]ListUsersRow, error) {
  rows, err := db.Query(ctx, "SELECT id, name FROM users ORDER BY name LIMIT $1", Limit)
  if err != nil {
    return nil, err
  }
  defer rows.Close()

  var result []ListUsersRow
  for rows.Next() {
    var r ListUsersRow
    if err := rows.Scan(&r.Id, &r.Name); err != nil {
      return nil, err
    }
    result = append(result, r)
  }
  return result, rows.Err()
}

:exec

func CreateUser(ctx context.Context, db *pgxpool.Pool, Name string, Email *string) error {
  _, err := db.Exec(ctx, "INSERT INTO users (name, email) VALUES ($1, $2)", Name, Email)
  return err
}

Enum generation

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');

type UserStatus string

const (
  UserStatusActive   UserStatus = "active"
  UserStatusInactive UserStatus = "inactive"
  UserStatusBanned   UserStatus = "banned"
)

Type mappings

SQL Type	Neutral	Go (pgx)
SERIAL / INTEGER	int32	int32
BIGINT	int64	int64
TEXT / VARCHAR	string	string
BOOLEAN	bool	bool
BYTEA	bytes	[]byte
UUID	uuid	uuid.UUID
NUMERIC	decimal	decimal.Decimal
DATE / TIME / TIMESTAMPTZ	date / time / datetime_tz	time.Time
INTERVAL	interval	time.Duration
JSON / JSONB	json	json.RawMessage
INET	inet	netip.Addr
TEXT[]	array<string>	[]string
nullable column	nullable	*T