Node Express에 sequelize를 이용해 mysql접속하기

들어가기

Express 프로젝트에 sequelize(시퀄라이저)를 이용하여 로컬에 있는 mysql에 접속하는 예제를 정리해본다.

미리 로컬에 mysql을 설치하고, 스키마와 계정을 생성 해놨다.

테스트용 Express 프로젝트 생성하기

1
express backend --view=pug

위 명령어로 express 프로젝트를 생성한다.

시퀄라이저(sequelize)란

promise기반 Node.js환경의 ORM(Object-relational Mapping)이다.

이것을 통해 Exrpess와 MariaDB, PostgreSQL, SQLite, MSSQL등과 연동이 가능하다.

내가 알고 있던 Mybatis와 같은 ORM과는 약간 DB에 대한 접근방식이 다르다.

Mybatis같은 경우 sql 쿼리를 xml로 관리하고, 이 파일을 이용하여 DB의 entity와 자바의 객체를 맵핑하는 방법으로 DB에 접근한다.

Express서버의 시퀄라이저 설정으로 DB의 entity와 entity간의 관계를 javascript로 선언하고, Express에서 시퀄라이저 문법을 통해서 DB에 접근한다.

Mybatis에 익숙한 나에게는 굉장히 낮선 방식이다.

시퀄라이저의 특징

1.DB Entity정의를 시퀄라이저의 model(js파일)로 정의할수 있다. 2.DB의 Entity의 관계역시 시퀄라이저에서 정의할수 있다. 3.Express에서 시퀄라이저의 Model을 이용하여 DB의 entity에 접근 하여 CRUD등을 할 수 있다. 4.Express 서버에서 시퀄라이저에 선언된 Entity정의와 그 관계정의(테이블간 fk등...)을 이용하여 서버에 테이블과 그 관계를 생성할수 있다.

여기서 애매한 것이 4번인데, 예를들어 Express서버를 실행할때 시퀄라이저에 선언된 DB의 model정보를 통해 DB에 테이블을 생성하고 그 Model간의 관계까지 생성할수 있다. 즉 개발자가 DB에 접근하여 DDL을 직접 날려 테이블을 생성하는 것이 아니라, 서버에 DB설계를 시퀄라이저를 통해 하고 서버가 start할때마다 DB에 그 내용을 반영 할수 있는 것이다.

이것은 어떻게 보면 굉장히 편리한 방법일수도 있지만, 특히 개인프로젝트인 경우에는 더더욱... 하지만 팀으로 개발을 하거나, DB설계를 ERD를 이용하여 하는 경우 필요 없는 기능이라 생각된다.

Express서버에 시퀄라이저(sequelize) 연동하기

Express 프로젝트 폴더인 backend폴더에 cmd로 이동하여 아래 명령어로 시퀄라이저를 설치하고 시퀄라이저 초기화 셋팅을 하자.

1
2
3
npm i sequelize mysql2
npm i -g sequelize-cli
sequelize init

현재 나는 Express프로젝트인 backend폴더에서 위 명령어로 필요한 모듈을 설치하고 마지막 sequelize init명령어를 통해서 express 서버에 sequelize환경을 셋팅한다.

sequelize init명령어를 실행하면 프로젝트 폴더에서 config, models, migrations, seeders 폴더라 생성된다.

sequelize init 실행결과로 생성된 폴더

여기에서 시퀄라이저의 기본적인 동작 과정을 집고 넘어가자.

Express와 sequelize연동시 동작과정

크게 1번과, 2번 루트로 나눌수 있다.

1번일경우 DB의 스키마 엔티티관리를 고전적인? 방법으로 DB에서 관리 할때

2번일 경우 DB의 스키마 엔티티관리를 Express서버에서 models폴더에 js파일로 선언한 엔티티(테이블)와 그 관계들(테이블간 1:1, 1:n, n:M관계)를 읽어서 DB 스키마에 적용한다.

2번일 경우 express서버가 시작될때 추가 옵션을 통해서 DB 스키마를 깨끗이 지우고(테이블과 그 관계등을), 시퀄라이저에서 js파일로 선언된 엔티티로 테이블과 그 관계를 성성할수 있다.

즉 DB 스키마 관리를 express내부에서 자바스크립트로 관리 할수 있다는 장점이 있다.

그러나, 난 꼰대라서 DB는 분리해서 entity를 관리하는것아 편하다.

ERD로 DB를 설계하는 것이 편하기 때문이다.

1번 방법 시퀄라이저 연동(시퀄라이저에서 DB Entity를 관리하는 방식)

