(node.js) [Part.6] 데이터 베이스 사용하기 - MySQL 데이터베이스 사용하기

기존 데이터베이스로는 오라클이나 MySQL 같은 관계형 데이터베이스가 많은데 노드로 프로그램을 만들 때 이런 관계형 데이터베이스를 연결할 수 있어야한다.

노드에는 이런 종류의 기존 데이터베이스를 사용할 수 있는 외부 모듈이 있다. 여기에서는 MySQL 데이터베이스에 연결하여 데이터를 저장하거나 조회하는 방법을 알아보자.

 

관계형 데이터베이스 간단하게 이해하기

관계형 데이터베이스는 데이터베이스 저장소 안에 테이블(Table)을 만들어서 데이터를 저장한다.

테이블을 이해할 때는 엑셀 프로그램의 시트를 생각하면 쉽다. 즉, 엑셀의 시트 탭에 어떤 이름의 데이터가 어떤 타입으로 들어갈지 정의한 후 데이터를 한 줄씩 입력하는 과정과 비슷하기 때문이다.

한 줄에 해당하는 데이터를 레코드(Record) 라고 부르며, 이 레코드가 들어 있는 테이블은 검색할 수 있다.

 

다음은 관계형 데이터베이스의 기본 구조이다.

 

관계형 데이터베이스의 기본 구조

 

관계형 데이터베이스를 다루는 일은 크게 네 단계로 나뉜다.

 

첫 번째 단계에서는 데이터베이스에 연결하고 두 번째 단계에서는 테이블을 만든다.

테이블을 만들 때는 각 칼럼의 이름과 함께 어떤 타입의 값이 들어갈 수 있는지 정의한다.

세 번째 단계에서는 레코드를 추가하고 네 번째 단계에서는 데이터를 조회한다.

 

첫 번째 단계를 제외한 나머지 세 단계는 모두 SQL 문을 사용한다.

SQL 문의 형태만 다시 한 번 정리하면 어렵지 않게 사용할 수 있다.

다음은 MySQL 사용방법을 단계별로 나눈 후 간단한 예를 들어보자.

 

단계	설명
1단계 : 데이터베이스 연결	어떤 데이터베이스에 연결할지 지정하는 정보와 보안 정보를 입력한다. ex) connectionLimit : 10 , host : 'localhost' , user : 'root' , password : '000000' , database : 'users' , debug : false
2단계 : 테이블 생성	테이블을 만들어 어떤 칼럼에 어떤 타입의 데이터가 들어가는지 정의한다. ex) CREATE TABLE users(id text, name text, age int, password text)
3단계 : 레코드 추가	레코드를 한 줄 추가한다. ex) INSET INTO users(id, name, age, password) VALUES ('101, '김진수', 20, '123456')
4단계 : 데이터 조회	원하는 조건을 넣어 데이터를 조회한다. ex) SELECT id, name, age, password FROM users WHERE age>10

 

MySQL 설치하기

 

해당 파트는 생략.

 

화면이 있는 관리 도구 HeidiSQL 설치하기

 

해당 파트도 생략.

 

MySQL 을 사용하는 사용자 추가 기능 만들기

 

다음단계인 데이터 추가와 데이터 조회는 익스프레스로 만든 웹 서버에서 진행한다.

노드에서 MySQL 데이터베이스에 연결하려면, mysql 모듈을 사용하면 된다.

먼저 명령 프롬프트에서 이 모듈을 설치한다.

 

%npm install mysql --save

 

몽고디비에 연결할 떄 사용한 app.js 파일을 복사해서 app8.js 파일로 만든 후 코드를 일부 수정한다.

몽고디비를 연결할 때 추가하는 코드는 모두 삭제한다. connectDB 함수 부분은 물론 http.createServer 메소드 안에 들어있던 connectDB 함수 호출 부분도 모두 삭제한다.

코드 윗 부분에 mysql 모듈은 불러오는 코드를 추가한 후 그 아래에 MySQL 데이터베이스에 연결하는 코드를 입력한다.

 

// MySQL 데이터베이스를 사용할 수 있는 mysql 모듈 알아보기
var mysql = require('mysql');
 
// MySQL 데이터베이스 연결 설정
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : '비밀번호~',
    database : 'test',
    debug : false
});
 

