CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Jakub
Jakub

Reputation: 2709

How to add reference to another table column as foreignKey in the migration using Sequelize

In my Node API, I'm using the ORM Sequuilize and trying to understand how to make the migration file in this way to tables is referred to as each other by a foreign key.

I have a Student who HasMany Projects and Projects BelongTo one student.

So my models are like this:

module.exports = (sequelize, DataTypes) => {
    const Student = sequelize.define("Student", {
        _id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4,
            //autoIncrement: true
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        email: {
            type: DataTypes.TEXT,
            allowNull: false,
            unique: true
        },
        dateOfBirth: {
            type: DataTypes.DATEONLY,
            allowNull: false
        },
        createdAt: {
            type: DataTypes.DATE,
            allowNull: false
        },
        updatedAt: {
            type: DataTypes.DATE,
            allowNull: false
        }
    });

    Student.associate = function(models) {
        Student.hasMany(models.Project, {
            foreignKey: 'student_id',
            as: 'projects',
            onDelete: 'CASCADE',
          });
    };
    return Student;
};

Project model:

module.exports = (sequelize, DataTypes) => {
    const Project = sequelize.define("Project", {
        project_id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4,
            //autoIncrement: true
        },
        student_id: {
            type: DataTypes.UUIDV4,
            allowNull: false,
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        },
        description: {
            type: DataTypes.TEXT,
            allowNull: true
        },
        creationDate: {
            type: DataTypes.DATEONLY,
            allowNull: true
        },
        repoUrl: {
            type: DataTypes.TEXT,
            allowNull: true
        },
        liveUrl: {
            type: DataTypes.TEXT,
            allowNull: true
        },
        createdAt: {
            type: DataTypes.DATE,
            allowNull: false
        },
        updatedAt: {
            type: DataTypes.DATE,
            allowNull: false
        }
    });

    Student.associate = function(models) {
        Project.belongsTo(models.Student, {
            foreignKey: "student_id",
            as: "student",
            onDelete: "CASCADE"
        });
    };
    return Student;
};

My migration as follows but as I stated before I don't know how to include the foreign key and the way I did does not work, the migration is created but in the DB the relationship is not made:

Student migration

"use strict";

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable("Students", {
            _id: {
                primaryKey: true,
                type: Sequelize.UUID,
                defaultValue: Sequelize.UUIDV4,
                //autoIncrement: true
            },
            name: {
                type: Sequelize.STRING,
                allowNull: false
            },
            email: {
                type: Sequelize.TEXT,
                allowNull: false,
                unique: true
            },
            dateOfBirth: {
                type: Sequelize.DATEONLY,
                allowNull: false
            },
            createdAt: {
                type: Sequelize.DATE,
                allowNull: false
            },
            updatedAt: {
                type: Sequelize.DATE,
                allowNull: false
            }
        });
    },

    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable("Students");
    }
};

Project migration

"use strict";

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable("Projects", {
            project_id: {
                primaryKey: true,
                type: Sequelize.UUID,
                defaultValue: Sequelize.UUIDV4
                //autoIncrement: true
            },
            student_id: {
                foreignKey: true,
                type: Sequelize.UUID,
                defaultValue: Sequelize.UUIDV4,
                allowNull: false
            },
            name: {
                type: Sequelize.STRING,
                allowNull: false,
                unique: true
            },
            description: {
                type: Sequelize.TEXT,
                allowNull: true
            },
            creationDate: {
                type: Sequelize.DATEONLY,
                allowNull: true
            },
            repoUrl: {
                type: Sequelize.TEXT,
                allowNull: true
            },
            liveUrl: {
                type: Sequelize.TEXT,
                allowNull: true
            },
            createdAt: {
                type: Sequelize.DATE,
                allowNull: false
            },
            updatedAt: {
                type: Sequelize.DATE,
                allowNull: false
            }
        });
    },

    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable("Projects");
    }
};

Upvotes: 1

Views: 5010

Answers (1)

Lin Du
Lin Du

Reputation: 102247

Here is the migration workflow:

  1. Generate models and migration files using sequelize-cli.
  2. Define the association between Student and Project based on your business requirements.
  3. Based on the associations between the models, modify the migration files, add the foreign keys(add student_id column to Project table). For your case, Project table has a student_id column as its foreign key reference to the Student table's _id column.

Here is the working example:

models/student.js:

