Mikro-ORM: error: there is no parameter $1

Alright so I’ve been trying to search Mikro-ORM’s docs to find how to pass in native sql query params in but I haven’t been able to find anything. After playing with the code a bit this is what it looks like which I think is sort of right but I’m getting this error

error: SELECT DISTINCT * FROM direct_messages WHERE receiver_id = $1 OR sender_id = $1 ORDER BY sent_at DESC - there is no parameter $1
    at Parser.parseErrorMessage (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:400:28)
    at Socket.emit (domain.js:470:12)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 94,
  severity: 'ERROR',
  code: '42P02',
  detail: undefined,
  hint: undefined,
  position: '60',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_expr.c',
  line: '907',
  routine: 'transformParamRef'
}

My code currently looks like this

@Query(() => String)
  async Conversations(@Ctx() { em, userData }: MyContext): Promise<string> {
    const connection = em.getConnection();
    const queryString = `SELECT DISTINCT * FROM direct_messages WHERE receiver_id = $1 OR sender_id = $1 ORDER BY sent_at DESC`;
    return await connection
      .execute(queryString, [userData['sub']])
      .then((results) => {
        console.log(results);
        return 'worked';
      })
      .catch((error) => {
        console.log(error);
        return 'Failed';
      });
  }

Just for some context userData[‘sub’] is the userID of type string from the googleOAuth API. Thank you!

Answer

You need to use ? instead of $1 in the raw query. This is how knex, the underlying query builder, works.

const connection = em.getConnection();
const queryString = `SELECT DISTINCT * FROM direct_messages WHERE receiver_id = ? OR sender_id = ? ORDER BY sent_at DESC`;
return await connection
  .execute(queryString, [userData['sub'], userData['sub']])

Alternatively you could use knex’s named bindings, which is similar (allows to use one parameter multiple times in the query):

// the `em` needs to be typed to `SqlEntityManager` to have the `getKnex` method
const knex = em.getKnex();
const qb = knex.raw('SELECT DISTINCT * FROM direct_messages WHERE receiver_id = :id OR sender_id = :id ORDER BY sent_at DESC', { id: userData['sub'] });
const res = await em.execute(qb);
// ...

Or the MikroORM query builder has qb.raw() method that is just a shortcut to em.getKnex().raw():

https://github.com/mikro-orm/mikro-orm/blob/master/tests/QueryBuilder.test.ts#L1314-L1319