속성	설명
connectionLimit	커넥션 풀에서 만들 수 있는 최대 연결 개수를 설정한다.
host	연결할 호스트 이름을 설정한다. 내 컴퓨터인 경우, localhost 또는 127.0.0.1 을 입력할 수 있다.
port	데이터베이스가 사용하는 포트번호를 설정한다. Mysql 의 디폴트 포트는 3306 이다.
user	데이터베이스 사용자 아이디를 설정한다. MySQL 에서 루트 권한을 가진 디폴트 사용자 아이디는 root 이다.
password	데이터베이스 사용자의 비밀번호를 입력한다.
database	데이터베이스 이름을 설정한다.
debug	데이터베이스 처리 과정을 로그로 남길 것인지 설정한다.

 

createPool 메소드를 호출하면 데이터베이스에 연결해서 커넥션 풀을 만들게 된다. 따라서 그 다음부터는 pool 객체에서 연결 객체를 가져와 사용할 수 있다.

 

MySQL 데이터베이스에 연결하는 과정이 끝났으니 이제 사용자 추가 기능을 넣어보자. addUser 함수는 다음코드 처럼 만든다.

 

// 사용자를 등록하는 함수
var addUser = function(id, name, age, password, callback){
    console.log('addUser 호출됨.');
    
    // 커넥션 풀에서 연결 객체를 가져온다.
    pool.getConnection(function(err,conn){
        if(err){
            if(conn){
                conn.release(); // 반드시 해제해야한다.
            }
            
            callback(err,null);
            return;
        }
        console.log('데이터베이스 연결 스레드 아이디 : ' + conn.threadId);
        
        // 데이터를 객체로 만든다.
        var data = {id:id , name:name, age:age, password:password};
        
        // SQL 문을 실행한다.
        var exec = conn.query('insert into users set ?', data, function(err, result){
            conn.release(); // 반드시 해제해야한다.
            console.log('실행 대상 SQL : ' + exec.sql);
            
            if(err){
                console.log('SQL 실행 시 오류 발생함.');
                console.dir(err);
                
                callback(err,null);
                return;
            }
            callback(null, result);
        });
    })
}

코드가 약간 길긴 하지만 몽고디비를 사용하던 방식과 코드 구조가 거의 비슷해서 이해하기 쉬울 것이다.

 

addUser 함수에는 다섯 개의 파라미터를 전달한다. id, name, age, password 파라미터는 웹 브라우저에서 요청할 때 전달한 요청 파라미터이며 callback 은 결과를 처리하는 콜백함수이다.

pool 객체의 getConnection 메소드를 호출하면 커넥션 풀에서 연결 객체를 하나 가져올 수 있다. 연결 객체를 성공적을 가져오면 conn 파라미터로 연결 객체가 전달된다.

연결 객체에는 query 메소드가 있어 SQL 문을 실행할 수 있다.

여기에서는 데이터를 추가하는 INSERT 문을 사용했다. SQL문을 만들어 실행할 때는 SQL 문안에 ? 기호를 넣을 수 있는데, 이 기호는 query 메소드를 호출할 때 전달하는 추가 파라미터를 사용해 대체한 후 실행한다.

 

즉, data 변수에 {id:id , name:name , age:age, password:password} 객체를 할당한 후 query 메소드를 호출하면서 파라미터로 전달하면 SQL 문은 다음과 같은 형태로 만들어진 후 실행된다.

 

insert into users set id='test01' , name='김준수' , age=20 , password = '123456'

 

SQL 문이 실행되면 콜백 함수가 호출되면서 결과가 result 파라미터로 전달된다. SQL 문을 실행한 후에는 연결 객체의 release 메소드를 호출하여 연결 객체를 커넥션 풀로 반환해야한다. 

실행 결과는 함수 쪽에서 처리할 수 있도록 callback(null, result) 코드를 넣어 콜백 함수를 실행한다.

 

사용자 추가 요청을 처리하는 함수 만들기

사용자를 추가할 떄 사용하는 라우팅 함수의 코드도 몽고디비를 사용할 때와 거의 비슷하다. 다음과 같이 사용자 추가 기능을 처리하는 함수를 추가한다.

 

