Querying and Mutating Data

Last updated Jul 2, 2026

Querying and mutating data are the core operations you perform to interact with your database. Whether you need to retrieve specific information, organize results, or combine data from multiple tables, these features provide a clear, readable way to express your intent without needing to write raw database language.

This system uses a "builder" approach, where you chain methods together to construct your request step-by-step.

Querying Data

To retrieve data, you start with the select method. You can either select all columns from a table or define specific fields to retrieve.

Initialize the query: Start by calling db.select() on your database object.
Define the source: Use .from() to specify which table or data source you are querying.
Apply filters: Use .where() to restrict results based on conditions.
Organize your output: Add methods like .orderBy() for sorting, .limit() for capping result counts, or .offset() for pagination.

Filtering and Aggregation

You can filter your data using logical conditions. For example, combine conditions with and() or or() to create complex filters. If you are grouping data to perform calculations (like counting items), use .groupBy() alongside aggregate functions, and further filter those groups using .having().

Tip

You can use a function inside methods like .where() or .orderBy() to access a helper that lets you reference columns dynamically, making your queries cleaner and more maintainable.

Combining Data

When your data is split across different tables, you can use built-in join methods to combine them into a single result set.

Method	Description
`innerJoin`	Returns only rows where there is a match in both tables.
`leftJoin`	Returns all rows from the left table, plus matched rows from the right table (or null).
`rightJoin`	Returns all rows from the right table, plus matched rows from the left table (or null).
`fullJoin`	Returns all rows when there is a match in either the left or right table.
`crossJoin`	Combines every row from the first table with every row from the second table.

Note

For advanced scenarios, many of these joins have "lateral" variants, which allow the joined query to reference columns defined in the main table.

Set Operations

You can combine the results of multiple independent queries using set operators. These are useful for merging lists of data that share the same structure.

Union: Merges two queries. You can use methods to remove duplicate rows or keep all duplicates.
Intersect: Returns only the rows present in both queries.
Except: Returns rows from the first query that do not exist in the second query.

Warning

When using set operations, ensure that both queries select the same fields in the exact same order. The system will throw an error if the structures do not match.

Efficiency and Performance

Prepared Statements: If you run the same query frequently with different values, use the .prepare() method. This allows the database to compile the query once and reuse it, which can significantly improve performance.
Locking: Use the .for() method to specify a lock strength if you need to ensure data consistency during concurrent operations.
Caching: Use $withCache() to enable automatic caching for your query results, reducing the load on your database for frequently accessed data.

Caution

Always be mindful of query performance when using crossJoin or large offset values, as these can put significant strain on your database resources.

Defining Table Relations

Was this page helpful?

Declaring Database Schemas

Defining Table Relations

Querying and Mutating Data

Querying Data

Filtering and Aggregation

Combining Data

Set Operations

Efficiency and Performance

Related

Querying and Mutating Data

Querying Data

Filtering and Aggregation

Combining Data

Set Operations

Efficiency and Performance

Related