How to add insert objects by id

Question

So I'm sending data from my React app to my PostgreSQL database using NodeJS

ReactJS

let data = files.map(( { name }, index, album_id ) => ({ name, index : index, album_id: 
params.albumId }));

    let request = new Request(`http://localhost:3000/albums/${params.albumId}/songs`, {
    method: 'POST',
    headers: new Headers({ 'Content-Type': 'application/json' }),
    body: JSON.stringify(data),
    });

     fetch(request)
      .then((response) =>
        response.json())
          .then((data) => {
          })
          .catch((error) => {
            console.log(error)
          })

NodeJS - queries.js

const addSong = (request, response) => {
  const id = parseInt(request.params.id)
  const { name, link, index, album_id } = request.body;

for (var i = 0; i < request.body.length; i++) {
  pool.query('INSERT INTO songs (name, link, index, album_id) VALUES ($1, $2, $3, $4) ON 
  CONFLICT (index) DO NOTHING RETURNING *', [request.body[i].name, request.body[i].link, 
  request.body[i].index, request.body[i].album_id], (error, results) => {
    if (error) {
     throw error
     console.log(error)
     } else {
       console.log("addSong " + JSON.stringify(results.rows) + id);

   }
  });
 }
}

NodeJS - index.js

const app = express();
const db = require('./queries');

app.post('/albums/:id/songs', db.addSong)

The index constraint is a unique constraint saying index can't be added more than once if it has the same value. My issue is the constraint is being set on each object even if the album_id is different but I want each page to be able to have separate index constraints.

Here is what my json looks like from my GET request:

My question is how can I put something in my code in my POST request like INSERT WHERE album_id = id which id is parseInt(request.params.id)

I'm not sure what you're asking. `id` is the album id, which is also in each of your objects, right? Are you getting a postgresql error? Do you just want to ignore songs that are posted that don't have the same album id as specified in the route you called? That would just be `let matchedSongs = request.body.filter(x => x.album_id === parseInt(request.params.id));` right? — Jason Goemaat, Jan 30 '20 at 22:25
@JasonGoemaat Sorry I'm kind of new to Node. Where in my code would I put that? — DDavis25, Jan 30 '20 at 22:33
Sorry, I haven't used postgresql from node. I see that the query uses callbacks, so you would want to wait for all of them to complete before sending the response... That should probably be the focus of your question or a separate one... — Jason Goemaat, Jan 30 '20 at 22:48
@JasonGoemaat it's not letting me send a response because it says "Cannot set headers after they are sent to the client". I like your code though. I'm just curious on where in my code I should have it and how I can implement it? — DDavis25, Jan 31 '20 at 16:20
You'll need some way to send the response once the last callback has returned. Right now I think you make all the calls to `query()` and the function completes, but the callbacks haven't been called yet. I'd try using the promise version and `pomise.all(xxx).then(() => sendResponse())` to wait for all of them to complete and send the response... — Jason Goemaat, Jan 31 '20 at 18:54
I'd create a base method to return a hard-coded response to make sure it works. Then try a single hard-coded query and return the results to make sure that works. Then try adding multiple hard-coded queries to make sure that works. Then try adding in your logic to do the updates... — Jason Goemaat, Jan 31 '20 at 18:55
@JasonGoemaat what would the response be and do I still implement the code the you put? — DDavis25, Jan 31 '20 at 20:09

Mohan Ohanra · Answer 1 · 2020-02-01T13:14:52.597

Try this way

/// Import async module from npm

function insertData(item,callback) {
    const { name, link, index, album_id } = item;
    client.query('INSERT INTO songs (name, link, index, album_id) VALUES ($1,$2,$3,$4)', [
        name, link, index, album_id
         ], 
    function(err,result) {
      // return any err to async.each iterator
     if(err)
      return callback(err);

      console.log("addSong " + JSON.stringify(results.rows) + id);
        callback(err, result);
    })
  }
  const addSong = (request, response) => {
  async.each(request.body,insertData,(err) => {
    // Release the client to the pg module
    done();
    if (err) {
      return console.error('error running query', err);
    }
   // callback here
  });
}

And For your reference Bulk insert into Postgres with brianc/node-postgres

Multi-row insert with pg-promise

Thanks, but how does it make it so the index constraint is not ran on each object the same. Because I want the request to be ran by id or something so that each page would act different since on my front end I'm fetching the data by album id — DDavis25, Feb 01 '20 at 19:00

How to add insert objects by id

1 Answers1