Robby - 全端的 Front-End Engineer

先前曾發過一篇使用 express + mysql，

本篇介紹使用 express + mssql，

若對 node.js express 不熟悉，建議可以先看此篇介紹 Node.js Express4。

樣版部分則改使用 jade（pug），不熟悉樣樣板使用方式可以先看看～

本篇所需資料庫以及完整範例已放置 Github 上，

如果對你有所幫助，不妨丟一顆 ★ 吧~ 

 

實作結果畫面

利用 npm mssql  （Github）套件，進行使用者清單列表管理，包含新增、編輯、刪除。

資料表清單。

 

 

框架安裝

語法：express 專案名稱。

express mssqldemo

cd mssqldemo

npm install

 

 

資料庫套件安裝

安裝 mssql。

npm install mssql

 

 

連線設定

連線內容建議移置外部另外寫，

在專案底下建立 config 資料夾，並新增一個 db.js ，

當然，資料庫的帳號密碼就是依您的環境自行修改囉！

const db =
    {
        "user": "sa",
        "password": "e2345678",
        "server": "localhost",
        "database": "ExpressDemo"
    };

module.exports = db;

 

 

SQL 應用

npm mssql 語法部分，本篇使用較為簡易的 Callbacks 方式，

該套件也提供了許多種方式，若環境許可，也可以改使用：

• Async/Await（ES7）
• Promises（ES6）
• Callbacks
• Streaming

 

Connect 語法說明

建立簡單的連線，使用 connect，帶入資料庫連線資訊，

結束後以 close() 關閉連線資訊。

sql.connect(db, function (err) {

　　sql.close();
});

 

Query 語法說明

建立一個 query，並寫入一些 sql 語法，callback 部分有 err、result，

而在 result 陣列中， result.recordset 則是我們要的資料集合，

想要了解 result 結構，直接 console.log( result ); 就能清楚明白囉！

var request = new sql.Request();
request.query("select * from UserList", function (err, result) {

});

 

Parameter 語法說明

通常在使用 sql 查詢，為了防止 SQL Injection，都會使用預儲式（Parameter）。

var request = new sql.Request();
request.input('id', sql.Int, req.body.id);
request.input('username', sql.NVarChar(50), req.body.username);

也可簡寫成：

var request = new sql.Request();
request.input('id', sql.Int, req.body.id)
       .input('username', sql.NVarChar(50), req.body.username)

需注意的是，request （get / post）過來的資料取得方式不太相同：

• get：req.params.[網址列參數]
• post：req.body.[表單中的 name]

而資料型別的方式也有很多種，簡單列舉幾項，選擇時須注意：

• sql.Int
• sql.NVarChar()
• sql.Date
• sql.DateTime
• sql.Decimal
• sql.Float

 

 

實作 - 環境前置

為了方便撰寫，編輯器不妨安裝一些相關的套件吧～

個人是使用 VScode，如果你也使用這個，可以參考這些套件：

 

本範例路由結構

路徑	說明	SQL	方法
/	首頁（根目錄）	select * from UserList	GET
/edit/:id	編輯頁面（id primary key）	select * from UserList where id = @id	GET
/update	此頁由 edit 頁面 post 過來的 form，以進行更新	update UserList set pwd=@pwd ...	POST
/add	route 接收到 /add 時，render add.jade 樣板	-	GET
/add	form 將本頁資料 post 到後端進行 insert	insert into UserList (...) values (...)	POST
/delete/:id	刪除指定 id 資料	delete from UserList where id = @id	GET

 

所有的路由皆在 routes / index.js 編輯，方便大家理解，統一在此，

由於要進行資料庫的讀取使用，因此首先引用 MSSQL 套件。

var express = require('express');
var router = express.Router();

var db = require('../config/db');
var sql = require('mssql');

引用資料庫設定檔案以及MSSQL套件

1. db：require('../config/db')
2. sql：require('mssql')

 

 

實作 - Select

【routes / index.js】

/* GET home page. */
router.get('/', function (req, res, next) {

  sql.connect(db, function (err) {
    if (err)
      console.log(err);

    var request = new sql.Request();
    request.query("select * from UserList", function (err, result) {

      if (err) {
        console.log(err)
        res.send(err);
      }
      // var rowsCount = result.rowsAffected;
      sql.close();
      res.render('index', {
        route: 'home',
        data: result.recordset
      });

    }); // request.query
  }); // sql.conn
}); // get /

