※2020年に作った記事なのでご注意ください。
##環境
node：v8.11.3
##squelとは
メソッドチェーンやって、sqlを組み立てられる便利なnodeモジュール。
sqlを直書きしていると、他の人が見て分かりにくいものになってしまうので、sqlビルダーを使います。
僕、フレームワークとか使う前までは直書きしていて、
当時は、文字列連結でandを作っていたので何回もやり直した記憶がある。。。
const sql =
"INSERT INTO test_table (id , name) VALUES (" + id +","+ "'"+name+"'" +")" ;
このような地獄の文字列連結をforループの中でしていた時期が私にもあった。何度エラー出たことか。。。
larabelみたいな便利なフレームワークにはクエリビルダとかあったりするけど、今回はnodeなので、nodeのモジュールの力を借りる。
準備
●まず、インストール。
npmから、squelをインストールするだけ。
これは動作に必要なモジュールなので、--saveつける(何もつけないのと同じ)
●使い方
以下に書いたが、メソッドチェーンでfromやintoなどを直感的に指定できる。
selectを使った例
where()の中に、「squel.expr().and().and()」のように、拡張というか、追加で条件が書ける。
and()がいけるということは、もちろんor()もいける。
※ちなみに、.where().where()と繋げた場合はandになる。
なので、orもandもあるようなsqlなら書き方を合わせてexpr().and()を使うのもアリ
あと、order byなら「order()」
group byなら「group()」
を繋がるだけよい。
outer_left_join()とかははっきりとした名前なのになんでorderとgroupのときだけ省略されてんだって思ったけど、joinに関しては最後のほうにjoinという名前がつくので、まあ仕方ない。
join()だけでinner joinになる。
複数の条件の中で一部の条件に対して括弧をつけたいときは、再度where()を繋がればいい。
多少違和感を感じるかもしれないが、where()ごとに括弧で囲ったようなsqlが作れてラクなので結果的には使いやすい。
const squel = require('squel');
//selectを使った例。
let sql = squel.select()
.field("title")
.field("deadline")
.from("calendar")
.order("calendar_id",false) // falseをつけると、desc。指定しなかったら、asc
.toString();
console.log(sql);//SELECT title, deadline FROM calendar ORDER BY calendar_id DESC
//case文
sql = squel.select()
.from("calendar")
.where(squel.case()
.when("name = ?" , "Aichi").then("正義のヒーローやんけ")
.when("name = ?" , "Hoge").then("なんだか怪しいなぁ"))
.toString();
console.log(sql);//SELECT calendar_id, hogehoge_title FROM calendar `cal` LEFT OUTER JOIN eventMaster `e` ON (cal.article_id=e.article_id) WHERE (cal.deed_flag<>1 AND (isnull(cal.article_id)) OR e.target_id<>2)
//joinを使った例
sql = squel.select()
.from("calendar","cal")
.field("calendar_id")
.field("hogehoge_title")
.left_outer_join("eventMaster","e","cal.article_id=e.article_id")// as句がいらない場合は第二引数を「""」にする。第二引数を省略してはいけない。
.where(squel.expr().and("cal.deed_flag<>1").and("(isnull(cal.article_id))").or("e.target_id<>2"))
.toString();
console.log(sql);//SELECT calendar_id, hogehoge_title FROM calendar `cal` LEFT OUTER JOIN eventMaster `e` ON (cal.article_id=e.article_id) WHERE (cal.deed_flag<>1 AND (isnull(cal.article_id)) OR e.target_id<>2)
updateを使った例
set()を繋がるこどに、複数の値を更新できる。
const sql = squel.update()
.table("hogeTable")
.set("name","aichi")
.where("name = taro")
.toString();
console.log(sql);//UPDATE hogeTable SET name = 'aichi' WHERE (name = taro)
insertを使った例
set()を繋げていって、複数の値を指定できる。
//●insert
const sql = squel.insert()
.into("userMaster")
.set("name","aichi")
.set("age",23)
.toString();
console.log(sql);// insert into userMaster (name,age) values ("aichi" , 23)
deleteを使った例
これはまあ直感的。idとかは変数になると思うので、
バッククォートで埋め込むことになる。
//●delete
const sql = squel.delete()
.from("otakuMaster")
.where("id = 1")
.toString();
console.log(sql);//DELETE FROM otakuMaster WHERE (id = 1)
クォーテーションで囲みたくない場合
例えばOracleのCURRENT_TIMESTAMPを指定したい場合、これをダブルクォーテーションで囲むと変な感じになるので、setの第三引数で指定できる
const sql = squel
.insert()
.into('hogeTable')
.set('DATA_CREATE_DATE', 'CURRENT_TIMESTAMP', {
dontQuote: true,
})
.toString();
console.log(sql);//INSERT INTO hogeTable (DATA_CREATE_DATE) VALUES (CURRENT_TIMESTAMP)
## 連想配列を使って一気に指定
一つの組み合わせなら、setFields()に連想配列単体を入れればよい
```javascript:nogizaka-suko.js
//●フィールドを連想配列で指定
const sql = squel.insert()
.into("hogeTable")
.setFields({'name' : '愛知','age' : 23})
.toString();
console.log(sql);//INSERT INTO hogeTable (name, age) VALUES ('愛知', 23)
insertしたいデータの数が固定ではない場合のsql生成
他の処理結果からsql文を生成する場合、直接文字列連結なんかせず、データの連想配列をsetFieldsRows()に入れるだけで、sqlを生成してくれる。
insert文のvaluesの後が続いていく感じになるので、一回のinsert文で複数のレコードが入ることになります。
今回は、例として２つの連想配列データで試します。
実際は、for文でデータを回して１つの配列に対して連想配列データを入れていくことになります。
const hogeArray1 = {
'name' : 'Aichi',
'age' : 23,
}
const hogeArray2 = {
'name' : '山本彩',
'age' : 26,
}
const allHogeArray = [hogeArray1,hogeArray2];
let sqlInsert = squel.insert()
.into("livingNationalTreasureMaster")
.setFieldsRows(allHogeArray)
.toString();
console.log(sqlInsert);//INSERT INTO livingNationalTreasureMaster (name, age) VALUES ('Aichi', 23), ('山本彩', 26)
感想
直書きしていた時代からしたら、感動もんですね。
たまに公式のREADMEに書かれていない構文があるが、
同じようにスネークケースで書いてみると実はちゃんと用意されていて使えたりします。