CODEWITHSUNDEEP
node.js
codeclue
codeclue

Reputation: 237

Pass URL parameter into Database query in nodejs

I am trying to pass URL parameter into the SQL query. I have a column called "puppy_id" and one of the values is puppy1. I want to call this URL :- localhost:3000/api/puppies/puppy1 and it should execute the query in the database SELECT * FROM puppytable WHERE puppy_id='puppy1' and return the output.

I have no problem to connect to the database. But, it is showing that no data returned. I think, I am doing something wrong in executing the query.

My Code :-

index.js

var express = require('express');
var router = express.Router();

var db = require('../queries');

router.get('/api/puppies/:puppy_id', db.getPuppyStatus);

module.exports = router;

queries.js 

module.exports = {
  getPuppyStatus: getPuppyStatus
};

function getPuppyStatus(req, res, next) {
  var puppyID = parseInt(req.params.puppy_id);
  db.any('select * from puppytable where puppy_id =$1', puppyID)
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved puppies'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

queries.js is in root of project directory. It is calling from here in index.js 

var db = require('../queries');

This is my output :-

{"status":"success","data":[],"message":"Retrieved puppies"}

To debug when I am doing console.log(puppyID); , it is giving me NaN

What should be the recommended way to do this ?

Upvotes: 0

Views: 1522

Answers (2)

bmvr
bmvr

Reputation: 876

You're converting to a number a string "puppy1". This is the reason you're getting NaN.

I don't know what's the type of the id in your column. You've two options:

  1. id as number, try to send a number instead of a string and you're code should be fine.

  2. id as string, remove the parseInt.

    var puppyID = req.params.puppy_id;

Upvotes: 1

Matthew Moore
Matthew Moore

Reputation: 866

I don't see where req.params.family_id is coming from, but it looks like it should be req.params.puppy_id - as below - otherwise it would be undefined, which would not match anything in your database.

function getPuppyStatus(req, res, next) {
  var puppyID = req.params.puppy_id; 
  //call puppy_id, not family_id
  //puppy_id is also a string being passed in, it can't be turned into an integer
  db.any('select * from puppytable where puppy_id =$1', puppyID)
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved puppies'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

Upvotes: 1

Related Questions