LoginSignup
44
26

More than 3 years have passed since last update.

[Rust] sqlxを使ってみる

Posted at

今までずっとdieselを使ってきて
asyncが使えない以外はそこまで困ったことが無いんですが
sqlxを最近よく目にするようになって
使ってみようと思い立ったので、やってみたことをメモ。

環境

  • OS: Ubuntu 20.04
  • DockerCompose: 1.27.4
  • Rust: 1.48.0

DBマイグレーション

sqlxにもマイグレーション機能はあるようです。

cargo install --version=<version> sqlx-cli

でcliをインストール

sqlx migrate add <name>

でマイグレーションファイルが migrations/<timestamp>-<name>.sql というフォーマットで作成され

sqlx migrate run

で実行。

ただ、個人的にはこのマイグレーションファイルがどんどん増えていくスタイルは全体像が追いにくかったりするので
今回は k0kubun/sqldef にお世話になります。
いつもお世話になっております :pray:

環境準備

FROM rust:1.48.0-slim-buster

ENV CARGO_TARGET_DIR=/tmp/target \
    DEBIAN_FRONTEND=noninteractive \
    LC_CTYPE=ja_JP.utf8 \
    LANG=ja_JP.utf8 \
    SQLDEF_VERSION=v0.8.6

RUN apt-get update \
  && apt-get install -y -q \
     ca-certificates \
     locales \
     libpq-dev \
     gnupg \
     apt-transport-https\
     libssl-dev \
     pkg-config \
     curl \
     build-essential \
     git \
     wget \
  && echo "ja_JP UTF-8" > /etc/locale.gen \
  && locale-gen \
  \
  && echo "install sqldef" \
  && curl -L -O https://github.com/k0kubun/sqldef/releases/download/${SQLDEF_VERSION}/psqldef_linux_amd64.tar.gz \
  && tar xf psqldef_linux_amd64.tar.gz \
  && rm psqldef_linux_amd64.tar.gz \
  && mv psqldef /usr/local/bin \
  \
  && echo "install rust tools" \
  && rustup component add rustfmt \
  && cargo install cargo-watch cargo-make

WORKDIR /app

CMD ["cargo", "run"]

今回は単純に実行環境があればいいだけなので、依存ライブラリのキャッシュなどは作ってません。

docker-compose.yml
version: "3.7"

x-environment: &environment
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: postgres
  POSTGRES_HOST: postgres
  POSTGRES_PORT: 5432
  POSTGRES_DB: postgres
  PGSSLMODE: disable

services:
  app:
    build:
      context: .
    container_name: app
    working_dir: /app
    command: bash
    tty: true
    environment: *environment
    volumes:
      - ./:/app
    ports:
      - 3000:3000
    depends_on:
      - postgres

  postgres:
    image: postgres:12.3-alpine
    container_name: postgres
    environment: *environment
    ports:
      - "5433:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
    driver: local

sqlx試す用の環境とpostgresを入れてます。
postgresには後でpgcliでホスト側からアクセスしたいので、portを開けておきます。

テーブル準備

あとでjoinなども試したいので、お決まりなパターンで

