Using GET to Return Data from PostgreSQL | 點燈坊

If we want to use Express GET to return data from PostgreSQL, we can use Knex and Node-postgres to connect.

Version

Express 4.17.1
Knex 0.95.11
Node-postgres 8.7.1
PostgreSQL 14.0

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: 'pg',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'admin',
    password: '12345',
    database: 'DBLab'
  },
  searchPath: ['public']
})

app.get ('/api/articles', async (req, res) => {
  let { rows } = await knex.raw ('SELECT * FROM articles')
  res.send (rows)
})

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

Line 9

let knex = Knex ({
  client: 'pg',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'admin',
    password: '12345',
    database: 'DBLab'
  },
  searchPath: ['public']
})

Use Knex to connect to database :

• client: 'pg' : connect to PostgreSQL
• host：setup server
• port：setup port
• user：setup id
• password：setup password
• database：setup database
• searchPath：setup searching path, tables would be created under public

Line 21

app.get ('/api/articles', async (req, res) => {
  let { rows } = await knex.raw ('SELECT * FROM articles')
  res.send (rows)
})

• Use knex.raw to execute raw SQL query, it will return Promise
• Use Object Destructure to destruct rows, and then use res.send to return data

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

Conclusion

• Just install Knex and Node-progress library to connect PostgreSQL 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