Rust | SQLx の macro `query_file!` と `query_file_as!` で複雑な SQL クエリを分離する

まえがき

今回は別ファイルとして切り出した SQL クエリを読み込むことができる query_file! と query_file_as! に焦点をあてていきます！

query! と query_as! については、
以下の記事で触れていますので、ぜひチェックしてみてください😙

https://zenn.dev/collabostyle/articles/9747c20a516f34

query_file! と query_file_as! の特徴

A variant of query! where the SQL query is stored in a separate file.
Useful for large queries and potentially cleaner than multiline strings.
The syntax and requirements (see query!) are the same except the SQL string is replaced by a file path.

query!の一種で、SQLクエリを別のファイルに格納する。
大きなクエリに便利で、複数行の文字列よりもきれいになる可能性があります。
構文と要件（query!を参照）は同じですが、SQL文字列がファイルパスに置き換えられています。

参照: query_file in sqlx

SQL クエリを複数行の文字列で記述していると、どうしてもコードが長くなりがちです。
そのような場合に、SQL クエリ部分を別の .sql に分離できるというわけです！

実装サンプル

PostgreSQL で動作確認しています🐘

データベースの準備

テーブルとサンプルデータは以下のクエリで作成しました。

create table if not exists mountains (
    id serial primary key,
    "name" varchar not null,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now()
);

insert into
    mountains ("name")
values
    ('富士山'),
    ('高尾山'),
    ('大雪山');

クレートと .env の準備

非同期処理ランタイムには tokio を使います。

cargo add sqlx --features "postgres runtime-tokio-native-tls chrono"
cargo add tokio --features=full
cargo add dotenv

メンテナンスの状態を加味すると、dotenvy を使う方が無難かもしれません！

データベースの接続 URL を定義しておきます。

.env

DATABASE_URL=postgres://postgres:postgres@localhost:5432/sample_db

SQL ファイル

以下のようなディレクトリ構成でファイルを格納してみました。

今回はサンプルなので、queries　のみとしたが、
ファイルの数が増えることを想定して、階層は上手に切った方が良さげですね...

src
├── main.rs
└── queries
    ├── mountain-by-id.sql
    └── mountain-insert.sql

せっかく分離したのに、ファイル管理は煩雑になっては意味がないので
ファイル名には規則性を持たせるべきでしょう。

mountain-by-id.sql

SELECT クエリを分離させました。

mountain-by-id.sql

select
    m."id",
    m."name",
    m."created_at",
    m."updated_at"
from
    mountains as m
where
    m."id" = $1

【メモ】個人的に SQL を記述する際に意識していること

• as などは省略しない
• 予約語を考慮し、" " ではさむようにする
• 必要なカラムを取得するようにするため、* の使用は控える

mountain-insert.sql

INSERT クエリを分離させました。

mountain-insert.sql

insert into
    mountains ("name")
values
    ($1)

main.rs

SELECT で query_file!、INSERTで query_file_as! を使用しました。

短い SQL 文ですが、ファイルパスに置き換えられたことで、かなりスッキリしました🥳🎉

main.rs

use sqlx::postgres::{PgPoolOptions, PgQueryResult};
use sqlx::types::chrono::NaiveDateTime;
use sqlx::{Pool, Postgres};
use std::env;

#[tokio::main]
async fn main() {
    // 環境変数を読み取り、URLを生成
    dotenv::dotenv().expect("Failed to read .env file");
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");

    // DBコネクションを生成
    let db = PgPoolOptions::new()
        .max_connections(10)
        .connect(&database_url)
        .await
        .unwrap_or_else(|_| panic!("Cannot connect to the database"));

    match get_one(&db, 1).await {
        Ok(m) => println!("{:?}", m),
        Err(e) => println!("{:?}", e),
    }

    match create(&db, "高野山").await {
        Ok(res) => println!("{:?}", res),
        Err(e) => println!("{:?}", e),
    }
}

#[derive(Debug)]
struct Mountain {
    id: i32,
    name: String,
    created_at: NaiveDateTime,
    updated_at: NaiveDateTime,
}