schema.sql
CREATE TABLE users (
  id         BIGSERIAL NOT NULL PRIMARY KEY,
  name       VARCHAR(255),
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id         BIGSERIAL    NOT NULL PRIMARY KEY,
  user_id    BIGINT       NOT NULL,
  title      VARCHAR(255) NOT NULL,
  body       TEXT,
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  CONSTRAINT fk_posts_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE INDEX posts_user_id ON posts (user_id);

今回は sagiegurari/cargo-make を入れてあるので、Makefile.tomlを用意して、マイグレーション実行します。

Makefile.toml
[config]
skip_core_tasks = true

[tasks.test-watch]
watch = { watch = ["src"] }
run_task = "test"

[tasks.test]
command = "cargo"
args = ["test", "--", "--nocapture", "-q"]

[tasks.migrate]
command = "psqldef"
args = [
    "-U",
    "${POSTGRES_USER}",
    "-W",
    "${POSTGRES_PASSWORD}",
    "-p",
    "${POSTGRES_PORT}",
    "-h",
    "${POSTGRES_HOST}",
    "-f",
    "schema.sql",
    "${POSTGRES_DB}",
]
❯ docker-compose run --rm app bash
Creating rust-sqlx-examples_app_run ... done
root@53336ad9f513:/app# cargo make migrate
[cargo-make] INFO - cargo make 0.32.10
[cargo-make] INFO - Project: rust_sqlx_examples
[cargo-make] INFO - Build File: Makefile.toml
[cargo-make] INFO - Task: migration
[cargo-make] INFO - Profile: development
[cargo-make] INFO - Execute Command: "psqldef" "-U" "postgres" "-W" "postgres" "-p" "5432" "-h" "postgres" "-f" "schema.sql" "postgres"
-- Apply --
CREATE TABLE users (
  id         BIGSERIAL NOT NULL PRIMARY KEY,
  name       VARCHAR(255),
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP
);
CREATE TABLE posts (
  id         BIGSERIAL    NOT NULL PRIMARY KEY,
  user_id    BIGINT       NOT NULL,
  title      VARCHAR(255) NOT NULL,
  body       TEXT,
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  CONSTRAINT fk_posts_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE UNIQUE INDEX posts_user_id ON posts (user_id);
[cargo-make] INFO - Build Done in 0 seconds.

OK

sqlx使ってみる

Cargo.toml
[package]
name = "rust_sqlx_examples"
version = "0.1.0"
authors = ["yagince <xxxx@gmail.com>"]
edition = "2018"
publish = false

[dependencies]
sqlx = { version = "=0.4.2", features = [ "runtime-tokio-rustls", "postgres", "chrono" ] }
# sqlxが0.2系に依存しているのでバージョンを合わせる
tokio = { version = "0.2.21", features = ["full"] }
anyhow = "=1.0.36"
once_cell = "=1.5.2"
chrono = "=0.4.19"
main.rs
use once_cell::sync::Lazy;

struct Config {
    postgres_host: String,
    postgres_port: String,
    postgres_user: String,
    postgres_password: String,
    postgres_database: String,
}

impl Config {
    pub fn database_url(&self) -> String {
        format!(
            "postgres://{}:{}@{}:{}/{}",
            self.postgres_user,
            self.postgres_password,
            self.postgres_host,
            self.postgres_port,
            self.postgres_database
        )
    }
}

static CONFIG: Lazy<Config> = Lazy::new(|| Config {
    postgres_host: std::env::var("POSTGRES_HOST").unwrap(),
    postgres_port: std::env::var("POSTGRES_PORT").unwrap(),
    postgres_user: std::env::var("POSTGRES_USER").unwrap(),
    postgres_password: std::env::var("POSTGRES_PASSWORD").unwrap(),
    postgres_database: std::env::var("POSTGRES_DB").unwrap(),
});

#[derive(sqlx::FromRow)]
struct User {
    pub id: i64,
    pub name: String,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(20)
        .connect(&CONFIG.database_url())
        .await?;

    let users = sqlx::query_as::<_, User>("select * from users")
        .fetch_all(&pool)
        .await?;

    println!("{:?}", users.len());

    Ok(())
}
root@53336ad9f513:/app# cargo watch -x run
[Running 'cargo run']
    Finished dev [unoptimized + debuginfo] target(s) in 0.06s
     Running `/tmp/target/debug/rust_sqlx_examples`
0

とりあえず動きましたね。

pgcliからデータを入れてみます

postgres@0:postgres> insert into users(name) values ('test-1');
INSERT 0 1
Time: 0.003s
postgres@0:postgres> select * from users;
+------+--------+---------------------------+---------------------------+
| id   | name   | created_at                | updated_at                |
|------+--------+---------------------------+---------------------------|
| 1    | test-1 | 2020-12-25 13:50:52.34583 | 2020-12-25 13:50:52.34583 |
+------+--------+---------------------------+---------------------------+
SELECT 1
Time: 0.017s

[Running 'cargo run']
   Compiling rust_sqlx_examples v0.1.0 (/app)
    Finished dev [unoptimized + debuginfo] target(s) in 1.78s
     Running `/tmp/target/debug/rust_sqlx_examples`
1
[Finished running. Exit status: 0]

1件取れたみたいですね。

main.rs
#[derive(Debug, Clone, PartialEq, sqlx::FromRow)]
struct User {
    pub id: i64,
    pub name: String,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(20)
        .connect(&CONFIG.database_url())
        .await?;

    let users = sqlx::query_as::<_, User>("select * from users")
        .fetch_all(&pool)
        .await?;

    println!("{:?}", users.len());
    println!("{:?}", users);

    Ok(())
}
[Running 'cargo run']
   Compiling rust_sqlx_examples v0.1.0 (/app)
    Finished dev [unoptimized + debuginfo] target(s) in 1.79s
     Running `/tmp/target/debug/rust_sqlx_examples`
1
[User { id: 1, name: "test-1", created_at: 2020-12-25T13:50:52.345830, updated_at: 2020-12-25T13:50:52.345830 }]
[Finished running. Exit status: 0]

うん、ちゃんとデシリアライズできてますね。

Transaction

次にデータをinsertしたり操作がしたいので、その前にTransactionの使いかたを知りたいです。
その前にpostgresのログを出すようにしておきます

postgres@0:postgres> ALTER SYSTEM SET log_statement = 'all';
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER SYSTEM
Time: 1.936s (a second), executed in: 1.936s (a second)

コンテナは再起動しておく

...
2020-12-25T14:07:00.884675252Z 2020-12-25 14:07:00.884 UTC [27] LOG:  execute sqlx_s_1: select * from users
...

こんなログが出るようになったかな、と。

さて、本題、sqlxでトランザクションはどう扱うのか。

https://docs.rs/sqlx/0.4.2/sqlx/struct.Transaction.html
DeepLのちからを借りると...

トランザクションは Pool::begin または Connection::begin への呼び出しで始まります。

トランザクションはコミットまたはロールバックの呼び出しで終了しなければなりません。トランザクションがスコープ外になる前にどちらも呼ばれなかった場合、ロールバックが呼ばれます。言い換えれば、トランザクションがまだ進行中の場合は、ドロップ時にロールバックが呼ばれます。

セーブポイントはトランザクション内の特別なマークであり、確立された後に実行されたすべてのコマンドをロールバックして、トランザクションの状態をセーブポイントの時の状態に戻すことができます。

ということらしい。

なるほど。やってみる。

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(20)
        .connect(&CONFIG.database_url())
        .await?;

    let users = sqlx::query_as::<_, User>("select * from users")
        .fetch_all(&pool)
        .await?;

    println!("{:?}", users.len());
    println!("{:?}", users);

    pool.begin().await?;
    Ok(())
}
2020-12-25T14:13:13.933630783Z 2020-12-25 14:13:13.933 UTC [35] LOG:  statement: BEGIN
2020-12-25T14:13:13.934330348Z 2020-12-25 14:13:13.934 UTC [35] LOG:  unexpected EOF on client connection with an open transaction

なんかそれっぽいログ出てる。
ROLLBACKのログは出てないな。

Insertしてみる

ドキュメント見てもよくわからない...
https://github.com/launchbadge/sqlx/blob/96b76dc737/examples/postgres/todos/src/main.rs#L46-L59
これがinsertかな?
とりあえず、思いつきで書いてみる

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(20)
        .connect(&CONFIG.database_url())
        .await?;

    let users = sqlx::query_as::<_, User>("select * from users")
        .fetch_all(&pool)
        .await?;

    println!("{:?}", users.len());
    println!("{:?}", users);

    pool.begin().await?;
    let user = sqlx::query_as::<_, User>(
        r#"
insert into users (name)
values ($1)
returning *
"#,
    )
    .fetch_one(&pool)
    .await?;
    println!("{:?}", user);
    Ok(())
}
[Running 'cargo run']
   Compiling rust_sqlx_examples v0.1.0 (/app)
    Finished dev [unoptimized + debuginfo] target(s) in 2.38s
     Running `/tmp/target/debug/rust_sqlx_examples`
