Node.js & SQL Server Tedious Getting Started 따라하기

Node.js & SQL Server Tedious Getting Started 따라하기

1. node에 SQL Server Tedious module 설치

npm을 사용하여 다음과 같이 실행한다.

• npm init 
• npm install tedious
• npm install async

async는 비동기 자바스크립트를 동기식으로 지원하는 모듈이다.  Node의 기능을 async로 수행하도록 변환해 주는 역할을 한다.  기존 Node에서는 동기식 기법으로 setTimeout, callback, promise를 사용했다.  일부 사용하는 데 문제가 있다고 판단되었고 이를 async와 await로 해결한다.  Database처리는 동기식으로 진행해야 하므로 async 모듈을 사용해서 처리한다.

2. SQL Server 접속하기

SQL Server에 접속하기 위해서 다음과 같은 코드를 사용했다.  Tedious 샘플을 이용하여 deprecated 된 항목을 수정했다.

• var Connection = require('tedious').Connection;
• var Request = require('tedious').Request;
• var TYPES = require('tedious').TYPES;
• 
  
• // Create connection to database
• var config = {
•   server: "localhost",
•   // If you're on Windows Azure, you will need this:
•   options: {encrypt: false},
•   authentication: {
•     type: "default",
•     options: {
•       userName: "stormboy",
•       password: "*******",
•   }
• }
• /*
•  ,options: {
•    debug: {
•      packet: true,
•      data: true,
•      payload: true,
•      token: false,
•      log: true
•    },
•    database: 'DBName',
•    encrypt: true // for Azure users
•  }
•  */
• };
• 
  
• var connection = new Connection(config);
• 
  
• // Attempt to connect and execute queries if connection goes through
• connection.on('connect', function(err) {
•   if (err) {
•     console.log(err);
•   } else {
•     console.log('Connected');
•   }
• });

명령을 실행하면 다음과 같은 결과가 표시된다.

• PS C:\ubuntu\node\oauth2svr\mssql> node 01connect
• Connected

3. sqlcmd를 사용하여 테이블과 테스트 데이터 생성하기 

sqlcmd 명령을 사용하기 위해 cmd를 실행한다.  sqlcmd를 이용하여 SQL Server를 접속하고 새로운 테이블과 테스트 데이터를 생성한다.

• PS C:\ubuntu\node\oauth2svr\mssql> sqlcmd -S localhost -U stormboy -P ***** -d mydb01
• 1> create table Employees (
• 2> Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
• 3> Name NVARCHAR(50),
• 4> Location NVARCHAR(50)
• 5> );
• 6> go

다음은 생성된 테이블에 테스트 데이터를 추가한다.

• 1> INSERT INTO Employees (Name, Locaton) VALUES
• 2> (N'Jared',N'Australia'),
• 3> (N'Nikita', N'India'),
• 4> (N'Tom',N'Germany');
• 5> go

입력된 데이터는 다음과 같이 확인한다.

• 1> select * from Employees;
• 2> go
• Id          Name                                               Location
• ----------- -------------------------------------------------- --------------------------------------------------
•           1 Jared                                              Australia
•           2 Nikita                                             India
•           3 Tom                                                Germany
• 
  
• (3개 행 적용됨)

sqlcmd 접속 시 기본 데이터베이스를 지정했고 사용자를 일반 사용자로 지정했기 떄문에 쿼리문에 데이터베이스 지정을 하지 않았다.

4. node로 SQL Server와 CRUD 실행하기

SQL Server와 연결이 되면 다음으로 기본 CRUD를 테스트 한다.  연결 떄와 달리 connect 옵션으로 database를 지정해야 한다.

• var Connection = require('tedious').Connection;
• var Request = require('tedious').Request;
• var TYPES = require('tedious').TYPES;
• var async = require('async');
• 
  
• // Create connection to database
• var config = {
•   server: "localhost",
•   // If you're on Windows Azure, you will need this:
•   options: {encrypt: false},
•   authentication: {
•     type: "default",
•     options: {
•       userName: "stormboy",
•       password: "******",
•   }
• }
•  ,options: {
•    debug: {
•      packet: true,
•      data: true,
•      payload: true,
•      token: false,
•      log: true
•    },
•    database: 'mydb01',
•    encrypt: false // for Azure users
•  }
• 
  
• };
• 
  
