Sequelize: Max identifier length exceeded with nested includes

[seth-admittedly]

Postgres has a 63 character max for identifiers, which is pretty hard to hit in normal use, and pretty easy to hit with the current way nested includes are namespaced. Unfortunately this parameter is hardcoded into Postgres and can only be increased by changing a source file and recompiling.

[mickhansen]

Hmm, suppose one could solve it by applying a minification scheme to the names, would make the actual SQL queries harder to debug though. (Guess we can toggle it with a flag for when we need debugging).

[claaslange]

We're running into the same problem. The option to use minified identifiers would be great.

[sebastianhoitz]

We are running into the same issue.

:+1: for a fix for this plz

[mohlman3]

+1 .. a fix or workaround would be very helpful

[majodev]

+1

[emhagman]

+1

[renou]

+1

[pmettraux]

+1

[SharkofMirkwood]

+1

[thomaschaaf]

/12

[shawnbissell]

[phone]

[josser]

Sorry for dup, but maybe somebody found workaround or quick fix?
Quick idea: what if we will generate UUID for each 'as' which will be mapped to hash of real 'as' identifiers during query execution.
If this is good fix I may try to create PR for this.

[janmeier]

@josser As you can see in the top, we've discussed using some sort of minification scheme - it would probably be better to use some shortened version of the model name, so it's at least somewhat readable.

Unless @mickhansen has any strong objections, a PR would be welcome - It's not a pretty solution, but I don't think we can do much better - not all queries can be solved by .seperate i think

[robraux]

:+1: here as well, just hit this again in production. Separate works for some scenario's but there are other areas it does not.

[Srokap]

+1

[ryanrolds]

+1

[linpekka]

I have run into this, and was looking into if there is a way to at least get a nice error from sequelize when it happens. I noticed that setting log_min_messages = notice in postgresql.conf will generate a notice in the postgres log, but even with client_min_messages = notice I am not seeing any error or log generated by sequelize for the truncate.

Is there some option that I missed that will allow the notice to be propagated to the sequelize log, or even better, to be able to get an error for this specific notification.

[tanyabalyan]

+1
separate works only with certain associations. would be great to have a solution cross all associations.

[bockscar]

+1

[romandecker]

+1 agreed, I can't even use the separate: true workaround because I have lots of nested belongsTo-relations and separate: true is only supported for hasMany :/

[dennajort]

+1
I started reading code of QueryInterface.select and QueryGenerator.selectQuery and I think it should be doable with some modifications on QueryGenerator.quoteTable and maybe QueryGenerator.quoteIdentifier in order to make QueryGenerator.selectQuery return a mapping between the AS in the query and what we want to return from QueryInterface.select.

[mkzaker]

+1

[icemagno]

What a hell ....

NOTICE:  identifier "TABLE(seq integer, path_id integer, path_seq integer, node bigint, edge bigint, 
    cost double precision, agg_cost double precision)" will be truncated to "TABLE(seq integer, path_id integer, path_seq integer, node bigi"
ERROR:  type "TABLE(seq integer, path_id integer, path_seq integer, node bigi" does not exist
********** Error **********

ERROR: type "TABLE(seq integer, path_id integer, path_seq integer, node bigi" does not exist
SQL state: 42704

when

CREATE OR REPLACE FUNCTION public.calc_rotas_v3(
    source integer,
    target integer,
    k integer,
    directed boolean)
    RETURNS SETOF "TABLE(seq integer, path_id integer, path_seq integer, node bigint, edge bigint, 
    cost double precision, agg_cost double precision)"
    LANGUAGE 'sql'
    COST 100.0
    STABLE NOT LEAKPROOF 
    ROWS 1500.0
AS $function$ 

[mfpiccolo]

+1

[andre682]

+1

[andre682]

Worked fine after using @mliszewski separate: true
Thanks...

include: [{
        separate: true,
        model: db.t_order,
        required: false
      }]

[RaulTsc]

+1

[KopIlya]

+1

[Fatherbad]

I give you.... +11 !

Would love to see some config options

