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 that the value is stored as (see Stored keys). 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 row with the same primary key.

(*T).Delete(ctx)

Deletes the row that the value is stored as. Returns pgx.ErrNoRows if there is no row.

InsertTAll(ctx, values)

Calls Validate() on each value, then inserts every row with one COPY per table (see Bulk writes).

UpsertTAll(ctx, values)

Calls Validate() on each value, then inserts every row or replaces the row with the same primary key, with one INSERT ... ON CONFLICT from a staged copy (see Bulk writes).

GetT(ctx, keys...)

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

GetTForUpdate(ctx, keys...)

Reads the row like GetT, and locks it (SELECT ... FOR UPDATE) until the transaction ends.

SearchT(ctx, query, limit, offset)

Returns the rows that match query, best match first. Skips the first offset rows, and returns at most limit. Only a table with a search index has it (see Full-text search).

GetSummaryT(ctx, keys...)

Reads the TSummary of the row with the given primary key, with every row it refers to. Returns pgx.ErrNoRows if there is no row. Only a struct with a summary has it (see Summaries).

ListSummariesT(ctx, limit, offset)

Returns the TSummary of each row in primary-key order. Skips the first offset rows, and returns at most limit. Only a struct with a summary has it.

SearchSummariesT(ctx, query, limit, offset)

Returns the TSummary of each row SearchT would return, in its order. Only a table with both a search index and a summary has it.

QT(ctx, params..., [limit, offset])

Returns the rows that a query q declared on T selects, in the query’s order, or, as an mdl.Optional[T], the one row a query by a unique key finds. Only a struct that declares queries has these (see Declared queries).

Each file field f adds two more:

Operation

Behavior

(*T).OpenF(ctx)

Returns the content of f and its mdl.File, read together from the row the value is stored as. Returns mdl.ErrNoFile if the row has none, and pgx.ErrNoRows if there is no row.

(*T).SetF(ctx, content)

Sniffs, measures and hashes content, and refuses it with mdl.ErrFileTooLarge or mdl.ErrContentType if the field’s max_size or content_types does not allow it. Then it sets f to its mdl.File, runs the field’s upload hooks, and stores the content in the row the value is stored as. On an error the value is left as it was. Returns pgx.ErrNoRows if there is no row.

Insert, Update, Upsert, and the bulk writes write the metadata f holds, but never the content: a row is inserted without a file, and SetF uploads one to it. See Files.

Insert, Update, Upsert, Delete, and the bulk writes first run the struct’s hooks, and any of them can block the write (see Hooks).

When any table has a search index, the package also has Search(ctx, query, limit, offset), which searches all of them at once, and SearchSummaries, which reads each hit’s summary with it (see Searching every table).

A struct that a ref field refers to also gets (*T).ReferencedBy(ctx, limit, offset), and a (*T).ReferencedByU(ctx, limit, offset) for each struct U that refers to it (see Referring rows). A struct that holds a ref field, or that one refers to, gets (*T).Related(ctx, limit, offset), which lists the rows on either end of its references (see Related rows).

Declared queries#

A top_level struct can declare queries of its own, under queries. Each query gives a where condition, and optionally an order_by list, as PostgreSQL SQL fragments:

queries:
  in_city:
    desc: The records whose address is in a city, the latest due first.
    where: address.city = :city
    order_by: due_on DESC NULLS LAST
    params:
      city:
        display: City
        desc: The city the address is in.
        type: string
    limit: true

Each query becomes a function named for the query, then the struct. It takes each parameter in declared order, then limit and offset when the query sets limit: true:

func InCityRecord(ctx context.Context, city string, limit, offset int) ([]Record, error)

A fragment names the struct’s fields, not their columns. A field of an embedded struct is named by its dotted path, like address.city. The generator rewrites each field name to the column the field is stored in. A fragment names each parameter as :name. Each parameter is declared under params with a scalar field type, and its Go type is that type’s.

The fragments are parsed by PostgreSQL’s own parser when the code is generated, and the generated SQL is the parser’s own rendering of them. The model is rejected if a fragment:

  • does not parse, or holds more than its own clause, like a second statement, or a LIMIT in order_by;

  • holds a subquery, or reads another table;

  • names a field the struct does not have;

  • names a field stored in more than one column, or in none of its own: a whole embedded struct or union, a list, a map, or a file;

  • names a field that sets its own read perm, which a search never matches either;

  • names a parameter it does not declare, or declares one it never names.

A datetime is compared by its instant.

A rooted path is compared and sorted by its resolved path, the column every key and index of it uses (see Rooted paths), so any spelling of a path matches. A parameter compared with one is declared as a plain path, and resolved the way that field resolves before the query runs:

