sequelize(node.js) + mysql でSQL実行する #javascript #node.js #mysql
index:
環境
node.js 10.16.0
sequelize 5.11.0
準備など
・表の作成
https://github.com/kuc-arc-f/sequelize_1/blob/master/database/book.sql
CREATE TABLE `books` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `content` text DEFAULT NULL, `user_id` int(11) NOT NULL DEFAULT 0, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
sequelize 実行
https://github.com/kuc-arc-f/sequelize_1/blob/master/test1.js
インスタンス/ new Sequelize
の引数は、DB名、接続ユーザ、パスワード、dialect: mysql
const Sequelize = require('sequelize'); const sequelize = new Sequelize('vue1','db_user','password',{dialect:'mysql'}) /******************************** * *********************************/ sequelize.query("select * from books").spread((results, metadata) => { // console.log(results ); results.forEach( function (item) { console.log( item.id ); console.log( item.title ); }); sequelize.close(); })
外部結合する場合。
・表の追加
https://github.com/kuc-arc-f/sequelize_1/blob/master/database/users.sql
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `users` (`id`, `name`, `createdAt`, `updatedAt`) VALUES (1, 'hoge2', '2019-07-27 12:45:57', '2019-07-27 23:24:23'), (2, 'yamada tarou', '2019-07-27 12:46:28', '2019-07-27 12:46:28'), (7, 't3', '2019-07-28 05:24:56', '2019-07-28 05:24:56');
・結合のSQL実行
/******************************** * *********************************/ var sql ="select books.id, books.title ,users.name from books"; sql += " LEFT OUTER JOIN users ON users.id = books.user_id"; //console.log(sql); sequelize.query(sql).spread((results, metadata) => { // console.log(results ); results.forEach( function (item) { console.log( item.title ); console.log( item.name ); }); sequelize.close(); })