Performing raw SQL queries

Marten gives you the ability to execute raw SQL if the capabilities provided by query sets are not sufficient for the task at hand. When doing so, multiple solutions can be considered: you can either decide to perform raw queries that are mapped to actual model instances, or you can execute entirely custom SQL statements.

Performing raw queries

It is possible to perform raw SQL queries and expect to have the corresponding records mapped to actual model instances. This is possible by using the #raw query set method.

For example, the following snippet would allow iterating over all the Article model records (by assuming that the corresponding database table is the main_article one):

Article.raw("select * from main_article").each do |article|
  # Do something with `article` record
end

tip

You need to know the name of the model table you are targetting to use the #raw query set method. Unless you have explicitly overridden this name by using the #db_table class method, the name of the model table is automatically generated by Marten using the following format: <app_ame>_<model_name> (model_name being the underscore version of the model class name).

It should be noted that you can also "inject" parameters into your SQL query. To do so you have two options: either you specify these parameters as positional arguments, or you specify them as named arguments. Positional parameters must be specified using the ? syntax while named parameters must be specified using the :param format.

For example, the following query uses positional parameters:

Article.raw("SELECT * FROM articles WHERE title = ? and created_at > ?", "Hello World!", "2022-10-30")

And the following one uses named parameters:

Article.raw(
  "SELECT * FROM articles WHERE title = :title and created_at > :created_at",
  title: "Hello World!",
  created_at: "2022-10-30"
)

caution

Do not use string interpolations in your SQL queries!

You should never use string interpolations in your raw SQL queries as this would expose your code to SQL injection attacks (where attackers can inject and execute arbitrary SQL into your database).

As such, never - ever - do something like that:

Article.raw("SELECT * FROM articles WHERE title = '#{title}'")

And instead, do something like that:

Article.raw("SELECT * FROM articles WHERE title = ?", title)

Also, note that the parameters are left unquoted in the raw SQL queries: this is very important as not doing it would expose your code to SQL injection vulnerabilities as well. Parameters are quoted automatically by the underlying database backend.

Finally, it should be noted that Marten does not validate the SQL queries you specify to the #raw query set method. It is the developer's responsibility to ensure that these queries are (i) valid and (ii) that they return records that correspond to the considered model.

Filtering with raw SQL predicates

Marten provides a feature to filter query sets using raw SQL predicates within the #filter method. This is useful when you need more complex filtering logic than simple field comparisons but still want to leverage Marten's query building capabilities.

Using raw SQL predicates involves specifying a string containing the actual predicate and optional parameters to the #filter query set method. For example:

Author.filter("author_id IS NOT NULL")
Author.filter("first_name = ?", "John")

Specifying parameters

You can "inject" parameters into your SQL raw predicates when using the #filter method. To do so you have two options: either you specify these parameters as positional arguments, or you specify them as named arguments. Positional parameters must be specified using the ? syntax while named parameters must be specified using the :param format.

For example, the following query uses positional parameters:

Article.filter("title = ? and created_at > ?", "Hello World!", "2022-10-30")

And the following one uses named parameters:

Article.filter(
  "title = :title and created_at > :created_at",
  title: "Hello World!",
  created_at: "2022-10-30"
)

caution

Do not use string interpolations in your SQL predicates!

You should never use string interpolations in your raw SQL predicates as this would expose your code to SQL injection attacks (where attackers can inject and execute arbitrary SQL into your database).

As such, never - ever - do something like that:

Article.filter("title = '#{title}'")

And instead, do something like that:

Article.filter("title = ?", title)

Also, note that the parameters are left unquoted in the raw SQL queries: this is very important as not doing it would expose your code to SQL injection vulnerabilities as well. Parameters are quoted automatically by the underlying database backend.

Using q expressions

For even more flexibility, you can combine raw SQL predicates with the q expression syntax within a block:

Post.all.filter { q(category: "news") & q("created_at > ?", Time.local - 7.days) }

Executing other SQL statements

If it is necessary to execute other SQL statements that don't fall into the scope of what's provided by the #raw query set method, then it's possible to rely on the low-level DB connection capabilities.

Marten's DB connections are essentially wrappers around DB connections provided by the crystal-db package. They can be opened, which allows you to essentially execute any query on the considered database.

For example, the following snippet would open a connection to the default database and execute a simple query:

Marten::DB::Connection.default.open do |db|
  db.scalar("SELECT 1")
end

tip

If you are using multiple databases and need to execute SQL statements on a database that is not the default one, then you can retrieve the considered DB connection object by using the Marten::DB::Connection#get method. This method simply requires an argument corresponding to the DB alias you want to retrieve (ie. the alias you assigned to the database in the databases configuration) and returns the corresponding DB connection:

db = Marten::DB::Connection.get(:other_db)

db.open do |db|
  db.scalar("SELECT 1")
end

The #open method allows opening a connection to the considered database, which you can then use to perform queries. This method leverages Crystal's DB opening mechanism and it returns the same DB connection objects that you would get if you were using DB#open directly:

Marten::DB::Connection.default.open do |db|
  db.exec "create table contacts (name varchar(30), age int)"
end

Please refer to Crystal's official documentation on interacting with databases to learn more about this low-level API.