Digging into Marten query sets
Tags: crystal, orm, database, marten
Published: 2023-11-17 01:42:27

The [Marten web framework](https://martenframework.com/) incorporates an intuitive object-relational mapper (ORM), streamlining the development process for users who may not possess extensive knowledge of databases and SQL. To facilitate interactions with collections of model records, Marten introduces a dynamic mechanism called "Query sets". A query set represents a collection of model records in the database. It can have filters, be paginated, sliced, etc. In this guide, we will cover how to leverage such querying capabilities.

## Quick setup

Before delving into our initial examples of query sets, let's first set up a small project that will serve as the foundation for this guide. Assuming that the Marten CLI is [properly installed](https://martenframework.com/docs/getting-started/installation), we can use the [`marten new`](https://martenframework.com/docs/development/reference/management-commands#new) command to do so:

```bash
marten new project qset-showcase
cd qset-showcase && shards install
```

Next, let's define a very simple `User` model by creating a `src/models/user.cr` file and copying the following content in it:

```crystal
class User < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  
  field :username, :string, max_size: 128
  field :email, :email
  field :first_name, :string, max_size: 128, blank: true, null: true
  field :last_name, :string, max_size: 128, blank: true, null: true

  with_timestamp_fields
end
```

This provides us a good opportunity to appreciate Marten's declarative approach to model definition. In Marten, models are articulated as subclasses of the `Marten::Model` base class, explicitly outlining their "fields" through the use of a `#field` macro.

Marten’s vision when it comes to models is that everything needed to understand a model should be defined within the model itself. In this light, Marten's models drive tables (and not the reverse way around) and migrations are generated automatically from model definitions. While migrations can still be manually written using a convenient DSL if needed, the core idea revolves around the simplicity of defining a model and its fields to ensure that the framework takes care of the associated table automatically.

To generate the migration for the `User` model that we defined above, we can leverage the [`marten genmigrations`](https://martenframework.com/docs/development/reference/management-commands#genmigrations) command:

```bash
marten genmigrations
```

This should produce the following output:

```
Generating migrations for app 'main':
  › Creating [src/migrations/202311151921341_create_main_user_table.cr]... DONE
      ○ Create main_user table
```

If we inspect the generated file, we should see the following content:

```crystal
# Generated by Marten 0.3.3 on 2023-11-15 19:21:34 -05:00

class Migration::Main::V202311151921341 < Marten::Migration
  def plan
    create_table :main_user do
      column :id, :big_int, primary_key: true, auto: true
      column :username, :string, max_size: 128
      column :email, :string, max_size: 254
      column :first_name, :string, max_size: 128, null: true
      column :last_name, :string, max_size: 128, null: true
      column :created_at, :date_time
      column :updated_at, :date_time
    end
  end
end
```

This migration essentially takes care of creating the database table corresponding to the `User` model we defined previously.  Should any adjustments be necessary for this model in the future, the `marten genmigrations` command can be executed as many times as needed. When doing so, Marten will generate new migration files for each change and will ensure that the generated migrations are interdependent so that they are applied in the right order.

We can now apply our automatically-generated migration by running the [`marten migrate`](https://martenframework.com/docs/development/reference/management-commands#migrate) command:

```bash
marten migrate
```

Which should output something along those lines:

```
Running migrations:
  › Applying main_202311151921341_create_main_user_table... DONE
```

_And voilà!_ Our database is ready.

Before delving into query sets, let's finally open a Crystal playbook and let's create a few records. To do so, let's run the following command:

```bash
crystal play
```

We can now head to [http://localhost:8080](http://localhost:8080) and paste the following Crystal snippet into the live editor:

```crystal
require "./src/project"
Marten.setup

User.create!(username: "johndoe", email: "john.doe@example.com")
User.create!(username: "mary", email: "mary01@example.com", first_name: "Mary", last_name: "Williams")
```

This essentially requires our Marten project and creates two `User` records. We can keep the Crystal playground open as we will be using it for running and testing queries in the following sections.

## What are query sets?

A query set represents a specific query specified for a given model. It can involve filters, be paginated, etc. Unless a specific "write" operation is performed on such query sets, they will usually be mapped to a standard `SELECT` statement where filters are converted to `WHERE` clauses.

Query sets are **lazily evaluated**: defining a query set will usually not involve any database operations. Additionally, most methods provided by query sets also return new query set objects. Query sets are only translated to SQL queries hitting the underlying database when records need to be extracted or manipulated by the considered codebase.

For example:

```crystal
qset = User.filter(first_name: "John") # the query set is not evaluated
qset = qset.filter(last_name: "Doe") # the query set is not evaluated
puts qset # the query set is evaluated
```

## Basic queries

Let's start with some basic query set examples.

### Querying all records

Retrieving all the records of a specific model can be achieved through the use of the [`#all`](https://martenframework.com/docs/models-and-databases/reference/query-set#all) query set method:

```crystal
User.all
```

### Filtering specific records

Filtering records can be achieved by leveraging the [`#filter`](https://martenframework.com/docs/models-and-databases/reference/query-set#filter) query set method. For example:

```crystal
User.filter(username: "johndoe")
```

Will return all `User` records whose username is `johndoe`.

It is possible to filter records by applying multiple filters. To do so, either multiple arguments can be specified to `#filter` or calls to `#filter` can be chained. For example, the following queries are equivalent:

```crystal
User.filter(username: "johndoe").filter(first_name: "John")
User.filter(username: "johndoe", first_name: "John")
```

By default, filters involving multiple parameters like in the above examples always produce SQL queries whose parameters are "AND"ed together.

### Retrieving single objects

Retrieving a specific record is achieved through the use of the [`#get!`](https://martenframework.com/docs/models-and-databases/reference/query-set#get-1) query set method. For example:

```crystal
User.get!(username: "johndoe")
User.get!(id: 1)
```

As you can see, any kind of filters can be specified to this method, but only one record must be returned. If the record is not found, an exception is raised by this method (`Marten::DB::Errors::RecordNotFound`). If multiple records are found, another kind of exception is raised too (`Marten::DB::Errors::MultipleRecordsFound`).

It is also possible to use the [`#get`](https://martenframework.com/docs/models-and-databases/reference/query-set#get) method, which will simply return `nil` if the record is not found.

### Retrieving the first or last record

The [`#first`](https://martenframework.com/docs/models-and-databases/reference/query-set#first) and [`#last`](https://martenframework.com/docs/models-and-databases/reference/query-set#last) query set methods can be used to retrieve the first or last record for a given query set. For example:

```crystal
User.first
User.last

User.filter(username: "johndoe").first
User.filter(username: "johndoe").last
```

### Ordering records

The [`#order`](https://martenframework.com/docs/models-and-databases/reference/query-set#order) query set method can be used to specify in which order records should be queried. Multiple fields can be specified in order to define the final ordering.

For example:

```crystal
qset = User.all
qset.order("-created_at", "username")
```

In the above example, records would be ordered by descending creation date (because of the `-` prefix), and then by username (ascending).

## Advanced queries

Now that we covered some of the fundamental query set operations, let's delve into more advanced examples.

### Filter predicates

Filter predicates define the type of filtering that is done at the SQL level. They map to `WHERE` clauses in the produced SQL queries.

For example:

```crystal
User.filter(username__icontains: "john")
```

Will translate to a SQL query like the following one (using PostgreSQL's syntax):

```sql
SELECT * FROM main_user WHERE username LIKE UPPER("john")
```

By default, unless explicitly specified, the exact predicate (exact match) is used for all filters. As such, the following two query sets are equivalent:

```crystal
User.filter(username: "johndoe")
User.filter(username__exact: "johndoe")
```

Note the double underscores notation used in the above examples: `__` is used to separate the filtered field name (here, `username`) from the type of filtering that is intended to be performed on the corresponding column.

Other predicates can be used, such as `iexact` (case-insensitive match), `contains` (containment test), `startswith` (starts-with test), etc:

```crystal
User.filter(username__startswith: "john")
User.filter(username__contains: "do")
```

### Complex filters with `q` expressions

As mentioned previously, field predicates expressed as keyword arguments to the [`#filter`](https://martenframework.com/docs/models-and-databases/reference/query-set#filter) method will use an `AND` operator in the produced `WHERE` clauses. In order to produce conditions using other operators, it is necessary to use "`q` expressions".

In this light, most of the methods we mentioned previously like [`#filter`](https://martenframework.com/docs/models-and-databases/reference/query-set#filter) or [`#get`](https://martenframework.com/docs/models-and-databases/reference/query-set#get) can receive a block allowing to define complex conditions. Inside of this block, a `#q` method can be used in order to define conditions nodes that can be combined together using the following logical operators: `&` (logical "AND"), `|` (logical "OR"), and `-` (logical negation).

For example, the following snippet will return all the `User` records whose username starts with "john" or "mary":

```crystal
User.filter { q(username__startswith: "john") | q(username__startswith: "mary") }
```

Using this approach, it is possible to produce complex conditions by combining `q` expressions with the `&`, `|`, and `-` operators. Parentheses can also be used to group statements:

```crystal
User.filter { 
  (q(first_name: "John") |  q(first_name: "Mary")) & 
  -q(last_name: "Doe")
}
```

### Paginating results

Marten query sets provide a built-in pagination mechanism that you can leverage in order to easily iterate over records that are split across several pages of data. In this light, each query set has the capability to generate "paginator" objects that are initialized with a given page size and that can be used to request specific pages.

For example:

```crystal
query_set = User.all

paginator = query_set.paginator(10)
paginator.page_size   # => 10
paginator.pages_count # => 6

# Retrieve the first page and iterate over the underlying records
page = paginator.page(1)
page.each { |user| puts user }

page.number               # 1
page.previous_page?       # => false
page.previous_page_number # => nil
page.next_page?           # => true
page.next_page_number     # => 2
```

As you can see, each page object also gives access to some useful metadata (sibling page numbers, etc) in order to build pagination features.

### Performing raw SQL queries

Marten also lets you get fully instantiated records retrieved from [raw SQL queries](https://martenframework.com/docs/models-and-databases/raw-sql). This can be achieved by leveraging the [`#raw`](https://martenframework.com/docs/models-and-databases/reference/query-set#raw) query set method.

For example, the following snippet would allow iterating over all the `User` model records:

```crystal
User.raw("select * from main_user").each do |user|
  # Do something with `user` record
end
```

It is worth mentioning that is is possible to "inject" parameters into raw SQL queries. Both positional and named arguments are supported. Positional parameters must be specified using the `?` syntax while named parameters must be specified using the `:param` format.

For example:

```crystal
# Using positional arguments:
User.raw(
  "SELECT * FROM main_user WHERE first_name = ? and created_at > ?", 
  "John", 
  "2023-11-16"
)

# Using named arguments:
User.raw(
  "SELECT * FROM main_user WHERE first_name = :first_name and created_at > :created_at", 
  first_name: "John", 
  created_at: "2023-11-16"
)
```

## Conclusion

In this article, we delved into Marten's user-friendly object-relational mapper (ORM) and its dynamic "Query sets" mechanism, designed to simplify database interactions in web application projects. We covered both fundamental and advanced query operations, demonstrating Marten's adaptability for building efficient, database-driven applications. It's important to note that our coverage only scratched the surface of Marten's capabilities, as we did not explore more complex scenarios involving [relationships](https://martenframework.com/docs/models-and-databases/introduction#relationships), [joins](https://martenframework.com/docs/models-and-databases/queries#joins-and-filtering-relations), and other advanced query set functionalities.