シナプスの技術部、システム開発課の小園です。
C#にSQLクエリビルダを提供するライブラリ SqlKata Query Builder を試してみました。
目次
SqlKata Query Builder とは
C#でSQLを組み立てるためのライブラリです。
対応データベースは、SqlServer, MySql, PostgreSql, Oracle, Sqlite, Firebird です。
サイト
- SqlKata the dotnet SQL query builder
sqlkata.com - SqlKata GitHub
github.com
なぜ試したのか
シナプスではシステム構成の見直しを進めており、データベースORMとしてDapperを採用する事になりました。
Dapperにはクエリビルダの機能が無かったので、SqlKata Query Builderでどんな事が実現できるのかを検証しました。
検証について
検証は、SqlKataの提供するPlayGround機能と、SqlKataのコンパイル機能で生成されるSQLで行いました。
SqlKataのコンパイル機能というのはクエリ実行の必要が無い場合に、データベースへの接続文字列（接続インスタンス）は無しで、SQL文字列にコンパイルのみを行う機能です。
検証
Select
基本的な例
using System.Collections.Generic;
using System.Diagnostics;
using SqlKata;
using SqlKata.Compilers;
namespace SqlKataTest
{
class Program
{
static void Main()
{
var compiler = new SqlServerCompiler();
var query = GetQuery(1, "active");
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings;
Debug.WriteLine(sql);
// SELECT [Id], [Name] FROM [Users] WHERE [Id] = @p0 AND [Status] = @p1
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { 1, "active" };
}
static Query GetQuery(int? id, string status)
{
var query = new Query("Users").Select("Id", "Name");
if (id.HasValue)
{
query.Where("Id", id);
}
if (!string.IsNullOrWhiteSpace(status))
{
query.Where("Status", status);
}
return query;
}
}
}
Debug出力内容（Debug.WriteLine下のコメント）より次の事が判りました。
- SQLの条件（WHERE）が動的に生成できる。
- SQLがパラメータクエリを使用してSQLを生成している。
パラメータクエリを使用しているのはSQLインジェクション対策として安心できます。
例には入っていないですが。CAST、CONVERT、ISNULLのような特定の（この場合はSqlServer）環境に依存する関数を使う場合は .ForSqlServer(q => q.SelectRaw("CAST([Date] as DATE)")) のような書き方で動くらしい。（使わないで済むならば、その方が良いと思う。）
色々なWhere条件
var compiler = new SqlServerCompiler();
var query = new Query()
.From("Users")
.Select("Id", "Name")
.WhereIn("Id", new List<int>() { 10, 11, 12, 13 })
.WhereDate("CreateDate", ">=", "2021-09-01")
.WhereContains("Name", "Yama")
.WhereBetween("Id", 10, 25)
.OrWhere("Name", "Sato");
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings;
Debug.WriteLine(sql);
// SELECT [Id], [Name]
// FROM [Users]
// WHERE [Id] IN (@p0, @p1, @p2, @p3) AND
// CAST([CreateDate] AS DATE) >= @p4 AND
// LOWER([Name]) like @p5 AND
// [Id] BETWEEN @p6 AND @p7
// OR
// [Name] = @p8
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { 10, 11, 12, 13, "2021-09-01", "%yama%", 10, 25, "Sato" };
Debug出力内容（Debug.WriteLine下のコメント）より次の事が判りました。
- 全ての項目が、パラメータクエリを使用している。
- IN句, Between句に対応したメソッドがある。
- 日付列を処理して比較するための WhereDate（日付のみ）がある。他に、WhereTime、WhereDatePart なども存在する。
- 文字列 like句に対応する WhereContains がある。他に、WhereStarts、WhereEnds なども存在する。
- OR 句も存在する。（多様すると最終的なSQLが判りにくいので多用は避けた方が良いと思う。）
Whereで一般的に使われる句はサポートされています。
Join
var compiler = new SqlServerCompiler();
var query = new Query("Users")
.Select("Users.Id", "Users.Name")
.Join("Contacts", "Users.Id", "Contacts.UserId")
.LeftJoin("MailAddresses", c => c.On("Contacts.Id", "MailAddresses.ContactId").Where("MailAddresses.IsDelete","0"));
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
Debug.WriteLine(sql);
// SELECT [Users].[Id], [Users].[Name] FROM [Users]
// INNER JOIN [Contacts] ON [Users].[Id] = [Contacts].[UserId]
// LEFT JOIN [MailAddresses] ON ([Contacts].[Id] = [MailAddresses].[ContactId] AND [MailAddresses].[IsDelete] = @p0)
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { "0" };
Debug出力内容（Debug.WriteLine下のコメント）より以下の事が判りました。
- Joinの結合条件にパラメータクエリが使われる。
- Join時に複数項目を関連付ける場合にはラムダ式を使う。
- 他にRightJoin, CrossJoin もある。
Joinについても一般的に使われている句はサポートされている事が判りました。
その他
- 上記以外の、SelectのSubQuery, FromのSubQuery, Limit（SqlServerのTOP）, Group, Order, Having, Union なども実装されている事が確認できました。（サンプルは省略です…）
Insert
通常インサート
var compiler = new SqlServerCompiler();
var query = new Query("Users").AsInsert( new
{
Name = "Yamada",
MailAddress = "yamada@example.com",
Status = "active",
});
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings;
Debug.WriteLine(sql);
// INSERT INTO [Users] ([Name], [MailAddress], [Status]) VALUES (@p0, @p1, @p2)
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { "Yamada", "yamada@example.com", "active" };
Debug出力内容（Debug.WriteLine下のコメント）より、パラメータクエリを使用している事が判りました。
過不足の無いSQLになっていると思います。
複数レコードのインサート
var compiler = new SqlServerCompiler();
var cols = new[] { "Name", "MailAddress", "Status" };
var data = new[]
{
new object[] {"Yamada", "yamada@example.com", "active" },
new object[] {"Suzuki", "suzuki@example.com", "active" },
new object[] {"Tanaka", "Tanaka@example.com", "active" },
};
var query = new Query("Users")
.AsInsert(cols, data);
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings;
Debug.WriteLine(sql);
// INSERT INTO [Users] ([Name], [MailAddress], [Status]) VALUES (@p0, @p1, @p2), (@p3, @p4, @p5), (@p6, @p7, @p8)
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { "Yamada", "yamada@example.com", "active", "Suzuki", "suzuki@example.com", "active", "Tanaka", "tanaka@example.com", "active" };
Debug出力内容（Debug.WriteLine下のコメント）より、パラメータクエリを使用している事、複数レコードであっても1行のSqlで処理される事が判りました。
例には無いですが、Select結果のInsertも対応している事が判りました。
Update
var compiler = new SqlServerCompiler();
var query = new Query("Users").Where("Name", "Sato").AsUpdate(new
{
MailAddress = "hoge@example.com"
});
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings;
Debug.WriteLine(sql);
// UPDATE [Users] SET [MailAddress] = @p0 WHERE [Name] = @p1
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { "hoge@example.com", "Sato" };
Debug出力内容（Debug.WriteLine下のコメント）より、パラメータクエリを使用している事が判りました。
Delete
var compiler = new SqlServerCompiler();
var query = new Query("Users").Where("Name", "Yamada").AsDelete();
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings;
Debug.WriteLine(sql);
// DELETE FROM [Users] WHERE [Name] = @p0
Debug.WriteLine(ObjectDumper.Dump(bindings, DumpStyle.CSharp));
// new List<object> { "Yamada" };
Debug出力内容（Debug.WriteLine下のコメント）より、パラメータクエリを使用している事が判りました。
まとめ
SqlKata を試してみて以下のような事が判りました。
- 基本的なSQLはサポートされている。
- 直接SQLを書く必要が無いですが、最終的なSQLをイメージしながら書く必要がある。
- SQLとSqlKata両方の学習が必要。（学習コストの増加）
- SQLインジェクションは起きないようなコードが生成される。
- 確認できた限りですが、全ての値はパラメータクエリを使用していました。
- IDEのコード補完があるので、単純な書き間違いを減らせる。
- 複雑なSQLを書くには向かない。
- 複雑なSQLにはビジネスロジックが潜む可能性がある。
全てが駄目では無いが、複雑なSQLは書かない工夫が大事。
- 複雑なSQLにはビジネスロジックが潜む可能性がある。
- 環境に依存する関数も使える。
使うかどうかは、システムがどこまで抽象化を求めるかによると思う。- 使わなければ、DBの環境を変更をしても書き換えるは少なくて済むが、そういった事態を想定するかだと思う。
学習コストが増えるデメリットがありますが、意識しなくてもSQLインジェクションが起きないコードが生成されるのは嬉しいと思いました。
シナプスの環境に投入するには、DIとの連携、クエリ後のオブジェクトマッピングの確認など幾つかの検証すべき箇所はありますが、試してみても良いのではないかと思いました。
