출처 : REFFECT
express.js(node.js) 경험이 적은 분들은 어떻게 데이터베이스에 연결(접속)하여 데이터를 생성 / 읽기 / 갱신 / 삭제할 것인지 궁금해하실 겁니다. 그런 분들을 대상으로 이 포스팅에선 간단한 예제를 통해 express.js에서 MySQL을 사용하는 SQL 문장을 설명합니다. 이 글을 읽고 나면하면 CRUD(Create, Read, Update Delete) 작업을 이해할 수 있습니다.
- create database
- creata table
- select
- insert
- delete
- update
MAC 환경에서 테스트했으며, 사전에 node.js를 설치하셔야 합니다.
목차
- MySQL 설치
- Express.js 설치
- Express.js의 동작 확인
- Hello World보기
- nodemon 설치
- MySQL과 동작 확인
- MySQL 접속 드라이버 설치
- express.js에서 MySQL에 연결
- MySQL 데이터베이스 생성 및 연결
- 테이블 만들기
- select 문의 동작 확인
- select 문에 의해 데이터 검색
- 테이블 검색 데이터를 브라우저에 표시
- insert 문의 동작 확인
- insert 문을 사용하여 데이터 삽입
- 입력 양식 만들기
- body-parser 설치
- 폼에서 보내져 온 데이터를 insert
- 템플릿 엔진 ejs 설정
- ejs 설치
- ejs의 동작 확인
- delete 문의 동작 확인
- delete 문에 의해 데이터 삭제
- update 문에 의한 동작 확인
- update 의한 업데이트
- 업데이트 폼 만들기
- 정리
MySQL 설치
Homebrew를 이용하여 MySQL 설치합니다. MAC 환경에서 MySQL에 설치하는 방법은 아래의 문서를 참고해 주세요.
Express.js 설치
Express.js를 설치할 디렉토리를 만듭니다. 여기에서는 express_mysql 입니다.
$ mkdir express_mysql
$ cd express_mysql/
Express.js에서 사용하는 패키지를 관리하고자 npm init 명령으로 package.json 파일을 생성합니다. -y 옵션을 추가해 대화 모드를 건너뜁니다.
$ npm init -y
npm을 실행한 디렉토리에 package.json 파일이 생성됩니다.
이어서 express.js 설치합니다.
$ npm install express
Express.js 동작 확인
Hello World 표시
설치 완료되면 Express.js 동작 확인을 위해 브라우저에 Hello World를 표시합니다.
express_mysql 디렉토리에 app.js 파일을 다음 내용으로 생성합니다.
const express = require('express')
const app = express()
const port = 3000
app.get('/', (req, res) => res.send('Hello World!'))
app.listen(port, () => console.log(`Example app listening on port ${port}!`))
node 명령어로 app.js 파일을 실행합니다.
$ node app.js
Example app listening on port 3000!
브라우저를 시작하고 localhost 포트 3000에 접속했을 때, 아래 화면이 나타나면 Express.js가 정상적으로 설치 / 작동하는 것입니다.
Hello World가 브라우저에 표시
nodemon 설치
node app.js 명령을 실행하여 express.js 를 확인했습니다만, node 명령은 파일을 변경할 때마다 다시 실행해야 하는 번거로움이 있습니다. 파일 업데이트를 모니터링하고 자동으로 node 명령을 실행해주는 nodemon 설치합니다.
$ npm install nodemon
설치 완료 후, npx 명령으로 nodemon을 실행합니다. 이제 app.js 파일을 업데이트하면 자동으로 node app.js 명령어가 실행됩니다.
$ npx nodemon app.js
[nodemon] 1.19.2
[nodemon] to restart at any time, enter `rs`
[nodemon] watching dir(s): *.*
[nodemon] starting `node app.js`
Example app listening on port 3000!
MySQL 동작 확인
MySQL 접속 드라이버 설치
express.js에서 MySQL에 연결하려면, MySQL에 접속하기 위한 드라이버를 설치해야 합니다.
여기에서 설치하는 것은 mysql 드라이버입니다.
$ npm install mysql
express.js에서 MySQL 연결
app.js -> MySQL 연결을 확인합니다. app.js 파일의 port 행 아래에 mysql 연결 코드를 추가합니다. MySQL에 접속하기 위해선 host, user, password 정보가 필요합니다.
password가 ''(공백)인 이유는 아직 암호가 미설정 상태이기 때문입니다. 설치한 MySQL의 패스워드를 설정했다면, 암호를 입력하세요
const port = 3000
const mysql = require('mysql');
const con = mysql.createConnection({
host: 'localhost',
user: 'root',
password: ''
});
con.connect(function(err) {
if (err) throw err;
console.log('Connected');
});
소스 코드 추가 후, 파일을 저장하세요. 연결에 성공하면 nodemon 명령을 실행한 터미널에 Connected! 로그가 표시됩니다.
[nodemon] restarting due to changes...
[nodemon] starting `node app.js`
Example app listening on port 3000!
Connected
[comment] console.log 명령 내용이 nodemon 명령을 실행한 터미널에 표시됩니다. [/ comment]
MySQL 데이터베이스 생성 및 연결 (접속)
MySQL 데이터베이스는 접속 후 생성합니다. 데이터베이스 생성 시, CREATE DATABASE 명령어를 사용합니다. Express.js는 SQL 문을 실행하기 위해 query 메소드를 사용합니다.
con.connect(function(err) {
if (err) throw err;
console.log('Connected');
con.query('CREATE DATABASE express_db', function (err, result) {
if (err) throw err;
console.log('database created');
});
});
실행하면 database Created라는 로그가 표시됩니다. 데이터베이스는 하나 밖에 만들 수 없으니, 생성 코드는 삭제하세요.
nodemon이 실행되고 있으므로 database created가 표시된 후에 파일을 업데이트하면 다시 CREATE DATABASE가 실행됩니다. 이미 존재하는 DB를 또 생성하므로, Error :ER_DB_CREATE_EXISTS : Can not create database 'express_db'; database exists (데이터베이스가 존재한다)라는 오류가 발생합니다.
자, 데이터베이스 작성이 완료됐습니다. DB 연결 옵션에 생성된 데이터베이스 정보를 추가하면, createConnection 메소드에서 데이터베이스 접속까지 실행합니다.
const con = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'express_db' //追加
});
테이블 생성
데이터베이스에 연결됐으니 테이블 생성합니다. 테이블 생성 시, CREATE TABLE 명령어를 이용합니다.
작성하는 테이블 이름은 users에 id, name, email 3개이며, id는 auto_increment로 자동으로 1씩 증가되는 정수, primary key 입니다. name, email은 VARCHAR /NOT NULL 입니다.
SQL 문을 보면 다음과 같습니다.
CREATE TABLE users
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL)
CREATE TABLE 문을 추가한 코드는 다음과 같습니다.
con.connect(function(err) {
if (err) throw err;
console.log('Connected');
const sql = '위 SQL 쿼리 복사';
con.query(sql, function (err, result) {
if (err) throw err;
console.log('table created');
});
});
변경 사항을 저장하면 nodemon에 의해 테이블이 만들어집니다.
생성된 테이블은 데이터베이스 관리 GUI TablePlus 등을 통해 확인할 수 있습니다.
TablePlus에서 users 테이블을 확인할 수 있습니다.
TablePlus에서 users 테이블 확인
select문 동작 확인
select문으로 데이터 검색 (조회)
데이터베이스에서 select 문으로 데이터를 조회하기 위해, TAblePlus 이용하여 직접 데이터를 삽입합니다.
TablePlus를 사용하여 데이터 삽입
등록 후, users 테이블엔 데이터가 1개 밖에 없지만, 모든 데이터를 검색하는 select * from users를 실행합니다.
con.connect(function(err) {
if (err) throw err;
console.log('Connected');
const sql = "select * from users"
con.query(sql, function (err, result, fields) {
if (err) throw err;
console.log(result)
});
});
실행하면 방금 등록한 users 데이터를 확인할 수 있습니다.
[ RowDataPacket
{ id: 1,
name: 'John',
email: 'John@example.com' }
]
email만 조회하고 싶다면 다음과 같이 작성합니다.
console.log(result[0].email)
테이블 검색 데이터를 브라우저에 표시
다음은 select 문으로 얻은 데이터를 브라우저에 반환하는 소스 코드입니다.
const con = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'express_db'
});
con.connect(function(err) {
if (err) throw err;
console.log('Connected');
});
app.get('/', (request, response) => {
const sql = "select * from users"
con.query(sql, function (err, result, fields) {
if (err) throw err;
response.send(result)
});
});
브라우저에서 localhost:3000에 접속하면 테이블 데이터가 표시됩니다.
테이블의 데이터가 브라우저에 표시
지금까지 con.connect으로 SQL 데이터베이스에 연결했지만, SQL 실행 시, 데이터베이스 연결이 이루어지므로 con.connect 부분을 삭제해도 위의 코드는 작동합니다.
insert문 동작 확인
insert문으로 데이터 삽입
TablePlus를 사용하여 방금 1건의 사용자 데이터를 등록했습니다. 이번엔 insert 문을 이용하여 데이터를 삽입합니다.
INSERT 문은 다음과 같습니다.
INSERT INTO users(name,email) VALUES('kevin','kevin@test.com')
실행 코드는 아래와 같습니다.
const sql = "INSERT INTO users(name,email) VALUES('kevin','kevin@test.com')"
con.query(sql,function(err, result, fields){
if (err) throw err;
console.log(result)
})
console.log로 result에 들어있는 정보를 확인하면 affectedRow(추가된 행)가 OKPacket 개체에서 리턴되는 것을 알 수 있습니다.
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 2,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
TablePlus에서 insert한 데이터가 추가되었음을 확인할 수 있습니다.
또한 브라우저에서 localhost:3000에 접근하면 사용 가능한 데이터가 2개라는 것도 확인됩니다.
insert 문에서 직접 Values에 값을 넣을 수 있습니다만, ?를 사용해도 됩니다.
const sql = "INSERT INTO users(name,email) VALUES(?,?)"
con.query(sql,['Jack','jack@exsample.co.jp'],function(err, result, fields){
if (err) throw err;
console.log(result)
})
아래와 같이 SET ?를 사용할 수 있습니다.
const sql = "INSERT INTO users SET ?"
con.query(sql,{name:'Tommy',email:'tommy@test.co.jp'},function(err, result, fields){
if (err) throw err;
console.log(result)
});
두 방법 모두 테이블에 데이터가 추가됩니다.
입력 폼 Form 작성
브라우저에서 입력한 데이터를 데이터베이스 테이블에 추가 (insert)하는 방법을 확인합니다.
Form html 파일을 저장할 폴더를 만들고, 그 안에 form.html 파일을 만듭니다. name, email 2개 input 요소를 보여주는 간단한 폼입니다. CSS 등의 스타일 설정은 없습니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>입력 폼</title>
</head>
<body>
<h1>입력 폼</h1>
<form action="/" method="POST">
name <input type="text" name="name"><br>
email <input type="text" name="email"><br>
<button type="submit">보내기</button>
</form>
</body>
</html>
입력 폼이 표시되도록 app.js의 "/(루트)" 라우팅을 변경합니다. sendFile 메소드를 사용하여 html/form.html 파일을 "/(루트)"에 접속한 브라우저에 전달합니다.
app.get('/', (req, res) =>
res.sendFile(path.join(__dirname, 'html/form.html')))
브라우저에서 확인하면 name과 email 입력란이 있는 폼이 표시됩니다.
body-parser 설치
브라우저에서 입력한 name과 email은 POST 요청 결과로 express.js 에 전송됩니다. 전송된 name과 email을 express.js에서 처리하려면 body-parser 모듈이 필요합니다.
$ npm install body-parser
설치가 완료되면 body-parser를 로드합니다.
const path = require('path')
const express = require('express')
const bodyParser = require('body-parser')
const app = express()
const port = 3000
가져온 body-parser를 미들웨어로 설정합니다.
app.use(bodyParser.urlencoded({ extended: true }));
입력 폼에서 POST 요청으로 데이터를 얻을 수 있는지 확인하기 위해 라우팅을 설정합니다. req.body에 받은 데이터가 저장됩니다.
app.get('/', (req, res) =>
res.sendFile(path.join(__dirname, 'html/form.html')))
app.post('/', (req, res) => res.send(req.body)) // 추가
입력폼에 정보를 입력하고 보내기 버튼을 누릅니다.
express.js 에선 POST 요청으로 데이터 받고, res.send에서 req.body를 브라우저로 보내기 때문에 브라우저는 다음과 같이 req.body안의 값을 표시합니다.
폼에서 보내온 데이터 insert
위처럼 데이터 추가 시도를 하고 정상적으로 완료되면, 브라우저 화면에 "등록이 완료되었습니다"라고 표시할 겁니다.
app.post('/', (req, res) => {
const sql = "INSERT INTO users SET ?"
con.query(sql,req.body,function(err, result, fields){
if (err) throw err;
console.log(result);
res.send('등록이 완료 되었습니다');
});
});
실제로 입력 폼에서 보내기 버튼을 누르고, TablePlus 등을 사용해 데이터가 추가되었는지 확인해 보세요.
res.send ('등록이 완료되었습니다') 대신 res.redirect('/')로 설정하면, "/(루트)"로 리디렉션되어 추가된 행이 표시됩니다.
템플릿 엔진 ejs 설정
delete 문이나 update 문을 수행하려면, html 에서 변수를 사용해야 합니다. 여기선 HTML 및 JavaScript를 함께 작성할 수 있는 템플릿 엔진 ejs를 이용합니다.
ejs 설치
npm 명령을 사용하여 ejs 모듈 설치합니다.
$ npm install ejs
ejs 동작 확인
ejs를 이용하여 테이블에서 가져온 사용자 정보를 나열합니다.
ejs 이용하기 위해 require 문을 추가합니다.
const path = require('path')
const express = require('express')
const bodyParser = require('body-parser')
const ejs = require('ejs') //추가
const app = express()
const port = 3000
템플릿 엔진에 ejs를 이용하려면 아래 설정이 필요합니다.
app.set('view engine', 'ejs');
아래와 같이 "/(루트)" 접근이 발생하면 select 문이 실행되고, 실행 결과는 ejs 파일에 전달합니다. 이를 위해 render 함수를 사용하고, index ejs 파일을 이용합니다.
아래에서는 index를 지정하는데, 대상은 views 폴더의 index.ejs 파일입니다. 그래서 views 디렉토리를 만들고 그 안에 index.ejs 파일을 저장합니다. {users:result}에서 users라는 변수에 select 문 실행 결과 result를 저장합니다. index.ejs 파일은 users 변수를 사용하여 select 문 실행 결과를 표시합니다.
app.get('/', (req, res) => {
const sql = "select * from users";
con.query(sql, function (err, result, fields) {
if (err) throw err;
res.render('index',{users : result});
});
});
index.ejs 파일은 다음과 같습니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>User list</title>
</head>
<body>
<table>
<tr>
<th>이름</th>
<th>이메일</th>
<th>수정</th>
<th>삭제</th>
</tr>
<% users.forEach(function (value) { %>
<tr>
<td><%= value.name %></td>
<td><%= value.email %></td>
<td><a href="/edit/<%= value.id %>">수정</a></td>
<td><a href="/delete/<%= value.id %>">삭제</a></td>
</tr>
<% }); %>
</table>
</body>
</html>
여기에선 템플릿 엔진 ejs 구문을 설명하지 않습니다. users.forEach를 사용해 users에 저장된 사용자 정보를 loop로 꺼냅니다.
브라우저에서 "/(루트)"에 접속하면, 지금까지 테이블에 추가한 사용자 정보 목록이 표시됩니다.
사용자 목록
delete 문 동작 확인
delete 문 사용하여 사용자 목록에서 일부 정보를 삭제합니다.
delete 문으로 데이터 삭제
DELETE 문은 다음과 같습니다. ? 에는 사용자 데이터의 id를 기입합니다.
const sql = "DELETE FROM users WHERE id = ?";
실행 코드는 아래와 같습니다. 라우팅 /delete/:id를 추가합니다. :id에 들어오는 값은 브라우저에서 보낸 req.params.id에서 얻을 수 있습니다. WHERE 절의 id 값에 req.params.id를 설정하면, 그 id를 가진 행이 삭제됩니다.
app.get('/delete/:id',(req,res)=>{
const sql = "DELETE FROM users WHERE id = ?";
con.query(sql,[req.params.id],function(err,result,fields){
if (err) throw err;
console.log(result)
res.redirect('/');
})
});
index.ejs의 삭제 부분 코드를 확인합시다. /delete/ 뒤에서 value.id 행의 id를 꺼냅니다.
<td><a href="/delete/<%= value.id %>">삭제</a></td>
삭제 링크에는 /delete/id(행 id 설정)에 링크가 붙어 있습니다.
위에서 6번째 Ted 행에 있는 삭제 링크를 클릭하여 삭제되고, "/(루트)"로 리디렉션됩니다., Ted 행이 삭제된 사용자 목록 결과가 표시됩니다.
update 문 동작 확인
update 문을 사용하여 사용자 정보 업데이트 방법을 확인합니다.
update 사용하여 갱신(업데이트)
실행할 UPDATE 문은 다음과 같습니다. SET ? 엔 브라우저에서 전송된 POST 요청 내용을 설정합니다.
const sql = "UPDATE users SET ? WHERE id = " + req.params.id;
실행 코드는 아래와 같습니다. 라우팅에 /update/:id를 추가합니다. delete와 마찬가지로 :id에 들어가는 값은 req.params.id에서 얻습니다. WHERE 절에서 id 값에 req.params.id를 설정하고, 해당 id의 행을 업데이트합니다. 업데이트 내용은 insert 문에서 사용한 것처럼 req.body을 사용합니다.
app.post('/update/:id',(req,res)=>{
const sql = "UPDATE users SET ? WHERE id = " + req.params.id;
con.query(sql,req.body,function (err, result, fields) {
if (err) throw err;
console.log(result);
res.redirect('/');
});
});
갱신하기 위한 라우팅을 추가했습니다만, 현재 값을 업데이트하기 위해선 새로운 값을 입력할 업데이트 폼이 필요합니다.
업데이트 폼 Form 작성
데이터를 업데이트하려면 업데이트 처리를 위한 라우팅, 데이터 입력폼이 필요합니다. 따라서 업데이트 화면을 표시하는 라우팅을 추가합니다.
업데이트 화면에 라우팅 /edit/:id를 추가합니다. select 문 및 id를 이용하여 업데이트할 행의 현재값을 가져옵니다. 브라우저에 표시할 폼은 취득한 값을 전달할 ejs 파일을 사용하여 만듭니다. ejs 파일에 변수 user를 사용하여 얻은 값을 전달합니다.
app.get('/edit/:id',(req,res)=>{
const sql = "SELECT * FROM users WHERE id = ?";
con.query(sql,[req.params.id],function (err, result, fields) {
if (err) throw err;
res.render('edit',{user : result});
});
});
views 폴더에 edit.ejs 파일을 추가합니다. edit.ejs에선 select 문에서 취득한 데이터를 갖는 변수 user 를 이용합니다. input 요소의 value에 name, email의 현재 값을 확인하고, user[0].name, user[0].email을 각각 설정합니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>User Edit</title>
</head>
<body>
<h1>업데이트 폼</h1>
<form action="/update/<%= user[0].id %>" method="POST">
name <input type="text" name="name" value="<%= user[0].name %>"><br>
email <input type="text" name="email" value="<%= user[0].email %>"><br>
<button type="submit">업데이트</button>
</form>
</body>
</html>
업데이트 화면 URL은 index.ejs 의 갱신 a태그 링크입니다.
<td><a href="/edit/<%= value.id %>">업데이트</a></td>
사용자 목록에서 원하는 이름의 오른쪽에 있는 갱신 링크를 클릭합니다.
업데이트 폼 화면이 표시되고, 입력폼에는 현재 값이 설정되어 있습니다.
- name을 Johnson으로 변경하고 갱신 버튼을 클릭합니다.
- 행의 이름이 Johnson으로 변경되며, 업데이트가 완료됩니다.
- 갱신 프로세스가 완료됩니다.
정리
express.js에서 MySQL 데이터베이스에 연결하는 것을 포함해, 데이터베이스 생성, 테이블 생성, 데이터 추가 / 갱신 / 삭제 등 기본 조작을 이해하셨다고 생각합니다. 이제는 실용적인 응용 프로그램 완성에 도전해 보세요.
이번 작성한 app.js 파일은 다음과 같습니다.
const path = require('path')
const express = require('express')
const bodyParser = require('body-parser')
const ejs = require('ejs')
const app = express()
const port = 3000
app.use(bodyParser.urlencoded({ extended: true }));
app.set('view engine', 'ejs');
const mysql = require('mysql');
const con = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'express_db'
});
// con.connect(function(err) {
// if (err) throw err;
// console.log('Connected');
// });
app.get('/', (req, res) => {
const sql = "select * from users";
con.query(sql, function (err, result, fields) {
if (err) throw err;
res.render('index',{users : result});
});
});
app.post('/', (req, res) => {
const sql = "INSERT INTO users SET ?"
con.query(sql,req.body,function(err, result, fields){
if (err) throw err;
console.log(result);
res.redirect('/');
});
});
app.get('/create', (req, res) =>
res.sendFile(path.join(__dirname, 'html/form.html')))
app.get('/edit/:id',(req,res)=>{
const sql = "SELECT * FROM users WHERE id = ?";
con.query(sql,[req.params.id],function (err, result, fields) {
if (err) throw err;
res.render('edit',{user : result});
});
});
app.post('/update/:id',(req,res)=>{
const sql = "UPDATE users SET ? WHERE id = " + req.params.id;
con.query(sql,req.body,function (err, result, fields) {
if (err) throw err;
console.log(result);
res.redirect('/');
});
});
app.get('/delete/:id',(req,res)=>{
const sql = "DELETE FROM users WHERE id = ?";
con.query(sql,[req.params.id],function(err,result,fields){
if (err) throw err;
console.log(result)
res.redirect('/');
})
});
app.listen(port, () => console.log(`Example app listening on port ${port}!`));
입력 폼을 표시하는 라우팅을 "/(루트)"에서 /create로 변경했습니다.
사용자 정보 목록 index.ejs 는 아래와 같습니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>User list</title>
</head>
<body>
<p><a href="/create">사용자 추가</a></p>
<table>
<tr>
<th>이름</th>
<th>email</th>
<th>수정</th>
<th>삭제</th>
</tr>
<% users.forEach(function (value) { %>
<tr>
<td><%= value.name %></td>
<td><%= value.email %></td>
<td><a href="/edit/<%= value.id %>">수정</a></td>
<td><a href="/delete/<%= value.id %>">삭제</a></td>
</tr>
<% }); %>
</table>
</body>
</html>
index.ejs 상단에는 입력폼 링크를 추가했습니다.
입력 폼 form.html은 아래와 같습니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>입력 폼</title>
</head>
<body>
<h1>입력 폼</h1>
<form action="/" method="POST">
name <input type="text" name="name"><br>
email <input type="text" name="email"><br>
<button type="submit">추가</button>
</form>
</body>
</html>
갱신(업데이트) 폼 edit.ejs 파일은 다음과 같습니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>User Edit</title>
</head>
<body>
<h1>업데이트 폼</h1>
<form action="/update/<%= user[0].id %>" method="POST">
name <input type="text" name="name" value="<%= user[0].name %>"><br>
email <input type="text" name="email" value="<%= user[0].email %>"><br>
<button type="submit">업데이트</button>
</form>
</body>
</html>
전체적으로 제작해 보시길 권합니다.
댓글