Using POST to Add Data to MSSQL | 點燈坊

If we want to use Express POST to add data to MSSQL, we can use Knex and Tedious to connect.

Version

Express 4.17.1
Tedious 14.0.0
MSSQL 2017

POST

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.post ('/api/articles', async (req, res) => {
  let { title, content } = req.body

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

  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.post ('/api/articles', async (req, res) => {
  let { title, content } = req.body

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

  res.send (result [0])
})

• Use Object Destructure to destruct POST data from req.body
• Use Knex to generate SQL INSERT and return result :
  • knex : table to insert
  • insert : Object to insert, key is field and value is data
  • ['*'] : return whole Object after inserted, this is optional
• Use Array Destructure to destruct first row of Array, this is the Object we just inserted, then use res.send to send back

Vue

<script setup>
import axios from 'axios'

let url = 'http://localhost:8080/api/articles'
let body = {
  title: 'Title 1',
  content: 'Content 1'
}
let { data } = await axios.post (url, body)
console.log (data)
</script>

• Use axios.post to execute POST, 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.post in Axios to add data by POST