Issue with snake_case naming strategy

[bssergy]

Issue type:

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

Database system/driver:

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

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have two tables:

product

id

product_attachment

id
product_id

var productAttachment = repository.findAndCount({ relations: 'product'});

QueryFailedError: ER_DUP_FIELDNAME: Duplicate column name 'ProductAttachment_product_id'

This issue happens because query builder generate alias ProductAttachment_product_id twice:
product_attachment.product_id -> ProductAttachment_product_id
product_attachment.product.id -> ProductAttachment_product_id

[pleerock]

Can you provide more info? relations: 'product' is not correct, it shall be { relations: ['product'] }. Can you show your entities and what are actually doing?

[bssergy]

@pleerock

@Entity('product')
export class Product {

  @PrimaryGeneratedColumn()
  id: number;

  @OneToMany(() => ProductAttachment, productAttachment => productAttachment.product)
  @JoinColumn({ referencedColumnName: 'product_id' })
  product_attachments: ProductAttachment[];
}

@Entity('product_attachment')
export class ProductAttachment {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  product_id: number;

  @ManyToOne(() => Product, product => product.product_attachments)
  @JoinColumn({ name: 'product_id', referencedColumnName: 'id' })
  product: Product;
}

const attachments = await connection
  .getRepository(ProductAttachment)
  .findAndCount({ relations: ["product"] });

[pleerock]

Sounds like a bug. In 0.3.0 "relations" are completely refactored, so this issue shall be fixed in @next.

[ValiDraganescu]

I am using a custom naming strategy where all the interface functions are implemented and this is how I add it to the connection options:

let baseConfig: ConnectionOptions = {
  "type": "mysql",
  "host": localHost,
  "port": 3306,
  "username": "username",
  "password": "password",
  "database": "database",
  "bigNumberStrings": false,
  "charset": "utf8mb4",
  "connectTimeout": 1000,
  "synchronize": false,
  "logging": false,
  "cli": {
    "entitiesDir": "src/entity",
    "migrationsDir": "src/migration",
    "subscribersDir": "src/subscriber"
  },
  "namingStrategy": new MyNamingStrategy()
};

When I run migration:generate I get this error

TypeError: namingStrategy.tableName is not a function
    at EntityMetadata.build (/Users/user/git/project/subproject/node_modules/typeorm/metadata/EntityMetadata.js:467:58)

Why is this happening and how to fix it?
I'm using typeorm@0.2.7

[pleerock]

try to debug, what is your namingStrategy instance that its calling tableName of?

[ValiDraganescu]

Fixed it, we now have repurposed some cli code to build our migrations.
It was normal to be undefined as the cli is using ormconfig.json and you cannot have naming strategy there. Or can you?

[bhoudu]

Has someone found out a way to use a naming strategy when generating migration through typeorm cli?
Does using an ormconfig in js format enables this?

[fan-jin]

@bhoudu I ended up extending the default TableColumn and TableForeignKey as follows:

MyTableColumn.ts

import {TableColumn } from 'typeorm';
import { TableColumnOptions } from "typeorm/schema-builder/options/TableColumnOptions"
import { snakeCase } from 'typeorm/util/StringUtils';

export default class MyTableColumn extends TableColumn {
  constructor(options?: TableColumnOptions) {
    options.name = snakeCase(options.name);
    super(options)
  }
}

MyTableForeignKey.ts

import {TableForeignKey } from 'typeorm';
import { TableForeignKeyOptions } from "typeorm/schema-builder/options/TableForeignKeyOptions"
import { snakeCase } from 'typeorm/util/StringUtils';

export default class MyTableForeignKey extends TableForeignKey {
  constructor(options?: TableForeignKeyOptions) {
    options.columnNames = options.columnNames.map(name => snakeCase(name))
    options.referencedColumnNames = options.referencedColumnNames.map(name => snakeCase(name))
    super(options)
  }
}

Then in my migrations, I use new MyTableColumn and new MyTableForeignKey.

[jeromesth]

I had a similar issue and using @next works.

[grbatinic-bogdan]

I ended up creating javascript config file that I only use to generate migration based on model changes.

This is my migration-ormconfig.js file in the root of the project. One thing worth noting about this is that you can't have .env file with this setup. I have my environment variables named differently to avoid overriding configuration by accident.

const { CustomNamingStrategy } = require('FILE_PATH_TO_MY_CUSTOM_STRATEGY_FILE');

module.exports = {
    type: 'postgres',
    host: '127.0.0.1',
    port: 5432,
    username: 'username',
    password: 'pwd',
    database: 'dbname',
    synchronize: false,
    entities: ['PATH_TO_MODEL_DIR'],
    migrations: ['PATH_TO_MIGRATION_DIR'],
    migrationsRun: true,
    logging: true,
    namingStrategy: new CustomNamingStrategy(),
    cli: {
        migrationsDir: 'MIGRATIONS_DIR_PATH'
    }
};

