Using DELETE to Delete Data from PostgreSQL | 點燈坊

If we want to use Express DELETE to delete 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

DELETE

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

  let result = await knex ('articles')
    .delete (['*'])
    .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.delete ('/api/articles/:id', async (req, res) => {
  let { id } = req.params

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

• Use Object Destructure to destruct id from req.params
• Use Knex to generate SQL DELETE and return result :
  • knex : table to update
  • delete (['*']) : delete data and return whole Object after update, returning argument 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 deleted, then use res.send to send back

Vue

<script setup>
import axios from 'axios'

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

• Use axios.delete to execute DELETE, 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.delete in Axios to delete data by DELETE