• result.rowsAffected：取得的總筆數，與 result.recordset.legnth 相同
• res.render：回傳方式帶入的樣板以及相關參數，index 就是 views / index.jade
• route:'home'：回傳這個用意是為了讓 jade 方便判斷應載入的樣板
• data：將取得的結果 result.recordset 指定給 data 變數

 

【views / layout.jade】

doctype html
html
  head
    title Express With MSSQL
    link(href='/stylesheets/style.css', rel='stylesheet')
    link(href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css", rel="stylesheet")

  body
    h1 Express With MSSQL - #{route}
    p template by jade (Pug) 

    case route
      when 'home': block home
      when 'edit': block edit
      when 'add': block add
      default: block home

• #{route}：從 res 回傳的變數中取得，在 jade（pug） 語法中，使用 #{ 變數 }，即可取出
• 補充上述：如果 element 沒有要塞入其他字串，則可寫成 →  h1=變數，就不需要 #{ 變數 }，例如：h1=route
• case ... when ... default：此為 jade 語法中的條件式語法
• block [自訂名稱]：此對應到 jade 檔案中，有 block [自訂名稱] 相同的區塊，與 ASP.NET MVC 中的 section 之意相同

 

【views / index.jade】

extends layout

block home
    label Total： #{data.length}
    hr
    a(href="/add", title="title").btn Add 
    table.list
      thead
        tr
          th UserName
          th UserID
          th Passwd
          th Email
          th 
      each item in data
        tr
          td= item.username
          td= item.userid
          td= Array(item.pwd.length + 1).join('*')
          td= item.email
          td  
            a(href="/edit/#{item.id}", title="Edit").icon
              i(aria-hidden="true").fa.fa-pencil-square
            a(href="/delete/#{item.id}", title="Delete" onclick="return confirm('Delete user： #{item.username} ?');").icon
              i(aria-hidden="true").fa.fa-trash

• block home：呼應 layout.jade 中的 block home
• each [變數] in [陣列]：即為我們熟悉的 for each
• （補充）如何在 jade 中寫出帶有 attribute 或 class 的 element：
      a( attribute=" 內容 " ).[樣式] [文字內容]
      即為 <a attribute="內容"  class=[樣式] > [文字內容] </a>

 

啟動系統

為了方便系統 debug，建議可以使用套件 nodemon

全域安裝。

npm install -g nodemon

 安裝到當前專案，並列入 package.json 初始化安裝套件之一（即 npm install 時的安裝清單）。

npm install --save-dev nodemon

修改 package.json。

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

執行吧~

npm run dev

打開瀏覽器應該會是如下圖（畫面如有不同，應該是 css 樣式差異，本篇有提供完整範例，css 可從此取得）

http:localhost:3000

 

 

實作 - Update

當點選首頁的編輯按鈕後（鉛筆圖案），會利用 get 方式到編輯頁面 http://localhost:3000/edit/id。

【routes / index.js】

/* GET Edit page. */
router.get('/edit/:id/', function (req, res, next) {

  sql.connect(db, function (err) {
    if (err)
      console.log(err);

    var request = new sql.Request();
    request.input('id', sql.Int, req.params.id)
    request.query("select * from UserList where id=@id", function (err, result) {

      if (err) {
        console.log(err)
        res.send(err);
      }
      // var rowsCount = result.rowsAffected;
      sql.close();
      res.render('edit', {
        route: 'edit',
        data: result.recordset[0]
      });

    }); // request.query
  }); // sql.conn
});

• 我們利用 req.params.id，即可從網址列取得 id 參數
• data：由於只會有一筆資料，因此我們直接回傳 result.recordset[0]

 

在 views / 底下新增檔案 edit.jade。

【views / edit.jade】

extends layout

block edit
    label UserName： #{data.username}
    hr
    form(method='POST' action='/update')
        input(type="hidden" name="id" value=data.id)
        table.list-edit
            tr
                th UserID
                td=data.userid     
            tr
                th Passwd
                td
                    input(type="password" name="pwd" value=data.pwd).text
            tr
                th UserName
                td 
                    input(type="text" name="username" value=data.username).text
            tr
                th Email
                td
                    input(type="email" name="email" value=data.email).text
            tr
                td(colspan="2")
                    button(type="submit").btn Save
                    button(type="button" onclick="javascript: location.href='/'; ").btn Back

• 為了讓後端資料當前 id，暫時塞到 input hidden 中
• form部分則會利用 post 方式到 /update

 

【routes / index.js】

/* POST Edit page. */
router.post('/update', function (req, res, next) {

  sql.connect(db, function (err) {
    if (err)
      console.log(err);

    var request = new sql.Request();
    request.input('id', sql.Int, req.body.id)
      .input('username', sql.NVarChar(50), req.body.username)
      .input('pwd', sql.NVarChar(50), req.body.pwd)
      .input('email', sql.NVarChar(50), req.body.email)
      .query('update UserList set username=@username,pwd=@pwd,email=@email where id=@id', function (err, result) {

        if (err) {
          console.log(err);
          res.send(err);
        }
        sql.close();
        res.redirect('/');
      });
  });
});

• 由於資料從 form 過來，故使用 req.body 來取得表單欄位資料
• 更新完畢後直接利用 req.redirect('/')  重新導向至首頁

 

完成後切至畫面，當點選其中一筆資料時，就會順利地出現資料囉～

http:localhost:3000　→　http://localhost:3000/edit/2

 

 

實作 - Insert

當點選首頁的新增按鈕後（add），會利用 get 方式到編輯頁面 http://localhost:3000/add。

【routes / index.js】

/* GET Add page. */
router.get('/add', function (req, res, next) {
  res.render('add', {
    route: 'add',
  });
});

 

相關方法與 update 大同小異，至 veiws 底下新增 add.jade 。

【views / add.jade】

extends layout

block add
    label Create
    hr
    form(method='POST' action='/add')
        table.list-edit
            tr
                th UserID
                td
                    input(type="text" name="userid").text
            tr
                th Passwd
                td
                    input(type="password" name="pwd").text
            tr
                th UserName
                td 
                    input(type="text" name="username").text
            tr
                th Email
                td
                    input(type="email" name="email").text
            tr
                td(colspan="2")
                    button(type="submit").btn Submit
                    button(type="button" onclick="javascript: location.href='/'; ").btn Back

 

當新增頁面提交後，會將表單 post 至後端。

【routes / index.js】

/* POST Add page. */
router.post('/add', function (req, res, next) {

  sql.connect(db, function (err) {
    if (err)
      console.log(err);

    var request = new sql.Request();
    request.input('userid', sql.NVarChar(50), req.body.userid)
      .input('pwd', sql.NVarChar(50), req.body.pwd)
      .input('username', sql.NVarChar(50), req.body.username)
      .input('email', sql.NVarChar(50), req.body.email)
      .query('insert into UserList (userid, pwd, username, email) values (@userid, @pwd, @username, @email)', function (err, result) {

        if (err) {
          console.log(err);
          res.send(err);
        }
        sql.close();
        res.redirect('/');
      });
  });
});

 

實際畫面，應為空白，試著新增看看吧～

 

 

實作 - Delete

還記得剛開始我們編寫樣板的時候，有寫到刪除的按鈕嗎～？

由於 href="/delete/id" 是 get 方法，因此在後端我們直接進行刪除，再重新導向回首頁即可。

【views / index.jade】

【routes / index.js】

/* GET Delete page. */
router.get('/delete/:id', function (req, res, next) {
  sql.connect(db, function (err) {
    if (err)
      console.log(err);

    var request = new sql.Request();
    request.input('id', sql.Int, req.params.id)
      .query('delete from UserList where id=@id', function (err, result) {

        if (err) {
          console.log(err);
          res.send(err);
        }
        sql.close();
        res.redirect('/');
      });
  });
});

 

實際畫面，點選垃圾桶，觸發 confirm() ，就可由 callback 決定是否繼續刪除了！

 

 

本實作範本 + 資料表下載

範例存於小弟的 Github 

有提供資料庫，可先匯入。

專案 clone

git clone https://github.com/explooosion/Node.js-Express-With-MySQL.git

進入專案

cd express-mssql

安裝套件

npm install

使用 nodemon 執行（通常用於 debug）

npm install nodemon -g
npm run dev

也可使用 pm2 執行（通常用於正式發布）

npm install pm2 -g
npm start
npm stop

 

 

相關參考

Github - node-msssql

npm mssql

iT邦幫忙 - Day16 - Node.js 串接 MS-SQL Server

 

以上為簡單的 Node.js Express + MSSQL

 

有勘誤之處，不吝指教。ob'_'ov