[vagabondan]

Hi everybody, here is the "too long names" fix for Oracle dialect fork (sequelize-oracle):
https://github.com/vagabondan/cu8-sequelize-oracle/commit/e3edb224b813b049a3c1c5b840a3a61b17fe9f84

I believe that PostgreSQL could be fixed the same way.
Main idea:

• tables get aliases like t0, t1, ... and so on, they should be remembered some way in aliases map
• aliases map should be used to substitute t# back to be able to decode real object structure after select query returned data

TODO:

• apply fix for "group by", "having" and other expressions excluding "order by" (order by has been fixed)

Example:
Before patch:
SELECT users.ID, users.PROFILE_ID, users.USER_SOURCE_ID, users.USER_SOURCE_ACCOUNT, users.ATTRIBUTES, users.STATUS, users.OPERATOR, users.CREATED_AT, users.UPDATED_AT, users.created_at, users.updated_at, users.user_source_ID, users.profile_ID, profile.ID "profile.ID", profile.USER_SOURCE_ID "profile.USER_SOURCE_ID", profile.NAME "profile.NAME", profile.USER_FILTER "profile.USER_FILTER", profile.DESCRIPTION "profile.DESCRIPTION", profile.PROFILE_KEY "profile.PROFILE_KEY", profile.OPERATOR "profile.OPERATOR", profile.CREATED_AT "profile.CREATED_AT", profile.UPDATED_AT "profile.UPDATED_AT", profile.created_at "profile.created_at", profile.updated_at "profile.updated_at", profile.user_source_ID "profile.user_source_ID", profile.roles.ID "profile.roles.ID", profile.roles.ROLE_KEY "profile.roles.ROLE_KEY", profile.roles.NAME "profile.roles.NAME", profile.roles.DESCRIPTION "profile.roles.DESCRIPTION", profile.roles.MACROS_EXPRESSIONS "profile.roles.MACROS_EXPRESSIONS", profile.roles.OPERATOR "profile.roles.OPERATOR", profile.roles.CREATED_AT "profile.roles.CREATED_AT", profile.roles.UPDATED_AT "profile.roles.UPDATED_AT", profile.roles.created_at "profile.roles.created_at", profile.roles.updated_at "profile.roles.updated_at", profile.roles.profile_roles.PROFILE_ID profile.roles.profile_roles.PROFILE_ID, profile.roles.profile_roles.ROLE_ID profile.roles.profile_roles.ROLE_ID, profile.roles.profile_roles.ROLESET_ID profile.roles.profile_roles.ROLESET_ID, profile.roles.profile_roles.STEP profile.roles.profile_roles.STEP, profile.roles.profile_roles.OPERATOR profile.roles.profile_roles.OPERATOR, profile.roles.profile_roles.CREATED_AT profile.roles.profile_roles.CREATED_AT, profile.roles.profile_roles.UPDATED_AT profile.roles.profile_roles.UPDATED_AT, profile.roles.profile_roles.created_at profile.roles.profile_roles.created_at, profile.roles.profile_roles.updated_at profile.roles.profile_roles.updated_at, profile.roles.profile_roles.profile_id profile.roles.profile_roles.profile_id, profile.roles.profile_roles.role_id profile.roles.profile_roles.role_id, profile.roles.request_templates.ID "profile.roles.request_templates.ID", profile.roles.request_templates.TARGET_SYSTEM_ID "profile.roles.request_templates.TARGET_SYSTEM_ID", profile.roles.request_templates.REQUEST_TEMPLATE_KEY "profile.roles.request_templates.REQUEST_TEMPLATE_KEY", profile.roles.request_templates.BODY_TEMPLATE "profile.roles.request_templates.BODY_TEMPLATE", profile.roles.request_templates.OPTIONS "profile.roles.request_templates.OPTIONS", profile.roles.request_templates.PROTOCOL "profile.roles.request_templates.PROTOCOL", profile.roles.request_templates.NAME "profile.roles.request_templates.NAME", profile.roles.request_templates.DESCRIPTION "profile.roles.request_templates.DESCRIPTION", profile.roles.request_templates.OPERATOR "profile.roles.request_templates.OPERATOR", profile.roles.request_templates.CREATED_AT "profile.roles.request_templates.CREATED_AT", profile.roles.request_templates.UPDATED_AT "profile.roles.request_templates.UPDATED_AT", profile.roles.request_templates.created_at "profile.roles.request_templates.created_at", profile.roles.request_templates.updated_at "profile.roles.request_templates.updated_at", profile.roles.request_templates.target_system_ID "profile.roles.request_templates.target_system_ID", profile.roles.request_templates.role_request_templates.ROLE_ID profile.roles.request_templates.role_request_templates.ROLE_ID, profile.roles.request_templates.role_request_templates.REQUEST_TEMPLATE_ID profile.roles.request_templates.role_request_templates.REQUEST_TEMPLATE_ID, profile.roles.request_templates.role_request_templates.STEP profile.roles.request_templates.role_request_templates.STEP, profile.roles.request_templates.role_request_templates.OPERATOR profile.roles.request_templates.role_request_templates.OPERATOR, profile.roles.request_templates.role_request_templates.CREATED_AT profile.roles.request_templates.role_request_templates.CREATED_AT, profile.roles.request_templates.role_request_templates.UPDATED_AT profile.roles.request_templates.role_request_templates.UPDATED_AT, profile.roles.request_templates.role_request_templates.created_at profile.roles.request_templates.role_request_templates.created_at, profile.roles.request_templates.role_request_templates.updated_at profile.roles.request_templates.role_request_templates.updated_at, profile.roles.request_templates.role_request_templates.request_template_id profile.roles.request_templates.role_request_templates.request_template_id, profile.roles.request_templates.role_request_templates.role_id profile.roles.request_templates.role_request_templates.role_id FROM users users LEFT OUTER JOIN profiles profile ON users.profile_ID = profile.ID LEFT OUTER JOIN (profile_roles profile.roles.profile_roles INNER JOIN roles profile.roles ON profile.roles.ID = profile.roles.profile_roles.role_id) ON profile.ID = profile.roles.profile_roles.profile_id LEFT OUTER JOIN (role_request_templates profile.roles.request_templates.role_request_templates INNER JOIN request_templates profile.roles.request_templates ON profile.roles.request_templates.ID = profile.roles.request_templates.role_request_templates.request_template_id) ON profile.roles.ID = profile.roles.request_templates.role_request_templates.role_id WHERE users.STATUS = 'new' ORDER BY profile.roles.profile_roles.STEP ASC, profile.roles.request_templates.role_request_templates.STEP ASC

