Using GET to Return Data from MSSQL

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

Version

Express 4.17.1
Tedious 14.0.0
MSSQL 2017

GET

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.get ('/api/articles', async (req, res) => {
  let result = await knex.raw ('SELECT * FROM articles')
  res.send (result)
})

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.get ('/api/articles', async (req, res) => {
  let result = await knex.raw ('SELECT * FROM articles')
  res.send (result)
})

Use knex.raw to execute raw SQL query, it will return Promise
Use res.send to return data

get000

Vue

<script setup>
import axios from 'axios'

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

Use axios.get to execute GET, 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 Array

get001

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.get in Axios to get back result from GET