queries:
  by_file:
    desc: The volume of a file, however it is spelled.
    where: file = :file
    params:
      file:
        display: File
        desc: The volume's file, under /volumes unless absolute.
        type: path

ByFileVolume(ctx, mustPath("a.epub")) and ByFileVolume(ctx, mustPath("/volumes/a.epub")) find the same row. The model is also rejected if such a parameter has a root of its own, is compared with a second rooted path, or is named anywhere else in the query, where it would be used unresolved. A field relative_to another field, or to a root base, resolves against its row, which the query has not found yet, so its parameter must be absolute; a relative one returns mdl.ErrNoBase.

The rows come in the query’s order_by order, or by primary key when the query has none. Ties are broken by primary key, so successive pages neither repeat nor skip a row. Each value is read whole, with its lists and maps, in the request transaction. As SearchT does, a query keeps only the rows the request may find by a search: those it may both summarize and read (see Searches need read).

With the REST endpoints, a QUERY of the struct’s collection runs each query by name.

Finding every match first#

A paged query with an order_by can be served two ways. PostgreSQL can walk its B-tree in order_by order, testing each row, and stop once the page is full, which is fast when matches are common but reads the whole table for a rare one that sorts late. Or it can find every match, by a trigram index or a scan, and sort them, at a cost that grows with the number of matches rather than with where they fall. The planner picks by how many rows it expects to match, assuming they are spread evenly through the order. For a LIKE or ILIKE, which pg_trgm gives no statistics, that guess comes from a sample, and when it is wrong the walk can read most of the table for a page of ten.

plan: filter_first makes the query always find every match first:

queries:
  named:
    desc: The records whose note contains a text, by note.
    where: "note ILIKE '%' || :text || '%'"
    order_by: note
    params:
      text:
        display: Text
        desc: What the note contains.
        type: string
    limit: true
    plan: filter_first

The query finds its matches in a materialized CTE, which PostgreSQL runs in full, so it chooses the plan that finds every match most cheaply. The CTE keeps only each match’s key and the fields its order_by names, then the page’s keys are sorted out of it, and only the page’s rows are read whole. With child tables, only the page’s rows are share-locked. The parameters, and the function, are the same as without it.

Use it on a query whose matches are found by an index other than its B-tree, a text search being the usual one, when some texts match few rows late in the order. It costs a text matching a large part of the table: every match is found and sorted for every page, where the walk would have stopped after a few rows. A text of one or two letters has no trigrams, so its matches are found by reading the table; a search box sending one should wait for a third letter.

It needs limit: true and an order_by: without a page there is no walk to avoid. The default, plan: planner, leaves the choice to PostgreSQL.

Queries finding one row#

A query whose where compares every column of the primary key, or of a unique field, by = to a value finds at most one row, so its function returns that row, if there is one, rather than a slice:

queries:
  by_login:
    desc: The account logging in as a login.
    where: login = :login
    params:
      login:
        display: Login
        desc: Who logs in.
        type: string
func ByLoginAccount(
    ctx context.Context, login string,
) (mdl.Optional[Account], error)

It is mdl.None when no row matches, or the request may not find the one that does. As for an index, only the conditions joined by AND at the top of the where count, and a datetime is unique by its instant and its zone, so comparing its instant alone does not find one row. The query has no rows to order or page through, so it cannot set order_by or limit, and the unique index serves it, so it gets no index of its own.

Query indexes#

The schema gets an index for each query, named <table>_query_<query>_index, built from its fragments:

  • Its key starts with each field the where compares to a value by =, the value being parameters and constants.

  • With an order_by, the sort keys follow, each with its direction and nulls order, then the primary key. The index then gives the rows in the order the query returns them, and a page reads only its own. A sort key that is an expression rather than a field ends the key there.

  • Without one, the field of a range comparison (<, <=, >, >=, BETWEEN) follows, or, if there is none, the primary key.

  • A condition that tests a field (IS NULL, IS TRUE, a boolean field) or compares one to a constant makes the index partial, holding only the rows that meet it. A condition calling a function or casting is not one PostgreSQL can index by, so it is left out of the index.

  • A condition matching a field by LIKE or ILIKE, against a pattern naming no field, gets the field a trigram index of its own, named <table>_<column>_trgm_index, which the schema’s pg_trgm extension provides. It serves a pattern whether or not it is anchored, so a bare parameter, whose anchoring cannot be known, gets one too. A pattern starting with text that is no wildcard ('abc%', 'abc' || :rest) gets none, and nor does NOT LIKE. Every query matching the field shares the one index, and a LIKE adds nothing to the B-tree’s key.

