Using POST to Add Data to PostgreSQL | 點燈坊

If we want to use Express POST to add 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

POST

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

  let result = await knex ('articles')
    .insert ({ title, content }, ['*'])

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

  let result = await knex ('articles')
    .insert ({ title, content }, ['*'])

  res.send (result [0])
})

• Use Object Destructure to destruct POST data from req.body
• Use Knex to generate SQL INSERT and return result :
  • knex : table to insert
  • insert : Object to insert, key is field and value is data
  • ['*'] : return whole Object after inserted, this is optional
• Use Array Destructure to destruct first row of Array, this is the Object we just inserted, then use res.send to send back

Vue

<script setup>
import axios from 'axios'

let url = 'http://localhost:8080/api/articles'
let body = {
  title: 'Title 1',
  content: 'Content 1'
}
let { data } = await axios.post (url, body)
console.log (data)
</script>

• Use axios.post to execute POST, 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.post in Axios to add data by POST