How to disable automatic downcasing in NestJS' typeORM query to my postgres DB?

Question

I am using TypeORM to create and manage my DataBase using TypeORM Entities.

I have a very peculiar relation as such

@Entity()
export class User extends BaseEntity {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  login: string;

  @Column()
  nickname: string;

  @Column()
  wins: number;

  @Column()
  looses: number;

  @Column()
  current_status: string;

  @ManyToMany(() => User)
  @JoinTable()
  friends: User[];

  @ManyToMany(() => MatchHistory)
  @JoinTable()
  match_histories: MatchHistory[];
}

Where User has a ManyToMany relationship with itself. Because of this, typical tools do not work correctly (I haven't found a way to access a User's friends with TypeORM's tools).

So I am doing a good old SQL request as such:

const res = await this.manager.query("SELECT * FROM user WHERE user.id IN (SELECT F.userId_2 AS Friends FROM user_friends_user F WHERE F.userId_1=?);", [user.id]);

This can be translated as "get all the users who's ID is in the friend list of user user.

The problem is that I have noticed that all my requests seem to be downcased. When doing this I face the column f.userid_2 does not exist.

I do not know if it is TypeORM or Postgres which downcases my requests, all I want is it to stop doing so. Thank you very much !

Jackie McDoniel · Accepted Answer · 2021-06-28 15:57:12Z

1

This is something Postgres does by default. If you need to use uppercase values, you need to pass a string literal to Postgres. In your case, that would look like

const res = await this.manager.query(
  'SELECT * FROM user WHERE user.id IN (SELECT "F"."userId_2" AS "Friends" FROM user_friends_user "F" WHERE "F"."userId_1"=?);',
  [user.id]
);

answered Jun 28, 2021 at 15:57

Jackie McDoniel

71.7k9 gold badges191 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ORoberts Over a year ago

Exactly what I needed. little precision that, apparently, to the the parameters the correct syntax is @1 (postgres seems to be the only system using this syntax)

ORoberts Over a year ago

Edit: it is actually $1, as in bash

Collectives™ on Stack Overflow

How to disable automatic downcasing in NestJS' typeORM query to my postgres DB?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related