A filter_first query never reads its rows in order_by order, so its B-tree is built as though it had none: the fields it compares by =, then a range comparison’s or the primary key. A query matching only by LIKE gets its trigram index and no B-tree.

Only the conditions joined by AND at the top of the where count. A query the primary key, or an index already on the table, serves as well gets no B-tree, and one finding one row no index at all. The in_city query above, and a noted query whose where is note IS NOT NULL, get:

CREATE INDEX record_query_in_city_index ON record (
    address_city, due_on DESC NULLS LAST, id
);
CREATE INDEX record_query_noted_index ON record (id) WHERE note IS NOT NULL;

A named query whose where is note ILIKE '%' || :text || '%', ordered by note, gets a B-tree for its order and a trigram index for its match:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX record_query_named_index ON record (note, id);
CREATE INDEX record_note_trgm_index ON record USING gin (note gin_trgm_ops);

Queries users write#

With the find feature, each table also gets a FindT, which takes a query in a small text language. Your users may write these queries: a field name only ever comes from a table generated for the struct, and a value is only ever a bound parameter.

func FindRecord(ctx context.Context, query string, limit, offset int) ([]Record, error)
func FindSummariesRecord(ctx context.Context, query string, limit, offset int) ([]RecordSummary, error)
priority = high AND due_on < 2026-01-01 AND "emma" ORDER BY due_on DESC

A query that cannot run is an *mdl.QueryError, which says where in the text the problem is. Unlike a declared query, nothing here has an index of its own, so each statement runs under mdlpg.QueryTimeout, 5 seconds unless your server sets it, in a savepoint. One that takes longer is mdlpg.ErrQueryTimeout, and leaves the request’s transaction usable.

The language#

Form

Matches

a = 1, a:1

a is 1

a != 1

a is not 1, or has no value

a < 1, a <= 1, a > 1, a >= 1

a compares so with 1

a ~ text

a contains text, ignoring case

a = null, a = ""

a has no value

a != null, a != ""

a has a value

x y, x AND y

both

x OR y

either

NOT x

x is not true, which a row with no value counts as

( x )

x, grouped

word, "a phrase"

the row’s search index holds the word, or the words in that order

ORDER BY a, b DESC

the rows sorted, a missing value last either way

AND binds tighter than OR, and keywords are upper-case only, so and and or are ordinary words. A word followed by an operator is a comparison, and any other word is free text, so free text holding a : must be quoted, as "foo:bar". In quotes a backslash escapes the next character, so \" is a quote. A value is a whole word, so at = 2026-01-01T00:00:00Z needs no quotes.

A query with free text and no ORDER BY puts the best match first. Any other sorts by the primary key, which also breaks every tie, so pages do not overlap. A query may be at most 4096 bytes, of at most 64 terms, nested at most 16 deep.

Which fields a query can name#

Every field stored in one column, at any depth of embedded structs, spelled address.city, unless:

  • it sets a read perm, even for a request that holds it, since which rows match would tell what it holds;

  • it sets queryable: false, for a field that may be read but should not be filtered or sorted on;

  • its type has no operators: a password, secret, constant, or file.

An excluded field gets the same error as one that does not exist. queryable: false leaves a field to free text, which reads the search index. To hide a field from every search, set a read perm.

Type

Operators

string

all, with ~

integer, decimal, estimate, date, datetime, time

=, :, !=, <, <=, >, >=

enum, boolean, uuid, isbn, url, path, nerd_font_glyph, a reference

=, :, !=

A value is read as its field’s type, and a value it cannot read is an error at that position, but it is not checked against the field’s constraints. A datetime needs an offset, and compares by its instant. An enum compares by its value, not its display. A reference compares as its key, unless that is a rooted path. A rooted path resolves the way its field does, so one relative to something in its row must be absolute.

Referring rows#

A TRef reads the row it refers to with Resolve. To go the other way, from a row to the rows that refer to it, a struct that some ref field refers to gets ReferencedBy:

func (self *Author) ReferencedBy(ctx context.Context, limit, offset int) ([]Referrer, error)

It finds a referring row wherever the reference is: in a field, in a nested struct or union, in a list item, or in a map key. A row that refers through several of these comes once. The rows come grouped by table, in an order that is fixed for the model, then in primary-key order. It skips the first offset rows and returns at most limit. self’s row is the one it was last read or written as, or, if it was neither, the row with its primary key (see Stored keys).

A Referrer is the TReferrer of the referring row’s struct. Like a THit, it holds only the row’s primary key, and Get reads the row:

