Queries guide

Sqldelight Query Patterns That Stay Maintainable

Write maintainable Sqldelight query files with clear labels, focused projections, safe parameters, transactions, pagination and result models.

Sqldelight Query Patterns That Stay Maintainable feature image

A Sqldelight query becomes part of the Kotlin API, so naming and result design matter as much as the SQL itself. Clear labels, focused projections and predictable parameter rules make generated code easier to use and reduce unnecessary changes when a table evolves.

Name queries by application intent

Prefer selectActiveAccounts over selectWhereStatus. The first name tells callers why the query exists; the second exposes an implementation detail. Treat labels as public function names inside your module.

Select only required columns

A screen that needs an id, title and timestamp should not select a large body column. Focused projections reduce data transfer, clarify generated result types and make schema changes less disruptive.

Focused list query
recentNoteSummaries:
SELECT id, title, updated_at
FROM note
WHERE archived = 0
ORDER BY updated_at DESC
LIMIT :limit;

Use parameters consistently

Choose parameter names that match domain language. Handle nullable filters deliberately; a comparison against NULL follows SQL semantics and may require a different predicate than a normal equality check. Test every optional filter combination.

Separate dynamic behavior

When a query has many optional filters, one enormous statement can become difficult to reason about. Consider a small set of explicit queries or a carefully reviewed dynamic strategy rather than hiding every condition inside one clause.

Group writes in transactions

Multiple statements that represent one business action should commit or fail together. Keep transaction boundaries at a repository or service layer so generated operations remain small and reusable.

Avoid N+1 query loops

If a list requires related data for every row, consider a join or a batched query instead of executing another SELECT inside each iteration. Measure the final statement and add indexes that match its filters and ordering.

Design stable result models

Generated row types are convenient inside the data layer. For public module APIs, map them to domain models when the database shape should not leak into business logic. This also provides one place to apply adapters and defaults.

Review checklist

  • Does the label express product intent?
  • Are only necessary columns selected?
  • Are nullable parameters covered by tests?
  • Does the query use an index for its main filter and order?
  • Will a schema change create an acceptable generated API change?
  • Is the transaction boundary visible to reviewers?

Related Sqldelight guides