---
title: Performing raw SQL queries
description: Learn how to perform raw SQL queries.
sidebar_label: Raw SQL
---

Marten gives you the ability to execute raw SQL if the capabilities provided by [query sets](./queries.md) 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`](./reference/query-set.md#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):

```crystal
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`](./reference/query-set.md#raw) query set method. Unless you have explicitly overridden this name by using the [`#db_table`](pathname:///api/0.5/Marten/DB/Model/Table/ClassMethods.html#db_table(db_table%3AString|Symbol)-instance-method) 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:

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

And the following one uses named parameters:

```crystal
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:

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

And instead, do something like that:

```crystal
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`](./reference/query-set.md#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:

```crystal
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:

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

And the following one uses named parameters:

```crystal
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:

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

And instead, do something like that:

```crystal
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](./queries#complex-filters-with-q-expressions) syntax within a block:

```crystal
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`](./reference/query-set.md#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](https://github.com/crystal-lang/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:

```crystal
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`](pathname:///api/0.5/Marten/DB/Connection.html#get(db_alias%3AString|Symbol)-class-method) 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](../development/reference/settings.md#database-settings)) and returns the corresponding DB connection:

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

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

The [`#open`](pathname:///api/0.5/Marten/DB/Connection/Base.html#open(%26)-instance-method) 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](https://crystal-lang.org/reference/database/index.html#open-database) and it returns the same DB connection objects that you would get if you were using `DB#open` directly:

```crystal
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](https://crystal-lang.org/reference/database/index.html) to learn more about this low-level API.