config/config.json 파일은 시퀄라이저가 DB에 접근하는 정보를 저장한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
"development": {
"username": "hanumoka",
"password": "password",
"database": "band_of_coder",
"host": "127.0.0.1",
"dialect": "mysql",
"operatorsAliases": false
},
"test": {
"username": "root",
"password": null,
"database": "database_test",
"host": "127.0.0.1",
"dialect": "mysql"
},
"production": {
"username": "root",
"password": null,
"database": "database_production",
"host": "127.0.0.1",
"dialect": "mysql"
}
}

나는 위처럼 development에 로컬 mysql 정보를 입력했다. 이 파일은 접근할 DB 접건정보 뿐만 아니라 define이라는 시퀄라이저 부가옵션을 추가적으로 줄 수도 있다.

models/index.js 파일을 아래처럼 수정하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
const path = require('path');
const Sequelize = require('sequelize');

const env = process.env.NODE_ENV || 'development';
const config = require(path.join(__dirname, '..', 'config', 'config.json'))[env];
const db = {};

const sequelize = new Sequelize(config.database, config.username, config.password, config);

db.sequelize = sequelize;
db.Sequelize = Sequelize;

//모델정보를 읽어온다.
db.User = require('./user')(sequelize, Sequelize);
db.Comment = require('./comment')(sequelize, Sequelize);

//모델간의 관계를 정의한다.
db.User.hasMany(db.Comment, { foreignKey: 'commenter', sourceKey: 'id' });
db.Comment.belongsTo(db.User, { foreignKey: 'commenter', targetKey: 'id' });

module.exports = db;

위 모델관계는 user : comment = 1:N 관계정의이다.

1:1, 1:n, n:m관계등을 정의 가능하며, 이렇게 정의한 것들을 express서버가 동작할때 DB에 DDL로 적용할수 있다.

자세한 내용은 시퀄라이저 공식문서를 참고하자.

models/user.js 파일은 아래와 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
module.exports = (sequelize, DataTypes) => {
return sequelize.define('user', {
name: {
type: DataTypes.STRING(20),
allowNull: false,
unique: true,
},
age: {
type: DataTypes.INTEGER.UNSIGNED,
allowNull: false,
},
married: {
type: DataTypes.BOOLEAN,
allowNull: false,
},
comment: {
type: DataTypes.TEXT,
allowNull: true,
},
created_at: {
type: DataTypes.DATE,
allowNull: false,
defaultValue: sequelize.literal('now()'),
},
}, {
timestamps: false,
});
};

models/comment.js 파일은 아래와 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
module.exports = (sequelize, DataTypes) => {
return sequelize.define('comment', {
comment: {
type: DataTypes.STRING(100),
allowNull: false,
},
created_at: {
type: DataTypes.DATE,
allowNull: true,
defaultValue: sequelize.literal('now()'),
},
}, {
timestamps: false,
});
};

models/user.js와 models/comment.js는 DB의 엔티티 즉, 테이블을 정의 하는 것이다.

데이터 타입, key, not null등 다양한 기능들을 시퀄라이저 문법을 통해 정의 해야 한다. ORM을 위해 시퀄라이저에서 엔티티를 정의하는 문법을 새로 배워야 한다는 것은, 좀 문제가 있다고 본다.

이것은 2번 방법에서 좀더 나은방법으로 해결해보자.

시퀄라이저의 자세한 문법은 구글에서 공식페이지에서 확인해보길 바란다.

Express프로젝트의 app.js폴더로 이동해서 시퀄라이저를 등록하자.

아래 내용을 app.js에 추가하자.

1
2
var sequelize = require('./models').sequelize;   // mysql 시퀄라이저 모델
sequelize.sync(); //서버가 실행될때 시퀄라이저의 스키마를 DB에 적용시킨다.

app.js에 시퀄라이저 적용

위 두줄의 명령으로인해, express 프로젝트가 start될때 시퀄라이저 models/index.js와 models/user.js, models/comment.js를 이용하여 DB 스키마에 테이블과 그 관계를 DDL로 생선한다.

그럼 한번 express start를 시작해보자.

깨끗한 DB상황

위를 보면 현재 테이블이 하나도 없는 것이 보인다.

1
DEBUG=backend:* npm start

위 명령어를 실행하자.

express 실행결과

서버가 실행되면서 Create table등의 로그가 보일것이다.

sequelize.sync();가 동작하면서 앞서 시퀄라이저로 정의한 것들이 DB에 테이블따위로 생성되는 것이다.

다시 DB를 확인해보자.

생성된 테이블 확인

위처럼 models폴더에 js파일로 선언한 시퀄라이저 엔티티가 DB에 테이블로 생성된 것을 확인 할 수 있다.

