Deleting data from associated tables using knex.js

Question

I want to delete from an articles table using knex by article_id. This already exists in comments table as a foreign key.

How can I test that data has been deleted and how can I send that to the user.

I decided to approach this by writing a function to delete from both functions with a .then. Does this look like I am on the right lines?

exports.deleteArticleById = function (req, res, next) {
  const { article_id } = req.params;
  return connection('comments')
    .where('comments.article_id', article_id)
    .del()
    .returning('*')
    .then((deleted) => {
      console.log(deleted);
      return connection('articles')
        .where('articles.article_id', article_id)
        .del()
        .returning('*');
    })
    .then((article) => {
      console.log(article);
      return res.status(204).send('article deleted');
    })
    .catch(err => next(err));
};

At the moment I am getting the correct data with the logs but I am getting a status 500 but I think I need to be trying to get a 204?

Any help would be much appreciated.

Answer 1

What you're trying to do is called a cascading deletion.

These are better (and almost always) handled at the database level instead of the application level. It's the job of the DBMS to enforce this kind of referential integrity assuming you define your schema correctly so that entities are correctly linked together, via foreign keys.

In short, you should define your database schema as such that when you delete an Article, it's associated Comments also get deleted for you.

Here's how I would do it using knex.js migrations:

// Define Article.
db.schema.createTableIfNotExists('article', t => { 
  t.increments('article_id').primary()
  t.text('content')
})

// Define Comment.
// Each Comment is associated with an Article (1 - many).
db.schema.createTableIfNotExists('comment', t => { 
  t.increments('comment_id').primary() // Add an autoincrement primary key (PK).
  t.integer('article_id').unsigned() // Add a foreign key (FK)...
    .references('article.article_id') // ...which references Article PK.
    .onUpdate('CASCADE') // If Article PK is changed, update FK as well.
    .onDelete('CASCADE') // If Article is deleted, delete Comment as well.
  t.text('content')
})

So when you run this to delete an Article:

await db('article').where({ article_id: 1 }).del()

All Comments associated with that Article also get deleted, automatically.

Don't try to perform cascading deletions yourself by writing application code. The DBMS is specifically designed with intricate mechanisms to ensure that deletions always happen in a consistent manner; It's purpose is to handle these operations for you. it would be wasteful, complicated and quite error-prone to attempt to replicate this functionality yourself.