前回の記事で、Sequelize-cliを使ってDB上にテーブルを作成し、そこにレコード(シードデータ)を挿入するところまで出来た。今回はその続きで、SequelizeによるDB上のデータのCRUD操作を行ってみる。前回Sequelize-cliを使って作ったテーブルとデータをそのまま使っていくンゴ。
開発環境
sequelize: 6.21.0
sequelize-cli: 6.4.1
node: 17.1.0
express: 4.18.1
準備
レコード取得系のメソッドに関しては、デベロッパーツールでデバッグポイントを貼って、検索したモデルのインスタンスの中身をコンソールで見るとかするのもめんどくさいので、DBの検索結果をそのままクライアント側に返すAPIサーバをNode.jsのフレームワークであるexpressを利用して構築し、返ってきたjsonレスポンスを確認するという形にしたい。
まずは以下のひな型を用意して、これにルーティングとかを追加する形で肉付けしていく。expressは別途npm install
しておきたい。
const express = require('express');
const app = express();
app.listen(3000, function() {
console.log('server running on port 3000...');
});
またこのhoge.js
上で、今回作成したStudent
モデルを使えるようにしなきゃいけないので、
const models = require('{modelsフォルダの相対パス}');
を追加する。
実はnpx sequelize init
を実行した際に作成された/models/index.js
によって、models
直下のindex.js
以外のモデルファイルが読み込まれ、各モデルクラスがオブジェクトのプロパティとして設定されてmodule.exports
されている。これによってjsのコード上で別フォルダに配置しているモデルを参照することが出来る。なのでrequire
するのはmodels
フォルダ全体ではなく、ピンポイントで/models/index.js
としても挙動は変わらない(試してみて分かった)。
Sequelizeによるクエリング
これでSequelizeのメソッドを使ってCRUD操作を行う準備が出来たので、APIを実行するとSequelizeによって特定のクエリング(DBのデータ操作)が実行されるコードを実装していく。使用するHTTPメソッドだけど、SELECT系はGET
、INSERT・UPDATE・DELETEはPOST
と理解しているので、このルールに従ってルーティングしていく。
create
まずはINSERT(CREATE)から。以下を先ほどのhoge.js
に追加してみる。
app.post('/create', (req, res) => {
const student = {
studentNo: '006',
firstName: '誠也',
lastName: '鈴木'
};
models.Student.create(student)
.then((result) => {
res.status(200).json(result);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
これでnode hoge.js
を実行してサーバを起動させ、http://localhost:3000/create
によってリクエストを送信するのだが、POST
の場合、ブラウザのurl入力欄で実行するとGET
メソッドとして送信されてしまうため、Talend APIなどのツールを利用するといいンゴ。
さて、上記APIを送信すると、上で追加(create)したstudentがレスポンスとして返ってくる。もう一度実行してみると、{"error message": "重複したキー値は一意性制約\"Student_pkey\"違反となります"}
というレスポンスが返ってくる。このように、エラーが発生した場合、Sequelizeはrejected
状態のPromiseを返してくれるため、上で追加したようにcatch文などでエラーハンドリングが出来る。
またcreate()
の代わりにbulkCreate()
を使うと、複数のデータを同時に挿入することが出来る。ただしcreate()
と違ってデフォルトだとバリデーションが実行されないため、Sequelizeでバリデーションをしたい場合は第二引数に{validate: true}
を渡してあげる必要がある(詳細はこちら)。
ちなみにバリデーションはSequelizeサイドで行われるチェックのこと。Sequelizeで用意されているものもあるし、自分で作ることもできる。もしバリデーションに引っかかった場合、SQLのクエリがDBで発行されることはない。詳しくはこちら
finders
SELECT(READ)系のメソッドは、Sequelizeではfinders
と呼ばれていて、Sequelizeの6系(=メジャーバージョン6)では5つ存在している。色々と尾ひれがついたSELECT文をSequelizeで実装する方法は後の章で紹介してるので、ここではシンプルなSELECT文だけを扱うンゴね。
findAll
これは、SQLでいうSELECT
文そのもの。取得してきたレコードが全件返ってくる。実装は省略。
findOne
これはfindAll
と違って、取得してきたレコードのうち1番目のレコードだけを返す。今回のStudent
テーブルとデータの例でいくと、001
の大谷翔平のレコードだけがSequelizeから返される。実際に実装・実行してサーバのログを見てみると、SQL文の最後にLIMIT 1
がついているのが分かる。実装は省略。
findByPk
これは名前の通り主キーでレコードを1件だけ一意に検索する時に使うメソッド。findByPk('001')
というように、引数に主キーの値を入れるだけ。複合キーの場合は引数も複数にすればいいんだろうか、試してないから分からない…。実装は省略。
findOrCreate
これも名前の通り(?)、SELECTした結果レコードが取得できた(DBに存在した)らそれを返却し、取得できなかった(DBに存在しなかった)らデータをINSERTして、そのレコードを返却するという、findersとcreateのハイブリッド型のメソッド。hoge.js
に以下を追加する。
app.post('/findOrCreate', (req, res) => {
models.Student.findOrCreate({
where: {firstName: '朗希'},
defaults: {studentNo: '007', lastName: '佐々木'}
}).then((result) => {
const response = {};
response.result = result[0];
response.isCreated = result[1];
res.status(200).json(response);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
/findOrCreate
にリクエストを送信すると、以下のレスポンスが返ってくる。
{
"result": {
"studentNo": "007",
"lastName": "佐々木",
"firstName": "朗希",
"updatedAt": "20XX-XX-XXTXX:XX:XX.XXXZ",
"createdAt": "20XX-XX-XXTXX:XX:XX.XXXZ",
"deletedAt": null
},
"isCreated": true
}
(createdAt
とupdatedAt
はマスキングしてるンゴ)
ということで、findOrCreate
は、where
で指定した条件に合致するレコードが存在しなかった場合、新しいレコードを作成してくれるんだけど、defaults: {studentNo: '007', lastName: '佐々木'}
の部分でwhere
で指定したカラムの値以外の部分を指定している。それから戻り値として、検索された or 作成されたレコードと、新規されたかどうかを表すBoolean型の値の2つを要素とする配列を返す。
findAndCountAll
こちらはまず実装をして、それから説明をしたいので以下をhode.js
に追加する。
app.get('/findAndCountAll', (req, res) => {
models.Student.findAndCountAll({
limit: 2
}).then((result) => {
res.status(200).json(result);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
/findAndCountAll
にリクエストを送信すると、以下のレスポンスが返ってくる。
{
"count": 7,
"rows": [
{
"studentNo": "001",
"firstName": "翔平",
"lastName": "大谷",
"createdAt": "20XX-XX-XXTXX:XX:XX.XXXZ",
"updatedAt": "20XX-XX-XXTXX:XX:XX.XXXZ",
"deletedAt": null
},
{
"studentNo": "002",
"firstName": "有",
"lastName": "ダルビッシュ",
"createdAt": "20XX-XX-XXTXX:XX:XX.XXXZ",
"updatedAt": "20XX-XX-XXTXX:XX:XX.XXXZ",
"deletedAt": null
}
]
}
(createdAt
とupdatedAt
はマスキングしてるンゴ)
ということで、findAndCountAll
は、戻り値として以下の2つのプロパティをもつオブジェクトを返す。
-
count
:limit
がなかった場合のレコード数(※group
が指定されている場合、配列になる) -
rows
: 指定した条件で検索した結果のレコードを要素とする配列
update
次はUPDATE。以下をhoge.js
に追加してみる。
app.post('/update', (req, res) => {
models.Student.update({firstName: '嘉智', lastName: '筒香'},{
where: {studentNo: '007'}
}).then(() => {
return models.Student.findAll();
}).then((result) => {
res.status(200).json(result);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
/update
にリクエストを送信すると、レスポンスとして7件データが返ってきて、7件目が更新されていることが分かる。
destroy
最後にDELETE。以下をhoge.js
に追加してみる。
app.post('/soft-delete', (req, res) => {
models.Student.destroy({
where: {studentNo: '007'}
}).then(() => {
return models.Student.findAll();
}).then((result) => {
res.status(200).json(result);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
/soft-delete
にリクエストを送信すると、レスポンスとして6件データが返ってくる。前の記事でStudent
のモデルファイルのStudent.init()
の第二引数でparanoid: true
としてたと思うんだけど、実はこれは「このモデルでは論理削除を有効にします」っていう宣言で、destroy()
メソッドで削除を実行するとレコードが物理削除されるんじゃなくてdeletedAt
のカラムに実行日時が挿入されるという挙動になる。
ほんで上のコードでは、論理削除に成功した場合はfindAll()
を実行して、その結果をレスポンスとして返しているけども、Sequelizeで実行されるクエリは論理削除されたレコードを自動的に無視するというか、存在しないものとして扱うというような挙動をする(一方でDB上でSELECT
文を実行した場合、論理削除されたレコードも取得される)。findByPk
で一意検索したとしても、目的のレコードが論理削除されている場合はnull
が返ってくる。上のコードで論理削除されたレコードも取得してレスポンスに含めたい場合は、findAll()
の部分をfindAll({paranoid: false})
とする必要がある。
ちなみに論理削除したレコードを元の状態に戻すこともできるのでその実装についても紹介する。hoge.js
に以下を追加する。
app.post('/restore', (req, res) => {
models.Student.restore({
where: {studentNo: '006'}
}).then(() => {
return models.Student.findByPk('006');
}).then((result) => {
res.status(200).json(result);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
まあrestore()
メソッドで元に戻せるよ、where
で戻したいレコード指定してね、って感じよね。/restore
にリクエストを送信すると、レスポンスjsonでdeletedAt
がnull
になっていることが分かる。
さて、上記のAPIで論理削除の操作は実装できたので、今度は物理削除を実装してみる。hoge.js
に以下を追加する。
app.post('/hard-delete', (req, res) => {
models.Student.destroy({
where: {studentNo: '006'}, force: true
}).then(() => {
return models.Student.findAll();
}).then((result) => {
res.status(200).json(result);
}).catch((err) => {res.status(500).json({"error message": err.message});});
});
/hard-delete
にリクエストを送信すると、レスポンスとして6件データが返ってくるので、1件物理削除されたことが分かる。findAll()
に{paranoid: false}
を渡してないから、レスポンスjsonの確認だけじゃ心もとないという場合は実際のDBを見てみてるべし。
色んなバリエーションのSELECT文をSequelizeで表現してみる
準備
以下の2行をhoge.js
に追加する。
const sequelize = models.sequelize;
const { Op } = require('sequelize');
AND句
models.Student.findAll({
where: {firstName: '翔平', lastName: '大谷'}
})
以下のように書く方法もある。
models.Student.findAll({
where: {[Op.and]: [{firstName: '翔平'}, {lastName: '大谷'}]}
})
OR句
models.Student.findAll({
where: {[Op.or]: [{firstName: '翔平'}, {firstName: '雄星'}]}
})
上の例ではたまたま条件のカラムがfirstName
で同じなため、以下のように書く方法もある。
models.Student.findAll({
where: {firstName: {[Op.or]: ['翔平', '雄星']}}
})
ANDとORの組み合わせ
models.Student.findAll({
where: {studentNo: {[Op.or]: ['001', '003']}, firstName: {[Op.or]: ['翔平', '雄星']}}
})
上の例は、SQLだと以下のように検索しているのと同じ。
SELECT FROM Student WHERE (studentNo = '001' OR studentNo = '003') AND (firstName = '翔平' OR firstName = '雄星');
ORDER BY句
名前と名字で並べ替えする意味とかって全くないし、2つ目のlastName
の昇順っていう条件なくても結果同じだからあんま意味ないんだけど、書くならこんな感じ。
models.Student.findAll({order: [['firstName', 'DESC'], ['lastName']]})
上の例以外にも色々バリエーションがある。詳細はこちら
GROUP BY句
名字でグルーピングとか今回の例だとあんま意味ないけど、やるならこんな感じに書く。
models.Student.findAll({group: 'lastName'})
LIMIT句・OFFSET句
models.Student.findAll({offset: 2, limit: 3})
発展的なクエリ
SELECT * FROM "Student" WHERE char_length("firstName") = 1;
みたいな検索をSequelizeで実行したい場合は、以下のように書く。
models.Student.findAll({
where: sequelize.where(sequelize.fn('char_length', sequelize.col('firstName')), 1)
})
コードにあるsequelize
は、/models/index.js
で生成されたSequelize
のインスタンスで、準備のところでこれを変数に代入して使えるようにしたもの(const sequelize = models.sequelize;
)
その他
Sequelizeには他にも色々使える表現がある。SQLで言うところのこの句使いたいんだよな~ってときはこちらを参照すンゴ。