1
[User { id: 1, name: "test-1", created_at: 2020-12-25T13:50:52.345830, updated_at: 2020-12-25T13:50:52.345830 }]
Error: error returned from database: bind message supplies 0 parameters, but prepared statement "sqlx_s_1" requires 1
[Finished running. Exit status: 1]

エラーになりましたね。
supplies 0 parameters そりゃそうか...

postgresの方見てみる

postgres    | 2020-12-25 14:22:02.153 UTC [46] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 14:22:02.154 UTC [46] LOG:  execute sqlx_s_1: select * from users
postgres    | 2020-12-25 14:22:02.154 UTC [46] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 14:22:02.154 UTC [46] LOG:  statement: BEGIN
postgres    | 2020-12-25 14:22:02.155 UTC [46] LOG:  statement: ROLLBACK
postgres    | 2020-12-25 14:22:02.157 UTC [47] ERROR:  bind message supplies 0 parameters, but prepared statement "sqlx_s_1" requires 1
postgres    | 2020-12-25 14:22:02.157 UTC [47] STATEMENT:
postgres    |   insert into users (name)
postgres    |   values ($1)
postgres    |   returning *
postgres    |
postgres    | 2020-12-25 14:22:02.158 UTC [47] LOG:  could not receive data from client: Connection reset by peer

