Express.js PUT Handler Problem

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)

Hello,

Your code snippet doesn’t include your PUT

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});
      });
    }
  });
});

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

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

Thanks very much for the help.

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