mysql> CREATE SCHEMA nodejs DEFAULT CHARACTER SET utf8;
Query OK, 1row affected (0.01sec)
mysql> use nodejs;
Database changed
mysql> CREATE TABLE nodejs.users(
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> age INT UNSIGNED NOT NULL,
-> married TINYINT NOT NULL,
-> comment TEXT NULL,
-> created_at DATETIME NOT NULL DEFAULT now(),
-> PRIMARY KEY(id),
-> UNIQUE INDEX name_UNIQUE (name ASC))
-> COMMENT = '사용자 정보'
-> DEFAULT CHARACTER SET = utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> DESC users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | int unsigned | NO | | NULL | |
| married | tinyint | NO | | NULL | |
| comment | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
mysql> CREATE TABLE nodejs.comments(
-> id INT NOT NULL AUTO_INCREMENT,
-> commenter INT NOT NULL,
-> comment VARCHAR(100) NOT NULL,
-> created_at DATETIME NOT NULL DEFAULT now(),
-> PRIMARY KEY(id),
-> INDEX commenter_idx (commenter ASC),
-> CONSTRAINT commenter
-> FOREIGN KEY (commenter)
-> REFERENCES nodejs.users (id)
-> on DELETE CASCADE
-> on UPDATE CASCADE)
-> COMMENT = '댓글'
-> DEFAULT CHARSET=utf8mb4
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW TABLES;
+------------------+
| Tables_in_nodejs |
+------------------+
| comments |
| users |
+------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('zero', 24, 0, '자기소개1');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('nero', 32, 1, '자기소개2');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO nodejs.comments (commenter, comment) VALUES (1, '안녕하세요. zero의 댓글입니다');
Query OK, 1 row affected (0.01 sec)
SELECT 칼럼 FROM 테이블명
mysql> SELECT * FROM nodejs.users;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment | created_at |
+----+------+-----+---------+-----------+---------------------+
| 1 | zero | 24 | 0 | 자기소개1 | 2023-01-19 18:35:02 |
| 2 | nero | 32 | 1 | 자기소개2 | 2023-01-19 18:35:24 |
+----+------+-----+---------+-----------+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT name, married FROM nodejs.users;
+------+---------+
| name | married |
+------+---------+
| zero | 0 |
| nero | 1 |
+------+---------+
2 rows in set (0.00 sec)
WHERE로 조건을 주어 선택 가능
mysql> SELECT id, name, age, married FROM nodejs.users WHERE married = 1 AND age > 30;
+----+------+-----+---------+
| id | name | age | married |
+----+------+-----+---------+
| 2 | nero | 32 | 1 |
+----+------+-----+---------+
1 row in set (0.00 sec)
mysql> SELECT id, name, age, married FROM nodejs.users WHERE married = 0 OR age > 30;
+----+------+-----+---------+
| id | name | age | married |
+----+------+-----+---------+
| 1 | zero | 24 | 0 |
| 2 | nero | 32 | 1 |
+----+------+-----+---------+
2 rows in set (0.00 sec)
ORDER BY로 특정 칼럼 값 순서대로 정렬 가능
mysql> SELECT id, name, age FROM nodejs.users ORDER BY age DESC;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | nero | 32 |
| 1 | zero | 24 |
+----+------+-----+
2 rows in set (0.00 sec)
mysql> SELECT id, name, age FROM nodejs.users ORDER BY age ASC;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | zero | 24 |
| 2 | nero | 32 |
+----+------+-----+
2 rows in set (0.00 sec)
mysql> SELECT id, name, age FROM nodejs.users ORDER BY age DESC LIMIT 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | nero | 32 |
+----+------+-----+
1 row in set (0.00 sec)
mysql> SELECT id, name, age FROM nodejs.users ORDER BY age DESC LIMIT 1 OFFSET 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | zero | 24 |
+----+------+-----+
1 row in set (0.00 sec)
mysql> UPDATE nodejs.users SET comment='내용 바뀜' WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM nodejs.users;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment | created_at |
+----+------+-----+---------+-----------+---------------------+
| 1 | zero | 24 | 0 | 자기소개1 | 2023-01-19 18:35:02 |
| 2 | nero | 32 | 1 | 내용 바뀜 | 2023-01-19 18:35:24 |
+----+------+-----+---------+-----------+---------------------+
2 rows in set (0.00 sec)
mysql> DELETE FROM nodejs.users WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM nodejs.users;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment | created_at |
+----+------+-----+---------+-----------+---------------------+
| 1 | zero | 24 | 0 | 자기소개1 | 2023-01-19 18:35:02 |
+----+------+-----+---------+-----------+---------------------+
1 row in set (0.00 sec)
{
"name": "learn-sequelize",
"version": "0.0.1",
"description": "시퀄라이즈를 배우자",
"main": "app.js",
"scripts": {
"start": "nodemon app"
},
"author": "yeobi01",
"license": "MIT"
}
$ npm i express morgan nunjucks sequelize sequelize-cli mysql2
$ npm i -D nodemon
$ npx sequelize init
Sequelize CLI [Node: 18.12.1, CLI: 6.5.2, ORM: 6.28.0]
Created "config\config.json"
Successfully created models folder at ...
Successfully created migrations folder at ...
Successfully created seeders folder at ...
const Sequelize = require('sequelize');
const env = process.env.NODE_ENV || 'development';
const config = require('/../config/config.json')[env];
const db = {};
const sequelize = new Sequelize(config.database, config.username, config.password, config);
db.Sequelize = Sequelize;
module.exports = db;
const express = require('express');
const path = require('path');
const morgan = require('morgan');
const nunjucks = require('nunjucks');
const { sequelize } = require('./models');
const app = express();
app.set('port', process.env.PORT || 3001);
app.set('view engine', 'html');
nunjucks.configure('views', {
express: app,
watch: true,
});
sequelize.sync({ force: false})
.then(() => {
console.log('데이터베이스 연결 성공');
})
.catch((err) => {
console.error(err);
});
app.use(morgan('dev'));
app.use(express.static(path.join(__dirname, 'public')));
app.use(express.json());
app.use(express.urlencoded( {extended: false} ));
app.use((req, res, next) => {
const error = new Error(`${req.method} ${req.url} 라우터가 없습니다.`);
error.status = 404;
next(error);
});
app.use((err, req, res, next) => {
res.locals.message = err.message;
res.locals.error = process.env.NODE_ENV != 'production' ? err : {};
res.status(err.status || 500);
res.render('error');
});
app.listen(app.get('port'), () => {
console.log(app.get('port'), '번 포트에서 대기 중');
});
$ npm start
> learn-sequelize@0.0.1 start
> nodemon app
[nodemon] 2.0.20
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node app.js`
3001 번 포트에서 대기 중
Executing (default): SELECT 1+1 AS result
데이터베이스 연결 성공
const Sequelize = require('sequelize');
class User extends Sequelize.Model {
static initiate(sequelize){
User.init({
name: {
type: Sequelize.STRING(20),
allowNull: false,
unique: true,
},
age: {
type: Sequelize.INTEGER.UNSIGNED,
allowNull: false,
},
married: {
type: Sequelize.BOOLEAN, // ture false
allowNull: false,
},
comment: {
type: Sequelize.TEXT,
allowNull: true,
},
created_at: {
type: Sequelize.DATE, // DATETIME, MySQL DATE -> Sequelize DateOnly
allowNull: false,
defaultValue: Sequelize.NOW,
},
}, {
sequelize,
timestamps: false,
underscored: false,
paranoid: false,
modelName: 'User',
tableName: 'users',
charset: 'utf8',
collate: 'utf8_general_ci',
});
}
static associate(db) {
db.User.hasMany(db.Comment, { ForeignKey: 'commenter', sourceKey: 'id'});
}
};
module.exports = User;
MySQL | 시퀄라이즈 |
---|---|
VARCHAR(100) | STRING(100) |
INT | INTEGER |
TINYINT | BOOLEAN |
DATETIME | DATE |
INT UNSIGNED | INTEGER.UNSIGNED |
NOT NULL | allowNull: false |
UNIQUE | unique: true |
DEFAULT now() | defaultValue: Sequelize.NOW |
const Sequelize = require('sequelize');
class Comment extends Sequelize.Model{
static initiate(sequelize){
Comment.init({
comment: {
type: Sequelize.STRING(100),
allowNull: false,
},
created_at: {
type: Sequelize.DATE,
allowNull: true,
defaultValue: Sequelize.NOW,
},
}, {
sequelize,
timestamps: false,
modelName: 'Comment',
tableName: 'comments',
paranoid: false,
charset: 'utfmb4',
collate: 'utf8mb4_general_ci',
});
}
static associate(db) {
db.Comment.belongsTo(db.User, { foreignKey: 'commenter', targetKey: 'id'});
}
};
module.exports = Comment;
const Sequelize = require('sequelize');
const User = require('./user');
const Comment = require('./comment');
const env = process.env.NODE_ENV || 'development';
const config = require('../config/config.json')[env];
const db = {};
const sequelize = new Sequelize(config.database, config.username, config.password, config);
db.sequelize = sequelize;
db.User = User;
db.Comment = Comment;
User.initiate(sequelize);
Comment.initiate(sequelize);
User.associate(db);
Comment.associate(db);
module.exports = db;
// user.js
...
static associate(db) {
db.User.hasMany(db.Comment, { ForeignKey: 'commenter', sourceKey: 'id'});
}
};
// comment.js
...
static associate(db) {
db.Comment.belongsTo(db.User, { foreignKey: 'commenter', targetKey: 'id'});
}
};
db.User.hasOne(db.Info, { foreignKey: 'UserId', sourceKey: 'id' });
db.Info.belongsTo(db.User, { foreignKey: 'UserId', targetKey: 'id' });
db.Post.belongsToMany(db.Hashtag, { through: 'PostHashtag' });
db.Hashtag.belongsToMany(db.Post, { through: 'PostHashtag' });
const { User } = require('../models');
// INSERT INTO nodejs.users (name, age, married, comment) VALUES ('zero', 24, 0, '자기소개1');
User.create({
name: 'zero',
age: 24,
married: false,
comment: '자기소개1',
});
// SELECT * FROM nodejs.users;
User.findAll({});
// SELECT name, married FROM nodejs.users;
User.findAll({
attributes: ['name', 'married'],
});
const { OP } = require('sequelize'); // gt, it, gte, ite, in, ne 사용 가능
const { User } = require('../models');
// SELECT name, age FROM nodejs.users WHERE married = 1 AND age > 30;
User.findAll({
attributes: ['name', 'age'],
where: {
married: true,
age: { [Op.gt]: 30 },
};
});
// SELECT name, age FROM nodejs.users WHERE married = 1 OR age > 30;
const { OP } = require('sequelize');
const { User } = require('../models');
User.findAll({
attributes: ['name', 'age'],
where: {
[Op.or]: [{ married: 0 }, { age: { [Op.gt]: 30} }],
};
});
// SELECT id, name FROM users ORDER BY age DESC;
User.findAll({
attributes: ['id', 'name'],
order: [['age', 'DESC']],
});
// SELECT id, name FROM users ORDER BY age DESC LIMIT 1;
User.findAll({
attributes: ['id', 'name'],
order: [['age', 'DESC']],
limit: 1,
});
// SELECT id, name FROM users ORDER BY age DESC LIMIT 1 OFFSET 1;
User.findAll({
attributes: ['id', 'name'],
order: ['age', 'DESC'],
limit: 1,
offset: 1,
});
// UPDATE nodejs.users SET comment = '바꿀 내용' WHERE id = 2;
User.update({
comment: '바꿀 내용',
}, {
where: { id: 2 },
});
// DELETE FROM nodejs.users WHERE id = 2;
User.destory({
where: { id: 2 },
});
const user = await User.findOne({});
console.log(user.nick); // 사용자 닉네임
const user = await User.findOne({
include: [{
modle: Comment,
}]
});
consolt.log(user.Comments); // 사용자 댓글
db.sequelize.models.PstHashtag
const user = await User.findOne({});
const comments = await user.getComments();
console.log(comments); // 사용자 댓글
// 관계를 설정할 때 as로 등록
db.User.hasMany(db.Comment, { foregin: 'commenter', sourceKey: 'id', as: 'Answers' });
// 쿼리할 때는
const user = await User.findOne({});
const comments = await user.getAnswers();
console.log(comments); // 사용자 댓글
const user = await User.findOne({
include: [{
modle: Comment,
where: {
id: 1,
},
attributes: ['id'],
}]
});
// 또는
const comments = await user.getComments({
where: {
id: 1,
},
attributes: ['id'],
});
const user = await User.findOne({});
const comment = await Comment.create();
await user.addComment(comment);
// 또는
await user.addComment(comment.id);
const user = await User.findOne({});
const comment1 = await Comment.create();
const comment2 = await Comment.create();
await user.addComment([comment1, comment2]);
const [result, metadata] = await sequelize.query('SELECT * FROM comments');
console.log(result);