Deno のクエリビルダーを試す

引き続き、Deno での DB アクセスを詰めて行きます。
今回は、sql_builder を使ってクエリの組み立てを試します。

参考

sql-builder 使ってみる

基本的なクエリ

サンプルが充実しているので、ほとんど README 見れば話が済む(なぜまとめようと思ったのか・・・)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
import { Query, Where } from "https://deno.land/x/sql_builder/mod.ts";

// Query は毎回作り直す必要あり作り直さないと次のクエリに影響する
let builder = new Query();

let sql = builder.table("users").select("*").build();
console.log(sql);
// => SELECT * FROM `users`

builder = new Query();
sql = builder.table("users").where(Where.field("id").eq(1)).select("*").build();
console.log(sql);
// => SELECT * FROM `users`
// Where が README 通りの Where("id", "=", 1) みたいなパターンが動かない・・・。

builder = new Query();
sql = builder
  .table("users")
  .where(Where.and(Where.field("id").eq(1), Where.field("age").gt(10)))
  .select("*")
  .build();
console.log(sql);
// => SELECT * FROM `users` WHERE (`id` = 1 AND `age` > 10)

builder = new Query();
sql = builder
  .table("users")
  .where(Where.or(Where.field("id").eq(1), Where.field("age").gt(10)))
  .select("*")
  .build();
console.log(sql);
// => SELECT * FROM `users` WHERE (`id` = 1 OR `age` > 10)

builder = new Query();
sql = builder
  .table("users")
  .where(Where.field("id").in([1, 2, 3]))
  .select("*")
  .build();
console.log(sql);
// => SELECT * FROM `users` WHERE `id` IN (1,2,3)

builder = new Query();
sql = builder
  .table("users")
  .where(Where.field("id").eq(1))
  .update({ name: "AAA" })
  .build();
console.log(sql);
// => UPDATE `users` SET `name` = "AAA" WHERE `id` = 1

builder = new Query();
sql = builder.table("users").where(Where.field("id").eq(1)).delete().build();
console.log(sql);
// => DELETE FROM `users` WHERE `id` = 1

// .order("id", "DESC") の呼び出しは、エラーになるので、別の記述で対応
builder = new Query();
sql = builder
  .table("users")
  .where(Where.field("id").eq(1))
  .select("*")
  .order(Order.by("id").desc)
  .build();
console.log(sql);
// => SELECT * FROM `users` WHERE `id` = 1 ORDER BY `id` DESC

builder = new Query();
sql = builder
  .table("users")
  .join("LEFT OUTER JOIN items ON items.user_id = users.id")
  .where(Where.field("users.id").eq(1))
  .select("*")
  .build();
console.log(sql);
// => SELECT * FROM `users` LEFT JOIN items ON items.user_id = users.id WHERE `users`.`id` = 1

// left outer join は次の書き方も可
builder = new Query();
sql = builder
  .table("users")
  .join(Join.left("items").on("items.user_id", "users.id"))
  .where(Where.field("users.id").eq(1))
  .select("*")
  .build();
console.log(sql);
// => SELECT * FROM `users` LEFT OUTER JOIN `items` ON `items`.`user_id` = `users`.`id` WHERE `users`.`id` = 1

// このほかの join (FULL INNER RIGHT) も実装している

builder = new Query();
sql = builder.table("users").select("*").limit(0, 3).build();
console.log(sql);
// => SELECT * FROM `users` LIMIT 0, 3

README 通り動かないものがあったものの、書き方を変えれば対応できました。

副問い合わせを含むクエリ

副問い合わせについては、README にどうやら記載が無いです。
考えてみました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
let builder = new Query();
const sub = builder
  .table("users")
  .where(Where.field("id").eq(1))
  .select("id")
  .build();

builder = new Query();
const sql = builder
  .table("items")
  .where(Where.expr(`user_id in (${sub})`))
  .select("*")
  .build();
console.log(sql);
// => SELECT * FROM `items` WHERE user_id in (SELECT `id` FROM `users` WHERE `id` = 1)

1回での生成ができないものの、副問い合わせのあるクエリを生成できました。
副問い合わせまでできれば、十分使えそうです。

実際にクエリを実行してみる

deno_mysql で実行するクエリを sql_builder で

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
//クエリビルダで生成したSQLを実行
import { Client } from "https://deno.land/x/mysql/mod.ts";
import { Query } from "https://deno.land/x/sql_builder/mod.ts";

// User 型定義
interface User {
  id: number;
  name: string;
  created_at: string;
}

// 接続パラメータ
const connectionParam = {
  hostname: "db",
  username: "root",
  password: "パスワード",
  db: "testdb",
};

// クライアント作成
const client = await new Client().connect(connectionParam);

// クエリ作成
const builder = new Query();
let sql = builder.table("users").select("*").build();

// データ検索
const { rows } = await client.execute(sql) as { fields: any; rows: User[] };
console.log(rows);

// クライアントの切断
await client.close();

クエリビルダーだけが分離したものを初めて使ったのですが、使用感は悪くはなさそうです。
ここまで DB ドライバ、ORM、マイグレーションツール、クエリビルダーと使ってきましたが、ある程度方向が見えました。

nessie と、deno_mysql のデータベースへの接続コンフィグは、外に出して共通化すればより良さそうです。
これをまとめた形で、次また記載したいところです。

ではでは。