TypeORM Postgres identifier limit is 63 bytes, column aliases get truncated

[jakari]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[ ] @next
[x] 0.2.8

I have found that there are some issues with column aliasing when doing queries from the database. The column names might get very long inside the actual query when they concatenate the entity name into the column name. This becomes an issue in Postgres since the maximum length of a Postgres column is 63 bytes. This is especially a problem with embedded entities.

This same 63 byte name limit restriction applies to column aliases as well.

Currently, when TypeORM creates a query it creates an alias for each column it selects. The alias is of format "tablename_columnname". Now if we have a long table name and a long column name, that are both under 63 by themselves, but concatenated over 63 bytes, TypeORM won't find the column data because of the fact that Postgres also truncates the alias to 63 bytes.

A description of the limit is found in the postgres docs

This issue might not be limited to Postgres only, however I haven't tested other databases.

Steps to reproduce or a small repository showing the problem:

Repository at https://github.com/jakari/typeorm-column-length-issue

An example of the issue by doing a query in Postgres:

[typeorm_test=# SELECT "andHereWehaveAnTooLongEntityNameThatIsVeryDescriptive" AS "EntityWithVeryLongAndDescriptiveName_andHereWehaveAnTooLongEntityNameThatIsVeryDescriptive" FROM entity_with_very_long_and_descriptive_name;
NOTICE:  identifier "EntityWithVeryLongAndDescriptiveName_andHereWehaveAnTooLongEntityNameThatIsVeryDescriptive" will be truncated to "EntityWithVeryLongAndDescriptiveName_andHereWehaveAnTooLongEnti"
 EntityWithVeryLongAndDescriptiveName_andHereWehaveAnTooLongEnti
-----------------------------------------------------------------
                                                               1
(1 row)

This means that TypeORM omits this column result because it can't find the column.

Initial suggestion:

I suggest the table names could be mapped to simple numerical incremental names like:

SELECT "some_table"."some_column" AS "t0_c0, "some_other_table"."some_other_column" AS "t1_c0"

[SebastianEdwards]

Does anyone have a workaround for this problem?

[pleerock]

The only solution for now is to use shorter names. We had same problems in oracle where alias names are so short that almost unusable and we decided to shorten them. We need to apply same strategy to all drivers, depend on their max alias names length. Feel free to contribute!

[ddura]

Can you provide some direction as to what you did in the Oracle driver to work around the length issue? I am not super familiar with the code but would be happy to take a crack at this. This bug has hit us and unfortunately we can't change the column names and don't really want to switch libraries!

[pleerock]

@ddura you can find implementation here.

[Kononnable]

PR was merged, so this issue can be closed.

[wesleyide]

When I have two columns like "transaction_response_reference_code" and "transaction_response_reference_source", both was split in "tran". So I have two equal alias, returning wrong values from database.
Would be interesting if I could specify which ShortenOptions I want to use.
Or alias be independent of columns name, like using just letters, AA, AB, AC, etc, or something like that.

[lekev]

Same issue here on Mysql: I have two columns that have only the last character different :
linear_regression__distance_from_trendline_perc_1 and linear_regression__distance_from_trendline_perc_2.
Unfortunately, I can't change the columns name

[gulsharan]

I was facing the same issue (with Postgres) – solved it using nanoid in a custom naming strategy to generate unique fixed-length aliases instead.

The _aliasCache ensures that the default naming behavior is not broken, i.e. the same alias is returned for an alias-propertyPath pair even if called more than once.

import { customAlphabet } from 'nanoid';
import { DefaultNamingStrategy } from 'typeorm';

const ALIAS_LENGTH = 10;

export class NamingStrategy extends DefaultNamingStrategy {
  
  private _aliasCache: { [key: string]: string } = {};
  
  eagerJoinRelationAlias(alias: string, propertyPath: string): string {

    const key = `${alias}:${propertyPath}`;

    if (this._aliasCache[key]) return this._aliasCache[key];

    const orig = super.eagerJoinRelationAlias(alias, propertyPath);
    const characters = orig.replace(/_/g, '').toUpperCase();
    const nanoid = customAlphabet(characters, ALIAS_LENGTH);
    const out = nanoid();

    this._aliasCache[key] = out;
    return out;
  }
}

/** 
 * Generates unique fixed-length aliases like these -- 
 * 
 *   - SRAGRGERRR
 *   - UTCRRGARTD
 *   - ZTLEAEMMLC
 * 
*/

[switch120]

@gulsharan that was a lifesaver. For me it was Entity relationships that needed to be long to be descriptive, but had similar roots so TypeOrm was generating the same Aliased name for them. Definitely something that should be fixed in TypeOrm!