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

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 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