TypeORM Postgres WHERE ANY or IN With QueryBuilder Or Find?

Question

I've been trying to figure out how to select every record in a Postgres db that includes a certain id in a column that has arrays of integers. I'm new at all this but I believe I want an SQL statement that looks like this:

SELECT * FROM members WHERE 2 = ANY(skill_id_array);

The data in the skill_id_array column looks like this: {1,4,7}.

From the Angular front-end I pass in an id such as 2 as a parameter. The logging in terminal shows the end of the select statement as this but it fails:

... WHERE $1 ANY (skill_id_array) -- PARAMETERS: [2]

I believe the issue is I'm trying to pass a variable into the code and it hates it. Notice that I've been trying the find method and QueryBuilder. Resulting errors are in the comments. (Full CRUD is working so my overall setup is fine.)

This works great but I understand this has a sql injection problem:

const membersBySkill = await this.entityManager.query(
      `SELECT * FROM members WHERE ${integerId} = ANY(members.skill_id_array)`
    );

This works but I'm not sure of a sql injection problem plus I would like to use TypeORM find or QueryBuilder.

  const sql = 'SELECT * FROM members WHERE '+ integerId + ' = ANY(skill_id_array)';
  const membersBySkill = await this.entityManager.query(sql);

In my service:

import {Any} from "typeorm";

  async getMembersBySkill(id) {
    const integerId = parseInt(id, 10); // convert id to integer for Postgres array.
    // console.log('skill_id in service', integerId);
    // Find one skill id in an array of id's.
/*
    const membersBySkill = await this.connection.getRepository(Members).find({
      skill_id_array: Any(integerId)
    });
    //  This results in an IDE error.  It doesn't like a number as 
    //  the ANY param.  "{integerId: number} is not assignable to
    //  parameter type '{}[] | FindOperator<{}>'.
    //  In terminal: error: could not find array type for data type integer[].  
    //  However, the db has integers.
*/

    const membersBySkill = await getRepository(Members)
      .createQueryBuilder("members")
      .select('*')
      .where(":id IN (skill_id_array)", {id: integerId})
      .getMany();
      // SELECT * FROM "members" "members" WHERE $1 IN (skill_id_array) -- PARAMETERS: [2]
      // server console: error: malformed array literal: "2"

    console.log('membersBySkill: ', membersBySkill);
    return membersBySkill;
  }

The entity:

  @Column('int', { array: true, nullable: true})
  skill_id_array: number[];

The Postgres column type: integer[]

I stop and start the Nestjs server with every change.

Answer 1

currently it is possible with syntax

.where("id IN(:...ids)", { ids: [1,2,3] })

Answer 2

If you use OOP

const list = await this.repository.find({ where: { id: In([...idArr]) } });