Using PUT to Edit Data to MSSQL | 點燈坊

If we want to use Express PUT to edit data to MSSQL, we can use Knex and Tedious to connect.

Version

Express 4.17.1
Tedious 14.0.0
MSSQL 2017

PUT

import express from 'express'
import cors from 'cors'
import Knex from 'knex'

let app = express ()
app.use (cors ())
app.use (express.json ())

let knex = Knex ({
  client: 'mssql',
  connection: {
    host: 'mssql-2017',
    port: 1433,
    user: 'sa',
    password: '111111',
    database: 'DBLab'
  }
})

app.put ('/api/articles/:id', async (req, res) => {
  let { id } = req.params
  let { title, content } = req.body

  let result = await knex ('articles')
    .update ({ title, content }, ['*'])
    .where ({ id })

  res.send (result [0])
})

app.listen (8080, _ => console.log ('Node listen on port: 8080'))

Line 9

let knex = Knex ({
  client: 'mssql',
  connection: {
    host: 'mssql-2017',
    port: 1433,
    user: 'sa',
    password: '111111',
    database: 'DBLab'
  }
})

Use Knex to connect to database :

• client: 'mssql' : connect to MSSQL
• host：setup server
• port：setup port
• user：setup id
• password：setup password
• database：setup database

Line 20

app.put ('/api/articles/:id', async (req, res) => {
  let { id } = req.params
  let { title, content } = req.body

  let result = await knex ('articles')
    .update ({ title, content }, ['*'])
    .where ({ id })

  res.send (result [0])
})

• Use Object Destructure to destruct id from req.params

• Use Object Destructure to destruct body from req.body

• Use Knex to generate SQL UPDATE and return result :

  • knex : table to update
  • update : Object to update, key is field and value is data
  • ['*'] : return whole Object after updated, this is optional
  • where : where condition for =, key is field and value is data

• Use Array Destructure to destruct first row of Array, this is the Object we just updated, then use res.send to send back

Vue

<script setup>
import axios from 'axios'

let url = 'http://localhost:8080/api/articles/3'
let body = {
  title: 'Facebook',
  content: 'React'
}
let { data } = await axios.put (url, body)
console.log (data)
</script>

• Use axios.put to execute PUT, it will return Promise. We have to use top level await to deal with Promise
• Axios will put result in data property, we will use Object Destructure to destruct data to get back Object

Conclusion

• Just install Knex and Tedious library to connect MSSQL in Express
• Although we can use Knex to generate SQL query, but DQL is more complex so I usually use knex.raw to execute raw SQL query, and let Knex to generate DML for us
• Knex will return Promise, so we have to use async await to deal with Promise
• We can use axios.put in Axios to edit data by PUT