After patch:
SELECT users.ID, users.PROFILE_ID, users.USER_SOURCE_ID, users.USER_SOURCE_ACCOUNT, users.ATTRIBUTES, users.STATUS, users.OPERATOR, users.CREATED_AT, users.UPDATED_AT, users.created_at, users.updated_at, users.user_source_ID, users.profile_ID, profile.ID "profile.ID", profile.USER_SOURCE_ID "profile.USER_SOURCE_ID", profile.NAME "profile.NAME", profile.USER_FILTER "profile.USER_FILTER", profile.DESCRIPTION "profile.DESCRIPTION", profile.PROFILE_KEY "profile.PROFILE_KEY", profile.OPERATOR "profile.OPERATOR", profile.CREATED_AT "profile.CREATED_AT", profile.UPDATED_AT "profile.UPDATED_AT", profile.created_at "profile.created_at", profile.updated_at "profile.updated_at", profile.user_source_ID "profile.user_source_ID", t0.ID "t0.ID", t0.ROLE_KEY "t0.ROLE_KEY", t0.NAME "t0.NAME", t0.DESCRIPTION "t0.DESCRIPTION", t0.MACROS_EXPRESSIONS "t0.MACROS_EXPRESSIONS", t0.OPERATOR "t0.OPERATOR", t0.CREATED_AT "t0.CREATED_AT", t0.UPDATED_AT "t0.UPDATED_AT", t0.created_at "t0.created_at", t0.updated_at "t0.updated_at", t1.PROFILE_ID "t1.PROFILE_ID", t1.ROLE_ID "t1.ROLE_ID", t1.ROLESET_ID "t1.ROLESET_ID", t1.STEP "t1.STEP", t1.OPERATOR "t1.OPERATOR", t1.CREATED_AT "t1.CREATED_AT", t1.UPDATED_AT "t1.UPDATED_AT", t1.created_at "t1.created_at", t1.updated_at "t1.updated_at", t1.profile_id "t1.profile_id", t1.role_id "t1.role_id", t2.ID "t2.ID", t2.TARGET_SYSTEM_ID "t2.TARGET_SYSTEM_ID", t2.REQUEST_TEMPLATE_KEY "t2.REQUEST_TEMPLATE_KEY", t2.BODY_TEMPLATE "t2.BODY_TEMPLATE", t2.OPTIONS "t2.OPTIONS", t2.PROTOCOL "t2.PROTOCOL", t2.NAME "t2.NAME", t2.DESCRIPTION "t2.DESCRIPTION", t2.OPERATOR "t2.OPERATOR", t2.CREATED_AT "t2.CREATED_AT", t2.UPDATED_AT "t2.UPDATED_AT", t2.created_at "t2.created_at", t2.updated_at "t2.updated_at", t2.target_system_ID "t2.target_system_ID", t3.ROLE_ID "t3.ROLE_ID", t3.REQUEST_TEMPLATE_ID "t3.REQUEST_TEMPLATE_ID", t3.STEP "t3.STEP", t3.OPERATOR "t3.OPERATOR", t3.CREATED_AT "t3.CREATED_AT", t3.UPDATED_AT "t3.UPDATED_AT", t3.created_at "t3.created_at", t3.updated_at "t3.updated_at", t3.request_template_id "t3.request_template_id", t3.role_id "t3.role_id" FROM users users LEFT OUTER JOIN profiles profile ON users.profile_ID = profile.ID LEFT OUTER JOIN (profile_roles t1 INNER JOIN roles t0 ON t0.ID = t1.role_id) ON profile.ID = t1.profile_id LEFT OUTER JOIN (role_request_templates t3 INNER JOIN request_templates t2 ON t2.ID = t3.request_template_id) ON t0.ID = t3.role_id WHERE users.STATUS = 'new' ORDER BY t1.STEP ASC, t3.STEP ASC