• var connection = new Connection(config);
• 
  
• function Start(callback) {
•     console.log('Starting...');
•     callback(null, 'Jake', 'United States');
• }
• 
  
• function Insert(name, location, callback) {
•     console.log("Inserting '" + name + "' into Table...");
• 
  
•     request = new Request(
•         'INSERT INTO Employees (Name, Location) OUTPUT INSERTED.Id VALUES (@Name, @Location);',
•         function(err, rowCount, rows) {
•         if (err) {
•             callback(err);
•         } else {
•             console.log(rowCount + ' row(s) inserted');
•             callback(null, 'Nikita', 'United States');
•         }
•         });
•     request.addParameter('Name', TYPES.NVarChar, name);
•     request.addParameter('Location', TYPES.NVarChar, location);
• 
  
•     // Execute SQL statement
•     connection.execSql(request);
• }
• 
  
• function Update(name, location, callback) {
•     console.log("Updating Location to '" + location + "' for '" + name + "'...");
• 
  
•     // Update the employee record requested
•     request = new Request(
•     'UPDATE Employees SET Location=@Location WHERE Name = @Name;',
•     function(err, rowCount, rows) {
•         if (err) {
•         callback(err);
•         } else {
•         console.log(rowCount + ' row(s) updated');
•         callback(null, 'Jared');
•         }
•     });
•     request.addParameter('Name', TYPES.NVarChar, name);
•     request.addParameter('Location', TYPES.NVarChar, location);
• 
  
•     // Execute SQL statement
•     connection.execSql(request);
• }
• 
  
• function Delete(name, callback) {
•     console.log("Deleting '" + name + "' from Table...");
• 
  
•     // Delete the employee record requested
•     request = new Request(
•         'DELETE FROM Employees WHERE Name = @Name;',
•         function(err, rowCount, rows) {
•         if (err) {
•             callback(err);
•         } else {
•             console.log(rowCount + ' row(s) deleted');
•             callback(null);
•         }
•         });
•     request.addParameter('Name', TYPES.NVarChar, name);
• 
  
•     // Execute SQL statement
•     connection.execSql(request);
• }
• 
  
• function Read(callback) {
•     console.log('Reading rows from the Table...');
• 
  
•     // Read all rows from table
•     request = new Request(
•     'SELECT Id, Name, Location FROM Employees;',
•     function(err, rowCount, rows) {
•     if (err) {
•         callback(err); 
•     } else {
•         console.log(rowCount + ' row(s) returned');
•         callback(null);
•     }
•     });
• 
  
•     // Print the rows read
•     var result = "";
•     request.on('row', function(columns) {
•         columns.forEach(function(column) {
•             if (column.value === null) {
•                 console.log('NULL');
•             } else {
•                 result += column.value + " ";
•             }
•         });
•         console.log(result);
•         result = "";
•     });
• 
  
•     // Execute SQL statement
•     connection.execSql(request);
• }
• 
  
• function Complete(err, result) {
•     if (err) {
•    //     callback(err); // 맨 마지막 callback function should be replaced to "console.error(err)"
•            console.error(err);
•     } else {
•         console.log("Done!");
•     }
• }
• 
  
• // Attempt to connect and execute queries if connection goes through
• connection.on('connect', function(err) {
•   if (err) {
•     console.log(err);
•   } else {
•     console.log('Connected');
• 
  
•     // Execute all functions in the array serially
•     async.waterfall([
•         Start,
•         Insert,
•         Update,
•         Delete,
•         Read
•     ], Complete)
•   }
• });

node로 실행을 하면 다음과 같은 결과가 표시된다.

• PS C:\ubuntu\node\oauth2svr\mssql> node 02connect
• Connected
• Starting...
• Inserting 'Jake' into Table...
• 1 row(s) inserted
• Updating Location to 'United States' for 'Nikita'...
• 1 row(s) updated
• Deleting 'Jared' from Table...
• 1 row(s) deleted
• Reading rows from the Table...
• 2 Nikita United States
• 3 Tom Germany
• 4 Jake United States
• 3 row(s) returned

데이터베이스 Query 실행 중 오류가 발생하면 다음과 같은 엉뚱한 결과가 표시된다.

• C:\ubuntu\node\oauth2svr\mssql\02connect.js:134
•         callback(err);
•         ^
• 
  