// 사용자 추가 라우팅 함수
router.route('/process/adduser').post(function(req,res){
    console.log('/process/adduser 호출됨.');
    
    var paramId = req.body.id || req.query.id;
    var paramPassword = req.body.password || req.query.password;
    var paramName = req.body.name || req.query.name;
    var paramAge = req.body.age || req.query.age;
    
    console.log('요청 파라미터 : ' + paramId + ', ' + paramPassword + ', ' + paramName + ', ' + paramAge );
    
    // pool 객체가 초기화된 경우, addUser 함수 호출하여 사용자 추가
    if (pool){
        addUser(paramId,paramName,paramAge,paramPassword, function(err, addedUser){
            // 동일한 id 로 추가할 때 오류 발생 - 클라이언트로 오류 전송
            if(err){
                console.error('사용자 추가 중 오류 발생 : ' + err.stack);
                
                res.writeHead('200',{'Content-Type':'text/html;charset=utf8'});
                res.write('<h2>사용자 추가 중 오류 발생</h2>');
                res.write('<p>'+ err.stack +'</p>');
                res.end();
                
                return;
            }
            if(addedUser){
                console.dir(addedUser);
                
                console.log('inserted ' + addedUser.affectedRows + ' rows');
                
                var insertId = addedUser.insertId;
                console.log('추가한 레코드의 아이디 : ' + insertId);
                
                res.wirteHead('200', {'Content-Type':'text/html;charset=utf8'});
                res.write('<h2>사용자 추가 성공</h2>');
                res.end();
            }else{
                res.writeHead('200', {'Content-Type':'text/html;charset=utf8'});
                res.write('<h2>사용자 추가 실패</h2>');
                res.end();
            }
        });
    }else{
        //데이터베이스 객체가 초기화되지 않은 경우 실패 응답 전송
        res.writeHead('200', {'Content-Type':'text/html;charset=utf8'});
        res.write('<h2>데이터베이스 연결 실패</h2>');
        res.end();
    }
    
});

 

POST 방식으로 요청된 /process/adduser 패스를 처리하는 코드는 앞에서 본 것과 거의 같다.

먼저 웹 브라우저로부터 id, name, age, password 파라미터를 받아 처리하도록 수정한다. 그리고 addUser 함수를 실행한 결과를 확인한 후 응답을 보낸다.

 

이제 사용자를 등록할 때 [public] 폴더 안에 만들었던 adduser.html 파일을 복사해서 adduser2.html 파일을 만들고 다음 코드처럼 <body> 안의 태그를 수정한다.

 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>사용자 추가 테스트</title>
</head>
<body>
    <h1>MySQL 사용자 추가</h1>
    <br>
    <form method="post" action="/process/adduser">
        <table>
            <tr>
                <td><label>아이디</label></td>
                <td><input type="text" name="id"></td>
            </tr>
            <tr>
                <td><label>사용자명</label></td>
                <td><input type="text" name="name"></td>
            </tr>
            <tr>
                <td><label>나이</label></td>
                <td><input type="text" name="age"></td>
            </tr>
            <tr>
                <td><labeL>비밀번호</labeL></td>
                <td><input type="password" name="password"></td>
            </tr>
        </table>
        <input type="submit" value="전송" name="">
    </form>
</body>
</html>

 

<input> 태그가 하나 더 추가되었다. 이제 app8.js 를 실행하고 웹 브라우저에 접속해보자.

 

http://localhost:3000/public/adduser2.html

 

MySQL 에 들어 있는 사용자 정보로 로그인하기

이제 MySQL 에 들어 있는 사용자 정보로 로그인하는 기능을 만들어보자.

먼저 사용자가 보낸 아이디와 비밀번호를 사용해 데이터베이스 안에 일치하는 레코드가 있는지 조회하는 authUser 함수를 만든다.

 

// 사용자를 인증하는 함수
var authUser = function(database, id, password, callback){
    console.log('authUser 호출됨.');
    
    // 커넥션 풀에서 연결 객체를 가져온다.
    pool.getConnection(function(err,conn){
        if(err){
            if(conn){
                conn.release(); // 반드시 해제해야한다.
            }
            callback(err, null);
            return;
        }
        console.log('데이터베이스 연결 스레드 아이디 : ' + conn.threadId);
        
        var columns = ['id', 'name', 'age'];
        var tablename = 'users';
        
        //SQL 문을 실행한다.
        var exec = conn.query("select ?? from ?? where id = ? and password = ?", [columns, tablename, id, password], function(err,rows){
            conn.release(); // 반드시 해제해야한다.
            console.log('실행 대상 SQL : ' + exec.sql);
            
            if(rows.length > 0 ){
                console.log('아이디 [%s], 패스워드 [%s] 가 일치하는 사용자 찾음', id, password);
                callback(null,rows);
            }else{
                console.log('일치하는 사용자 찾지 못함');
                callback(null,null);
            }
        });
    })
}

