CODEWITHSUNDEEP
javascriptreactjspostgresql
Josh Susa
Josh Susa

Reputation: 415

How to modify a PSQL table from React?

I set up a SQL database in PSQL that stores all the data from the client which uses React. I'm trying to add a new value to one of the tables (named conversations) in my database which uses the following SQL code.

UPDATE conversations 
SET "dateLastAccessed" = <date> 
WHERE id = <id>;

The values for <date> and <id> would be whatever parameters the user specifies. The problem is that I'm not sure how I can modify a table from within React. For my program, when data is being stored inside of the database, it would use a create function on the server side to create a new row. For example, in my conversations table, a new row would be created like this.

router.post("/", async (req, res, next) => {
  const {id, dateLastAccessed} = req.body;
  try {
    await Conversation.update({ dateLastAccessed: dateLastAccessed }, {
      where: {
        id: id,
      }
    });
    res.json(req.body);
  } catch (error) {
    next(error);
  }
});

This will add a new row to the conversation table with an id, the two id's that the user specified, and the date that it was created. The problem is that I have no idea how to access this function to see exactly what's going on. Here's the Conversation model.

const { Op } = require("sequelize");
const db = require("../db");

const Conversation = db.define("conversation", {});

Conversation.findConversation = async function (user1Id, user2Id) {
  const conversation = await Conversation.findOne({
    where: {
      user1Id: {
        [Op.or]: [user1Id, user2Id]
      },
      user2Id: {
        [Op.or]: [user1Id, user2Id]
      }
    }
  });
  return conversation;
};

module.exports = Conversation;

And here's db.js.

const Sequelize = require("sequelize");

const db = new Sequelize(process.env.DATABASE_URL || "postgres://localhost:5432/messenger", {
  logging: false
});

module.exports = db;

From what I can see, it looks like these functions are from PSQL but I've been looking at everything in my database and I can't find any functions like these.

So, I'm trying to figure out how to view functions like the create function to see exactly how they work and I also want to make a new function to modify an existing row column value in my table. Does anyone know anything about this?

Upvotes: 0

Views: 431

Answers (1)

Alan Willian Duarte
Alan Willian Duarte

Reputation: 134

you'll do that exactly the same way you did with the create part. You will send the update data to your server side, and then you will use (as sequelize docs says):

let id = 1; // the id of the conversation
let date = "2021-08-27"; // date of the conversation
await Conversation.update({ dateLastAccessed: date }, {
  where: {
    id: id
  }
});

Edit:

after searching a little, I noticed that when you are defining the table Conversations, you are not telling what columns it has. You need to provide this for sequelize so it can do its job properly. Try this:

const Conversation = db.define("conversation", {
  id: {
    type: DataTypes.INTEGER, //has to be the type it is on database
    allowNull: false
  },
  dateLastAccessed: {
    type: DataTypes.DATE
  }
})

Upvotes: 1

Related Questions