주의할점은 시퀄라이저가 생성한 table명을 보면 뒤에 s가 붙어있다. 즉 model명의 복수형태로 테이블을 만든다.

일단 테스트를 위해 user테이블에 더미데이터를 하나 추가하고 pug에서 조회해 보자.

1
2
insert into users
values('1', 'hanumoka', 32, false, '코딩은 재밌어', now());

/routes/userManager.js 파일을 생성하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
var express = require('express');

var User = require('../../models').User;
var router = express.Router();

router.get('/', async(req, res, next) =>{
try{
const users = await User.findAll();
res.render('userManager', {users});
} catch(error) {
console.error(error);
next(error);
}

});


module.exports = router;

require로 models에 접근해 User라는 모델을 가져온다. User의 첫걸자가 대문자인것을 주의하자. 시퀄라이저는 promise, async 방식 두가지 방법을 지원하며 위 예제는 ansyc방식의 전체 조회예제이다.

아래는 userManager.pug 파일이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
doctype html
html
head
meta(charset='utf-8')
title 시퀄라이즈 서버
style.
table {
border: 1px solid black;
border-collapse: collapse;
}

table th, table td {
border: 1px solid black;
}
body
table#user-list
thead
tr
th ID
th 이름
tbody
for user in users
tr
td= user.id
td= user.name
br

express의 app.js에 라우터 routes/userManager.js를 등록하자.

1
2
var userManagerRouter = require('./routes/backend_pug/userManager');
app.use('/usermanager', userManagerRouter);

그리고 서버를 재시작후 url로 접근하면 다음과 같이 조회되는것을 확인 할수 있다.

생성된 테이블 확인

지금까지 간단하게 Express와 시퀄라이저를 연동해서 mysql에 테이블을 조회하는 예제를 진행해 보았다.

하지만 서버에서 엔티티를 관리하고 생성하는 방법은 개인적으로 불편해서 2번방법을 생각해 보았다.

2번 방법 진화된 시퀄라이저 연동(DB는 DB에서 따로 관리하는 방식)

1번 방법처럼 Expess에서 시퀄라이저를 이용하여 엔티티를 생성하고 그 관계를 만들어서 프로젝트 개발과 DB를 통합으로 개발 관리하는것은 일견 유용해보인다.

하지만 보편적으로 팀으로 프로젝트를 개발하고, DB는 DBA나 별도 관리자가 따로 관리할 것이다.

개인적으로 가장큰 문재는 ERD로 DB를 설계하는 것이 편한데, 1번 방법일 경우 ERD를 개발에 쓰기가 어렵다는 것이다.

그리고 시퀄라이저에서 엔티티를 생성하기 위한 모델정의 문법을 따로 배워야 한다는 것은.... 뭔가 영양가가 없어보이는 공부라고 생각되었다.

2번 방법으로 연동한 express sequelize연동 환경에서 개발프로세스는 다음과 같다.

  1. 1번 방법에서는 express서버가 실행될 때 sequelize모델을 DB에 생성했지만, 이것을 제거한다. 즉 DB스키마는 Express에서 건드리지 않는다.
  2. 1번 방법에서는 models폴더에 각각 테이블이 될 model들을 js파일로 생성했지만, 거꾸로 DB에서 그정보를 읽어 자동으로 js파일을 생성한다.(sequelize-auto)
  3. workbench를 이용하여 ERD를 생성하고 DB스키마는 ERD로 생성관리한다.
  4. 테이블명과 필드명은 underscore를 사용한다.

하나하나 실습으로 해보자.

workbench로 ERD로 DB 스키마 정의

일단 기존 테이블을 다 drop 하고 다음처럼 ERD를 간단하게 만들었다.

예제 ERD

workbench로 ERD를 만드는 방법은 구글링을 하길 바란다.

database-forward to Database를 이용해서 ERD를 이용해 테이블을 생성했다.

ERD로 생성된 테이블

Express가 시작될 때 table생성 못하게 설정

간단하다.

Express의 app.js에 위에서 설정한 시퀄라이저 부분을 다 제거하자.

1
2
//var sequelize = require('./models').sequelize;   // mysql 시퀄라이저 모델
//sequelize.sync(); //서버가 실행될때 시퀄라이저의 스키마를 DB에 적용시킨다.

난 위처럼 둘다 주석처리 했다. 이제 Express가 시작될 때 DB 스키마를 건드리는 걱정을 안해도 된다.

DB 테이블을 자동으로 models폴더에 js파일로 생성하기.

sequelize-auto라는 라이브러리를 사용하면 된다. https://github.com/sequelize/sequelize-auto

express 프로젝트에 sequelize-auto를 설치하자.

1
2
npm install -g sequelize-auto
npm install -g mysql

