How to select a specific record by Id in a database using sequelize?

Omar Martin
Omar Martin used Ask the Experts™
on
I would like to select a record from a mysql database by "id" using sequelize.  I currently can do it with a mysql query and it works but I am receiving a null result when attempting to do it in sequelize.  Here is the query in my mysql which works and the query in sequelize which does not work. What am I doing wrong with the syntax in the sequelize version?




``````````````````````````````````````````````````````````````````````````````
        (1)

        router.get("/station/number/:id", (req, res) => {
      db.query(
            "SELECT * FROM stores WHERE id = ?",
            [req.params.id],
            (err, rows, fields) => {
                  if (!err) res.send(rows);
                  else console.log(err);
            }
      );
});


       (2)
 
      router.get("/station/number/:id", (req, res) =>

      Store.findOne ({
      where: {id:'?'}})
      .then(data=>{
     res.send({stores:data})
      })
     .catch(err => console.log(err)));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
The sequelize seems to be rarely used.

If you get no comments here, contact the sequelize developer for assistance.
Omar MartinBridgemaker

Author

Commented:
What do most developers use instead of sequelize?

I like the idea of being able to use the same code across various databases..ie. Mysql, Firebase, MariaDb, Postgre....etc. you can plug in quickly.
Prepress/OMS Specialist
Commented:
How does the sequelize version know which id to search for?

Your SQL version:
router.get("/station/number/:id", (req, res) => {
  db.query(
    "SELECT * FROM stores WHERE id = ?",
    [req.params.id],
    (err, rows, fields) => {
      if (!err) res.send(rows);
        else console.log(err);
    }
  );
});

Open in new window


Your sequelize version:
Store.findOne({
  where: {id:'?'}
  }).then(data=>{
    res.send({stores:data})
  }).catch(err => console.log(err))
);

Open in new window

Note that on line 4 in your SQL version, you are referencing req.params.id, but I don't see anywhere in the sequelize version where that happens.

I think line 2 in your sequelize version should look like the following:
  where: {id:req.params.id}

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Omar MartinBridgemaker

Author

Commented:
Jim:

Yes.....thank you.....I will review it.....are you handy with sequelize, I could use your help.
Omar MartinBridgemaker

Author

Commented:
Jim.....Here is a solution I tracked down.....

router.get("/station/number/:id", (req, res) =>

Store.findByPk(req.params.id)
   .then(stores =>{
     res.json(stores);
   })
   .catch(err => {
     console.log(err);
     res.status(500).json({msg: "error", details:err});
   }));
Omar MartinBridgemaker

Author

Commented:
It use to be .......findById..........but version 5 has been changed that to .......findByPk
Jim RiddlesPrepress/OMS Specialist

Commented:
I have a passing familiarity with sequelize, but would definitely call myself an Expert on the subject.  Reading the documentation for findOne is where I noticed the issue.  findByPk is probably the best solution for your specific request.  Did my suggestion not work?
Omar MartinBridgemaker

Author

Commented:
Yes...it worked.......I will mark it as the solution however, I have other routes written in sql query which I would like help with to convert them to sequelize......can I count on your assistance Sir?
Jim RiddlesPrepress/OMS Specialist

Commented:
I'll do what I can.  Send me a direct message with a link to the questions you create and I will see how I can assist you.
Omar MartinBridgemaker

Author

Commented:
Thanks...
Omar MartinBridgemaker

Author

Commented:
Thank you David and Jim for your assistance....It was greatly appreciated.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial