node js: Database(MySql) & Routing Modules

Database 연동 기본 (MySQL)

npm install mysql --save

mysql 기본 정보 등록 (connection 정보)

mysql 외 db 연동 방법은 express doc 에 모두 있음

const mysql = require('mysql')
const connection = mysql.createConnection({
	host: 'localhost',
	port: 3006,
	user: 'root',
	password: 'password',
	database: 'db name'
})

connection.connect()

db에 query 보내기

app.post('url', function(request, response) {
	const email = request.body.email;
	const responseData = {};
	
	const query = connection.query('select name from user where email="'+email +'"', function(err, rows){
		if (err) throw err;
		if (rows[0]) {
			responseData.result = "ok";
			responseData.name = rows[0].name;
		} else {
			responseData.result = "none";
			responseData.name = "";
		}
		response.json(responseData)
	})
})

Routing 모듈화

dir router을 만들어 각 router.js 파일을 만듬

// router/main.js
const express = require('express')
const app = express()
const router = express.Router();
// path는 상대 경로. 현재 dir보다 상위 dir에서 찾아야할 때 사용
const path = require('path')

router.get('url', function(req, res) {
	res.sendFile = (path.join(__dirname, '../url'))
})

module.exports = router;

const main = require('./router/main')

app.use('/main', main)

POST router

// router/email.js
const express = require('express')
const app = express()
const router = express.Router();
const path = require('path')

//DATABASE SETTING
const mysql = require('mysql')
const connection = mysql.createConnection({
	host: 'localhost',
	port: 3006,
	user: 'root',
	password: 'password',
	database: 'db name'
})

connection.connect()

//ROUTER 처리
router.post('url', function(request, response) {
	const email = request.body.email;
	const responseData = {};
	
	const query = connection.query('select name from user where email="'+email +'"', function(err, rows){
		if (err) throw err;
		if (rows[0]) {
			responseData.result = "ok";
			responseData.name = rows[0].name;
		} else {
			responseData.result = "none";
			responseData.name = "";
		}
		response.json(responseData)
	})
})

module.exports = router;

// app.js
const email = require('./router/email')

app.use('/email', email)

만약 '/email/form' 라는 url이 있다면 app.js에서 email을 찾고 router/email.js에서 /form을 찾음

Routing 리팩토링

Routing == controller

router을 모두 모아서 통합 관리하는 middleware 만들기

router/index.js 만들기

// router/index.js
const express = require('express')
const app = express()
const router = express.Router()
const path = require('path')
const main = require('./main')
const email = require('./email')

// Root
router.get('url', function(req, res) {
	res.sendFile = (path.join(__dirname, '../public/main.html'))
})

router.use('/main', main)
router.use('/email', email)

module.exports = router;

// app.js
const router = require(./router/index)

app.use(router)

더 세부적으로 나눌 수도 있다

index.js 에 여러 router dir을 등록하고, dir 안에 router.js를 등록

ex) index/main/email 이라면 router/index/main/email.js 라는 경로로 찾아가게 하는 것

DB에 데이터 추가

DB에 DATA INSERT

// router/index.js
const join = require('./join/index')

router.use('/join', join)

// router/join/index.js
const express = require('express')
const app = express()
const router = express.Router();
const path = require('path')

//DATABASE SETTING
const mysql = require('mysql')
const connection = mysql.createConnection({
	host: 'localhost',
	port: 3006,
	user: 'root',
	password: 'password',
	database: 'db name'
})

connection.connect()

//ROUTER 처리
router.get('/', function(req, res){
    res.sendFile(path.join(__dirname, '../../public.join.html'))
})

router.post('/', function(req, res){
    const body = req.body;
    const email = body.email;
    const name = body.name;
    const pwd = body.password;
    
    //const query = connection.query('insert into user {email, name, pw} values("'+email +'", "'+name+'","'+pwd'")'), function(err) { if (err) {throw err}}
    const sql = {email: email, name: name, pwd: pwd};
    const query = connection.query('insert into user set ?', sql, function(err){if (err) throw err
        else res.render('welcome.ejs', {'name': name, 'id': rows.insertId})
    })
})

module.exports = router;

public/join.html
<form action="/join" method="post">
    <input type="text" name="email">
    <input type="text" name="name">
    <input type="text" name="password">
    <input type="submit" name="submit">
</form>

출처: https://www.inflearn.com/course/node-js-%EC%9B%B9%EA%B0%9C%EB%B0%9C
위의 무료 강의를 보고 정리한 내용입니다.