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套件
- db:require('../config/db')
- 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 可從此取得)
實作 - 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
相關參考
iT邦幫忙 - Day16 - Node.js 串接 MS-SQL Server
以上為簡單的 Node.js Express + MSSQL
有勘誤之處,不吝指教。ob'_'ov