Using PUT to Edit Data to PostgreSQL | 點燈坊

If we want to use Express PUT to edit data to 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

PUT

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

  let result = await knex ('articles')
    .update ({ title, content }, ['*'])
    .where ({ id })
  
  res.send (result [0])
})

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

  let result = await knex ('articles')
    .update ({ title, content }, ['*'])
    .where ({ id })
  
  res.send (result [0])
})

• Use Object Destructure to destruct id from req.params
• Use Object Destructure to destruct body from req.body
• Use Knex to generate SQL UPDATE and return result :
  • knex : table to update
  • update : Object to update, key is field and value is data
  • ['*'] : return whole Object after updated, this 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 updated, then use res.send to send back

Vue

<script setup>
import axios from 'axios'

let url = 'http://localhost:8080/api/articles/5'
let body = {
  title: 'Microsoft',
  content: 'VSCode'
}
let { data } = await axios.put (url, body)
console.log (data)
</script>

• Use axios.put to execute PUT, 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 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.put in Axios to edit data by PUT