Express.js PUT Handler Problem

Get Help
#1

Hi,

I’m working through the Express.js project ‘X-Press Publishing’, and encountering some problems on my PUT Handler when running npm test. While the other handlers I’ve written so far (GET, POST & DELETE) are passing as expected. The specific error I’m seeing in an internal server error (500). It says there a syntax error but I cannot see where.

I would appreciate anyone to review my code and let me know if there’s a mistake, or any other advice. Thanks!

Excerpt from artists.js

// POST HANDLER
artistsRouter.post('/', (req, res, next) => {
  const name = req.body.artist.name;
  const dateOfBirth = req.body.artist.dateOfBirth;
  const biography = req.body.artist.biography;
  const isCurrentlyEmployed = req.body.artist.isCurrentlyEmployed === 0 ? 0 : 1;
  if (!name || !dateOfBirth || !biography) {
    return res.sendStatus(400);
  }

  const sql = 'INSERT INTO Artist (name, date_of_birth, biography, is_currently_employed)' +
    'VALUES ($name, $dateOfBirth, $biography, $isCurrentlyEmployed)';
  const values = {
    $name: name,
    $dateOfBirth: dateOfBirth,
    $biography: biography,
    $isCurrentlyEmployed: isCurrentlyEmployed
  };

  db.run(sql, values, function(error) {
    if (error){
      next(error);
    } else {
      db.get(`SELECT * FROM Artist WHERE Artist.id = ${this.lastID}`,
        (error, artist) => {
          res.status(201).json({artist: artist});
        });
    }
  });
});

Taken from terminal:

  GET /api/artists
GET /api/artists 200 2.351 ms - 242
     should return all currently-employed artists
GET /api/artists 200 0.335 ms - 242
     should return a status code of 200

  GET /api/artists/:id
GET /api/artists/2 200 1.294 ms - 127
     should return the artist with the given ID
GET /api/artists/2 200 0.517 ms - 127
     should return a 200 status code for valid IDs
GET /api/artists/999 404 0.590 ms - 9
     should return a 404 status code for invalid IDs

  POST /api/artists
POST /api/artists/ 201 82.755 ms - 127
     should create a valid artist (101ms)
POST /api/artists/ 201 79.933 ms - 127
     should return a 201 status code after artist creation (83ms)
POST /api/artists/ 201 96.067 ms - 127
     should return the newly-created artist after artist creation (101ms)
POST /api/artists/ 201 82.561 ms - 127
     should set new artists as currently-employed by default (86ms)
POST /api/artists/ 400 0.196 ms - 11
     should return a 400 status code for invalid artists

  PUT /api/artists/:id
PUT /api/artists/1 500 1.766 ms - -
Error: SQLITE_ERROR: near "Artist": syntax error
    1) should update the artist with the given ID
PUT /api/artists/1 500 0.778 ms - -
Error: SQLITE_ERROR: near "Artist": syntax error
    2) should return a 200 status code after artist update
PUT /api/artists/1 500 0.578 ms - -
Error: SQLITE_ERROR: near "Artist": syntax error
    3) should return the updated artist after artist update
PUT /api/artists/1 400 0.597 ms - 11
     should return a 400 status code for invalid artist updates

  DELETE /api/artists/:id
DELETE /api/artists/1 200 158.298 ms - 122
     should set the artist with the given ID as not currently-employed (162ms)
DELETE /api/artists/1 200 75.443 ms - 122
     should return a 200 status code after artist delete (78ms)
DELETE /api/artists/1 200 75.636 ms - 122
     should return the deleted artist after artist delete (78ms)
#2

Hello,

Your code snippet doesn’t include your PUT

#3

Sorry about that! Here it is:

// PUT HANDLER
artistsRouter.put('/:artistId', (req, res, next) => {
  const name = req.body.artist.name;
  const dateOfBirth = req.body.artist.dateOfBirth;
  const biography = req.body.artist.biography;
  const isCurrentlyEmployed = req.body.artist.isCurrentlyEmployed === 0 ? 0 : 1;
  if (!name || !dateOfBirth || !biography) {
    return res.sendStatus(400);
}
  const sql = 'UPDATE Artist SET name = $name, date_of_birth = $dateOfBirth' +
    'biography = $biography, is_currently_employed = $isCurrentlyEmployed' +
    'WHERE Artist.id = $artistId';
  const values = {
    $name: name,
    $dateOfBirth: dateOfBirth,
    $biography: biography,
    $isCurrentlyEmployed: isCurrentlyEmployed,
    $artistId: req.params.artistId  // params takes from first paramenter in the url
  };

  db.run(sql, values, (error) => {
    if (error) {
      next (error);
    } else {
      db.get(`SELECT * FROM Artist WHERE Artist.id = ${req.params.artistId}`,
      (error, artist) => {
        res.status(200).json({artist: artist});
      });
    }
  });
});
#4

Ok, I see what’s going on now and I’ve definitely made this mistake before too when working with long strings like that, especially when concatenating on multiple lines.

  1. You’re missing a comma in your SQL statement between the date_of_birth and biography fields
  2. You are missing a space before your WHERE clause (either at the beginning of that line or at the end of the previous)

There isn’t a comma between the last field and the WHERE clause, so the space is even more important. I usually add a space at the end of each line or use a large template literal to not have these issues anymore.

You could do a console.log on the sql to see what is being put together right now

1 Like
#5

Ah that’s sorted it, I should just keep all my SQL queries in one line!

Thanks very much for the help.

#6

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.