Go and PostgreSQL

Go and PostgreSQL#

The postgres Go feature generates code that reads and writes the tables that [tool.model.postgres] declares. Enable it in model.toml:

[tool.model.go]
out = "output.go"
package = "mypkg"

[tool.model.go.features]
postgres = 1

The generated code uses github.com/jackc/pgx/v5. Add it to the Go module that contains the generated file, alongside the runtime module every generated file needs:

go get gitlab.com/terryp/model/go-runtime
go get github.com/jackc/pgx/v5

To serve the tables as a REST API, see Go REST endpoints.

Operations#

Each top_level struct T gets these operations:

Operation

Behavior

(*T).Insert(ctx)

Calls Validate(), then inserts the row.

(*T).Update(ctx)

Checks the value against the stored row (see below), then replaces the row with the same primary key. Returns pgx.ErrNoRows if there is no row.

(*T).Upsert(ctx)

Checks the value against the stored row (see below), then inserts the row or replaces the existing row.

(*T).Delete(ctx)

Deletes the row with the same primary key. Returns pgx.ErrNoRows if there is no row.

GetT(ctx, keys...)

Reads the row with the given primary-key field values. Returns pgx.ErrNoRows if there is no row.

Stored rows that fail validation#

A stored row can fail Validate(), for example after a constraint was added to the model. Update and Upsert accept a value that fails validation if the value adds no violations to the stored row:

  1. If the value passes Validate(), the operation continues. It does not read the stored row.

  2. If the value fails, the operation locks the stored row (SELECT ... FOR UPDATE) and validates it.

  3. The operation continues only if each violation of the value is also a violation of the stored row. A violation is identified by its kind, path, and message. If a violation occurs two times in the value, it must also occur two times in the stored row.

If the value adds a violation, the error is a *ValidationRegressionError. Added holds the new violations. Existing holds all the violations of the stored row. errors.As also finds a *ValidationError in this error.

If there is no stored row, Update returns pgx.ErrNoRows, and Upsert returns the validation error of the value. Insert always refuses a value that fails Validate().

Database CHECK constraints still apply. A stored row cannot fail a CHECK constraint, so a value that fails the same rule always adds a violation.

Transactions#

The operations get their database from the context. All operations in one request use one transaction. The transaction starts only on the first query.

  • DBMiddleware(pool) gives each HTTP request a transaction.

  • WithTx(ctx, pool, fn) runs fn in a transaction. It commits when fn returns nil and rolls back when fn returns an error. If the context already has a transaction, fn uses that transaction.

  • UseTx(ctx, fn) gives fn the context’s pgx.Tx for your own queries.

  • Without a transaction in the context, the operations return ErrNoDB.

Generated route wrappers finish the transaction before they write the response:

  • If the handler returns a non-nil error or error response, the wrapper rolls back.

  • Otherwise the wrapper commits. If the commit fails, the client gets status 500.

Generated route wrappers also do these steps:

  • If the request body fails Validate(), the wrapper logs a warning with slog and gives the body to the handler.

  • If a struct success body fails Validate(), the wrapper logs an error with slog and sends the body without changes.

  • For an error, the wrapper uses the first status that is not 0, in this order:

    1. The int that the handler returns.

    2. HttpStatus() of the error response, if the error response type implements StatusCoder (with a value or a pointer receiver).

    3. HttpStatus() of the error, or of an error that it wraps (errors.As).

    4. 422 for a ValidationError.

    5. With postgres, 409 for a unique or foreign key violation, and 422 for other integrity constraint violations.

    6. 500.

For other handlers, DBMiddleware finishes the transaction after the handler returns:

  • On a panic, or a status of 400 or more, it rolls back.

  • Otherwise it commits. The response is already sent at that point, so the middleware can only log a failed commit.

Connections#

Use one *pgxpool.Pool for the whole process. The pool manages its connections. The middleware does not hold a connection for the length of the request.

NewPool(ctx, connString) creates the pool. It runs RegisterTypes on each new connection, because pgx must know the model’s enum, domain, and composite types before it can encode them. If you configure the pool yourself, set config.AfterConnect = RegisterTypes.

pool, err := genmodel.NewPool(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}
defer pool.Close()

mux := http.NewServeMux()
genmodel.RegisterAllEndpoints(mux)
return http.ListenAndServe(":8080", genmodel.DBMiddleware(pool)(mux))