---
title: "Querying and Mutating Data"
description: "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 tab..."
last_updated: "2026-07-02T09:37:14.208924+00:00"
canonical_url: "https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/guide/core-features/querying-and-mutating-data"
---

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.

1.  **Initialize the query:** Start by calling `db.select()` on your database object.
2.  **Define the source:** Use `.from()` to specify which table or data source you are querying.
3.  **Apply filters:** Use `.where()` to restrict results based on conditions.
4.  **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.

## Related

- [Defining Table Relations](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/guide/core-features/defining-table-relations)


## Sitemap

See the full [sitemap](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.