SequelizeでDBのテーブルとデータを作成し、CRUD操作してみる（2）

　前回の記事で、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しておきたい。

hoge.js

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で言うところのこの句使いたいんだよな～ってときはこちらを参照すンゴ。