그리도 다음과 같은 형태의 명령으로 현재 mysql 로컬 DB의 테이블을 이용하여 시퀄라이저 models폴더에 js들을 자동으로 생성할수 있다.

1
sequelize-auto -o "./models" -d sequelize_auto_test -h localhost -u my_username -p 5432 -x my_password -e postgres

내 로컬의 경우 다음과 같다.

1
sequelize-auto -o "./models" -d band_of_coder -h localhost -u hanumoka -p 3306 -x password -e mysql

sequelize-auto 실형결과1

models폴더에 index.js파일 하나뿐이 없는 상태에서 위 명령어를 실행 했다. 로그에서 무언가 쿼리같은것이 실행되는 것을 확인 할 수 있다.

sequelize-auto 실형결과2

위처럼 자동으로 user.js, user_post.js 파일이 자동으로 models폴더 내부에 생성된 것을 확인 할수 있다.

DB스키마 관리는 그냥 기존의 SQL, workbench, erd등으로 관리하고 sequelize-auto라이브러리를 이용하여 시퀄라이저의 문법을 공부할 필요가 없어진다.

sequelize models/index.js 업그레이드

models/index.js 파일은 시퀄라이저 핵심 엔트리 파일이다.

자동으로 models폴더를 읽어서 js파일을 적재할수 있게 수정하자.

수정된 models/index.js 파일은 다음과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
var fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');

const env = process.env.NODE_ENV || 'development';
const config = require(path.join(__dirname, '..', 'config', 'config.json'))[env];
const db = {};

const sequelize = new Sequelize(config.database, config.username, config.password, config);

fs
.readdirSync(__dirname)
.filter(function(file) {
return (file.indexOf(".") !== 0) && (file !== "index.js");
})
.forEach(function(file) {
var model = sequelize.import(path.join(__dirname, file));
db[model.name] = model;
console.log('model.name:' + model.name); // 테스트로그 model명..
});

Object.keys(db).forEach(function(modelName) {
if ("associate" in db[modelName]) {
db[modelName].associate(db);
}
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

시퀄라이저 config/config.json 수정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
"development": {
"username": "hanumoka",
"password": "password",
"database": "band_of_coder",
"host": "127.0.0.1",
"dialect": "mysql",
"operatorsAliases": false,
"define": {
"timestamps": false,
"underscored" : true
}
},
"test": {
"username": "root",
"password": null,
"database": "database_test",
"host": "127.0.0.1",
"dialect": "mysql"
},
"production": {
"username": "root",
"password": null,
"database": "database_production",
"host": "127.0.0.1",
"dialect": "mysql"
}
}

define을 추가했다. timestamps가 true이면 시퀄라이저가 생성하는 DDL, DML에 createAt, updateAt, deleteAt 컬럼들을 자동으로 생성한다. 난 필요가 없어 제거했다.

그리고 테이블명과 컬럼명을 스네이크케이스로 선언하므로 underscored를 true로 선언했다. 사실 현재는 필요없는 설정이다. 이 설정은 시퀄라이저가 DB에 DDL을 날릴때 적용되는 옵션같다. DML에서는 필요없는듯...

/routes/userManager.js 파일을 다음과 같이 조금 수정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
var express = require('express');
var userModel = require('../../models').user;
//var User = require('../../models').User;
var router = express.Router();


router.get('/', async(req, res, next) =>{
try{
const users = await userModel.findAll();
res.render('userManager', {users});
} catch(error) {
console.error(error);
next(error);
}

});


module.exports = router;

model require부분만 조금 수정되었다. 이번에는 user가 모두 소문자인것을 주의하자.

userManager.pug 아주 조금 수정 id대신 email로 변경

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
doctype html
html
head
meta(charset='utf-8')
title 시퀄라이즈 서버
style.
table {
border: 1px solid black;
border-collapse: collapse;
}

table th, table td {
border: 1px solid black;
}
body
table#user-list
thead
tr
th email
th 이름
tbody
for user in users
tr
td= user.email
td= user.name
br

서버를 실행하고 그 결과를 확인해보자.

2번방법 시퀄라이저연동 최종결과

데이터가 없어서 조회결과는 없지만, 정상동작하는 것을 확인 할 수 있다.

로그에서도 model.name이 잘 나온다.

그리고 조회 했을때 select 쿼리도 잘 찍히는 것을 확인 할 수 있다.

마무리

대충 시퀄라이저를 내 입맛에 맞게 수정했다. 아직 남아있는 것은 2번 시퀄라이저 연동환경에서 테이블관의 관계, 즉 엔티티의 관계도 자동으로 설정되었으면 좋겠는데... 이건 아직 테스트를 안해보았다.