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

Question

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.

damonholden · Accepted Answer · 2023-01-14 23:08:33Z

1

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.

edited Jan 14, 2023 at 23:08

answered Jan 14, 2023 at 22:08

damonholden

1,1827 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MarcinB Over a year ago

Thanks for quick answer. It kinda works, right now code saves in my database the first object in array (earlier I got just null, now it saves full object). But how can I save all objects? One under another?

damonholden Over a year ago

I have modified the answer to accommodate this.

MarcinB Over a year ago

Thanks again Damon, I think we're almost there :) I've corrected my code, and right now it sends all data to backend (conosle log sees data), but they're not saved and visible in mysql data base. I've edited code above so you can see my actuall code, with your suggestions. Is there still something missing? Maybe something with db.query request?

MarcinB Over a year ago

I've corrected this insert ("INSERT INTO terms (year, user_uuid, dayId, hourId, hour) VALUES ?") and now everything works fine. Data is saved in mysql data base.

damonholden Over a year ago

glad to here you got this sorted. Happy to help.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related