• ReferenceError: callback is not defined

왜 Callback 함수를 인식 못하는 건가?

확인해 보니 err를 처리하기 위해서는 맨 마지막으로 callback을 받는 함수는 callback(err)가 아닌 console.log(err)를 사용해야 한다.

그러므로 line 134는 console.log(err)로 변경해야 한다.

5. 더 나은 데이터베이스 조작 성능을 위한 Sequelize 사용

Sequelize.js는 Node.js 기반의 ORM(Object-Relational-Mapping)이다.  ORM을 사용하면 직접 데이터베이스를 조작하는 것보다 수배의 성능 향상을 가질 수 있다.  Sequelize가 지원하는 데이터베이스는 PostgreSQL, MySQL, MariaDB, SQLite, MS-SQL로 기본적인 데이터베이스를 모두 지원한다.

• npm install sequelize

기본적으로 제공되는 소스에서 사용자ID, 암호, 그리고 데이터베이스를 지정하고 실행하면 테이블이 생성되고 테스트 데이터가 추가된다.

• var Sequelize = require('sequelize');
• var userName = 'stormboy';
• var password = '*******'; // update me
• var hostName = 'localhost';
• var DbName = 'mydb01';
• 
  
• // Initialize Sequelize to connect to sample DB
• var sampleDb = new Sequelize(DbName, userName, password, {
•     dialect: 'mssql',
•     host: hostName,
•     port: 1433, // Default port
•     logging: false, // disable logging; default: console.log
• 
  
•     dialectOptions: {
•         requestTimeout: 30000 // timeout = 30 seconds
•     }
• });
• 
  
• // Define the 'User' model
• var User = sampleDb.define('user', {
•     firstName: Sequelize.STRING,
•     lastName: Sequelize.STRING
• });
• 
  
• // Define the 'Task' model
• var Task = sampleDb.define('task', {
•     title: Sequelize.STRING,
•     dueDate: Sequelize.DATE,
•     isComplete: Sequelize.BOOLEAN
• });
• 
  
• // Model a 1:Many relationship between User and Task
• User.hasMany(Task);
• 
  
• console.log('**Node CRUD sample with Sequelize and MSSQL **');
• 
  
• // Tell Sequelize to DROP and CREATE tables and relationships in the database
• sampleDb.sync({force: true})
• .then(function() {
•     console.log('\nCreated database schema from model.');
• 
  
•     // Create demo: Create a User instance and save it to the database
•     User.create({firstName: 'Anna', lastName: 'Shrestinian'})
•     .then(function(user) {
•         console.log('\nCreated User:', user.get({ plain: true}));
• 
  
•         // Create demo: Create a Task instance and save it to the database
•         Task.create({
•             title: 'Ship Helsinki', dueDate: new Date(2017,04,01), isComplete: false
•         })
•         .then(function(task) {
•             console.log('\nCreated Task:', task.get({ plain: true}));
• 
  
•             // Association demo: Assign task to user
•             user.setTasks([task])
•             .then(function() {
•                 console.log('\nAssigned task \''
•             + task.title
•             + '\' to user ' + user.firstName
•             + ' ' + user.lastName);
• 
  
•                 // Read demo: find incomplete tasks assigned to user 'Anna''
•                 User.findAll({
•                     where: { firstName: 'Anna'},
•                     include: [{
•                         model: Task,
•                         where: { isComplete: false }
•                     }]
•                 })
•                 .then(function(users) {
•                     console.log('\nIncomplete tasks assigned to Anna:\n',
•                 JSON.stringify(users));
• 
  
•                     // Update demo: change the 'dueDate' of a task
•                     // findById를 findByPk로 변경하여 사용
•                     Task.findByPk(1).then(function(task) {
•                         console.log('\nUpdating task:',
•                 task.title + ' ' + task.dueDate);
•                         task.update({
•                             dueDate: new Date(2016,06,30)
•                         })
•                         .then(function() {
•                             console.log('dueDate changed:',
•                     task.title + ' ' + task.dueDate);
• 
  
•                             // Delete demo: delete all tasks with a dueDate in 2016
•                             console.log('\nDeleting all tasks with with a dueDate in 2016');
•                             Task.destroy({
•                                 where: { dueDate: {$lte: new Date(2016,12,31)}}
•                             })
•                             .then(function() {
•                                 Task.findAll()
•                                 .then(function(tasks) {
•                                     console.log('Tasks in database after delete:',
•                         JSON.stringify(tasks));
•                                     console.log('\nAll done!');
•                                 })
•                             })
•                         })
•                     })
•                 })
•             })
•         })
•     })
• })

