Using DELETE to Delete Data from MSSQL | 點燈坊

If we want to use Express DELETE to delete data from MSSQL, we can use Knex and Tedious to connect.

Version

Express 4.17.1
Tedious 14.0.0
MSSQL 2017

DELETE

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.delete ('/api/articles/:id', async (req, res) => {
  let { id } = req.params

  let result = await knex ('articles')
    .delete (['*'])
    .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

20 行

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

  let result = await knex ('articles')
    .delete (['*'])
    .where ({ id })

  res.send (result [0])
})

• Use Object Destructure to destruct id from req.params
• Use Knex to generate SQL DELETE and return result :
  • knex : table to update
  • delete (['*']) : delete data and return whole Object after update, returning argument 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 deleted, then use res.send to send back

Vue

<script setup>
import axios from 'axios'

let url = 'http://localhost:8080/api/articles/26'
let { data } = await axios.delete (url)
console.log (data)
</script>

• Use axios.delete to execute DELETE, 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.delete in Axios to delete data by DELETE