And then I have my custom migration generate command in package.json:

"migration:generate": "./node_modules/.bin/ts-node node_modules/.bin/typeorm migration:generate -f migration-ormconfig -n $1"

which is being used like this:

npm run migration:generate NameOfMyMigration

[cliedelt]

Issue type:

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

Database system/driver:

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

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have two tables:

product

id

product_attachment

id
product_id

var productAttachment = repository.findAndCount({ relations: 'product'});

QueryFailedError: ER_DUP_FIELDNAME: Duplicate column name 'ProductAttachment_product_id'

This issue happens because query builder generate alias ProductAttachment_product_id twice:
product_attachment.product_id -> ProductAttachment_product_id
product_attachment.product.id -> ProductAttachment_product_id

how could this not been fixed yet? :(

[itairaz1]

Workaround: Downgrade to 0.2.9 solved the problem, here

[vicdup]

The same error is still present in all versions : ^0.3.0-alpha.23, 0.2.17, 0.2.9....

The patch 79807e1 does not seem to fix this as this is on the find function, not findOne.

@pleerock Please advise on how to fix this and I can work on it. This is super annoying and basically making TypeORM useless when working with existing snake_case databases.

Exemple : I have a "Contact" that have many "Bookings".
When doing a find the query builder will use alias Booking_contact_id for booking.contact_id and booking_contact.id....

[zwenza]

Can confirm that setting the snake-case naming strategy fixes the issue!

const SnakeNamingStrategy = require('typeorm-naming-strategies').SnakeNamingStrategy;

module.exports = {
   type: "mysql",
   host: "localhost",
   port: 3306,
   ...
   namingStrategy: new SnakeNamingStrategy(),
}

[ladrahul00]

Upgrading to typeorm@0.2.25 fixed it for me.

[jimsorock]

Has the original issue here been addressed? I see other talking about the snake case naming strategy but that doesn't answer or solve the issue. Snake case is actual part of the cause here.

[JessJNielsen]

It is solved in 0.2.9 as far as i can tell.

[lukzard]

Not resolved, can't believe that this is working for short words like pairId and does not for longer ones like buyerSendingAddress...

[lukzard]

also, it's the other way around - then you define your entity using camel case it does not rework back to snake case - this is as per definition, but likely no one on this planet uses camel case style in db, especially rdbmss

[blitzmann]

I am still experiencing the issue myself using a custom snake case naming strategy, see #7775. It's still present in v3.6. The example I have is nothing complicated, and can be shown to be bugged without a naming strat, but just a custom column name for the join (@JoinColumn({ name: "author_id" })) is enough to trigger funkiness.

eg:

@Entity("authors")
export class Author {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany((type) => Post, (post) => post.author)
    posts: Post[];
}

@Entity("posts")
export class Post {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

    @Column()
    text: string;

    @ManyToOne((type) => Author, (author) => author.posts)
    @JoinColumn({ name: "author_id" })
    author: Author;
}

The weird part is that is seems to affect drivers differently. sqlite driver works fine, but mssql driver brings back author_id (for my example) twice, which generates a collection of ids
sqlite

[
  Post {
    id: 1,
    title: 'Post 1 title',
    text: 'Post 1 text',
    author: Author { id: 1, name: 'Test Author' }
  },
  Post {
    id: 2,
    title: 'Post 2 title',
    text: 'Post 2 text',
    author: Author { id: 1, name: 'Test Author' }
  }
]

mssql

[
  Post {
    id: 1,
    title: 'Post 1 title',
    text: 'Post 1 text',
    author: Author { id: [Array], name: 'Test Author' }
  },
  Post {
    id: 2,
    title: 'Post 2 title',
    text: 'Post 2 text',
    author: Author { id: [Array], name: 'Test Author' }
  }
]

This is problematic for existing databases that have snake case and can't easily update to a new naming strat.

@pleerock can you re-evaluate this? It's been a while since you've commented and it was thought that v3 should fix this, but it is still an issue. Or if you have any insight as to where to look for the discrepancy in drivers, I would be interested in digging in a little

[blitzmann]

@pleerock alternatively, perhaps it may be a solution to allow the client, per connection, to determine the joiner character that they should use for the purposes of internal ORM mapping? Not sure how large of a change that may be (I assume pretty large if it's always been assumed _ would be standard). This will allow databases set up for snake_case to be able to provide their own character(s) (maybe this is technically possible already with custom naming strat, I'm not too familiar with it)

[blitzmann]

I'm re-opening my previous issue as it seems to be an issue with the driver itself (or rather, interpretation of the mssql driver results). Posting my findings at #7775.

[deivi98]

Same issue here. The issue can be solved just by adding a prefix to select aliases, like this:

qb.addSelect(selection, "test_" + selection.replaceAll(".", "_"));

The problem is that raw results then contain this prefix and I am unable to map results to entity. I have seen many workarounds, but all of them are for simple entities. I am working with multiple level entity relations.