★Node.js & MySQL (생활코딩) -1

1936 No

★Node.js & MySQL (생활코딩) -1

2022-09-21 02:34:24 By 라이트 0 397 댓글수 4 포스트 하기

12월 2025
일	월	화	수	목	금	토
	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

인프런 : https://www.inflearn.com/course/web2-node-js-mysql#

생활 코딩 : https://opentutorials.org/module/3560/21175

유튜브 목록 : https://www.youtube.com/playlist?list=PLuHgQVnccGMAicFFRh8vFFFtLLlNojWUh

소스 : https://github.com/web-n/node.js-mysql

https://github.com/web-n/node.js-mysql/releases/tag/1

준비 및 사용방법

1. 수업 소개

실습 코드

이 수업은 WEB2 - Node.js과 DATABASE2 - MySQL을 기반으로 하고 있습니다. 이를 위한 실습코드가 필요합니다. 아래 주소에 준비해 두었습니다.

다운로드1
다운로드2 (다운로드1이 작동하지 않으면 이 링크를 사용하세요)

2. 실습 준비

Mysql

CREATE database opentutorials;

CREATE USER `opentutorials`@`localhost` identified by '1111';

GRANT ALL  privileges on opentutorials.* to `opentutorials`@`localhost` ;

 
CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `profile` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
--
-- Dumping data for table `author`
--
 
INSERT INTO `author` VALUES (1,'egoing','developer');
INSERT INTO `author` VALUES (2,'duru','database administrator');
INSERT INTO `author` VALUES (3,'taeho','data scientist, developer');
 
--
-- Table structure for table `topic`
--
 
CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` text,
  `created` datetime NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
--
-- Dumping data for table `topic`
--
 
INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...','2022-10-01 12:10:11',1);
INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2022-10-03 13:01:10',1);
INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2022-10-20 11:01:10',2);
INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2022-10-23 01:03:03',3);
INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2022-10-30 12:31:03',1);

nodemon 모듈

Node.js 개발 시 자바 스크립트 파일들을 수정 할때마다 매번 ctrl+c를 통해 node를 종료 후 다시 실행해줘야 하는 번거로움 이 있었습니다.
하지만 파일들을 모니터링하고 있다가 수정될 경우 자동으로 서버를 재실행시켜주는 스크립트 모니터링 유틸리티 nodemon를 이용하면 번거로움이 상당히 줄어듭니다

nodemon 설치

npm 1.0Visit Website이상을 사용할 경우 nodemon은 커맨드라인에서 명령어를 사용할 것이기 때문에 -g 옵션을 붙혀서 글로벌로 설치를 합니다.

$ npm install nodemon -g

package.json 간편 설정

간편하게 npm start 명령어로도 nodemon을 실행시킬수 있습니다.

package.json 파일에 다음과 같이 script색션에 등록하면 됩니다.

  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "dev": "nodemon ./main.js"
  },

실행

$ npm run dev

3. Node.js MySQL 모듈의 기본 사용방법

mysql 설치

$npm i mysql

nodejs/mysql.js

var mysql = require('mysql');
// 비밀번호는 별도의 파일로 분리해서 버전관리에 포함시키지 않아야 합니다. 
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'opentutorials',
    password: '1111',
    database: 'opentutorials'
});

connection.connect();

connection.query('SELECT * FROM topic', function (error, results, fields) {
    if (error) {
        console.log(error);
    }
    console.log('The solution is :', results);
});

connection.end();

4. ER ACCESS DENIED ERROR 오류 처리

My SQL로 구현

5. My SQL로 홈페이지 구현 1

소스코드

main.js (변경사항)

~
var mysql = require('mysql');
var db = mysql.createConnection({
  host: 'localhost',
  user: 'opentutorials',
  password: '1111',
  database: 'opentutorials'
});
db.connect();
~


  if (pathname === '/') {
    if (queryData.id === undefined) {
      // fs.readdir('./data', function (error, filelist) {
      //   var title = 'Welcome';
      //   var description = 'Hello, Node.js';
      //   var list = template.list(filelist);
      //   var html = template.HTML(title, list,
      //     `<h2>${title}</h2>${description}`,
      //     `<a href="/create">create</a>`
      //   );
      //   response.writeHead(200);
      //   response.end(html);
      // });


      db.query("SELeCT * FROM topic", function (error, topics) {
        console.log(topics);
        response.writeHead(200);
        response.end("success");
      });


    }



~

6. My SQL로 홈페이지 구현 2

소스코드

main.js (변경사항)

main.js

~
      db.query("SELeCT * FROM topic", function (error, topics) {
        console.log(topics);

        var title = 'Welcome';
        var description = 'Hello, Node.js';

        var list = template.list(topics);
        var html = template.HTML(title, list,
          `<h2>${title}</h2>${description}`,
          `<a href="/create">create</a>`
        );

        response.writeHead(200);
        response.end(html);
      });
~

lib/template.js

, list: function (topics) {
    var list = '<ul>';
    var i = 0;
    while (i < topics.length) {
      list = list + `<li><a href="/?id=${topics[i].id}">${topics[i].title}</a></li>`;
      i = i + 1;
    }
    list = list + '</ul>';
    return list;
  }

7. My SQL로 상세보기 구현

소스코드

main.js (변경사항)

~



      db.query("SELECT * FROM topic", function (error, topics) {
        if (error) throw error;


        db.query(`SELECT * FROM topic WHERE id=?`, [queryData.id], function (error2, topic) {
          if (error2) throw error2;

          console.log("topic : ", topic);

          var title = topic[0].title;
          var description = topic[0].description;
          var list = template.list(topics);


          var html = template.HTML(title, list,
            `<h2>${title}</h2>${description}`,
            ` <a href="/create">create</a>
                <a href="/update?id=${queryData.id}">update</a>
                <form action="delete_process" method="post">
                  <input type="hidden" name="id" value="${queryData.id}">
                  <input type="submit" value="delete">
                </form>`

          );

          response.writeHead(200);
          response.end(html);
        });


      });




    }




 } else if (pathname === '/create') {

8. My SQL로 글생성 기능 구현

소스코드

main.js (변경사항)

  } else if (pathname === '/create_process') {



    var body = '';
    request.on('data', function (data) {
      body = body + data;
    });

    request.on('end', function () {
      var post = qs.parse(body);


      db.query(`INSERT INTO topic (title, description, created, author_id)  VALUES(?, ?, NOW(), ?)`,
        [post.title, post.description, 1], function (error, result) {
          if (error) throw error;  //등록 처리된 아이디 값 가져오기 insertId
          response.writeHead(302, { Location: `/?id=${result.insertId}` });
          response.end();

        });


    });



  }

9.My SQL로 글수정 기능 구현 1

소스코드 main.js (변경사항)

  } else if (pathname === '/update') {
    db.query("select * from topic", function (erro, topics) {
      if (erro) throw erro;

      db.query(`SELECT * FROM topic WHERE id=?`, [queryData.id], function (error2, topic) {
        if (error2) throw error2;
        var list = template.list(topics);
        var html = template.HTML(topic[0].title, list,
          `
                <form action="/update_process" method="post">
                  <input type="hidden" name="id" value="${topic[0].id}">
          <p><input type="text" name="title" placeholder="title" value="${topic[0].title}"></p>
          <p>
          <textarea name="description" placeholder="description">${topic[0].description}</textarea>
                  </p>
      <p>
        <input type="submit">
      </p>
                </form>
      `,
          `<a href="/create" > create</a> <a href="/update?id=${topic[0].id}">update</a>`
        );
        response.writeHead(200);
        response.end(html);
      });

    });

  } else if (pathname === '/update_process') {

10.My SQL로 글수정 기능 구현 2

소스코드 main.js (변경사항)

  } else if (pathname === '/update_process') {
    var body = '';
    request.on('data', function (data) {
      body = body + data;
    });
    request.on('end', function () {
      var post = qs.parse(body);


      db.query(`UPDATE topic SET title=?, description=?, author_id=? WHERE id=?`,
        [post.title, post.description, 1, post.id], function (error, result) {
          if (error) throw error;
          response.writeHead(302, { Location: `/?id=${post.id}` });
          response.end();
        });



    });
  }

11.My SQL로 글 삭제 기능 구현

소스코드

main.js (변경사항)

  } else if (pathname === '/delete_process') {
    var body = '';
    request.on('data', function (data) {
      body = body + data;
    });
    request.on('end', function () {
      var post = qs.parse(body);
      db.query("delete from topic where id=?", [post.id], function (error, result) {
        if (error) throw error;
        response.writeHead(302, { Location: `/ ` });
        response.end();
      });

    });
  } else {

My SQL join을 이용해서 구현

12.My SQL join을 이용해서 상세보기 구현

main.js (변경사항)

    } else {

      db.query("SELECT * FROM topic", function (error, topics) {
        if (error) throw error;

        db.query(`SELECT * FROM topic LEFT JOIN author  ON topic.author_id=author.id  WHERE topic.id=?`, [queryData.id], function (error2, topic) {
          if (error2) throw error2;

          console.log("topic : ", topic);

          var title = topic[0].title;
          var description = topic[0].description;
          var list = template.list(topics);

          var html = template.HTML(title, list,
            `<h2>${title}</h2>
            ${description}
            <p>by ${topic[0].name}</p>
            `,
            ` <a href="/create">create</a>
                <a href="/update?id=${queryData.id}">update</a>
                <form action="delete_process" method="post">
                  <input type="hidden" name="id" value="${queryData.id}">
                  <input type="submit" value="delete">
                </form>`

          );

          response.writeHead(200);
          response.end(html);
        });


      });

    }


  } else if (pathname === '/create') {

13.My SQL join을 이용해서 글 생성 구현

소스코드

변경사항 main.js

 } else if (pathname === '/create') {
    db.query("SELECT * FROM topic ", function (error, topics) {
      db.query("SELECT * FROM author ", function (error2, authors) {

        var title = 'create';
        var list = template.list(topics);
        var html = template.HTML(title, list, `
        <form action = "/create_process" method = "post" >
            <p><input type="text" name="title" placeholder="title"></p>
            <p>
              <textarea name="description" placeholder="description"></textarea>
            </p>
            <p>
             ${template.authorSelect(authors)}
            </p>
            <p>
              <input type="submit">
            </p>
          </form >
  `, '');
        response.writeHead(200);
        response.end(html);

      });

    });


  } else if (pathname === '/create_process') {



    var body = '';
    request.on('data', function (data) {
      body = body + data;
    });

    request.on('end', function () {
      var post = qs.parse(body);


      db.query(`INSERT INTO topic(title, description, created, author_id)  VALUES(?, ?, NOW(), ?)`,
        [post.title, post.description, post.author], function (error, result) {
          if (error) throw error;  //등록 처리된 아이디 값 가져오기 insertId
          response.writeHead(302, { Location: `/? id = ${result.insertId} ` });
          response.end();

        });


    });



  } else if (pathname === '/update') {

lib/template.js

  }, authorSelect: function (authors) {
    var tag = authors.map(author => (
      ` <option value="${author.id}">${author.name}</option>`
    ));

    return `<select name="author"> ${tag} </select>`;

  }

14.My SQL join을 이용해서 글 수정 구현

 } else if (pathname === '/update') {
    db.query("select * from topic", function (erro, topics) {
      if (erro) throw erro;

      db.query(`SELECT * FROM topic WHERE id =? `, [queryData.id], function (error2, topic) {
        db.query("SELECT * FROM author ", function (error2, authors) {
          if (error2) throw error2;
          var list = template.list(topics);
          var html = template.HTML(topic[0].title, list,
            `
        <form action = "/update_process" method = "post" >
          <input type="hidden" name="id" value="${topic[0].id}">
            <p><input type="text" name="title" placeholder="title" value="${topic[0].title}"></p>
            <p>
              <textarea name="description" placeholder="description">${topic[0].description}</textarea>
            </p>
           <p>
             ${template.authorSelect(authors, topic[0].author_id)}
            </p>
            <p>
              <input type="submit">
            </p>
          </form>
      `,
            `<a href = "/create" > create</a > <a href="/update?id=${topic[0].id}">update</a>`
          );
          response.writeHead(200);
          response.end(html);
        });
      });

    });

  } else if (pathname === '/update_process') {
    var body = '';
    request.on('data', function (data) {
      body = body + data;
    });
    request.on('end', function () {
      var post = qs.parse(body);


      db.query(`UPDATE topic SET title =?, description =?, author_id =? WHERE id =? `,
        [post.title, post.description, post.author, post.id], function (error, result) {
          if (error) throw error;
          response.writeHead(302, { Location: `/? id = ${post.id} ` });
          response.end();
        });



    });
  }

lib/template.js

  }, authorSelect: function (authors, author_id) {

    var tag = authors.map(author => (
      ` <option value="${author.id}" ${author.id === author_id ? 'selected' : ''}  >${author.name}</option>`
    ));

    return `<select name="author"> ${tag} </select>`;

  }

Node.js 정리정돈

15.수업의 정상

16.Node.js의 DB 설정정보 정리정돈

소스코드

변경사항 main.js

var db = require("./lib/db");

./lib/db.js

var mysql = require('mysql');
var db = mysql.createConnection({
    host: 'localhost',
    user: 'opentutorials',
    password: '1111',
    database: 'opentutorials'
});
db.connect();

module.exports = db;

17.Node.js 코드의 정리정돈 (topic) -1

소스코드

변경사항

 var topic = require("./lib/topic");


   if (queryData.id === undefined) {

      topic.home(response);

    } else {

lib/topic.js

var template = require('./template.js');
var db = require("./db");

exports.home = function (response) {

    db.query("SELECT * FROM topic", function (error, topics) {
        var title = 'Welcome';
        var description = 'Hello, Node.js';
        var list = template.list(topics);
        var html = template.HTML(title, list,
            `<h2>${title}</h2>${description}`,
            `<a href="/create">create</a>`
        );

        response.writeHead(200);
        response.end(html);
    });

}

18.Node.js 코드의 정리정돈 (topic) -2

소스코드

변경사항

  var pathname = url.parse(_url, true).pathname;
  if (pathname === '/') {
    if (queryData.id === undefined) {
      topic.home(request, response);

    } else {
      topic.page(request, response);
    }

  } else if (pathname === '/create') {
    topic.create(request, response);


  } else if (pathname === '/create_process') {
    topic.create_process(request, response);


  } else if (pathname === '/update') {

lib/topic.js

var url = require('url');
var qs = require('querystring');
var template = require('./template.js');
var db = require("./db");

exports.home = function (request, response) {

    db.query("SELECT * FROM topic", function (error, topics) {
        var title = 'Welcome';
        var description = 'Hello, Node.js';
        var list = template.list(topics);
        var html = template.HTML(title, list,
            `<h2>${title}</h2>${description}`,
            `<a href="/create">create</a>`
        );

        response.writeHead(200);
        response.end(html);
    });

}


exports.page = function (request, response) {

    var _url = request.url;
    var queryData = url.parse(_url, true).query;

    db.query("SELECT * FROM topic", function (error, topics) {
        if (error) throw error;

        db.query(`SELECT * FROM topic LEFT JOIN author  ON topic.author_id=author.id  WHERE topic.id=?`, [queryData.id], function (error2, topic) {
            if (error2) throw error2;

            console.log("topic : ", topic);

            var title = topic[0].title;
            var description = topic[0].description;
            var list = template.list(topics);

            var html = template.HTML(title, list,
                `<h2>${title}</h2>
            ${description}
            <p>by ${topic[0].name}</p>
            `,
                ` <a href="/create">create</a>
                <a href="/update?id=${queryData.id}">update</a>
                <form action="delete_process" method="post">
                  <input type="hidden" name="id" value="${queryData.id}">
                  <input type="submit" value="delete">
                </form>`

            );

            response.writeHead(200);
            response.end(html);
        });


    });
}



exports.create = function (request, response) {

    db.query("SELECT * FROM topic ", function (error, topics) {
        db.query("SELECT * FROM author ", function (error2, authors) {

            var title = 'create';
            var list = template.list(topics);
            var html = template.HTML(title, list, `
        <form action = "/create_process" method = "post" >
            <p><input type="text" name="title" placeholder="title"></p>
            <p>
              <textarea name="description" placeholder="description"></textarea>
            </p>
            <p>
             ${template.authorSelect(authors)}
            </p>
            <p>
              <input type="submit">
            </p>
          </form >
  `, '');
            response.writeHead(200);
            response.end(html);

        });

    });
}


exports.create_process = function (request, response) {


    var body = '';
    request.on('data', function (data) {
        body = body + data;
    });

    request.on('end', function () {
        var post = qs.parse(body);


        db.query(`INSERT INTO topic(title, description, created, author_id)  VALUES(?, ?, NOW(), ?)`,
            [post.title, post.description, post.author], function (error, result) {
                if (error) throw error;  //등록 처리된 아이디 값 가져오기 insertId
                response.writeHead(302, { Location: `/? id = ${result.insertId} ` });
                response.end();

            });


    });

}

19.Node.js 코드의 정리정돈 (topic) -3

소스코드

변경사항

main.js

var http = require('http');
var url = require('url');
var topic = require("./lib/topic");


var app = http.createServer(function (request, response) {
  var _url = request.url;
  var queryData = url.parse(_url, true).query;
  var pathname = url.parse(_url, true).pathname;
  if (pathname === '/') {
    if (queryData.id === undefined) {
      topic.home(request, response);
    } else {
      topic.page(request, response);
    }

  } else if (pathname === '/create') {
    topic.create(request, response);

  } else if (pathname === '/create_process') {
    topic.create_process(request, response);

  } else if (pathname === '/update') {
    topic.update(request, response);

  } else if (pathname === '/update_process') {
    topic.update_process(request, response);

  } else if (pathname === '/delete_process') {
    topic.delete_process(request, response);

  } else {
    response.writeHead(404);
    response.end('Not found');
  }
});
app.listen(3000);

lib/topic.js

var url = require('url');
var qs = require('querystring');
var template = require('./template.js');
var db = require("./db");

exports.home = function (request, response) {

    db.query("SELECT * FROM topic", function (error, topics) {
        var title = 'Welcome';
        var description = 'Hello, Node.js';
        var list = template.list(topics);
        var html = template.HTML(title, list,
            `<h2>${title}</h2>${description}`,
            `<a href="/create">create</a>`
        );

        response.writeHead(200);
        response.end(html);
    });

}


exports.page = function (request, response) {

    var _url = request.url;
    var queryData = url.parse(_url, true).query;

    db.query("SELECT * FROM topic", function (error, topics) {
        if (error) throw error;

        db.query(`SELECT * FROM topic LEFT JOIN author  ON topic.author_id=author.id  WHERE topic.id=?`, [queryData.id], function (error2, topic) {
            if (error2) throw error2;

            console.log("topic : ", topic);

            var title = topic[0].title;
            var description = topic[0].description;
            var list = template.list(topics);

            var html = template.HTML(title, list,
                `<h2>${title}</h2>
            ${description}
            <p>by ${topic[0].name}</p>
            `,
                ` <a href="/create">create</a>
                <a href="/update?id=${queryData.id}">update</a>
                <form action="delete_process" method="post">
                  <input type="hidden" name="id" value="${queryData.id}">
                  <input type="submit" value="delete">
                </form>`

            );

            response.writeHead(200);
            response.end(html);
        });


    });
}



exports.create = function (request, response) {

    db.query("SELECT * FROM topic ", function (error, topics) {
        db.query("SELECT * FROM author ", function (error2, authors) {

            var title = 'create';
            var list = template.list(topics);
            var html = template.HTML(title, list, `
        <form action = "/create_process" method = "post" >
            <p><input type="text" name="title" placeholder="title"></p>
            <p>
              <textarea name="description" placeholder="description"></textarea>
            </p>
            <p>
             ${template.authorSelect(authors)}
            </p>
            <p>
              <input type="submit">
            </p>
          </form >
  `, '');
            response.writeHead(200);
            response.end(html);

        });

    });
}


exports.create_process = function (request, response) {
    var body = '';
    request.on('data', function (data) {
        body = body + data;
    });

    request.on('end', function () {
        var post = qs.parse(body);
        db.query(`INSERT INTO topic(title, description, created, author_id)  VALUES(?, ?, NOW(), ?)`,
            [post.title, post.description, post.author], function (error, result) {
                if (error) throw error;  //등록 처리된 아이디 값 가져오기 insertId
                response.writeHead(302, { Location: `/? id = ${result.insertId} ` });
                response.end();

            });
    });
}

exports.update = function (request, response) {
    var _url = request.url;
    var queryData = url.parse(_url, true).query;
    db.query("select * from topic", function (erro, topics) {
        if (erro) throw erro;

        db.query(`SELECT * FROM topic WHERE id =? `, [queryData.id], function (error2, topic) {
            db.query("SELECT * FROM author ", function (error2, authors) {
                if (error2) throw error2;
                var list = template.list(topics);
                var html = template.HTML(topic[0].title, list,
                    `
        <form action = "/update_process" method = "post" >
          <input type="hidden" name="id" value="${topic[0].id}">
            <p><input type="text" name="title" placeholder="title" value="${topic[0].title}"></p>
            <p>
              <textarea name="description" placeholder="description">${topic[0].description}</textarea>
            </p>
           <p>
             ${template.authorSelect(authors, topic[0].author_id)}
            </p>
            <p>
              <input type="submit">
            </p>
          </form>
      `,
                    `<a href = "/create" > create</a > <a href="/update?id=${topic[0].id}">update</a>`
                );
                response.writeHead(200);
                response.end(html);
            });
        });

    });
}


exports.update_process = function (request, response) {
    var body = '';
    request.on('data', function (data) {
        body = body + data;
    });
    request.on('end', function () {
        var post = qs.parse(body);


        db.query(`UPDATE topic SET title =?, description =?, author_id =? WHERE id =? `,
            [post.title, post.description, post.author, post.id], function (error, result) {
                if (error) throw error;
                response.writeHead(302, { Location: `/? id = ${post.id} ` });
                response.end();
            });



    });
}

exports.delete_process = function (request, response) {
    var body = '';
    request.on('data', function (data) {
        body = body + data;
    });
    request.on('end', function () {
        var post = qs.parse(body);
        db.query("delete from topic where id=?", [post.id], function (error, result) {
            if (error) throw error;
            response.writeHead(302, { Location: `/ ` });
            response.end();
        });

    });
}