Deleting data from associated tables

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.

Solution

My answer is going to be peppered with particular links because the answer (& the linked terms) are central to the theory that underpins relational databases. You kinda have to know this if you're working with relational databases, otherwise you wont be using the database relationally, at least not intentionally.

What you're trying to do is called a cascading deletion which is a form of propagation constraint.

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 Primary -> Foreign key relationships.

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.

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