Query data

Find rows with typed conditions, sort and paginate them, aggregate, and update or delete in bulk.

Before you start

Define your models.

Steps

1. Build a query

db.Query[T](ctx) starts a query on T’s table. Conditions use the typed columns that anetos gen writes for each model, so PostCols.Views.Eq("ten") doesn’t compile:

// illustrative
posts, err := db.Query[Post](ctx).
	Where(PostCols.Title.Like("%go%"), PostCols.Views.Gte(100)). // AND
	Where(db.Or(PostCols.AuthorID.Eq(1), PostCols.AuthorID.Eq(2))).
	OrderBy(PostCols.Views.Desc()).
	Limit(20).
	Get()

A column has its field’s type: compare a nullable *time.Time column with new(t). For a quick query, db.C("views") is an untyped column, and db.Col[int]("views") declares a typed one by hand.

Columns have Eq, Ne, Gt, Gte, Lt, Lte, In, NotIn, Between, Like, NotLike, IsNull and NotNull. Combine conditions with db.And, db.Or and db.Not, and write anything else in SQL with db.SQL("lower(email) = ?", email) or WhereRaw.

Every method returns a new query, so a base query can be shared (a query keeps the context it was started with; WithContext changes it):

// illustrative
published := db.Query[Post](ctx).Where(PostCols.PublishedAt.NotNull())
latest, err := published.Latest().Limit(5).Get()
total, err := published.Count()

2. Get results

3. Paginate

// illustrative
page, err := db.Query[Post](ctx).Latest().Paginate(in.Page, 20)

Pages are 1-based; 0 or a negative page (a missing ?page=) is the first page, and a page past the end is empty. perPage often comes from the request: bound it with validate:"max:100". Order by a unique column last (PostCols.ID.Desc() after Latest()), or rows created in the same instant can move between pages.

A db.Page[T] has Data, CurrentPage, PerPage, Total and LastPage, with HasPrev() and HasMore(). It encodes like Laravel’s paginator, so an API handler returns it as it is: {"data": […], "current_page": 2, "per_page": 20, "total": 57, "last_page": 3}.

For an HTML list, pass the page to the view (ListNotes binds Page int `query:"page"` ):

func (Notes) Index(c *web.Ctx, in ListNotes) (web.Responder, error) {
	// Newest first; the ID breaks ties between notes created in the same
	// instant, so no note shows on two pages. With search words, the
	// best matches come first, then the newest. Search of no words
	// changes nothing.
	page, err := db.Query[Note](c).OrderBy(NoteCols.CreatedAt.Desc(), NoteCols.ID.Desc()).
		Search(in.Q).
		Paginate(in.Page, 10)
	if err != nil {
		return nil, err
	}
	return web.View(NotesPage(page, in.Q)), nil
}

(Copied from examples/forms, region index.)

and link to the pages around it with web.PageURL(ctx, n): a relative link (?q=go&page=2) that keeps the other query parameters (a search, a filter). Past the end, the “Newer” link goes back to the last page:

// NotesPage lists one page of notes, with links to the pages around it
// (they keep the search words, q) and a search box.
templ NotesPage(page db.Page[Note], q string) {
	@Layout("All notes") {
		<h1>Notes</h1>
		<p><a href={ web.URL(ctx, "notes.new") }>New note</a></p>
		<form method="get" action={ web.URL(ctx, "notes.index") } role="search">
			<input type="search" name="q" value={ q } aria-label="Search notes"/>
			<button type="submit">Search</button>
		</form>
		if page.Total == 0 && q != "" {
			<p>No notes match “{ q }”.</p>
		} else if page.Total == 0 {
			<p>No notes yet.</p>
		}
		<ul class="notes">
			for _, n := range page.Data {
				@noteItem(n)
			}
		</ul>
		<nav class="pages">
			if page.HasPrev() {
				<a href={ web.PageURL(ctx, min(page.CurrentPage-1, page.LastPage)) } rel="prev">Newer</a>
			}
			if page.LastPage > 1 {
				<span>Page { page.CurrentPage } of { page.LastPage }</span>
			}
			if page.HasMore() {
				<a href={ web.PageURL(ctx, page.CurrentPage+1) } rel="next">Older</a>
			}
		</nav>
	}
}

(Copied from examples/forms/notes.templ, region list.)

For long lists and infinite scroll, cursor pagination stays fast at any depth and never repeats or skips rows as new ones arrive:

// illustrative
page, err := db.Query[Post](ctx).OrderBy(PostCols.CreatedAt.Desc()).CursorPaginate(in.Cursor, 20)
// page.NextCursor and page.PrevCursor go back to the client

An invalid cursor returns db.ErrInvalidCursor, a 400.

4. Group and aggregate

Scan grouped results into a struct of your own:

// illustrative
type authorStats struct {
	AuthorID int64 `db:"author_id"`
	Posts    int64 `db:"posts"`
}
stats, err := db.Select[authorStats](
	db.Query[Post](ctx).GroupBy("author_id").Having(db.SQL("COUNT(*) > ?", 5)),
	"author_id", "COUNT(*) AS posts")

With a Join, qualify columns that both tables have: PostCols.ID.Of("posts") is posts.id. For joins across several tables, raw SQL is often clearer.

5. Update or delete many rows

// illustrative
n, err := db.Query[Post](ctx).Where(PostCols.AuthorID.Eq(id)).Update(
	PostCols.Title.Set("[removed]"),
	PostCols.Views.SetRaw("views + ?", 1))

n, err = db.Query[Post](ctx).Where(PostCols.Views.Eq(0)).Delete() // soft delete for SoftDeletes models

Mass updates set updated_at; they don’t run model hooks.

6. Reuse conditions as scopes

// illustrative
func Popular(q *db.Q[Post]) *db.Q[Post] { return q.Where(PostCols.Views.Gte(1000)) }

posts, err := db.Query[Post](ctx).Scope(Popular).Get()

Complete example

ListPosts in examples/database combines filters from the query string with pagination.

How it works

The query builder writes SQL for the database’s dialect (placeholders, quoting, LIMIT forms) and sends values as parameters, never inside the SQL text. Soft-deleted rows are excluded unless you call WithTrashed() or OnlyTrashed(). Every method is listed in the query builder reference.

Coming from Laravel? Where(PostCols.Title.Like(…)) is where('title', 'like', …), Paginate is paginate, and scopes are plain functions instead of scopeX methods. There is no lazy loading: load relations with With (Relations and eager loading).

Testing it

Create the rows a query should (and shouldn’t) find with factories, then run it on app.Context() of an anetostest app.

Common problems

Next steps

• Transactions
• Raw SQL
• Query builder reference