Node.js - Express + MSSQL

node.js 使用 express 讀取 mssql 應用。

 

 

先前曾發過一篇使用 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

nodemon logo

全域安裝。

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