node.jspostgresqlsequelize.js

Cascade deletion on paranoid tables - Sequelize


I have a relationship between two paranoid tables (User and Email) and I have the onDelete: 'cascade' option on the relationship ( Email.belongsTo(User, onDelete:'cascade') ).

The problem is that when I delete the user his email is not being deleted by cascade.

Did I make a mistake or there is bug on sequelize?

I am using sequelize 2.0.0-rc2 on a postgres database.

Thanks.

PS.: Please take a look at the code I used for test:

var Sequelize = require('sequelize')
    , sequelize = new Sequelize('test', 'test', 'test', {dialect: 'postgres'});

var User = sequelize.define('User', {
    username: Sequelize.STRING,
    birthday: Sequelize.DATE
}, {paranoid: true}); //Without paranoid the code works fine

var Email = sequelize.define('Email', {
    email: Sequelize.STRING,
    primary: Sequelize.BOOLEAN
}, {paranoid: true}); //Without paranoid the code works fine

Email.belongsTo(User, {onDelete: 'cascade'});

sequelize.sync().success(function () {
    var userCreatedInstance = null;

    var deletedUserId = null;
    var cascadeDeletedEmailId = null;

    User
        .create({
            username: 'user1',
            birthday: new Date(1986, 06, 28)
        })
        .then(function (_user) {
            userCreatedInstance = _user;
            deletedUserId = userCreatedInstance.id;
            return Email.create({email: 'email1@test.com', primary: true, UserId: userCreatedInstance.id});
        })
        .then(function (createdEmail) {
            cascadeDeletedEmailId = createdEmail.id;
            return userCreatedInstance.destroy();
        })
        .then(function () {
            return User.find(deletedUserId);
        })
        .then(function (foundUser) {
            if(foundUser){
                throw new Error("Shouldn't find the user with this id");
            }
            return Email.find(cascadeDeletedEmailId);
        })
        .then(function (foundEmail){
            if(foundEmail){
                console.log(foundEmail.values);
                throw new Error("Shouldn't find the email with this id");
            }
        });
});

Solution

  • if you want the cascade to work on paranoid, you will have to use hooks. please find how i worked it around below

    i added a afterDestroy hook on the parent table(base of the relatioship), i made it get the model asssociated with with and called the destroy method on it. Easy as that! I believe we could make it work this way till the maintainers give us another means

    // User Model
    const User = sequelize.define('users', {....},
    {
            tableName: 'users',
            deletedAt: 'deletedAt',
            paranoid: true,
            timestamps: true,
            hooks: {
                afterDestroy: function (instance, options) {
                    instance.getProduct().then(product=> product.destroy()); // Softdelete on product table
                    console.log('after destroy: destroyed');
                }
            }
        }
    );
    
    // Product Model
    const Product = sequelize.define('products', {....},
    {
            tableName: 'products',
            deletedAt: 'deletedAt',
            paranoid: true,
            timestamps: true,
        }
    )
    
    // User relationship with product
    User.hasOne(Product,
        {
            as: 'Product',
            onDelete: 'CASCADE',
            hooks: true,
            foreignKey: "userID"
        })
    Product.belongsTo(User, {
        as: 'User',
        foreignKey: "userID"
    });