referrers, err := author.ReferencedBy(ctx, 20, 0)
for _, referrer := range referrers {
    switch referrer := referrer.(type) {
    case BookReferrer:
        book, err := referrer.Get(ctx)
        // ...
    case ReviewReferrer:
        // ...
    }
}

To read the rows of one struct, use ReferencedByU, named for the referring struct U. It returns the whole rows, with their lists and maps, in primary-key order, paged the same way:

books, err := author.ReferencedByBook(ctx, 20, 0)

To list the referring rows by their summaries, use ReferencedBySummaries. It returns the rows that ReferencedBy returns, in the same order and pages, and it reads each row’s summary in the same query (see Summaries):

func (self *Author) ReferencedBySummaries(ctx context.Context, limit, offset int) ([]ReferrerSummary, error)

A ReferrerSummary is the TReferrerSummary of the referring row’s struct. It embeds the row’s TReferrer, and, when the struct has a summary, it has that summary in Summary.

Summaries#

GetSummaryT, ListSummariesT, SearchSummariesT, SearchSummaries, ReferencedBySummaries, and RelatedSummaries read summaries in one query each, however many rows they return and however deep their references go. The query reads only the columns the summaries use. A reference in a summary is read as a nested record of its row’s own summary columns, with that row’s references nested in it in turn, and a list as an array of records from its child table. So every mdlsummary.Ref in the result holds its row’s summary.

found, err := SearchSummariesBook(ctx, "emma", 20, 0)
for i := range found {
    text, err := found[i].Render()
    if err != nil {
        return err
    }
    fmt.Println(found[i].Isbn, text)
}

A summary holds its row’s primary key, which GetT takes, so a search can list summaries and read a whole row only when one is picked. A row that appears in many summaries is read once for each. They all use the request transaction, as every operation does.

Bulk writes#

InsertTAll and UpsertTAll write a batch of values in one go. Each takes a []*T, so it can set the stored key of each value, as Insert and Upsert do:

books := []*Book{&first, &second}
if err := InsertBookAll(ctx, books); err != nil {
    return err
}
  • Each value must pass Validate(), and its hooks run, before anything is written. The first error stops the batch, and names the index of its value: value 2: .... A nil value is the error value 2 is nil.

  • An empty batch returns nil without touching the database, so it needs no transaction in the context.

  • The batch is written in the request transaction, so it is written whole or not at all.

  • InsertTAll copies the rows into the table with COPY. UpsertTAll copies them into a temporary table, then inserts them from it in primary-key order, with the same ON CONFLICT as Upsert. Each child table then gets one COPY for the items of every value (see Lists and maps).

  • Two UpsertTAll batches that share keys lock their rows in the same order, so they do not deadlock each other.

  • When another transaction has inserted a row with the same primary key and not committed it yet, UpsertTAll waits for that transaction, then replaces the row.

  • Two values with the same primary key fail the whole batch, with the database’s error. The exception is an upsert into a table that holds only its primary key and has no lists or maps: the values are identical, so the second is skipped.

  • Unlike Upsert, UpsertTAll never accepts a value that fails Validate(), even when the stored row fails it as well (see Stored rows that fail validation).

  • A file is written as Upsert writes it: its stored content is kept while its digest is unchanged, and cleared otherwise.

Stored keys#

A value remembers the primary key of its row. GetT, GetTForUpdate, SearchT, Insert, Update, Upsert, and the bulk writes set this key, and Delete clears it. The key is not exported, and the JSON, YAML, TOML, and XML codecs do not read or write it.

  • Update and Delete find the row by the remembered key. A value that has no remembered key, such as a value that you make or decode, uses its own primary key, as before.

  • Update writes every column, the primary key included. Thus, if you change the primary key of a value that you read, Update moves the row to the new key. The child tables of lists and maps follow the row, and so does each ref that has on_update: cascade. A ref without it stops the move with a foreign key violation.

  • Upsert and UpsertTAll always use the primary key of the value. They do not move a row.

  • A copy of a value has the key that the value had when you copied it. After the original moves its row, Update of the copy returns pgx.ErrNoRows.

  • == and reflect.DeepEqual compare the remembered key too. A value that you read is not equal to a value with the same fields that you made.

To move a row whose rooted path key now resolves to a different path (see Rooted paths), read it and write it again:

v, err := GetVolumeForUpdate(ctx, key)
if err != nil {
    return err
}
return v.Update(ctx)

Lists and maps#

