Test database for joins

Model definitions

module.exports = function(sequelize, DataTypes) {
    return sequelize.define("allocation", {
        // Foreign keys: project
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        code: {
            type: DataTypes.STRING,
            allowNull: false
        },
        costScale: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        priceScale: {
            type: DataTypes.INTEGER,
            allowNull: false
        }
    },{
        tableName: 'allocations'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("invoice", {
        // Foreign keys: project
        date: {
            type: DataTypes.DATE,
            allowNull: false
        },
        amount: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        status: {
            type: DataTypes.STRING,
            allowNull: false
        },
        poReference: {
            type: DataTypes.STRING,
            allowNull: true
        },
        note: {
            type: DataTypes.STRING,
            allowNull: true
        }
    },{
        tableName: 'invoices'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("phase", {
        // Foreign keys: project
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        description: {
            type: DataTypes.STRING,
            allowNull: true
        },
        startDate: {
            type: DataTypes.DATE,
            allowNull: false
        },
        endDate: {
            type: DataTypes.DATE,
            allowNull: false
        }
    },{
        tableName: 'phases'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("portfolio", {
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        startDate: {
            type: DataTypes.DATE,
            allowNull: false
        },
        endDate: {
            type: DataTypes.DATE,
            allowNull: false
        }
    },{
        tableName: 'portfolios'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("project", {
        // Foreign keys: plan
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        premium: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        discount: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        fixedPrice: {
            type: DataTypes.BOOLEAN,
            allowNull: false
        }
    },{
        tableName: 'projects'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("rate", {
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        code: {
            type: DataTypes.STRING,
            allowNull: true
        },
        price: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        cost: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        scaled: {
            type: DataTypes.BOOLEAN,
            allowNull: false
        }
    },{
        tableName: 'rates'
    });
};

module.exports = function(sequelize, DataTypes) {
    return sequelize.define("specialDay", {
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        date: {
            type: DataTypes.DATE,
            allowNull: false
        },
        type: {
            type: DataTypes.STRING,
            allowNull: false
        }
    },{
        tableName: 'specialDays'
    });
};

module.exports = function(sequelize, DataTypes) {
    return sequelize.define("teamMember", {
        // Foreign keys: portfolio
        surname: {
            type: DataTypes.STRING,
            allowNull: false
        },
        givenName: {
            type: DataTypes.STRING,
            allowNull: false
        }
    },{
        tableName: 'teamMembers'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("teamMemberGrade", {
        // Foreign keys: teamMember, rate
        startDate: {
            type: DataTypes.DATE,
            allowNull: true
        }
    },{
        tableName: 'teamMemberGrades'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("user", {
        email: {
            type: DataTypes.STRING,
            unique: true,
            allowNull: false,
            validate: {isEmail: true}
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        password: {
            type: DataTypes.STRING,
            allowNull: false
        },
        currentPortfolio: {
            type: DataTypes.STRING,
            allowNull: true
        }
    },{
        tableName: 'users'
    });
};


module.exports = function(sequelize, DataTypes) {
    return sequelize.define("allocation", {
        // Foreign keys: project
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        code: {
            type: DataTypes.STRING,
            allowNull: false
        },
        costScale: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        priceScale: {
            type: DataTypes.INTEGER,
            allowNull: false
        }
    },{
        tableName: 'allocations'
    });
};


        User
            .hasMany(Portfolio,  {as: 'portfolios', foreignKey: 'userId', onDelete: 'cascade'});

        Portfolio
            .hasMany(SpecialDay, {as: 'specialDays', foreignKey: 'portfolioId', onDelete: 'cascade'})
            .hasMany(Rate,       {as: 'rates',       foreignKey: 'portfolioId', onDelete: 'cascade'})
            .hasMany(TeamMember, {as: 'teamMembers', foreignKey: 'portfolioId', onDelete: 'cascade'})
            .hasMany(Project,    {as: 'projects',    foreignKey: 'portfolioId', onDelete: 'cascade'});

        Project
            .hasMany(Allocation, {as: 'allocations', foreignKey: 'projectId', onDelete: 'cascade'})
            .hasMany(Invoice,    {as: 'invoices',    foreignKey: 'projectId', onDelete: 'cascade'})
            .hasMany(Phase,      {as: 'phases',      foreignKey: 'projectId', onDelete: 'cascade'});

Sample query and output

sqlite> select projects.*, phases.*, invoices.* from projects left outer join phases on projects.id = phases.projectId left outer join invoices on projects.id = invoices.projectId;
id          name         id          projectId   startDate   endDate     id          projectId   invoiceDate  amount    
----------  -----------  ----------  ----------  ----------  ----------  ----------  ----------  -----------  ----------
1           Project one  1           1           2013-01-01  2013-02-01  1           1           2013-02-01   10000     
1           Project one  1           1           2013-01-01  2013-02-01  2           1           2013-03-01   20000     
1           Project one  2           1           2013-03-01  2013-04-01  1           1           2013-02-01   10000     
1           Project one  2           1           2013-03-01  2013-04-01  2           1           2013-03-01   20000     

Test data

sqlite> create table projects (id integer auto_increment primary key, name varchar);
sqlite> create table phases (id integer auto_increment primary key, projectId integer, startDate date, endDate date);
sqlite> create table invoices (id integer auto_increment primary key, projectId integer, invoiceDate date, amount integer);
sqlite> insert into projects values (1, "Project one");
sqlite> insert into phases values (1, 1, "2013-01-01", "2013-02-01"), (2, 1, "2013-03-01", "2013-04-01");
sqlite> insert into invoices values (1, 1, "2013-02-01", 10000), (2, 1, "2013-03-01", 20000);