node로 소스를 실행하면 다음과 같은 결과가 표시된다.

• PS C:\ubuntu\node\oauth2svr\mssql> node 03connect
• sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators ..\node_modules\sequelize\lib\sequelize.js:242:13
• **Node CRUD sample with Sequelize and MSSQL **
• tedious deprecated The "config.userName" property is deprecated and future tedious versions will no longer support it. Please switch to using the new "config.authentication" property instead. ..\node_modules\sequelize\lib\dialects\mssql\connection-manager.js:70:26
• tedious deprecated The "config.password" property is deprecated and future tedious versions will no longer support it. Please switch to using the new "config.authentication" property instead. ..\node_modules\sequelize\lib\dialects\mssql\connection-manager.js:70:26
• tedious deprecated The default value for `options.encrypt` will change from `false` to `true`. Please pass `false` explicitly if you want to retain current behaviour. ..\node_modules\sequelize\lib\dialects\mssql\connection-manager.js:70:26
• 
  
• Created database schema from model.
• 
  
• Created User: { id: 1,
•   firstName: 'Anna',
•   lastName: 'Shrestinian',
•   updatedAt: 2019-03-03T06:41:08.857Z,
•   createdAt: 2019-03-03T06:41:08.857Z }
• 
  
• 
  
• Created Task: { id: 1,
•   title: 'Ship Helsinki',
•   dueDate: 2017-04-30T15:00:00.000Z,
•   isComplete: false,
•   updatedAt: 2019-03-03T06:41:08.919Z,
•   createdAt: 2019-03-03T06:41:08.919Z }
• 
  
• Assigned task 'Ship Helsinki' to user Anna Shrestinian
• 
  
• Incomplete tasks assigned to Anna:
•  [{"id":1,"firstName":"Anna","lastName":"Shrestinian","createdAt":"2019-03-03T06:41:08.857Z","updatedAt":"2019-03-03T06:41:08.857Z","tasks":[{"id":1,"title":"Ship Helsinki","dueDate":"2017-04-30T15:00:00.000Z","isComplete":false,"createdAt":"2019-03-3T06:41:08.919Z","updatedAt":"2019-03-03T06:41:09.025Z","userId":1}]}]
• sequelize deprecated Model.findById has been deprecated, please use Model.findByPk instead ..\node_modules\sequelize\lib\model.js:4208:9
• 
  
• Updating task: Ship Helsinki Mon May 01 2017 00:00:00 GMT+0900 (GMT+09:00)
• dueDate changed: Ship Helsinki Sat Jul 30 2016 00:00:00 GMT+0900 (GMT+09:00)
• 
  
• Deleting all tasks with with a dueDate in 2016
• Tasks in database after delete: []
• 
  
• All done!

홈페이지의 소스를 실행하면 Sequelize에서 2개 메소드, Tedious애서 3개 메소드가 deprecated 되었음을 확인할 수 있다. 

Sequelize는 연결하는 데이터베이스 종류에 따라 자동으로 Tedious모듈을 import하는 것으로 보인다.

Tedious모듈이 deprecated 된 메소드는 Sequelize가 업데이트 되어야 해결할 수 있다.

// blog를 확인해 보면 해당 문제는 v6에서 해결을 준비하는 것으로 보인다.

findById를 findByPk로 변경한 후 실행하면 첫번째 발생했던 symbol base operator 문제도 같이 해결되는 것을 확인할 수 있다.

[참고 사이트]

https://www.microsoft.com/en-us/sql-server/developer-get-started/node/windows/step/2.html (Create Node.js Application with SQL Server)
https://blueshw.github.io/2018/02/27/async-await/ (aysnc, await를 사용한 비동기 Javascript 동기식 만들기)
https://hyunseob.github.io/2016/03/27/usage-of-sequelize-js/ (Sequelize.js 다루기)
http://docs.sequelizejs.com/manual/installation/getting-started.html (Sequelize 공식사이트)