I need to fix my INSERT INTO method on my Express JS project

Question

Premise

I am trying to create a simple movie review blog web apps, based on Express.JS with some EJS for templating.

However, I stumbled upon the logic and algorithm here, So, in this project I combine Express for Backend, AXIOS for API and Postgresql for database.

As you can see, I have an array to store result from AXIOS:

let fetchResult = [
  {
    title: "Please fetch some data",
    image: "/images/nodata.png",
  },
];

and this is a POST /new endpoint, to fetch data from omdb.com API:

app.post("/new", async (req, res) => {
  const title = req.body.movietitle;
  try {
    const result = await axios.get(API_URL, {
      params: {
        apiKey: API_KEY,
        t: title,
      },
    });

    fetchResult.push({
      title: result.data.Title,
      year: result.data.Year,
      genre: result.data.Genre,
      director: result.data.Director,
      image: result.data.Poster,
      status: result.data.Response,
    });
    // console.log(fetchResult[0]);

    res.redirect("/new");
  } catch (error) {
    console.log(error);
  }
});

Once the button on EJS Pressed, it will fetch a data like cover image, title, years etc.

This is before the GET call to the Axios API:

and this is after GET API successfully:

Problem

Those simple steps work normally. Until...

I am trying to insert the data to the database. I have 2 tables in my database that store movie details, such as title, year, image etc (it comes from Axios.get API) and the table is called movie.

At the same time, I also have another table named post that stores the data of every blog post about movie review, such as title of the blog post, articles text, rating that I give, and the foreign key under the name movie_id that connect to the id in the movie table.

Here is my current attempt:

app.post("/submit", async (req, res) => {
  const postTitle = req.body.titlepost;
  const articles = req.body.articles;
  const rating = req.body.rating;

  fetchResult.forEach((item) => {
    const title = item.title;
    const year = item.year;
    const genre = item.genre;
    const director = item.director;
    const image = item.image;
  })
  const client = await pool.connect();
});

The hard part is on foreign key setting I guess, I am noob, I didn't quite understand the table relation.

1. I want to do INSERT INTO into the post table along with movie at the same time under /submit endpoint while doing POST request.
2. I want to have try catch error, if the movie data already exists, abort the blog posting process.

Answer 1

For this you should use transactions to perform multiple insertion, and rollback if there were some errors, so

app.post("/submit", async (req, res) => {
  const postTitle = req.body.titlepost;
  const articles = req.body.articles;
  const rating = req.body.rating;

  fetchResult.forEach((item) => {
    const title = item.title;
    const year = item.year;
    const genre = item.genre;
    const director = item.director;
    const image = item.image;
  })
  const client = await pool.connect();

  try{
   // perform movie insert
   const query = `INSERT INTO ...`
   const result = client.query(query)

   const movieId = // parse the result properly and get your movie id

   // perform post insert

   const query = `INSERT INTO POST ...` // here you will use your data + 
   movieId


  } catch(err){
     await client.rollback();
  } finally{
    await client.commit();
    client.close();
  }
});

If you need more detailed code instead of pseudo code i can update my answer later, hope it helps, below some useful references

Postgres Transactions db reference

pg driver node transasction reference