protoql

README

protoql

This package (protoql) and the corresponding protoc plugin (protoc-gen-go-protoql) are intended for rapid early development of gRPC services backed by SQL (mainly postgres) databases. This may not be as useful in a well-established product, where you have plenty of time to thoroughly test your database interactions; but for rapid development in early stage products, I think it's quite valuable.

Usage

First, make sure you've marked messages or files with the options that turn on query generation. See the option package (and its README.md) for details.

$ go install git.sr.ht/~nelsam/protoql/cmd/protoc-gen-go-protoql@latest
$ protoc --proto_path path/to/proto/root \
         --go-protoql_opt=paths=source-relative \
         --go-protoql_out=. path/to/somefile.proto

From there, you will be able to initialize and pass around a database wrapper with query methods:

package main

import (
    "os"
    "log"

    "github.com/jackc/pgx/v4/pgxpool"
    "git.sr.ht/~nelsam/protoql/protopgx"
    "some/package/proto"
)

func main() {
    conn, err := pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatalf("failed to connect to database: %v", err)
    }
    store := proto.NewProtoQL(protopgx.Wrap(conn))
    srv := server.New(store)
    log.Fatal(srv.ListenAndServe())
}

Options

Options are used within .proto files to manipulate query generation. See the option package for documentation about options.

Goals

• Generate go functions that will query a database for data to return as protobuf message types.
  • Other formats (e.g. IPLD) may be supported later.
• Generate code that is mockable for clean unit testing in production code.
• Return multiple rows as an iterator so that we don't force all rows into memory (in a single slice) at one time.
• Support standard SQL for queries, while still auto-loading column names from the actual message types that we return.
• Add failsafes to the generated code to help avoid common gotchas in SQL code (e.g. neglecting rows.Close()).

Anti-Goals

These are features that are specifically out-of-scope for this project.

• Provide a non-SQL language for defining queries.
  • Custom queries are simply SQL syntax with some optional go template logic to define query parameters and scan targets. We don't need to reinvent SQL.
• Auto-generate complicated queries.
  • Anything more complicated than "SELECT {fields} FROM {table} WHERE {pkey} = $1" should be written as a custom query, using the file-level 'query' option. We don't want to try to auto-generate JOINs.
• Tightly couple protobuf message types to SQL tables.
  • gRPC messages and the SQL tables they relate to usually have similar structure, but most good APIs do not have an exact 1:1 match. We should be able to define tables that make sense independently of message types that make sense, and vice versa.

Output

The following will be included in the output:

• A database wrapper type and constructor.
• Methods for any custom queries defined at the file level
• For each message with the (protoql.gen) = true option:
  • An insert method.
  • An update, delete, and read method if and only if the message has primary key fields defined.

Why This Approach?

We chose the wrapper and methods approach because:

1. I couldn't think of a way to use generics that allowed for custom queries and stored procedure calls.
2. I want the generated functions to be mockable, so it's important that they are methods. Mocking functions always requires wrapper types or weird package-global variables.
3. Separating the methods across multiple types gets confusing when custom queries need to return multiple types (e.g. foreign key relationships).
   • Does the ReadAccountProfile method belong on the Account or Profile struct?

Documentation

Overview

Package protoql includes common types that do not need to be generated, imported by generated code in protoc-gen-go-queries. These include interface types that database connections must implement, concrete types that the generated code uses, and any common errors that we may return.

Index

• Constants
• Variables
• type Querier
• type Result
• type Row
• type Rows
• type RowsIterator
  • func NewRowsIterator[T any](ctx context.Context, rows Rows, scan func(Rows) (T, error)) *RowsIterator[T]
  • func (i *RowsIterator[T]) Close()
  • func (i *RowsIterator[T]) Err() error
  • func (i *RowsIterator[T]) Next() (T, error)
  • func (i *RowsIterator[T]) Scan() (T, error)
• type Store
• type Tx

Constants

const NoRowsMsg = "protoql: no rows remaining"

NoRowsMsg is the message that ErrNoRows returns from its Error() method.

Variables

var ErrNoRows = errors.Wrap(sql.ErrNoRows, NoRowsMsg)

ErrNoRows is the error that protoql uses when there are no rows left. This will be used to signal the end of results from an iterator in multi-row queries, or to signal that no rows were found in single-row queries.

Note: this variable may be altered by wrapper implementations (e.g. protopgx) in order to wrap multiple error values. For example, protopgx's init() will ensure that errors.Is(protoql.ErrNoRows, sql.ErrNoRows) and errors.Is(protoql.ErrNoRows, pgx.ErrNoRows) will both return true.

Types

type Querier

type Querier interface {
	Query(context.Context, string, ...any) (Rows, error)
	QueryRow(context.Context, string, ...any) Row
	Exec(context.Context, string, ...any) (Result, error)
}

Querier is any type which can run queries in a database (i.e. a connection or a transaction).

type Result

type Result interface {
	RowsAffected() int
}

Result is the type required for write-only (i.e. zero-result) queries in protoql generated code.

type Row

type Row interface {
	Scan(...any) error
}

Row is the type required for single-result queries in protoql generated code.

type Rows

type Rows interface {
	Scan(...any) error
	Next() bool
	Err() error
	Close()
}

Rows is the type required for multi-result queries in protoql generated code.

type RowsIterator

type RowsIterator[T any] struct {
	// contains filtered or unexported fields
}

RowsIterator is an iterator that may be used to iterate over a result set. T is the type of data that will be returned for each row.

func NewRowsIterator

func NewRowsIterator[T any](ctx context.Context, rows Rows, scan func(Rows) (T, error)) *RowsIterator[T]

NewRowsIterator returns an iterator to iterate over a set of rows. This is used in generated code to return specific row iterator types.

A goroutine will be kicked off to monitor ctx.Done(), calling rows.Close() when ctx.Done() is closed. This works around a common gotcha in go code where a missing `rows.Close()` causes SQL connection leaks. In generated code, ctx.Done will be checked to ensure that it is non-nil to prevent leaked goroutines.

To prevent goroutine leaks in long-running contexts, the goroutine will also be freed when either Close() is called or Next() returns an error (including ErrNoRows).

func (*RowsIterator[T]) Close

func (i *RowsIterator[T]) Close()

Close closes the rows. It is a good practice to 'defer rows.Close()' in calling code.

To work around common gotchas, a missed `defer rows.Close()` will be caught when the constructor's context.Context is canceled. However, this is just a failsafe - callers should still `defer rows.Close()` (and test for that call).

func (*RowsIterator[T]) Err

func (i *RowsIterator[T]) Err() error

Err returns the error (if any) that occurred while scanning.

func (*RowsIterator[T]) Next

func (i *RowsIterator[T]) Next() (T, error)

Next scans and returns the next row from the result set.

If there was an error scanning, it will return the error and the rows will be closed.

If there was no error scanning but no rows are left, it returns ErrNoRows.

func (*RowsIterator[T]) Scan

func (i *RowsIterator[T]) Scan() (T, error)

Scan returns the current row from the result set. This will return the value that was last returned by Next().

Most SQL driver implementations will error if Next() has not been called prior to Scan().

type Store

type Store[T any] interface {
	Querier
	Begin(ctx context.Context, opts ...T) (Tx, error)
}

Store is a form of storage, usually a connection to a database. T is the type that may be used to alter transactions started against this Store, e.g. to set the isolation level of the transaction.

type Tx

type Tx interface {
	Querier
	Commit(context.Context) error
	Rollback(context.Context) error
}

Tx is a transaction type, used in generated code to run multiple queries in a single transaction.