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 |
|---|---|
|
Calls |
|
Checks the value against the stored row (see below), then replaces the row that the value is stored as (see Stored keys). Returns |
|
Checks the value against the stored row (see below), then inserts the row or replaces the row with the same primary key. |
|
Deletes the row that the value is stored as. Returns |
|
Calls |
|
Calls |
|
Reads the row with the given primary-key field values. Returns |
|
Reads the row like |
|
Returns the rows that match |
|
Reads the |
|
Returns the |
|
Returns the |
|
Returns the rows that a query |
Each file field f adds two more:
Operation |
Behavior |
|---|---|
|
Returns the content of |
|
Sniffs, measures and hashes |
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).
Search#
SearchT queries the table’s search column. query uses web search syntax,
as PostgreSQL’s websearch_to_tsquery reads it:
query |
matches a row that has |
|---|---|
|
both words |
|
the phrase |
|
either word |
|
|
Rows that match better come first, and rows that match equally well are in primary-key order, so successive pages of one query neither repeat nor skip a row. Each value is read whole, with its lists and maps, in the request transaction. Words in those lists and maps are searched as the row’s own are (see Full-text search).
page, err := SearchBook(ctx, "emma -persuasion", 20, 40)
Searching every table#
When any table has a search index, the package also has one Search. It
searches every table with a search index at once:
func Search(ctx context.Context, query string, limit, offset int) ([]Hit, error)
query reads as it does for SearchT, and each table’s rows are matched and
ranked as SearchT matches and ranks them, all in one query. So the rank of a
row in one table compares with the rank of a row in another. The hits come
best match first. Hits that match equally well come grouped by table, in an
order that is fixed for the model, then in primary-key order, so pages
neither repeat nor skip a hit.
A Hit is the THit of the row’s struct. A hit holds the row’s primary key
and rank, but not the row itself. Get reads the row, in the request
transaction, or returns pgx.ErrNoRows if the row has since been deleted:
hits, err := Search(ctx, "emma", 20, 0)
for _, hit := range hits {
switch hit := hit.(type) {
case BookHit:
book, err := hit.Get(ctx)
// ...
case AuthorHit:
author, err := hit.Get(ctx)
// ...
}
}
Each Get is a query of its own, so read only the hits you need. To list
the hits, use SearchSummaries instead:
func SearchSummaries(ctx context.Context, query string, limit, offset int) ([]HitSummary, error)
It returns the hits Search returns, in the same order, and reads the
summary of each in the same query. A HitSummary is the
THitSummary of the row’s struct. It embeds the row’s THit, so it has
Rank and Get too, and its Summary field holds the row’s TSummary.
A struct with no summary has a THitSummary with no Summary field.
hits, err := SearchSummaries(ctx, "emma", 20, 0)
for _, hit := range hits {
switch hit := hit.(type) {
case BookHitSummary:
text, err := hit.Summary.Render()
// ...
case AuthorHitSummary:
// ...
}
}
PostgreSQL ranks every row a query with a negation (emma -austen) matches
the same. Those hits come grouped by table, then in key order.
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
LIMITinorder_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
readperm, 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
wherecompares 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
LIKEorILIKE, against a pattern naming no field, gets the field a trigram index of its own, named<table>_<column>_trgm_index, which the schema’spg_trgmextension 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 doesNOT LIKE. Every query matching the field shares the one index, and aLIKEadds 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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
both |
|
either |
|
|
|
|
|
the row’s search index holds the word, or the words in that order |
|
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
readperm, 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, orfile.
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 |
|---|---|
|
all, with |
|
|
enum, |
|
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: .... Anilvalue is the errorvalue 2 is nil.An empty batch returns
nilwithout 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.
InsertTAllcopies the rows into the table withCOPY.UpsertTAllcopies them into a temporary table, then inserts them from it in primary-key order, with the sameON CONFLICTasUpsert. Each child table then gets oneCOPYfor the items of every value (see Lists and maps).Two
UpsertTAllbatches 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,
UpsertTAllwaits 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,UpsertTAllnever accepts a value that failsValidate(), even when the stored row fails it as well (see Stored rows that fail validation).A file is written as
Upsertwrites 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.
UpdateandDeletefind 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.Updatewrites every column, the primary key included. Thus, if you change the primary key of a value that you read,Updatemoves the row to the new key. The child tables of lists and maps follow the row, and so does eachrefthat hason_update: cascade. Arefwithout it stops the move with a foreign key violation.UpsertandUpsertTAllalways 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,
Updateof the copy returnspgx.ErrNoRows.==andreflect.DeepEqualcompare 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:
Insertinserts the row, then the items.Update,Upsert, andUpsertTAllwrite the row, delete its items, and insert the new items. The new value replaces the items. It does not merge with them.Deletedeletes the row. The foreign keys delete the items.GetTreads the row withSELECT ... 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:
If the value passes
Validate(), the operation continues. It does not read the stored row.If the value fails, the operation locks the stored row (
SELECT ... FOR UPDATE) and validates it.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 |
|---|---|
|
|
|
|
|
|
|
Storing a file field’s content: |
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)runsfnin a transaction. It commits whenfnreturnsniland rolls back whenfnreturns an error. If the context already has a transaction,fnuses that transaction.UseTx(ctx, fn)givesfnthe context’spgx.Txfor 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
erroror 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 withslogand gives the body to the handler.If a struct success body fails
Validate(), the wrapper logs an error withslogand sends the body without changes.For an error, the wrapper uses the first status that is not 0, in this order:
The
intthat the handler returns.HttpStatus()of the error response, if the error response type implementsStatusCoder(with a value or a pointer receiver).HttpStatus()of the error, or of an error that it wraps (errors.As).422for aValidationError.With
postgres,409for a unique or foreign key violation, and422for other integrity constraint violations.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))