お、ちゃんとROLLBACKされている?
ん?いや、でもROLLBACKのログのIDと、ERRORのログのID違う???

...
    let user = sqlx::query_as::<_, User>(
        r#"
insert into users (name)
values ($1)
returning *
"#,
        "testtest",
    )
    .fetch_one(&pool)
    .await?;
...

とりあえず、parameterが足りないって言われてるので、入れてみる

error[E0061]: this function takes 1 argument but 2 arguments were supplied
  --> src/main.rs:55:16
   |
55 |       let user = sqlx::query_as::<_, User>(
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^ expected 1 argument
56 | /         r#"
57 | | insert into users (name)
58 | | values ($1)
59 | | returning *
60 | | "#,
   | |__-
61 |           "testtest",
   |           ---------- supplied 2 arguments

なるほど、query_as は引数一個?

...
    let user = sqlx::query_as::<_, User>(
        r#"
insert into users (name)
values ($1)
returning *
"#,
    )
    .bind("testtest")
    .fetch_one(&pool)
    .await?;
...

こうか?

[Running 'cargo run']
   Compiling rust_sqlx_examples v0.1.0 (/app)
    Finished dev [unoptimized + debuginfo] target(s) in 2.33s
     Running `/tmp/target/debug/rust_sqlx_examples`
1
[User { id: 1, name: "test-1", created_at: 2020-12-25T13:50:52.345830, updated_at: 2020-12-25T13:50:52.345830 }]
User { id: 2, name: "testtest", created_at: 2020-12-25T14:31:00.223844, updated_at: 2020-12-25T14:31:00.223844 }
[Finished running. Exit status: 0]

あれ、上手く行った。
なるほど。

けど、おかしい。

4
[User { id: 1, name: "test-1", created_at: 2020-12-25T13:50:52.345830, updated_at: 2020-12-25T13:50:52.345830 }, User { id: 2, name: "testtest", created_at: 2020-12-25T14:31:00.223844, updated_at: 2020-12-25T14:31:00.223844 }, User { id: 3, name: "testtest", created_at: 2020-12-25T14:33:17.369830, updated_at: 2020-12-25T14:33:17.369830 }, User { id: 4, name: "testtest", created_at: 2020-12-25T14:40:46.523699, updated_at: 2020-12-25T14:40:46.523699 }]

どんどん増えてる...
ROLLBACKされていない。
poolを使ってるから、別コネクションになってるのか。

    let mut transaction = pool.begin().await?;
    let user = sqlx::query_as::<_, User>(
        r#"
insert into users (name)
values ($1)
returning *
"#,
    )
    .bind("testtest")
    .fetch_one(&mut transaction)
    .await?;

    println!("{:?}", user);

どうやらこうやるらしい。

postgres    | 2020-12-25 14:51:28.074 UTC [101] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 14:51:28.075 UTC [101] LOG:  execute sqlx_s_1: select * from users
postgres    | 2020-12-25 14:51:28.075 UTC [101] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 14:51:28.075 UTC [101] LOG:  statement: BEGIN
postgres    | 2020-12-25 14:51:28.076 UTC [101] LOG:  execute sqlx_s_2:
postgres    |   insert into users (name)
postgres    |   values ($1)
postgres    |   returning *
postgres    |
postgres    | 2020-12-25 14:51:28.076 UTC [101] DETAIL:  parameters: $1 = 'testtest'
postgres    | 2020-12-25 14:51:28.077 UTC [101] LOG:  could not receive data from client: Connection reset by peer
postgres    | 2020-12-25 14:51:28.077 UTC [101] LOG:  unexpected EOF on client connection with an open transaction

ログのコネクションIDも同じになったっぽい。

じゃあ、bindする時に構造体は使えるか?

#[derive(Debug, Clone, PartialEq, sqlx::Type)]
struct NewUser {
    pub name: String,
}
...

    let user = sqlx::query_as::<_, User>(
        r#"
insert into users (name)
values ($1)
returning *
"#,
    )
    .bind(&NewUser {
        name: "testtest".to_owned(),
    })
    .fetch_one(&mut transaction)
    .await
    .context("insert")?;

...

コンパイルは通ってしまったが

Error: insert

Caused by:
    no rows returned by a query that expected to return at least one row

postgresのログ

postgres    | 2020-12-25 14:54:36.600 UTC [106] LOG:  execute sqlx_s_3:
postgres    |   SELECT oid FROM pg_catalog.pg_type WHERE typname ILIKE $1
postgres    |
postgres    | 2020-12-25 14:54:36.600 UTC [106] DETAIL:  parameters: $1 = 'NewUser'

なるほど、なんか変な感じになっている

    let new_user = NewUser {
        name: "testtest".to_owned(),
    };
    let mut transaction = pool.begin().await?;
    let user = sqlx::query_as::<_, User>(
        r#"
insert into users (name)
values ($1)
returning *
"#,
    )
    .bind(&new_user.name)
    .fetch_one(&mut transaction)
    .await
    .context("insert")?;

こんなふうにしないとダメか。

User { id: 12, name: "testtest", created_at: 2020-12-25T14:57:37.009691, updated_at: 2020-12-25T14:57:37.009691 }

これはうまくいった。当然っちゃ当然だけど...

insertとかupdateみたいな関数があるわけじゃなく、自分でクエリ組み立てて渡すっていうスタイル
クエリビルダもないので、あくまでクエリ投げるのを非同期にして仲介してくれるだけ
(あとENUMとかのシリアライズ、structへのデシリアライズか)
っていう感じなのかな?

Serialize/Deserialize

  • timestampはchrono::NaiveDateTimeにdeserializeできた
  • NullableなカラムをOptionにした時はちゃんとNoneにdesrializeされるのだろうか?
    • Noneは NULL にserializeされるのだろうか
  • intをenumにserialize/deserializeできるか?

NullableなカラムをOptionにした時

#[derive(Debug, Clone, PartialEq, sqlx::FromRow)]
struct Post {
    pub id: i64,
    pub user_id: i64,
    pub title: String,
    pub body: Option<String>,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

#[derive(Debug, Clone, PartialEq)]
struct NewPost {
    pub user_id: i64,
    pub title: String,
    pub body: Option<String>,
}

...
    let new_post = NewPost {
        user_id: user.id,
        title: format!("Title-{}", user.id),
        body: None,
    };

    let post = sqlx::query_as::<_, Post>(
        r#"
insert into posts (user_id, title, body)
values ($1, $2, $3)
returning *
"#,
    )
    .bind(&new_post.user_id)
    .bind(&new_post.title)
    .bind(&new_post.body)
    .fetch_one(&mut transaction)
    .await?;
...
User { id: 17, name: "testtest", created_at: 2020-12-25T15:12:11.315320, updated_at: 2020-12-25T15:12:11.315320 }
Post { id: 2, user_id: 17, title: "Title-17", body: None, created_at: 2020-12-25T15:12:11.315320, updated_at: 2020-12-25T15:12:11.315320 }

ちゃんと NULLNone になってそうな予感。

postgresのログ

postgres    | 2020-12-25 15:12:11.314 UTC [133] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 15:12:11.314 UTC [133] LOG:  execute sqlx_s_1: select * from users
postgres    | 2020-12-25 15:12:11.315 UTC [133] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 15:12:11.315 UTC [133] LOG:  statement: BEGIN
postgres    | 2020-12-25 15:12:11.315 UTC [133] LOG:  execute sqlx_s_2:
postgres    |   insert into users (name)
postgres    |   values ($1)
postgres    |   returning *
postgres    |
postgres    | 2020-12-25 15:12:11.315 UTC [133] DETAIL:  parameters: $1 = 'testtest'
postgres    | 2020-12-25 15:12:11.316 UTC [133] LOG:  execute sqlx_s_3:
postgres    |   insert into posts (user_id, title, body)
postgres    |   values ($1, $2, $3)
postgres    |   returning *
postgres    |
postgres    | 2020-12-25 15:12:11.316 UTC [133] DETAIL:  parameters: $1 = '17', $2 = 'Title-17', $3 = NULL
postgres    | 2020-12-25 15:12:11.317 UTC [133] LOG:  could not receive data from client: Connection reset by peer
postgres    | 2020-12-25 15:12:11.317 UTC [133] LOG:  unexpected EOF on client connection with an open transaction

あぁ、ちゃんと NoneNULL になってますね。

intをEnumにserialize/deserializeできるか

https://docs.rs/sqlx/0.4.2/sqlx/types/trait.Type.html#enumeration
できるっぽい

posts テーブルに visibility カラムを追加してみる。

schema.sql
--- a/schema.sql
+++ b/schema.sql
@@ -8,6 +8,7 @@ CREATE TABLE users (
 CREATE TABLE posts (
   id         BIGSERIAL    NOT NULL PRIMARY KEY,
   user_id    BIGINT       NOT NULL,
+  visibility INTEGER      NOT NULL,
   title      VARCHAR(255) NOT NULL,
   body       TEXT,
   created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
root@53336ad9f513:/app# cargo make migrate
[cargo-make] INFO - cargo make 0.32.10
[cargo-make] INFO - Project: rust_sqlx_examples
[cargo-make] INFO - Build File: Makefile.toml
[cargo-make] INFO - Task: migrate
[cargo-make] INFO - Profile: development
[cargo-make] INFO - Execute Command: "psqldef" "-U" "postgres" "-W" "postgres" "-p" "5432" "-h" "postgres" "-f" "schema.sql" "postgres"
-- Apply --
ALTER TABLE "public"."posts" ADD COLUMN "visibility" integer NOT NULL;
[cargo-make] INFO - Build Done in 0 seconds.
#[derive(Debug, Clone, PartialEq, sqlx::Type)]
#[repr(i32)]
enum PostVisibility {
    Public = 1,
    Private = 2,
}

#[derive(Debug, Clone, PartialEq, sqlx::FromRow)]
struct Post {
    pub id: i64,
    pub visibility: PostVisibility,
    pub user_id: i64,
    pub title: String,
    pub body: Option<String>,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

#[derive(Debug, Clone, PartialEq)]
struct NewPost {
    pub user_id: i64,
    pub visibility: PostVisibility,
    pub title: String,
    pub body: Option<String>,
}
...
    let new_post = NewPost {
        user_id: user.id,
        visibility: PostVisibility::Public,
        title: format!("Title-{}", user.id),
        body: None,
    };

    let post = sqlx::query_as::<_, Post>(
        r#"
insert into posts (user_id, visibility, title, body)
values ($1, $2, $3, $4)
returning *
"#,
    )
    .bind(&new_post.user_id)
    .bind(&new_post.visibility)
    .bind(&new_post.title)
    .bind(&new_post.body)
    .fetch_one(&mut transaction)
    .await?;
...
User { id: 19, name: "testtest", created_at: 2020-12-25T15:32:29.128581, updated_at: 2020-12-25T15:32:29.128581 }
Post { id: 4, visibility: Public, user_id: 19, title: "Title-19", body: None, created_at: 2020-12-25T15:32:29.128581, updated_at: 2020-12-25T15:32:29.128581 }

postgresのログ

postgres    | 2020-12-25 15:32:29.126 UTC [157] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 15:32:29.128 UTC [157] LOG:  execute sqlx_s_1: select * from users
postgres    | 2020-12-25 15:32:29.128 UTC [157] LOG:  statement: /* SQLx ping */
postgres    | 2020-12-25 15:32:29.128 UTC [157] LOG:  statement: BEGIN
postgres    | 2020-12-25 15:32:29.129 UTC [157] LOG:  execute sqlx_s_2:
postgres    |   insert into users (name)
postgres    |   values ($1)
postgres    |   returning *
postgres    |
postgres    | 2020-12-25 15:32:29.129 UTC [157] DETAIL:  parameters: $1 = 'testtest'
postgres    | 2020-12-25 15:32:29.129 UTC [157] LOG:  execute sqlx_s_3:
postgres    |   insert into posts (user_id, visibility, title, body)
postgres    |   values ($1, $2, $3, $4)
postgres    |   returning *
postgres    |
postgres    | 2020-12-25 15:32:29.129 UTC [157] DETAIL:  parameters: $1 = '19', $2 = '1', $3 = 'Title-19', $4 = NULL
postgres    | 2020-12-25 15:32:29.131 UTC [157] LOG:  could not receive data from client: Connection reset by peer
postgres    | 2020-12-25 15:32:29.131 UTC [157] LOG:  unexpected EOF on client connection with an open transaction

$2 = '1' になってる。
ちゃんと Public で取得できてる。

Joinしたい場合

    let users = sqlx::query_as::<_, (User, Post)>(
        r#"
select * from users as u
inner join posts as p on u.id = p.user_id
"#,
    )
    .fetch_all(&mut transaction)
    .await?;

こんな簡単にはいかなかった

error[E0277]: the trait bound `User: sqlx::Decode<'_, _>` is not satisfied
   --> src/main.rs:125:17
    |
125 |     let users = sqlx::query_as::<_, (User, Post)>(
    |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `sqlx::Decode<'_, _>` is not implemented for `User`
    |
   ::: /usr/local/cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.4.2/src/query_as.rs:160:8
    |
160 |     O: for<'r> FromRow<'r, DB::Row>,
    |        ---------------------------- required by this bound in `sqlx::query_as`
    |
    = note: required because of the requirements on the impl of `for<'r> FromRow<'r, _>` for `(User, Post)`

error[E0277]: the trait bound `User: Type<_>` is not satisfied
   --> src/main.rs:125:17
    |
125 |     let users = sqlx::query_as::<_, (User, Post)>(
    |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `Type<_>` is not implemented for `User`
    |
   ::: /usr/local/cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.4.2/src/query_as.rs:160:8
    |
160 |     O: for<'r> FromRow<'r, DB::Row>,
    |        ---------------------------- required by this bound in `sqlx::query_as`
    |
    = note: required because of the requirements on the impl of `for<'r> FromRow<'r, _>` for `(User, Post)`

error[E0277]: the trait bound `Post: sqlx::Decode<'_, _>` is not satisfied
   --> src/main.rs:125:17
    |
125 |     let users = sqlx::query_as::<_, (User, Post)>(
    |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `sqlx::Decode<'_, _>` is not implemented for `Post`
    |
   ::: /usr/local/cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.4.2/src/query_as.rs:160:8
    |
160 |     O: for<'r> FromRow<'r, DB::Row>,
    |        ---------------------------- required by this bound in `sqlx::query_as`
    |
    = note: required because of the requirements on the impl of `for<'r> FromRow<'r, _>` for `(User, Post)`

error[E0277]: the trait bound `Post: Type<_>` is not satisfied
   --> src/main.rs:125:17
    |
125 |     let users = sqlx::query_as::<_, (User, Post)>(
    |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `Type<_>` is not implemented for `Post`
    |
   ::: /usr/local/cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.4.2/src/query_as.rs:160:8
    |
160 |     O: for<'r> FromRow<'r, DB::Row>,
    |        ---------------------------- required by this bound in `sqlx::query_as`
    |
    = note: required because of the requirements on the impl of `for<'r> FromRow<'r, _>` for `(User, Post)`

error[E0599]: no method named `fetch_all` found for struct `QueryAs<'_, _, (User, Post), _>` in the current scope
   --> src/main.rs:131:6
    |
131 |     .fetch_all(&mut transaction)
    |      ^^^^^^^^^ method not found in `QueryAs<'_, _, (User, Post), _>`
    |
    = note: the method `fetch_all` exists but the following trait bounds were not satisfied:
            `(User, Post): FromRow<'r, _>`

こんな感じ。
まぁそうですよね、という感じ。

https://github.com/launchbadge/sqlx/blob/96b76dc737/examples/realworld/src/db/pg.rs
この辺みると

        let recs = sqlx::query!(
            r#"
SELECT
    articles.*
    ,profiles.username, profiles.bio as bio, profiles.image
FROM articles
INNER JOIN profiles ON articles.author_id = profiles.user_id
ORDER BY created_at
            "#
        )
        .fetch_all(self)
        .await?;

        let entities = recs
            .into_iter()
            .map(|rec| {
                let article = ArticleEntity {
                    article_id: rec.article_id,
                    title: rec.title,
                    slug: rec.slug,
                    description: rec.description,
                    body: rec.body,
                    author_id: rec.author_id,
                    created_at: rec.created_at,
                    updated_at: rec.updated_at,
                };
                // FIXME(pg) for some reason query can't figure out the view columns are not nullable
                let author = ProfileEntity {
                    user_id: rec.author_id,
                    username: rec.username.unwrap(),
                    bio: rec.bio,
                    image: rec.image,
                };
                (article, author)
            })
            .collect::<Vec<_>>();
        Ok(entities)

こんな感じで書いてるけど
この query! マクロはDATABSE_URLを要求します。
つまりコンパイル時にDBを起動しておく必要がある。
これは。。。んー、いまいちな気がするんですけどねぇ...
DB起動してる状況じゃないとビルドできないって、どうなんだろう?

というわけで頑張ってみたのがこちら。

    let users = sqlx::query(
        r#"
select
  users.id, users.name, users.created_at, users.updated_at,
  posts.id, posts.user_id, posts.title, posts.body, posts.visibility, posts.created_at, posts.updated_at
from users
inner join posts on users.id = posts.user_id
"#,
    )
        .map(|row: PgRow| {
            (
                User {
                    id: row.get(0),
                    name: row.get(1),
                    created_at: row.get(2),
                    updated_at: row.get(3),
                },
                Post {
                    id: row.get(4),
                    user_id: row.get(5),
                    title: row.get(6),
                    body: row.get(7),
                    visibility: row.get(8),
                    created_at: row.get(9),
                    updated_at: row.get(10),
                },
            )
        })
    .fetch_all(&mut transaction)
    .await?;

    println!("{:#?}", users);
[
    (
        User {
            id: 23,
            name: "testtest",
            created_at: 2020-12-25T16:25:22.144518,
            updated_at: 2020-12-25T16:25:22.144518,
        },
        Post {
            id: 8,
            visibility: Public,
            user_id: 23,
            title: "Title-23",
            body: None,
            created_at: 2020-12-25T16:25:22.144518,
            updated_at: 2020-12-25T16:25:22.144518,
        },
    ),
]

こんな感じで取得できました。

まとめ

  • sqlxはすごくシンプルなクエリ実行とO/Rマッピング的な事に特化したライブラリっぽい感じ
    • dieselの方が高機能ではある
    • それゆえの面倒さもあるので、トレードオフですかね
    • 面倒さ → schema.rsが必要だったり、自動生成される型がカオスだったり
  • クエリビルダー的な機能はない
    • マクロを使うとコンパイル時にチェックしてくれる部分はあるのかな?
    • ※今回は使ってみてないので、わからなかった
    • dieselはクエリビルダの機能もあるけど、あれはあれで面倒さもある。
  • sqlxに感じた個人的メリット
    • 準備が楽で、導入が楽
    • コード書く量は増えそうだけど、逆にシンプルにSQLを書けばいいので、複雑なクエリは書きやすいかも
    • クエリビルダー的なものを使うとサブクエリとかEXISTS使ったクエリとか書きにくい時がある
    • async/await使える
44
26
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
44
26