Database
Queries and pagination
Query models with GORM, sort by a client-chosen column safely with lagoon.OrderBy, and return Laravel-shaped pages with lagoon.Paginate.
Queries are plain GORM: Where, Joins, Preload, Count, Find and the rest work as the GORM documentation describes. lagoon adds two helpers for list endpoints, which almost always let the client choose the sort order and ask for one page.
Pass the request context to every query with WithContext, so a cancelled request stops its query.
Sorting by a column the client names#
A list endpoint usually takes ?sort=title&order=desc. Never pass those values to Order directly: a column name is SQL, not a bound parameter. lagoon.OrderBy appends the ORDER BY only when the column is in an allow-list you give it and the direction is asc or desc, and returns an error for anything else:
// A dry-run handle shows the SQL without a database.
db, _ := gorm.Open(postgres.New(postgres.Config{DSN: "host=127.0.0.1"}), &gorm.Config{DryRun: true, DisableAutomaticPing: true})
allowed := []string{"title", "views"}
q, err := lagoon.OrderBy(db.Model(&Post{}), "views", "desc", allowed)
if err != nil {
fmt.Println(err)
return
}
var posts []Post
fmt.Println(q.Find(&posts).Statement.SQL.String())
_, err = lagoon.OrderBy(db, "api_token", "asc", allowed)
fmt.Println(err)
_, err = lagoon.OrderBy(db, "title", "asc; DROP TABLE acme_blog_posts", allowed)
fmt.Println(err)
// Output:
// SELECT * FROM "acme_blog_posts" WHERE "acme_blog_posts"."deleted_at" IS NULL ORDER BY views DESC
// lagoon: order column "api_token" is not allow-listed
// lagoon: order direction "asc; DROP TABLE acme_blog_posts" is not allow-listed
Answer the error as a validation failure (422). The column must match an allow-list entry exactly, so list the qualified name (acme_blog_posts.title) when the query joins another table.
Sorting with a collation#
Text sorts by the database's default collation unless you pass lagoon.Collate. A list that must follow one language's alphabet (Polish puts Ł between L and M, for example) passes lagoon.Collate("pl-x-icu"), and lagoon.OrderBy adds a COLLATE clause for that column only. ICU collations named <language>-x-icu exist in any PostgreSQL built with ICU support, which includes the official Docker images, so the database needs no special locale:
// A dry-run handle shows the SQL without a database.
db, _ := gorm.Open(postgres.New(postgres.Config{DSN: "host=127.0.0.1"}), &gorm.Config{DryRun: true, DisableAutomaticPing: true})
allowed := []string{"title", "views"}
// Polish alphabetical order, whatever the database's default locale.
q, err := lagoon.OrderBy(db.Model(&Post{}), "title", "asc", allowed, lagoon.Collate("pl-x-icu"))
if err != nil {
fmt.Println(err)
return
}
var posts []Post
fmt.Println(q.Find(&posts).Statement.SQL.String())
_, err = lagoon.OrderBy(db, "title", "asc", allowed, lagoon.Collate(`pl-x-icu" ASC, (SELECT 1) --`))
fmt.Println(err)
// Output:
// SELECT * FROM "acme_blog_posts" WHERE "acme_blog_posts"."deleted_at" IS NULL ORDER BY title COLLATE "pl-x-icu" ASC
// lagoon: order collation "pl-x-icu\" ASC, (SELECT 1) --" is not a valid collation name
The collation name is validated (ASCII letters, digits, _, -, . and @, at most 63 bytes) and quoted as an identifier; anything else is an error, and no SQL is built. An index only helps that ORDER BY when it is built with the same collation.
Pagination#
lagoon.Paginate wraps the rows of one page in the envelope Laravel's paginator produces for the API: data and a meta object with current_page, last_page, per_page and total. You run the count and the page query yourself, so the query stays under your control:
q, err := lagoon.OrderBy(db.WithContext(ctx).Model(&Post{}), sort, dir, []string{"title", "views"})
if err != nil {
return lagoon.Page[Post]{}, err // answer 422: the client asked for a column it may not sort by
}
var total int64
if err := q.Count(&total).Error; err != nil {
return lagoon.Page[Post]{}, err
}
var posts []Post
if err := q.Offset((page - 1) * perPage).Limit(perPage).Find(&posts).Error; err != nil {
return lagoon.Page[Post]{}, err
}
return lagoon.Paginate(posts, page, perPage, total), nil
The result is a lagoon.Page whose lagoon.PageMeta marshals to the Laravel field names:
rows := []map[string]any{{"id": 3, "title": "Third"}}
page := lagoon.Paginate(rows, 2, 2, 3)
out, _ := json.Marshal(page)
fmt.Println(string(out))
empty, _ := json.Marshal(lagoon.Paginate[map[string]any](nil, 1, 15, 0))
fmt.Println(string(empty))
// Output:
// {"data":[{"id":3,"title":"Third"}],"meta":{"current_page":2,"last_page":2,"per_page":2,"total":3}}
// {"data":[],"meta":{"current_page":1,"last_page":1,"per_page":15,"total":0}}
A nil slice becomes [], and a zero or negative page size gives one page instead of dividing by zero. There is no links block. When a ported endpoint's response has a different shape, build that shape yourself: the existing clients define the contract.
Clamp page and per_page from the request before you use them, for example to at least 1 and at most 100, so a client cannot ask for the whole table in one page.