A list or map is stored in a child table (see Lists and maps). The operations write and read its items together with the row, in the same transaction:

  • Insert inserts the row, then the items.

  • Update, Upsert, and UpsertTAll write the row, delete its items, and insert the new items. The new value replaces the items. It does not merge with them.

  • Delete deletes the row. The foreign keys delete the items.

  • GetT reads the row with SELECT ... FOR SHARE, then does one query for each child table. Each write locks the row before it changes the items, so the lock makes sure that the items are the items of that row.

The items of each child table go in with one COPY, however many there are, so a statement trigger on the table, such as the search index’s, runs once per write. A bulk write copies the items of all its values together, with one COPY per table. A table with no items is not written to. pgx copies in binary, by each column’s type. A table with a column of a type that has no binary input, such as isn’s ISBN, is copied in text format instead. pgx learns the column types by preparing a query once per table and connection. Under QueryExecModeExec or QueryExecModeSimpleProtocol it describes them again on every write.

A list that has no items is read as nil, and a map that has none as an empty mdl.Map. An item that is itself an empty list is read as an empty value, not nil. A map’s items are read in key order, not the order they were set in: the child table has no column for that order.

A length rule is a deferred trigger, which PostgreSQL checks at commit (see Lists and maps). The operations check these triggers before they return. Thus a violation is the error of the operation, and a generated route wrapper returns 422 for it. It is not a failed commit.

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(), and so do the bulk writes, UpsertTAll included.

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.

Hooks#

A hook is a function in your package that runs before a struct is written. If it returns an error, the operation returns that error and does not write. Name each hook in model.toml with the event it runs for:

[[tool.model.go.hooks]]
event = "save"
func = "CheckBook"

[[tool.model.go.hooks]]
event = "create"
func = "Audit"

Event

Runs before

create

Insert and InsertTAll

save

Insert, Update, Upsert, InsertTAll, and UpsertTAll

delete

Delete

upload

Storing a file field’s content: Set<Field>, and an upload to its URL

Insert runs the create hooks first, then the save hooks. A bulk write runs the hooks of each value in turn, before it writes any. If several hooks match one struct and event, they run in the order model.toml lists them, and the first error stops the rest.

A hook can take a context.Context. It then takes a pointer to the struct being written, and returns an error. That pointer’s type tells the generator which structs run the hook. A generic hook runs for every struct in its type parameter’s union:

// CheckBook runs for Book.
func CheckBook(ctx context.Context, v *Book) error {
    if v.Title == "" {
        return errors.New("a book needs a title")
    }
    return nil
}

// Audit runs for Book and Author.
func Audit[T Book | Author](ctx context.Context, v *T) error {
    log.Printf("creating %T", *v)
    return nil
}

An upload hook names the file field it runs for, and takes the content after the struct, which already holds the content’s mdl.File. It runs after the field’s max_size and content_types are checked, so it is the place for a check of its own, such as an image’s dimensions:

[[tool.model.go.hooks]]
event = "upload"
func = "CheckAvatar"
field = "avatar"
// CheckAvatar runs for Account's avatar.
func CheckAvatar(ctx context.Context, v *Account, content []byte) error {
    config, _, err := image.DecodeConfig(bytes.NewReader(content))
    if err != nil || config.Width > 4096 || config.Height > 4096 {
        return &mdl.ValidationError{Kind: "image", Message: "is not an image of at most 4096×4096 pixels", DataPath: "avatar"}
    }
    return nil
}

Each struct a hook takes must be top_level. A hook that the generator cannot find, or that has a different signature, fails the build, as does an upload hook whose struct has no such file field. Hooks need the postgres feature.

A hook runs after the value is validated (see Stored rows that fail validation) and before the row is written. It runs in the request transaction, so it can read other rows with GetT and write them. When the transaction rolls back, those writes roll back too. A delete hook gets the value Delete was called on. Over REST, that value is the stored row, read whole. Return a *ValidationError from a hook to give a REST client a 422 response.

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.

To change a row that you read, read it with GetTForUpdate, change the value, and write it with Update, all in one transaction. The lock stops other transactions from changing the row between the read and the write. GetT does not lock the row against writes, so a change that another transaction makes after GetT is overwritten.

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.

Other resources can commit with the transaction. DBMiddleware and WithTx also give the context a unit of work (package txn). A resource joins it on first use, and it commits right after the database commits, or rolls back if the database does not. The same rules apply to it: generated route wrappers report a failed commit, and other handlers can only log one. Transactional file edits uses this to keep files in step with the database.

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 and domain types, and the types of extensions such as isn, 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()

router := mdl.NewRouter()
genmodel.RegisterAllEndpoints(router)
return http.ListenAndServe(":8080", genmodel.DBMiddleware(pool)(router))