module.exports = (sequelize, DataTypes) => {
  const Student = sequelize.define('Student', {
    _id: {
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.TEXT,
      allowNull: false,
      unique: true,
    },
    dateOfBirth: {
      type: DataTypes.DATEONLY,
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    updatedAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
  });

  Student.associate = function(models) {
    Student.hasMany(models.Project, {
      foreignKey: 'student_id',
      as: 'projects',
    });
  };
  return Student;
};

models/project.js:

module.exports = (sequelize, DataTypes) => {
  const Project = sequelize.define('Project', {
    project_id: {
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    description: {
      type: DataTypes.TEXT,
      allowNull: true,
    },
    creationDate: {
      type: DataTypes.DATEONLY,
      allowNull: true,
    },
    repoUrl: {
      type: DataTypes.TEXT,
      allowNull: true,
    },
    liveUrl: {
      type: DataTypes.TEXT,
      allowNull: true,
    },
    createdAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    updatedAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
  });

  Project.associate = function(models) {
    Project.belongsTo(models.Student, {
      foreignKey: 'student_id',
      as: 'student',
      onDelete: 'CASCADE',
    });
  };
  return Project;
};

migrations/20200213113039-create-student.js:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Students', {
      _id: {
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      email: {
        type: Sequelize.TEXT,
        allowNull: false,
        unique: true,
      },
      dateOfBirth: {
        type: Sequelize.DATEONLY,
        allowNull: false,
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Students');
  },
};

migrations/20200213113231-create-project.js:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Projects', {
      project_id: {
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
      },
      student_id: {
        type: Sequelize.UUID,
        references: {
          model: {
            tableName: 'Students',
          },
          key: '_id',
        },
        allowNull: false,
        onDelete: 'CASCADE',
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
      },
      description: {
        type: Sequelize.TEXT,
        allowNull: true,
      },
      creationDate: {
        type: Sequelize.DATEONLY,
        allowNull: true,
      },
      repoUrl: {
        type: Sequelize.TEXT,
        allowNull: true,
      },
      liveUrl: {
        type: Sequelize.TEXT,
        allowNull: true,
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Projects');
  },
};

After executing the command npx sequelize-cli db:migrate, check the table information in the database(I also created seed file to insert some demo data):

node-sequelize-examples=# select * from "Students";
                 _id                  |     name      |          email           | dateOfBirth |           createdAt           |           updatedAt
--------------------------------------+---------------+--------------------------+-------------+-------------------------------+-------------------------------
 2c6db3cb-82c7-4728-a259-23520bd760d0 | Rosalind Mohr | [email protected] | 2020-02-13  | 2020-02-13 12:39:23.508379+00 | 2020-02-13 12:39:23.508379+00
(1 row)

node-sequelize-examples=# select * from "Projects";
              project_id              | name  | description | creationDate | repoUrl | liveUrl |           createdAt           |           updatedAt           |              student_id
--------------------------------------+-------+-------------+--------------+---------+---------+-------------------------------+-------------------------------+--------------------------------------
 b271060c-bd20-48f4-a8a5-65508b99cfbf | nobis |             |              |         |         | 2020-02-13 12:39:23.540054+00 | 2020-02-13 12:39:23.540054+00 | 2c6db3cb-82c7-4728-a259-23520bd760d0
(1 row)

Describe the tables:

node-sequelize-examples=# \d "Students";
              Table "public.Students"
   Column    |           Type           | Modifiers
-------------+--------------------------+-----------
 _id         | uuid                     | not null
 name        | character varying(255)   | not null
 email       | text                     | not null
 dateOfBirth | date                     | not null
 createdAt   | timestamp with time zone | not null
 updatedAt   | timestamp with time zone | not null
Indexes:
    "Students_pkey" PRIMARY KEY, btree (_id)
    "Students_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE ""Projects"" CONSTRAINT "Projects_student_id_fkey" FOREIGN KEY (student_id) REFERENCES "Students"(_id) ON UPDATE CASCADE ON DELETE CASCADE

node-sequelize-examples=# \d "Projects";
               Table "public.Projects"
    Column    |           Type           | Modifiers
--------------+--------------------------+-----------
 project_id   | uuid                     | not null
 name         | character varying(255)   | not null
 description  | text                     |
 creationDate | date                     |
 repoUrl      | text                     |
 liveUrl      | text                     |
 createdAt    | timestamp with time zone | not null
 updatedAt    | timestamp with time zone | not null
 student_id   | uuid                     |
Indexes:
    "Projects_pkey" PRIMARY KEY, btree (project_id)
    "Projects_name_key" UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
    "Projects_student_id_fkey" FOREIGN KEY (student_id) REFERENCES "Students"(_id) ON UPDATE CASCADE ON DELETE CASCADE

I believe that you want to delete all projects of a user when the user is deleted.

Upvotes: 5

Related Questions