CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
hotcakedev
hotcakedev

Reputation: 2504

How to associate jsonb type field and a table in sequelize?

I have two tables in my Postgre database.

And here's the project model defined.

const project = (sequelize, DataTypes) => {
  const Project = sequelize.define('project', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    customers: {
      type: DataTypes.JSONB,
      defaultValue: [],
      allowNull: true,
    }
  })
  return Project
}

export default project

And here's the project model defined.

export default (sequelize, DataTypes) => {
  return sequelize.define(
    'customer',
    {
      id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
      },
      name: {
        type: DataTypes.STRING,
        allowNull: true,
      },
  )}
}

So the customers field in project table has json type values. For example (array of uuid):

customers = ["0000-0000-0000-0000", "1111-1111-1111-1111"];

In this case, how to add associations between projects and customers table?

Ideally, I want to include the id matching customers while finding all projects like the following.

const getProjects = async () => {
  try {
    return await models.Projects.findAll({
      include: [{
        model: models.Customers // It should include all customers for a project
      }]
    })
  } catch (error) {
    throw error
  }
}

Hope to hear from you soon. Thank you.

Upvotes: 1

Views: 513

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246878

Your data model is wrong and will only cause trouble for you. If you want to model a many-to-many relationship between two entities, create a “junction table” with foreign keys to both related tables. The primary key is the combination of both foreign keys.

Upvotes: 1

Related Questions