Express.js Project Question

Hi,

I’m on the final steps of the Express.js project ‘X-Press Publishing’, and encountering some errors when testing the PUT and DELETE Handlers on ‘issues.js’ file. All of the other tests are passing as expected. The specific error I’m seeing in an internal server error (500).

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

Excerpt from issues.js:

// PUT HANDLER
issuesRouter.put('/:issueId', (req, res, next) => {
  const name = req.body.issue.name;
  const issueNumber = req.body.issue.issueNumber;
  const publicationDate = req.body.issue.publicationDate;
  const artistId = req.body.issue.artistId;
  const artistSql = 'SELECT * FROM Artist WHERE Artist.id = $artistId';
  const artistValues = {$artistId: artistId};
  db.get(artistSql, artistValues, (error, artist) => {
    if (error) {
      next(error);
    } else {
      if (!name || !issueNumber || !publicationDate || !artist) {
        return res.sendStatus(400);
      }

    const sql = 'UPDATE Issue SET name = $name, $issueNumber = issueNumber, ' +
      'publication_date = $publicationDate, artist_id = $artistId ' +
      'WHERE Issue.id = $issueId';
      const values = {
        $name: name,
        $issueNumber: issueNumber,
        $publicationDate: publicationDate,
        $artistId: artistId,
        $issueId: req.params.issueId
      };

    db.run(sql, values, function(error) {
      if (error) {
        next(error);
      } else {
        db.get(`SELECT * FROM Issue WHERE Issue.id = ${req.params.issueId}`,
        (error, issue) => {
          res.status(200).json({issue: issue});
        });
        }
      });
    }
  })
});

// DELETE HANDLER
issuesRouter.delete('/:issueId', (req, res, next) => {
  const sql = 'DELETE FROM Issue WHERE Issue.Id = $issueId'
  const values = {$issueId: req.params.issueId};

  db.run(sql, values, (error) => {
    if (error) {
      next(error);
    } else {
      res.sendStatus(204);
    }
  });
});

Delete handler from series.js:

// DELETE HANDLER
seriesRouter.delete('/:seriesId', (req, res, next) => {
  const issueSql = 'SELECT * FROM Issue WHERE Issue.series_id = $seriesId';
  const issueValues = {$seriesId: req.params.seriesId};
  db.get(issueSql, issueValues, (error, issue) => {
    if (error) {
      next(error);
    } else if (issue) {
      res.sendStatus(400);
    } else {
      const deleteSql = 'DELETE FROM Series WHERE Series.id = $seriesId';
      const deleteValues = {$seriesId: req.params.seriesId};

      db.run(deleteSql, deleteValues, (error) => {
        if (error) {
          next(error);
        } else {
          res.sendStatus(204);
        }
      });
    }
  });
});

Excert from test terminal:

  POST /api/series/:seriesId/issues
POST /api/series/2/issues 201 83.287 ms - 119
     should create a valid issue (87ms)
POST /api/series/2/issues 201 75.045 ms - 119
     should return a 201 status code after issue creation (78ms)
POST /api/series/2/issues 201 82.389 ms - 119
     should return the newly-created issue after issue creation (86ms)
POST /api/series/2/issues 400 0.736 ms - 11
     should return a 400 status code for invalid issues
POST /api/series/2/issues 400 0.695 ms - 11
     should return a 400 status code if an artist with the issue's artist ID doesn't exist

  PUT /api/series/:seriesId/issues/:issueId
PUT /api/series/2/issues/1 500 1.059 ms - -
Error: SQLITE_RANGE: column index out of range
    2) should update the issue with the given ID
PUT /api/series/2/issues/1 500 0.533 ms - -
Error: SQLITE_RANGE: column index out of range
    3) should return a 200 status code after issue update
PUT /api/series/2/issues/1 500 0.532 ms - -
Error: SQLITE_RANGE: column index out of range
    4) should return the updated issue after issue update
PUT /api/series/2/issues/999 500 0.580 ms - -
Error: SQLITE_RANGE: column index out of range
    5) should return a 404 status code for invalid issue IDs
PUT /api/series/2/issues/1 500 0.509 ms - -
Error: SQLITE_RANGE: column index out of range
    6) should return a 400 status code for invalid issue updates
