Camel cased property names produce quoted column names in DB but lower case do not

[IRCraziestTaxi]

I got an error that threw me off for several minutes while using QueryBuilder.OrderBy when trying to reference a camel cased column name. I didn't realize it was quoted in the database and I had to actually put double quotes in the string passed to OrderBy. Without knowing the details of the code that handles that, it seems to me like there could be a better, more uniform way of avoiding that error. Maybe all property names passed into the DB could be automatically quoted? Again, not sure what the implications are, but it definitely doesn't feel right having several quoted column names while most are not and having to deal with them differently.

[pleerock]

Are you using postgres? Yeah right now quoting is an issue and we need to figure out how to handle that properly. The problem is that query builder just helps you to create an sql query. You can create almost any kind of sql query in query builder. And the question is that if its possible to always know what needs to be quoted in this sql query and whats not (can we parse it and always find whats needs to be quoted and whats not).

[IRCraziestTaxi]

Sorry, I should have specified. Yes, I am using postgres.

[IRCraziestTaxi]

I did discover that quoting column names that are not quoted in the table definition did not affect the query, which is why the thought crossed my mind that quoting could always be automatic.

[rightisleft]

I've seen this behavior as well.

[pleerock]

@IRCraziestTaxi can you provide a code example where I can reproduce your issue? Also whats your own opinion on how orm should handle quotes in custom sql expressions passed to query builder? I need somebody who knows all aspects of sql and is it possible to understand where alias or some column/table name is used to automatically replace it in any kind of sql query. But I guess that its not possible.

[IRCraziestTaxi]

@pleerock I will work on a code sample. Any recommendations on delivery method? You want me to just send you a zip somehow or put it up on some site?

It's pretty simple to reproduce, for anybody curious - just create a model with some all-lower-case properties and some camel-cased properties, sync schema (Postgres), try to use (for example) OrderBy in QueryBuilder, and observe that an error is thrown if camel-cased property names are not double-quoted. You can see the reason why by observing your DB schema - the column names for camel cased properties on table definitions are double quoted since Postgres apparently can't handle capital letters without quotes. I know your time is valuable, so I will still work on that code sample.

Honestly, I'm not a SQL expert, which is the biggest reason I use ORMs and was so excited to find this awesome Node/TS one (kudos again, seriously). I'm not sure if auto-quoting strings going into OrderBy, etc. would cause issues elsewhere, and there's still the issue of backwards-compatibility with existing code that already double quotes property names to mitigate the error that is thrown - checking for double quotes before automatically adding them would make the code heavier for sure. There may not be an extremely elegant solution without investigating aliases or something, but I'm not enough of a SQL expert to speculate on that.

[pleerock]

I need smallest possible reproduction example you can post it right here in github

[NoNameProvided]

Here is an example:

@Entity()
export class Recipe {
  @PrimaryGeneratedColumn()
  public id: number;

  @Column({ default: 0 })
  public likesCount: number;
}

// then run
this.recipeRepository.createQueryBuilder('recipe')
      .update(Recipe)
      .where('recipe.id = :id', { id: 1 })
      .set({ likesCount: () => '"likesCount" + 1'})
      .execute();

This will generates the following sql:

UPDATE "recipe" SET likesCount = "likesCount" + 1 WHERE recipe.id = 1;

What will throw the following error:

ERROR:  column "likescount" of relation "recipe" does not exist
LINE 1: UPDATE "recipe" SET likesCount = "likesCount" + 1 WHERE reci...
                            ^

The generated query should be:

UPDATE "recipe" SET "likesCount" = "likesCount" + 1 WHERE recipe.id = 1;

The problem is there is no way to tell TypeORM to tell that I want to quote the keys of the object used in the set() function.

[pleerock]

@NoNameProvided your issue is fixed and will be released in next version. However if author still have some example how to reproduce his issue - please let me know.