[BUG]: In the Query API generated identifier exceeds 63-bytes length limit

[lukejagodzinski]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

I have a query using Query API that is doing a lot of nested joins. I've chcecked the generated query and it appears that the generated identifier name is getting truncated because it's too long. From what I read Postgres has a limit of 63-bytes long identifiers. Here is the generated query (I've replaced params with actual values):

SELECT
  "microcycles"."id",
  "microcycles"."name",
  "microcycles"."created_at",
  "microcycles"."updated_at",
  "microcycles_microcycleDays"."data" AS "microcycleDays"
FROM
  "microcycles"
  LEFT JOIN lateral (
    SELECT
      coalesce(
        json_agg(
          json_build_array(
            "microcycles_microcycleDays"."id",
            "microcycles_microcycleDays"."weekday",
            "microcycles_microcycleDays"."created_at",
            "microcycles_microcycleDays"."updated_at",
            "microcycles_microcycleDays"."training_template_id",
            "microcycles_microcycleDays"."microcycle_id",
            "microcycles_microcycleDays_trainingTemplate"."data"
          )
        ),
        '[]' :: json
      ) AS "data"
    FROM
      "microcycle_days" "microcycles_microcycleDays"
      LEFT JOIN lateral (
        SELECT
          json_build_array(
            "microcycles_microcycleDays_trainingTemplate"."id",
            "microcycles_microcycleDays_trainingTemplate"."name",
            "microcycles_microcycleDays_trainingTemplate"."created_at",
            "microcycles_microcycleDays_trainingTemplate"."updated_at",
            "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."data"
          ) AS "data"
        FROM
          (
            SELECT
              *
            FROM
              "training_templates" "microcycles_microcycleDays_trainingTemplate"
            WHERE
              "microcycles_microcycleDays_trainingTemplate"."id" = "microcycles_microcycleDays"."training_template_id"
            LIMIT
              1
          ) "microcycles_microcycleDays_trainingTemplate"
          LEFT JOIN lateral (
            SELECT
              coalesce(
                json_agg(
                  json_build_array(
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."id",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."gender",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."order",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_sets",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_reps",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_weight",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_time",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_kcal",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."reps_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."weight_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."time_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."kcal_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."created_at",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."updated_at",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."exercise_id",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."training_template_id",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."data"
                  )
                ),
                '[]' :: json
              ) AS "data"
            FROM
              "training_step_templates" "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"
              LEFT JOIN lateral (
                SELECT
                  json_build_array(
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."id",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_rep_set",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_weight_set",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_time_set",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_kcal_set",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_sets",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_reps",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_weight",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_time",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_kcal",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."reps_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."weight_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."time_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."kcal_progression",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."created_at",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."updated_at",
                    "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."weight_list_id"
                  ) AS "data"
                FROM
                  (
                    SELECT
                      *
                    FROM
                      "exercises" "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"
                    WHERE
                      "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."id" = "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."exercise_id"
                    LIMIT
                      1
                  ) "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"
              ) "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise" ON TRUE
            WHERE
              "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."training_template_id" = "microcycles_microcycleDays_trainingTemplate"."id"
          ) "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates" ON TRUE
      ) "microcycles_microcycleDays_trainingTemplate" ON TRUE
    WHERE
      "microcycles_microcycleDays"."microcycle_id" = "microcycles"."id"
  ) "microcycles_microcycleDays" ON TRUE
WHERE
  "microcycles"."id" = '93c737a0-d54c-420c-9d47-ebf53cb1215d'
LIMIT
  1;

And the error says:

ERROR:  column microcycles_microcycleDays_trainingTemplate_trainingStepTemplat.exercise_id does not exist
LINE 103: ...ngTemplate_trainingStepTemplates_exercise"."id" = "microcycl...
                                                               ^
HINT:  There is a column named "exercise_id" in table "microcycles_microcycleDays_trainingTemplate_trainingStepTemplat", but it cannot be referenced from this part of the query.

Expected behavior

When identifier length is too long it should try to maybe generate acronyms of tables. Instead of training_step_templates maybe it should use tst or maybe even random names to avoid conflicts.

Environment & setup

No response

[Angelelz]

I think the best solution would be to create an md5 hash. The problem is that it would have a dependency on node. Perhaps we should implement something like some of the answers in this SO question? Like this for example? Maybe do it only when we detect that string.length() > 50 or something like that. @dankochetov ?

[lukejagodzinski]

Does it need to be md5 hash? Could it be just something like table1, table2, table3 ... whenever a new table name has to be used? I don't think there is a risk of name conflict. I guess query builder doesn't run in parallel?

[jakubriedl]

It can be anything that ensures that the any depth of tables will fit inside the 63 characters. So almost any form of hash or table sequence will work.

[Angelelz]

I believe that table1 etc. is a better choice that an md5 because it will be easier if people wants to refer to those for some more advanced use cases.

[lukejagodzinski]

I really need to fix this issue and maybe I can help with it and create some PR. Can you at least point me to the place where the changes should be made?

I was investigating the code and I think the problem is with the relationTableAlias variable in this method buildRelationalQueryWithoutPK.

I'm not sure if changing it will break something so I need some guidance. I think it's just that variable that needs trimming/modification. Am I right?

[Angelelz]

The relational query builder is very complex. This issue needs to be discussed with the team IMO because we need to guarantee uniqueness and repeatability.

[AirZona]

Any update update on this issue? Thanks!

[anishLearnsToCode]

Any updates on this issue ??

[Andkleven]

Any update update on this issue?

[AirZona]

Dying for this to be resolved

[charnowsky]

Any updates? What's the point of a query system if you can't use it?

[charnowsky]

I really need to fix this issue and maybe I can help with it and create some PR. Can you at least point me to the place where the changes should be made?

I was investigating the code and I think the problem is with the relationTableAlias variable in this method buildRelationalQueryWithoutPK.

I'm not sure if changing it will break something so I need some guidance. I think it's just that variable that needs trimming/modification. Am I right?

Solved it for now using this solution, just encoding relationTableAlias and sql.identifier(${tableAlias}_${tsKey}) with MD5, works like a charm. Don't know if there's any repercussions, patching the package has done the trick. If the solution is that simple, please include it, one of the reasons I was using Prisma for so long is the relational queries, I would like them working out of the box here!

[samiulhsohan]

@jakubczarnowski Can you please give an example how you did it?

[Wundero]

@jakubczarnowski Can you please give an example how you did it?

Maybe not how others have done this, but I have implemented a solution which sort of mimic's Django by naming all relations t0, t1, etc.

The idea is to store a map of the existing alias (e.g. microcycles_microcycleDays) to the new alias (e.g. t1), and each time a new alias is created, the number is incremented.

Feel free to co-opt this, I'm not 100% sure this works yet since I haven't fully tested it but I think it could be worth a shot.

Patch file

diff --git a/pg-core/dialect.cjs b/pg-core/dialect.cjs
index 143dd2ca3b5aa953d82348dac97ce5a83e8b62aa..f51f9102870cf92943b35e4d015caab31a896449 100644
--- a/pg-core/dialect.cjs
+++ b/pg-core/dialect.cjs
@@ -882,29 +401,32 @@ class PgDialect {
     tableConfig,
     queryConfig: config,
     tableAlias,
+    tableAliasMap,
     nestedQueryRelation,
     joinOn
   }) {
     let selection = [];
     let limit, offset, orderBy = [], where;
+    const realAlias = tableAliasMap.get(tableAlias) ?? `t${tableAliasMap.size}`;
+    tableAliasMap.set(tableAlias, realAlias);
     const joins = [];
     if (config === true) {
       const selectionEntries = Object.entries(tableConfig.columns);
       selection = selectionEntries.map(([key, value]) => ({
         dbKey: value.name,
         tsKey: key,
-        field: (0, import_alias.aliasedTableColumn)(value, tableAlias),
+        field: (0, import_alias.aliasedTableColumn)(value, realAlias),
         relationTableTsKey: void 0,
         isJson: false,
         selection: []
       }));
     } else {
       const aliasedColumns = Object.fromEntries(
-        Object.entries(tableConfig.columns).map(([key, value]) => [key, (0, import_alias.aliasedTableColumn)(value, tableAlias)])
+        Object.entries(tableConfig.columns).map(([key, value]) => [key, (0, import_alias.aliasedTableColumn)(value, realAlias)])
       );
       if (config.where) {
         const whereSql = typeof config.where === "function" ? config.where(aliasedColumns, (0, import_relations.getOperators)()) : config.where;
-        where = whereSql && (0, import_alias.mapColumnsInSQLToAlias)(whereSql, tableAlias);
+        where = whereSql && (0, import_alias.mapColumnsInSQLToAlias)(whereSql, realAlias);
       }
       const fieldsSelection = [];
       let selectedColumns = [];
@@ -941,7 +463,7 @@ class PgDialect {
         for (const [tsKey, value] of Object.entries(extras)) {
           fieldsSelection.push({
             tsKey,
-            value: (0, import_alias.mapColumnsInAliasedSQLToAlias)(value, tableAlias)
+            value: (0, import_alias.mapColumnsInAliasedSQLToAlias)(value, realAlias)
           });
         }
       }
@@ -949,7 +471,7 @@ class PgDialect {
         selection.push({
           dbKey: (0, import_entity.is)(value, import_sql2.SQL.Aliased) ? value.fieldAlias : tableConfig.columns[tsKey].name,
           tsKey,
-          field: (0, import_entity.is)(value, import_column.Column) ? (0, import_alias.aliasedTableColumn)(value, tableAlias) : value,
+          field: (0, import_entity.is)(value, import_column.Column) ? (0, import_alias.aliasedTableColumn)(value, realAlias) : value,
           relationTableTsKey: void 0,
           isJson: false,
           selection: []
@@ -961,9 +483,9 @@ class PgDialect {
       }
       orderBy = orderByOrig.map((orderByValue) => {
         if ((0, import_entity.is)(orderByValue, import_column.Column)) {
-          return (0, import_alias.aliasedTableColumn)(orderByValue, tableAlias);
+          return (0, import_alias.aliasedTableColumn)(orderByValue, realAlias);
         }
-        return (0, import_alias.mapColumnsInSQLToAlias)(orderByValue, tableAlias);
+        return (0, import_alias.mapColumnsInSQLToAlias)(orderByValue, realAlias);
       });
       limit = config.limit;
       offset = config.offset;
@@ -976,11 +498,13 @@ class PgDialect {
         const relationTableName = relation.referencedTable[import_table2.Table.Symbol.Name];
         const relationTableTsName = tableNamesMap[relationTableName];
         const relationTableAlias = `${tableAlias}_${selectedRelationTsKey}`;
+        const relationRealAlias = `t${tableAliasMap.size}`;
+        tableAliasMap.set(relationTableAlias, relationRealAlias);
         const joinOn2 = (0, import_sql.and)(
           ...normalizedRelation.fields.map(
             (field2, i) => (0, import_sql.eq)(
-              (0, import_alias.aliasedTableColumn)(normalizedRelation.references[i], relationTableAlias),
-              (0, import_alias.aliasedTableColumn)(field2, tableAlias)
+              (0, import_alias.aliasedTableColumn)(normalizedRelation.references[i], relationRealAlias),
+              (0, import_alias.aliasedTableColumn)(field2, realAlias)
             )
           )
         );
@@ -992,14 +516,15 @@ class PgDialect {
           tableConfig: schema[relationTableTsName],
           queryConfig: (0, import_entity.is)(relation, import_relations.One) ? selectedRelationConfigValue === true ? { limit: 1 } : { ...selectedRelationConfigValue, limit: 1 } : selectedRelationConfigValue,
           tableAlias: relationTableAlias,
+          tableAliasMap,
           joinOn: joinOn2,
           nestedQueryRelation: relation
         });
-        const field = import_sql2.sql`${import_sql2.sql.identifier(relationTableAlias)}.${import_sql2.sql.identifier("data")}`.as(selectedRelationTsKey);
+        const field = import_sql2.sql`${import_sql2.sql.identifier(relationRealAlias)}.${import_sql2.sql.identifier("data")}`.as(selectedRelationTsKey);
         joins.push({
           on: import_sql2.sql`true`,
-          table: new import_subquery.Subquery(builtRelation.sql, {}, relationTableAlias),
-          alias: relationTableAlias,
+          table: new import_subquery.Subquery(builtRelation.sql, {}, relationRealAlias),
+          alias: relationRealAlias,
           joinType: "left",
           lateral: true
         });
@@ -1021,7 +546,7 @@ class PgDialect {
     if (nestedQueryRelation) {
       let field = import_sql2.sql`json_build_array(${import_sql2.sql.join(
         selection.map(
-          ({ field: field2, tsKey, isJson }) => isJson ? import_sql2.sql`${import_sql2.sql.identifier(`${tableAlias}_${tsKey}`)}.${import_sql2.sql.identifier("data")}` : (0, import_entity.is)(field2, import_sql2.SQL.Aliased) ? field2.sql : field2
+          ({ field: field2, tsKey, isJson }) => isJson ? import_sql2.sql`${import_sql2.sql.identifier(tableAliasMap.get(`${tableAlias}_${tsKey}`))}.${import_sql2.sql.identifier("data")}` : (0, import_entity.is)(field2, import_sql2.SQL.Aliased) ? field2.sql : field2
         ),
         import_sql2.sql`, `
       )})`;
@@ -1039,7 +564,7 @@ class PgDialect {
       const needsSubquery = limit !== void 0 || offset !== void 0 || orderBy.length > 0;
       if (needsSubquery) {
         result = this.buildSelectQuery({
-          table: (0, import_alias.aliasedTable)(table, tableAlias),
+          table: (0, import_alias.aliasedTable)(table, realAlias),
           fields: {},
           fieldsFlat: [{
             path: [],
@@ -1056,14 +581,14 @@ class PgDialect {
         offset = void 0;
         orderBy = [];
       } else {
-        result = (0, import_alias.aliasedTable)(table, tableAlias);
+        result = (0, import_alias.aliasedTable)(table, realAlias);
       }
       result = this.buildSelectQuery({
-        table: (0, import_entity.is)(result, import_table.PgTable) ? result : new import_subquery.Subquery(result, {}, tableAlias),
+        table: (0, import_entity.is)(result, import_table.PgTable) ? result : new import_subquery.Subquery(result, {}, realAlias),
         fields: {},
         fieldsFlat: nestedSelection.map(({ field: field2 }) => ({
           path: [],
-          field: (0, import_entity.is)(field2, import_column.Column) ? (0, import_alias.aliasedTableColumn)(field2, tableAlias) : field2
+          field: (0, import_entity.is)(field2, import_column.Column) ? (0, import_alias.aliasedTableColumn)(field2, realAlias) : field2
         })),
         joins,
         where,
@@ -1074,11 +599,11 @@ class PgDialect {
       });
     } else {
       result = this.buildSelectQuery({
-        table: (0, import_alias.aliasedTable)(table, tableAlias),
+        table: (0, import_alias.aliasedTable)(table, realAlias),
         fields: {},
         fieldsFlat: selection.map(({ field }) => ({
           path: [],
-          field: (0, import_entity.is)(field, import_column.Column) ? (0, import_alias.aliasedTableColumn)(field, tableAlias) : field
+          field: (0, import_entity.is)(field, import_column.Column) ? (0, import_alias.aliasedTableColumn)(field, realAlias) : field
         })),
         joins,
         where,
diff --git a/pg-core/dialect.js b/pg-core/dialect.js
index efb2308cc31b55004b57b4ec9e6675a2d5c1b4f1..c08c441d7adac3d7c80b99a14f6a5c305db03d45 100644
--- a/pg-core/dialect.js
+++ b/pg-core/dialect.js
@@ -880,29 +399,32 @@ class PgDialect {
     tableConfig,
     queryConfig: config,
     tableAlias,
+    tableAliasMap,
     nestedQueryRelation,
     joinOn
   }) {
     let selection = [];
     let limit, offset, orderBy = [], where;
+    const realAlias = tableAliasMap.get(tableAlias) ?? `t${tableAliasMap.size}`;
+    tableAliasMap.set(tableAlias, realAlias);
     const joins = [];
     if (config === true) {
       const selectionEntries = Object.entries(tableConfig.columns);
       selection = selectionEntries.map(([key, value]) => ({
         dbKey: value.name,
         tsKey: key,
-        field: aliasedTableColumn(value, tableAlias),
+        field: aliasedTableColumn(value, realAlias),
         relationTableTsKey: void 0,
         isJson: false,
         selection: []
       }));
     } else {
       const aliasedColumns = Object.fromEntries(
-        Object.entries(tableConfig.columns).map(([key, value]) => [key, aliasedTableColumn(value, tableAlias)])
+        Object.entries(tableConfig.columns).map(([key, value]) => [key, aliasedTableColumn(value, realAlias)])
       );
       if (config.where) {
         const whereSql = typeof config.where === "function" ? config.where(aliasedColumns, getOperators()) : config.where;
-        where = whereSql && mapColumnsInSQLToAlias(whereSql, tableAlias);
+        where = whereSql && mapColumnsInSQLToAlias(whereSql, realAlias);
       }
       const fieldsSelection = [];
       let selectedColumns = [];
@@ -939,7 +461,7 @@ class PgDialect {
         for (const [tsKey, value] of Object.entries(extras)) {
           fieldsSelection.push({
             tsKey,
-            value: mapColumnsInAliasedSQLToAlias(value, tableAlias)
+            value: mapColumnsInAliasedSQLToAlias(value, realAlias)
           });
         }
       }
@@ -947,7 +469,7 @@ class PgDialect {
         selection.push({
           dbKey: is(value, SQL.Aliased) ? value.fieldAlias : tableConfig.columns[tsKey].name,
           tsKey,
-          field: is(value, Column) ? aliasedTableColumn(value, tableAlias) : value,
+          field: is(value, Column) ? aliasedTableColumn(value, realAlias) : value,
           relationTableTsKey: void 0,
           isJson: false,
           selection: []
@@ -959,9 +481,9 @@ class PgDialect {
       }
       orderBy = orderByOrig.map((orderByValue) => {
         if (is(orderByValue, Column)) {
-          return aliasedTableColumn(orderByValue, tableAlias);
+          return aliasedTableColumn(orderByValue, realAlias);
         }
-        return mapColumnsInSQLToAlias(orderByValue, tableAlias);
+        return mapColumnsInSQLToAlias(orderByValue, realAlias);
       });
       limit = config.limit;
       offset = config.offset;
@@ -974,11 +496,13 @@ class PgDialect {
         const relationTableName = relation.referencedTable[Table.Symbol.Name];
         const relationTableTsName = tableNamesMap[relationTableName];
         const relationTableAlias = `${tableAlias}_${selectedRelationTsKey}`;
+        const relationRealAlias = `t${tableAliasMap.size}`;
+        tableAliasMap.set(relationTableAlias, relationRealAlias);
         const joinOn2 = and(
           ...normalizedRelation.fields.map(
             (field2, i) => eq(
-              aliasedTableColumn(normalizedRelation.references[i], relationTableAlias),
-              aliasedTableColumn(field2, tableAlias)
+              aliasedTableColumn(normalizedRelation.references[i], relationRealAlias),
+              aliasedTableColumn(field2, realAlias)
             )
           )
         );
@@ -990,14 +514,15 @@ class PgDialect {
           tableConfig: schema[relationTableTsName],
           queryConfig: is(relation, One) ? selectedRelationConfigValue === true ? { limit: 1 } : { ...selectedRelationConfigValue, limit: 1 } : selectedRelationConfigValue,
           tableAlias: relationTableAlias,
+          tableAliasMap,
           joinOn: joinOn2,
           nestedQueryRelation: relation
         });
-        const field = sql`${sql.identifier(relationTableAlias)}.${sql.identifier("data")}`.as(selectedRelationTsKey);
+        const field = sql`${sql.identifier(relationRealAlias)}.${sql.identifier("data")}`.as(selectedRelationTsKey);
         joins.push({
           on: sql`true`,
-          table: new Subquery(builtRelation.sql, {}, relationTableAlias),
-          alias: relationTableAlias,
+          table: new Subquery(builtRelation.sql, {}, relationRealAlias),
+          alias: relationRealAlias,
           joinType: "left",
           lateral: true
         });
@@ -1019,7 +544,7 @@ class PgDialect {
     if (nestedQueryRelation) {
       let field = sql`json_build_array(${sql.join(
         selection.map(
-          ({ field: field2, tsKey, isJson }) => isJson ? sql`${sql.identifier(`${tableAlias}_${tsKey}`)}.${sql.identifier("data")}` : is(field2, SQL.Aliased) ? field2.sql : field2
+          ({ field: field2, tsKey, isJson }) => isJson ? sql`${sql.identifier(tableAliasMap.get(`${tableAlias}_${tsKey}`))}.${sql.identifier("data")}` : is(field2, SQL.Aliased) ? field2.sql : field2
         ),
         sql`, `
       )})`;
@@ -1037,7 +562,7 @@ class PgDialect {
       const needsSubquery = limit !== void 0 || offset !== void 0 || orderBy.length > 0;
       if (needsSubquery) {
         result = this.buildSelectQuery({
-          table: aliasedTable(table, tableAlias),
+          table: aliasedTable(table, realAlias),
           fields: {},
           fieldsFlat: [{
             path: [],
@@ -1054,14 +579,14 @@ class PgDialect {
         offset = void 0;
         orderBy = [];
       } else {
-        result = aliasedTable(table, tableAlias);
+        result = aliasedTable(table, realAlias);
       }
       result = this.buildSelectQuery({
-        table: is(result, PgTable) ? result : new Subquery(result, {}, tableAlias),
+        table: is(result, PgTable) ? result : new Subquery(result, {}, realAlias),
         fields: {},
         fieldsFlat: nestedSelection.map(({ field: field2 }) => ({
           path: [],
-          field: is(field2, Column) ? aliasedTableColumn(field2, tableAlias) : field2
+          field: is(field2, Column) ? aliasedTableColumn(field2, realAlias) : field2
         })),
         joins,
         where,
@@ -1072,11 +597,11 @@ class PgDialect {
       });
     } else {
       result = this.buildSelectQuery({
-        table: aliasedTable(table, tableAlias),
+        table: aliasedTable(table, realAlias),
         fields: {},
         fieldsFlat: selection.map(({ field }) => ({
           path: [],
-          field: is(field, Column) ? aliasedTableColumn(field, tableAlias) : field
+          field: is(field, Column) ? aliasedTableColumn(field, realAlias) : field
         })),
         joins,
         where,
diff --git a/pg-core/query-builders/query.cjs b/pg-core/query-builders/query.cjs
index ecb0273fa7068f343fee42c9ad69141a8b4fbcf9..a79c9236937348ceffa6d809d195debc06759454 100644
--- a/pg-core/query-builders/query.cjs
+++ b/pg-core/query-builders/query.cjs
@@ -110,7 +110,8 @@ class PgRelationalQuery extends import_query_promise.QueryPromise {
       table: this.table,
       tableConfig: this.tableConfig,
       queryConfig: this.config,
-      tableAlias: this.tableConfig.tsName
+      tableAlias: this.tableConfig.tsName,
+      tableAliasMap: new Map(),
     });
   }
   /** @internal */
diff --git a/pg-core/query-builders/query.js b/pg-core/query-builders/query.js
index ac64578184db985daeb308b20f92cb96bbfe917f..8eba2e7e297d2a7e4a05e70b73640319f0bd9a28 100644
--- a/pg-core/query-builders/query.js
+++ b/pg-core/query-builders/query.js
@@ -88,7 +88,8 @@ class PgRelationalQuery extends QueryPromise {
       table: this.table,
       tableConfig: this.tableConfig,
       queryConfig: this.config,
-      tableAlias: this.tableConfig.tsName
+      tableAlias: this.tableConfig.tsName,
+      tableAliasMap: new Map(),
     });
   }
   /** @internal */

[AirZona]

I am curious what the team thinks of @jakubczarnowski 's solution, are there and repercussions to this. If not, would love to see them commit it to the project.

[lirbank]

If it's helpful to someone else, I work around the issue with a little helper like this:

/**
 * Truncate a constraint name to 63 characters and prepend a provided random
 * string to it to make it unique.
 */
function trunkateConstraintName(name: string, id: string) {
  const delimiter = "_";
  const extension = "_fk";

  const end = 63 - id.length - extension.length - delimiter.length;

  return id + delimiter + name.slice(0, end) + extension;
}

For the id param is provide a random 8 char string. Note that this should be a hardcoded random string, not generated at RT.

[gpaiva985]

Any update on this?

[gpaiva985]

I really need to fix this issue and maybe I can help with it and create some PR. Can you at least point me to the place where the changes should be made? I was investigating the code and I think the problem is with the relationTableAlias variable in this method buildRelationalQueryWithoutPK. I'm not sure if changing it will break something so I need some guidance. I think it's just that variable that needs trimming/modification. Am I right?

Solved it for now using this solution, just encoding relationTableAlias and sql.identifier(${tableAlias}_${tsKey}) with MD5, works like a charm. Don't know if there's any repercussions, patching the package has done the trick. If the solution is that simple, please include it, one of the reasons I was using Prisma for so long is the relational queries, I would like them working out of the box here!

How exactly you did this? Can you give a more detailed example, please?

[AirZona]

Dying to get this resolved, thank you!

[MrCuriousGuy]

The relational query builder is very complex. This issue needs to be discussed with the team IMO because we need to guarantee uniqueness and repeatability.

@Angelelz Have you had the chance yet to talk to the team? This bug is quite critical for us. Thanks!

[wohlben]

Its also very easy to encounter this issue with the auto generated foreign key constraint names.

I guess the drizzle team are shortened table name enjoyers, making everyone rename their fully spelled out tables and fields into 3 -5 letter acronyms

[n3xt-auz]

Another company who seeks to have this resolved!

[AirZona]

bump...

[L-Mario564]

Closing in favor of #2066.