[y0hnn]

+1

[fatstar415]

+1

[jostang]

+1

[smartpierre]

[rbjarnason]

+1

[OrH]

+1

[singhravi1]

+1

[zlatinejc]

+1

[vincepoencet]

+1

[anistark]

+1

[mfpiccolo]

We just started using this library now: https://github.com/Vincit/objection.js

[pdefreitas]

+1000000 !!!

[TdevM]

+1

[mrrinot]

+1

[BenjaminDish]

Hello, any update about this ? I can't use @andre682 's workaround as I'm using sequelize v5 and the separate attribute doesn't exist anymore. Is the only solution to rename entities shorter ??

[shawnbissell]

@BenjaminDish "separate attribute doesn't exist anymore" I don't believe this is true. It's not documented on the upgrade guide here:
https://github.com/sequelize/sequelize/blob/master/docs/upgrade-to-v5.md
Also 5.0.0-beta.5 fixed an issue with using separate and attributes in the same include
https://github.com/sequelize/sequelize/pull/9396
https://github.com/sequelize/sequelize/issues/8228
https://github.com/sequelize/sequelize/issues/7514

[BenjaminDish]

@shawnbissell Thanks for the precision. Maybe it is still included in the sequelize library, but the most used typings of sequelize doesn't know it..

About the original problem, which is that sequelize add 'truncated-name' attributes to instances, is there a fix/workaround ?

[GienekGenin]

+1

[paulmowat]

Just hit this today. Be good to have it. Limits having more than a few nested includes to tie multiple tables together.

[danilospa]

Until the fix is ready, what do you guys think about sequelize throwing an error when aliases got greater than 63 chars on Postgres dialect?

