[Rust] sqlxを使ってみる

今までずっと[diesel](https://github.com/diesel-rs/diesel)を使ってきて
asyncが使えない以外はそこまで困ったことが無いんですが
[sqlx](https://github.com/launchbadge/sqlx)を最近よく目にするようになって
使ってみようと思い立ったので、やってみたことをメモ。

# 環境
- OS: Ubuntu 20.04
- DockerCompose: 1.27.4
- Rust: 1.48.0

# DBマイグレーション

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

https://github.com/launchbadge/sqlx/tree/master/sqlx-cli#usage

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

でcliをインストール

```shell
sqlx migrate add <name>
```

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

```shell
sqlx migrate run
```
で実行。

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

# 環境準備

```Dockerfile
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"]
```

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

```yaml: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](https://www.pgcli.com/)でホスト側からアクセスしたいので、portを開けておきます。

# テーブル準備

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

```sql: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](https://github.com/sagiegurari/cargo-make) を入れてあるので、Makefile.tomlを用意して、マイグレーション実行します。

```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}",
]
```

```shell
❯ 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使ってみる

```toml: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"
```

```rust: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(())
}
```

```shell
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からデータを入れてみます

```shell
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
```

```shell

[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]
```

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

```rust: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(())
}
```

```shell
[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のログを出すようにしておきます

```sql
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 への呼び出しで始まります。
> 
> トランザクションはコミットまたはロールバックの呼び出しで終了しなければなりません。トランザクションがスコープ外になる前にどちらも呼ばれなかった場合、ロールバックが呼ばれます。言い換えれば、トランザクションがまだ進行中の場合は、ドロップ時にロールバックが呼ばれます。
> 
> セーブポイントはトランザクション内の特別なマークであり、確立された後に実行されたすべてのコマンドをロールバックして、トランザクションの状態をセーブポイントの時の状態に戻すことができます。```
```
ということらしい。

なるほど。やってみる。

```rust
#[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かな？
とりあえず、思いつきで書いてみる

```rust
#[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違う？？？

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

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

```shell
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` は引数一個？

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

こうか？

```shell
[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]
```

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

けど、おかしい。

```shell
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を使ってるから、別コネクションになってるのか。

```rust
    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);
```

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

```shell
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する時に構造体は使えるか？

```rust
#[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")?;

...
```

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

```shell
Error: insert

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

postgresのログ

```shell
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'
```

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

```rust
    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")?;
```
こんなふうにしないとダメか。

```shell
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にした時

```rust
#[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?;
...
```

```shell
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 }
```

ちゃんと `NULL` が `None` になってそうな予感。

postgresのログ

```shell
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
```

あぁ、ちゃんと `None` が `NULL` になってますね。

## intをEnumにserialize/deserializeできるか

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

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

```diff: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,
```

```shell
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.
```

```rust
#[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?;
...
```

```shell
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したい場合

```rust
    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?;
```

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

```shell
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
この辺みると

```rust
        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起動してる状況じゃないとビルドできないって、どうなんだろう？

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

```rust
    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);
```

```rust
[
    (
        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使える