클라이언트로부터 전달받은 파라미터는 아이디와 비밀번호이다.

authUser 함수는 이 두개의 파라미터와 함께 콜백함수를 전달받는다. 그리고 그 안에서 getConnection 메소드를 사용해 커넥션 풀에서 연결 객체를 가져온다.

그다음에는 연결 객체의 query 메소드로 SQL 문을 실행하는데 데이터를 조회해야하므로 SELECT 문을 사용한다.

SELECT 문에는 어떤 칼럼을 어떤 테이블에서 조회할지 그리고 어떤 조건으로 조회할지 등의 정보를 넣어주는데 이 세가지 정보는 객체나 변수로 만들어 전달한다.

query 메소드로 전달하는 첫 번째 파라미터는 SQL 문이고 두 번째 파라미터는 배열 객체이다.

이 배열 객체는 SQL문과 데이터를 사용해 SQL문 안에 들어있는 ?? 또는 ? 기호를 대체한 후 다음과 같은 SQL문을 만들어낸다.

 

SELECT id,name, age from users where id = 'test01' and password = '123456'

 

SQL 문이 정상적으로 실행되면 연결 객체의 release 메소드를 호출하여 연결 객체를 커넥션 풀로 반환한 후 callback 함수를 실행한다.

이렇게 하면 이 함수를 호출한 쪽에서 rows 객체를 전달받게 된다.

 

이제 로그인 처리를 요청하는 /process/login 패스에 라우팅 함수를 추가할 때가 되었다. 다음처럼 사용자가 있는지 확인한 후 그에 맞는 응답을 보내주는 코드를 입력한다.

 

// 로그인 라우터 함수 - 데이터베이스의 정보와 비교
router.route('/process/login').post(function(req,res){
    console.log('/process/login 호출됨.');
    
    // 요청 파라미터 확인
    var paramId = req.body.id || req.query.id;
    var paramPassword = req.body.password || req.query.password;
    
    console.log('요청 파라미터 : ' + paramId + ', ' + paramPassword);
    
    //pool 객체가 초기화된 경우, authUser 함수 호출하여 사용자 인증
    if(pool){
        authUser(paramId, paramPassword, function(err,rows){
            // 오류가 발생했을 때 클라이언트로 오류 전송
            if(err){
                console.error('사용자 로그인 중 오류 발생 : ' + err.stack);
                
                res.writeHead('200', {'Content-Type':'text/html;charset=utf8'});
                res.write('<h2>사용자 로그인 중 오류 발생</h2>');
                res.write('<p>' + err.stack + '</p>');
                res.end();
                
                return;
            } 
            if(rows){
                console.dir(rows);
                
                var username = rows[0].name;
                
                res.writeHead('200',{'Content-Type':'text/html;charset=utf8'});
                res.write('<h1>로그인 성공</h1>');
                res.write('<div><p>사용자 아이디 : ' + paramId +  '</p></div>');
                res.write('<div><p>사용자 이름' + username + '</p></div>');
                res.write("<br><br><a href='/public/login2.html'>다시 로그인하기</a>");
                res.end();
            }
        })
    } 
});

사용자가 요청한  id 와 password 파라미터를 확인한 후 authUser() 메소드를 호출하면서 파라미터로 전달한다.

MySQL 데이터베이스에서 조회한 사용자 데이터는 rows 파라미터에 들어 있으므로 rows 객체의 첫 번째 요소를 참조한 후 사용자 이름을 확인한다.

웹 페이지는 login.html 파일을 그대로 사용해도 되지만 제목을 약간 바꾸기 위해서 login.html 파일을 복사해서 새로운 login2.html 파일을 만들어 제목을 변경한다.

이제 app8.js 를 실행하고 접속하면 정상적으로 처리된다.