How to send array of objects to mysql database (React, NodeJS)-CodePudding

I'm new to NodeJS and Mysql databases. I make small project in React with backend. It's week calendar, where students will be able to book lessons. Right now I'm making admin panel where teacher can create a week calendar with selected available hours. All selected hours are gathered in state, and they look like this:

[
{year: '2023', user_uuid: 2, dayId: 2, hourId: 0, hour: '7:00'}
{year: '2023', user_uuid: 2, dayId: 5, hourId: 0, hour: '7:00'}
{year: '2023', user_uuid: 2, dayId: 4, hourId: 0, hour: '7:00'}
{year: '2023', user_uuid: 2, dayId: 4, hourId: 1, hour: '7:45'}
etc.]

What I'm trying to do is to send this state to my table in Mysql data base. I've created backend in NodeJS and it looks like this:

(Code edited with demonholden suggestions).

    const express = require("express");
    const mysql = require("mysql");
    const cors = require("cors")
    
    const app = express();
    
    const db = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "",
      database: "meetapp"
    }) 
    
    app.use(express.json())
    app.use(cors())
    
    app.get("/", (req, res) =>{
      res.json("hello this is the backend")
    })
    
    app.post("/terms", (req, res) => {
    const q =  "INSERT INTO terms (year, user_uuid, dayId, 
      hourId, hour) VALUES ?"
    
    const bulkValues = req.body.map((values) => [
      values.year,
      values.user_uuid,
      values.dayId,
      values.hourId,
      values.hour,
    ]);

  console.log('bulk is', bulkValues)

  db.query(q, [bulkValues], (err, data) => {
      if(err) return res.json(err)
      return res.json(data)
    })
    
    app.listen(8801, () => {
      console.log(`Backend works! Listening on 8801`);
    });

And this is my post function in React code:

const saveWeek = async e => {
    e.preventDefault()
    try {
        await axios.post("http://localhost:8801/terms", sortedTerms)
    } catch (err) {
        console.log(err)
    }
}

"sortedTerms" is the state with all gathered hour data, which I mentioned above. When I run onClick function saveWeek it sends all gathered data to backend. Console log in backend shows this:

bulk is [
  [ '2023', 2, 0, 0, '7:00' ],
  [ '2023', 2, 1, 0, '7:00' ],
  [ '2023', 2, 2, 0, '7:00' ],
  [ '2023', 2, 3, 0, '7:00' ],
  [ '2023', 2, 3, 1, '7:45' ],
  [ '2023', 2, 3, 2, '8:30' ],
  [ '2023', 2, 6, 20, '22:00' ],
  [ '2023', 2, 5, 20, '22:00' ]
]

EDIT: I've used demonholden suggestion and now code works fine. It saves all data to mysql database. Hope it will be helpfull for other devs.

CodePudding user response：

If you are sending the entire array in the post request then you will need to specify an index position in the values assignment in your Express '/terms' post route:

const values = [
  req.body[0].year,
  req.body[0].user_uuid,
  req.body[0].dayId,
  req.body[0].hourId,
  req.body[0].hour,
];

Alternatively, just send a single object in the Axios request:

axios.post('http://localhost:8801/terms', sortedTerms[0]);

If, however, you wanted to do a bulk insert with the entire array, then you will need to map req.body to an array of arrays, with each sub-array containing the values of each entry and assign the array to values:

const bulkValues = req.body.map((values) => [
  values.year,
  values.user_uuid,
  values.dayId,
  values.hourId,
  values.hour,
]);

Hope this helps.