今までずっと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 にお世話になります。
いつもお世話になっております 
環境準備
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]
１件取れたみたいですね。
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されるのだろうか
- Noneは
- 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 }
ちゃんと NULL が None になってそうな予感。
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
あぁ、ちゃんと None が NULL になってますね。
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使える