[stephendj]

Encountered this issue today, quite surprised that this issue was opened more than 4 years ago and still relevant.

[swissarmybox]

Ayyy, +1!

[konstantinbarkalov]

17167 starred, most downloadable ORM for node.js (280,788 weekly) is silently returning wrong data from database for four years. Well, that's allright!

[shawnbissell]

Is there any chance of this bug being fixed ever? This bug has plagued us for years now and we have implemented numerous hacks and workarounds. I've had a look and implementing @vagabondan's suggested patch myself, but I'm not even sure that is the correct approach. It's in such a core area of the package it really needs one of the core contributors to at least have a look and suggest an solution. @sdepold @sushantdhiman @mickhansen @janmeier any thoughts or help on this one would be greatly appreciated!

[ClaytonAstrom]

If you don't mind hitting your db for each result's associations, here's a workaround I think. Probably doesn't fit every situation, but it should at least help someone somewhere (please excuse my horrendous js):

const asyncForEach = async (array, callback) => {
  for (let index = 0; index < array.length; index = index + 1) {
    await callback(array[index], index, array);
  }
};

const recurseThroughAssociations = async (findJson, model) => {
  const currModel = sequelize.import(`${__dirname}/../models/${model}`);
  return currModel
    .findAll({
      where: findJson
    })
    .then(async results => {
      const cleanResults = [];
      await asyncForEach(results, async (result, index) => {
        cleanResults.push(result.dataValues);
        await asyncForEach(Object.keys(currModel.associations), async (association) => {
          const nextModel = currModel.associations[association].target.name;
          const nextFind = {};
          if (currModel.associations[association].associationType.includes('Has')) {
            console.log(currModel.primaryKeyField);
            nextFind[currModel.associations[association].foreignKey] =
              result.dataValues[currModel.primaryKeyField];
          } else if (currModel.associations[association].associationType.includes('Belong')) {
            nextFind[sequelize.import(`${__dirname}/../models/${nextModel}`).primaryKeyField] =
              result.dataValues[currModel.associations[association].foreignKey];
          }
          const path = currModel.associations[association].as;
          cleanResults[index][path] = await recur(nextFind, nextModel);
        });
      });
      return cleanResults;
    });
};

Most people will probably screw around with the conditions of creating the nextFind, but in effect this should return something similar (hopefully the same) to what a { include: all, nested: true } returns.

[papb]

@sushantdhiman Could you consider adding this to some milestone?

[CanTheAlmighty]

... . I've had a look and implementing @vagabondan's suggested patch myself, but I'm not even sure that is the correct approach. ...

I think it's exactly the right approach, a bidirectional map that turns all long names "User->Detail->Product->..." into a simplified name "alias_1", and then turned back when the results come in for the given columns. Add the option for the prefix to be adjustable across to prevent accidental matching (say, if you work with an old DB that has columns named a_1...").

Not saying it's easy, but certainly possible.

[rahulverma]

How is this not fixed in 4 years. Need to find a better alternative.

[shawnbissell]

@mrrinot THANK YOU! Finally somebody tackled this issue! I've started testing your PR and will give you feedback there.

[karltaylor]

OMG

[sushantdhiman]

Fixed by https://github.com/sequelize/sequelize/pull/11095

[tapz]

minifyAliases: true, but still getting this with nested joins (Postgre 12):

identifier "AbcdefghijklMnopqrstuvwxy->Skills->abcdefghijkl_abcdefghijklm_skills" will be truncated to "AbcdefghijklMnopqrstuvwxy->Skills->abcdefghijkl_abcdefghijklm_s"

[lukas1994]

Just following up on @tapz comment. Is minifyAliases safe to use? I had to enable it today.

[tapz]

@lukas1994 It seems to be safe, though the produced sql could be even less verbose. However, lately I have had problems with sequelize performance and memory usage and thus moving more to raw SQL.

[CanTheAlmighty]

@lukas1994 I've been running minifyAliases as soon as it launched on multiple projects, no issues so far.