Digging into Marten query sets

The Marten web framework 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, we can use the marten new command to do so:

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:

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 command:

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:

# 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 command:

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:

crystal play

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

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:

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 query set method:

User.all

Filtering specific records

Filtering records can be achieved by leveraging the #filter query set method. For example:

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:

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! query set method. For example:

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 method, which will simply return nil if the record is not found.

Retrieving the first or last record

The #first and #last query set methods can be used to retrieve the first or last record for a given query set. For example:

User.first
User.last

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

Ordering records

The #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:

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:

User.filter(username__icontains: "john")

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

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:

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:

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 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 or #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":

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:

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:

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. This can be achieved by leveraging the #raw query set method.

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

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:

# 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, joins, and other advanced query set functionalities.