Use Array for many to many association on Postgres

[phanect]

I want to use array when Sequelize create many to many association, rather than creating another table.

When you create many to many association, another table to connect tables is created.
For example, when WebService.hasMany(User) and User.hasMany(WebService), table WebServiceUser is generated.

This is ordinary design pattern for relational databases, but it is complex.

PostgreSQL has Array data type, so it can store foreign keys of multiple records. For example, WebService.UserId can store foregin keys of multiple records of Users.

This is not only reduce complexity of DB structure, someone claims that it improves performance significantly.
Using postgres arrays in relations for very significant performance improvements ?

[mickhansen]

I'd be interested to see actual sources on the performance improvements.
If there's a real performance improvement i could see this landing as a feature, since yes, once in a while you just want something simple.

[janmeier]

I'd still like to see some actual measurements of the differences in performance, but I'm closing this for now

[phanect]

@janmeier OK.
I'm considering to implement this, but not sure when I can do it...
BTW I'm more interested in this function because of simplicity of database structure, rather than performance.

[simg]

I finally got around to running some benchmarks on Arrays vs Join Tables.

Created a Gist along with my benchmark code.

https://gist.github.com/simg/2f28e9dcb6207dbaa11a285021935fe2

tl;dr Array references 5 times faster than join tables for retrieving "objects". Arrays up to twice as fast for inserting "objects" but only as the number of relationships gets quite high (eg ~100 or so).

[mickhansen]

@simg very interesting, we're not looking to have such a core feature have such a different syntax for a specific dialect. But perhaps it could be done as a plugin

[simg]

@mickhansen - understandable. any advantages really only matter under specific circumstances.

[clov3r]

This'd also be nice to have as a user with a legacy database, that already has the relationship set up through an array of foreign_key ids.

But I suppose PRs would be welcome ;)

[DaAwesomeP]

Is it currently possible to define an array reference? I'd like to only use foreign keys in my models.

[DaAwesomeP]

I'm going to try this syntax:

sequelize.define('work', {
  id: { type: Sequelize.UUID, defaultValue: Sequelize.UUIDV4, primaryKey: true },
  authors: {
    type: Sequelize.ARRAY({
      type: Sequelize.UUID,
      references: {
        model: Author,
        key: 'id',
        deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
      },
      allowNull: false
    }),
    allowNull: false
  }
})

[DaAwesomeP]

So this method works in theory, but it makes it impossible to include the Author on a query searching work.

[JaffParker]

Hello everyone,

I just encountered this issue as well and wanted to ask if there's been any progress since May?

[JaffParker]

The solution I decided to use until there's some movement with this issue (if ever) looks like this:

const Painting = sequelize.define(
  'paintings',
  {
    id: {
      type: Sequelize.BIGINT,
      primaryKey: true,
      autoIncrement: true
    },
    title: {
      type: Sequelize.STRING
    },
    tags: {
      type: Sequelize.ARRAY({
        type: Sequelize.BIGINT,
        references: {
          model: Tag,
          key: 'id'
        }
      }),
      async get() {
        return await Tag.findAll({
          where: {
            id: {$in: this.getDataValue('tags')}
          }
        })
      }
    }
  })

That getter unfortunately returns promises that need to be individually resolved, and doing so many queries is sure not good for performance. I use it because my app is tiny and I can afford such a performance drop.

[rowlandekemezie]

@JaffParker I'm confused on how you were able to access Tag inside another model.
Using sequelize.models.Tag.findAll... returns data that's very had to use

[ Blend {
    dataValues:
     { id: 1,
       blend_name: 'Focus',
       description: null,
       createdAt: 2018-05-17T00:29:30.957Z,
       updatedAt: 2018-05-17T00:29:30.957Z,
       oilset_id: 1,
       hotel_id: 1,
       OilSet: [Object] },
    _previousDataValues:
     { id: 1,
       blend_name: 'Focus',
       description: null,
       createdAt: 2018-05-17T00:29:30.957Z,
       updatedAt: 2018-05-17T00:29:30.957Z,
       oilset_id: 1,
       hotel_id: 1,
       OilSet: [Object] },
    _changed: {},
    _modelOptions:
     { timestamps: true,
       validate: {},
       freezeTableName: false,
       underscored: false,
       underscoredAll: false,
       paranoid: false,
       rejectOnEmpty: false,
       whereCollection: [Object],
       schema: null,
       schemaDelimiter: '',
       defaultScope: {},
       scopes: [],
       indexes: [],
       name: [Object],
       omitNull: false,
       timeStamps: true,
       sequelize: [Object],
       hooks: {},
       uniqueKeys: {} },
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       include: [Array],
       includeNames: [Array],
       includeMap: [Object],
       includeValidated: true,
       attributes: [Array],
       raw: true },
   }
]

Here's my model:

module.exports = (sequelize, DataTypes) => {
  const OilSet = sequelize.define(
    'OilSet',
    {
      oils: {
        type: DataTypes.ARRAY({
          type: DataTypes.SMALLINT,
          references: {
            model: sequelize.models.Oil,
            key: 'id'
          },
          allowNull: false
        }),
         async get() {
          return await this.sequelize.models.Oil.findAll({
            where: {
              id: { in: this.getDataValue('oils') }
            }
          });
        },
        allowNull: false
      }
    },
    {
      timeStamps: true
    }
  );
 return OilSet
}

Not sure what's wrong

[seromenho]

Just checking if this was implemented? Thanks

[ramsestom]

Same. need this feature

[thomascharbit]

Would love it too!

[ycraaron]

A workaround towards this is to get the array value and then do another query with IN using this array of values.

[johnculviner]

Would love to be able to use the feature of PostgreSQL without a linking/join table

[mi-mazouz]

Any update on this?

[zfben]

I need this feature.

[tcamde]

Would love to use this for simplicity

[nmargaritis]

Any updates ?

[vosho]

Any updates ?

[ramsestom]

@janmeier
May I suggest you to reopen this issue?
An increasing number of users seems interested into beeing able to use postgresql arrays associations feature with sequelize (probably because it is a feature supported by postgresql for many years now, so it isn't new anymore (this issue is already 6 years old...) and more and more postgresql databases probably use it for simplicity and performances) so it is definitively something the sequelize team should consider to implement.

For users asking for updates on this issue that didn't upvote the first comment of this thread, please do it. Upvote scores probably help sequelize developpers to prioritize the issues to fix.

[bondarenkovladislav]

Really need it

[CoveMB]

I would have a use for this!

[jh97uk]

I definitely have a use for this, it would make my life infinitely easier. Please consider this!

[mnprt-d]

Hi, any updates on this issue ? I have been stuck at same for last 2 days and then I found this.

[oldner]

any improvement?

[ephys]

We were initially opposed to this feature because you could not set the foreign key constraint on each element of an array. A patch has been in the works for years but it's not ready yet: https://commitfest.postgresql.org/17/1252/

I'll reopen the thread but we will only implement something after at least one popular DBMS supports arrays of FKs.