async fn get_one(db: &Pool<Postgres>, id: i32) -> Result<Mountain, sqlx::Error> {
    // 返り値の型、SQL ファイルのパス、引数を指定する
    // src からのパス指定
    sqlx::query_file_as!(Mountain, "src/queries/mountain-by-id.sql", id)
        .fetch_one(db)
        .await
}

async fn create(db: &Pool<Postgres>, name: &str) -> Result<PgQueryResult, sqlx::Error> {
    // SQL ファイルのパス、引数を指定
    // src からのパス指定
    sqlx::query_file!("src/queries/mountain-insert.sql", name.to_string())
        .execute(db) // insert なので fetch_one ではなく execute
        .await
}

query_as! と query_file_as! の比較

比較してみると、スッキリしてますね🫶🫶🫶

Before: query_as!

async fn get_one_2(db: &Pool<Postgres>, id: i32) -> Result<Mountain, sqlx::Error> {
    sqlx::query_as!(
        Mountain,
        r#"
            select
                m."id",
                m."name",
                m."created_at",
                m."updated_at"
            from
                mountains as m
            where
                m."id" = $1
        "#,
        id
    )
    .fetch_one(db)
    .await
}

After: query_file_as!

async fn get_one(db: &Pool<Postgres>, id: i32) -> Result<Mountain, sqlx::Error> {
    // 返り値の型、SQL ファイルのパス、引数を指定する
    // src からのパス指定
    sqlx::query_file_as!(Mountain, "src/queries/mountain-by-id.sql", id)
        .fetch_one(db)
        .await
}

JOIN が多い場合や WITH 句がある場合などは、
より query_file_as! の恩恵を受けることができそうです。

また、query_file_as! は返り値の型が保証されているので、
SQL クエリが外部の別ファイルにあっても、分離したことによって返り値はわからないということもなさそうです。

拡張子の縛りはあるの？

ドキュメントを見ていると、別ファイル という記載があるので、
拡張子は .sql 以外でも動作するのか検証してみました笑

結論：問題なし！

async fn get_one(db: &Pool<Postgres>, id: i32) -> Result<Mountain, sqlx::Error> {
    // 返り値の型、SQL ファイルのパス、引数を指定する
    // src からのパス指定
    sqlx::query_file_as!(Mountain, "src/queries/mountain-by-id.txt", id)
        .fetch_one(db)
        .await
}

テキスト .txt でも、問題なく動作することが確認できました。
ファイルの中身を文字列として読み込んでいると考えれば、当然と言えば当然の結果です。

ただ、.sql 以外を採用するメリットはなさそうです🤫

所感・使いどころ

複数行になるとコードの可読性が落ちるので、ファイルを分離できるのは良いなと思いました！

IntelliJ IDEA で開発をしていますが、
SQL の入力補完は働くので文字列より楽に記述できる点も良かったです。

当然、$1 の箇所ではエラーになるので、
エラーにならないように回避ができると開発が捗りそう...😗😗

エラーの回避策について、ご教授いただきました！

IDE の設定より「データベース | データベース | クエリ実行 | ユーザーパラメーター」から「パラメーターパターン」を追加からパターンを \$\d+ と設定すると SQL エディターでエラーにならなくなるそうです！！！！

参考: User Parameters | PyCharm Documentation

実践導入にあたっては、以下を検討したいです🤔

• queries ディレクトリ配下の構成
  • ファイルが増えた場合のことを考慮しておきたい
• フォルダ名やファイル名の命名規則
  • SELECT, INSERT, UPDATE, DELETE はフォルダ名やファイル名で判断がつくと良さそう
• すべて分離させるか、一部を分離させるか
  • 一部の場合、何を基準として分離させるのか
  • 中途半端になるくらいなら、すべて分離が分かりやすい？

まとめ

SQLx は本当に使いやすい！

よく考えられているなと感じます👏✨

参考

• query_file in sqlx
• query_file_as in sqlx