PUT /api/series/2/issues/1 500 0.572 ms - -
Error: SQLITE_RANGE: column index out of range
    7) should return a 400 status code if an artist with the updated artist ID doesn't exist

  DELETE /api/series/:seriesId/issues/:issueId
DELETE /api/series/2/issues/1 500 0.589 ms - -
Error: SQLITE_RANGE: column index out of range
    8) should remove the issue with the specified ID from the database
DELETE /api/series/2/issues/1 500 0.618 ms - -
Error: SQLITE_RANGE: column index out of range
    9) should return a 204 status code after issue delete
DELETE /api/series/2/issues/999 500 0.572 ms - -
Error: SQLITE_RANGE: column index out of range
    10) should return a 404 status code for invalid issue IDs


Hello, you’re really close to the end of this one. Just one thing to fix from what I can see, and all your tests will probably pass.

Double check your SQL statement. Since the test mentioned something about the SQLITE_RANGE column index out of range, then it’s usually a good idea to scrutinize your SQL statements. You can console.log() out your SQL statement and look for it in the test results if it helps.

Click if you can't find it for another hint

$issueNumber = issueNumber

Your $issueNumber should be on the right, and you’d need the proper column name

Thanks for pointing out this error, however I rectified it to issue_number = $issueNumber but I’m still seeing the same test error. Any ideas?

I don’t see any other errors in the code that you posted that would prevent the test from running.

Just replaced my blocks of code with your code, fixed the SQL query, and re-ran the tests. They all passed

Ok, I appreciate your continued help.

I’m really not sure what the issue is. I ended up running the application and making changes on the UI. From the logs below you can see that the DELETE requests on issues are not working:

GET /api/artists 304 0.606 ms - -
GET /api/series 304 1.032 ms - -
GET /api/artists 304 0.312 ms - -
GET /api/series/4 304 0.938 ms - -
GET /api/series/4/issues 304 1.099 ms - -
DELETE /api/series/4/issues/4 500 0.496 ms - -
Error: SQLITE_RANGE: column index out of range
DELETE /api/series/4/issues/3 500 0.477 ms - -
Error: SQLITE_RANGE: column index out of range
DELETE /api/series/4 400 0.549 ms - 11
GET /api/series 304 0.641 ms - -
GET /api/artists 304 1.358 ms - -
GET /api/artists 304 0.381 ms - -
GET /api/series/4 304 1.203 ms - -
GET /api/series/4/issues 304 1.403 ms - -

Is your PUT route working at least now? You can try that in the front-end too.

GET /api/series 200 4.883 ms - 13
GET /api/artists 200 7.764 ms - 14
POST /api/series 201 21.959 ms - 59
GET /api/series/1/issues 200 2.903 ms - 13
GET /api/artists 304 0.831 ms - -
GET /api/artists 304 0.520 ms - -
GET /api/series 200 1.605 ms - 61
POST /api/artists 201 28.014 ms - 153
GET /api/artists 200 0.736 ms - 156
GET /api/series 304 0.646 ms - -
GET /api/series/1 200 1.701 ms - 59
GET /api/series/1/issues 304 5.854 ms - -
GET /api/artists 304 1.484 ms - -
POST /api/series/1/issues 201 34.393 ms - 121
DELETE /api/series/1/issues/1 204 16.828 ms - -
DELETE /api/series/1 204 16.915 ms - -
GET /api/series 200 1.074 ms - 13
GET /api/artists 304 0.800 ms - -
GET /api/artists/1 200 0.997 ms - 153
DELETE /api/artists/1 200 37.909 ms - 153

It has to be something outside of the code you posted (with the corrected SQL)

By the way, if you push your project to GitHub, I could quickly look over other areas for you to see if anything stands out. It’s up to you though. I may even end up cloning it and trying it in a VM just to see if I can replicate the same issues.

Some of the areas that I’d look over off the top of my head:

  • package.json to see version numbers of certain packages, like sqlite3
  • router.param() blocks of code
  • router flags like mergeParams
  • migration.js for the database structure
  • the middleware and order in server.js
  • other routes that may be passing the test but could be problematic with different data

Probably more, depending on what I notice