FG
💻 Software🗄️ Databases

JoinTable query generates incorrectly when entity doesn't match standard naming conventions

Fresh3 days ago
Mar 14, 20260 views
Confidence Score58%
58%

Problem

Steps to reproduce or a small repository showing the problem: 1. Create two entities that don't follow the naming convention (ex. `Thing` in table `things` and `Tag` in table `tags`) 2. Create a join table for them (`thing_tags`) (note that due to the departure from naming convention earlier, this is also a departure from naming convention) 3. Create a `@ManyToMany` relation using `@JoinTable` inside `Thing` as follows: [code block] When trying to retrieve a `Thing`, the query is just barely wrong: [code block] The problem here is that `Thing_tags_relation_id` is not quoted everywhere it's mentioned. If I take the query to postgres, and add quotes around every unquoted `Thing_tags_relation_id`, the query works great: [code block] PS I personally would love a `parametrizedQuery` parameter in error reports that prints the complete query with params filled out (exactly as it would have been sent). As always thanks for all the work on `typeorm`, it is 100% improving the development lives of me and my team :)

Error Output

ERROR:  missing FROM-clause entry for table "thing_tags_relation_id"

Unverified for your environment

Select your OS to check compatibility.

1 Fix

Canonical Fix
Unverified Fix
New Fix – Awaiting Verification

Fix JoinTable Naming Convention for Correct Query Generation

Medium Risk

The issue arises because the naming conventions for the entities and the join table do not align with TypeORM's expectations. Specifically, the generated SQL query does not properly quote the identifiers for the join table, leading to errors when querying the database.

Awaiting Verification

Be the first to verify this fix

  1. 1

    Update Entity Naming Conventions

    Ensure that the entity names and table names follow a consistent naming convention. For example, rename the `Thing` entity to `ThingEntity` and the `Tag` entity to `TagEntity`.

    typescript
    class ThingEntity { /* entity definition */ }
class TagEntity { /* entity definition */ }
  2. 2

    Modify JoinTable Definition

    Update the `@JoinTable` decorator in the `ThingEntity` to specify the correct table name and column names explicitly, ensuring they are quoted correctly.

    typescript
    @ManyToMany(() => TagEntity)
@JoinTable({
  name: 'thing_tags',
  joinColumn: { name: 'thing_id', referencedColumnName: 'id' },
  inverseJoinColumn: { name: 'tag_id', referencedColumnName: 'id' }
})
private tags: TagEntity[];
  3. 3

    Ensure Proper Quoting in Queries

    Review the generated SQL queries to ensure that all identifiers, especially for the join table, are properly quoted. This can be done by modifying the TypeORM configuration to enable quoting for identifiers.

    typescript
    const connection = new Connection({
  type: 'postgres',
  // other connection options
  namingStrategy: new DefaultNamingStrategy(),
  // Ensure identifiers are quoted
});
  4. 4

    Implement Parameterized Query Logging

    Add a feature to log parameterized queries with filled parameters to help in debugging and ensure that the queries are generated as expected.

    typescript
    const logger = new Logger();
connection.logger = logger;
logger.logQuery = (query, parameters) => {
  console.log('Query:', query, 'Parameters:', parameters);
};
  5. 5

    Test the Changes

    Run the application and test retrieving a `Thing` entity to confirm that the query executes without errors and returns the expected results.

    typescript
    const thing = await repository.findOne({ where: { id: 1 }, relations: ['tags'] });
console.log(thing);

Validation

Confirm that the query executes successfully without errors and that the expected `Thing` entity and its related `Tag` entities are returned correctly. Check the logs for the parameterized query output to ensure it matches expectations.

Sign in to verify this fix

Environment

Submitted by

AC

Alex Chen

2450 rep

Tags

typeormormtypescriptbug