Drizzle Team•4mo ago

How do I dynamically insert multiple encrypted values?

  const encryptedEmails = emails.map(email => encryptColumn(email));

  const fetchedUsers = await db
    .select({
      id: neptun_user.id,
    })
    .from(neptun_user)
    /* @ts-ignore */
    .where(inArray(neptun_user.primary_email, encryptedEmails))
    .catch((err) => {
      if (LOG_BACKEND) console.error('Failed to fetch user from database', err);
      return null;
    })

  const encryptedEmails = emails.map(email => encryptColumn(email));

  const fetchedUsers = await db
    .select({
      id: neptun_user.id,
    })
    .from(neptun_user)
    /* @ts-ignore */
    .where(inArray(neptun_user.primary_email, encryptedEmails))
    .catch((err) => {
      if (LOG_BACKEND) console.error('Failed to fetch user from database', err);
      return null;
    })

1 Reply

JonasOP•4mo ago

The code above creates this:

Query: select "id" from "neptun_user" where "neptun_user"."primary_email" in (encode(encrypt($1, $2, 'aes'), 'hex'), encode(encrypt($3, $4, 'aes'), 'hex'), encode(encrypt($5, $6, 'aes'), 'hex')) -- params: ["[email protected]", "xxx", "[email protected]", "xxx", "[email protected]", "xxx"]

Query: select "id" from "neptun_user" where "neptun_user"."primary_email" in (encode(encrypt($1, $2, 'aes'), 'hex'), encode(encrypt($3, $4, 'aes'), 'hex'), encode(encrypt($5, $6, 'aes'), 'hex')) -- params: ["[email protected]", "xxx", "[email protected]", "xxx", "[email protected]", "xxx"]

This doesn't seem to work properly. Can i not use functions inside of inArray? I feel like this just compares the strings and not the computed values [email protected] exists and the result is an empty array also tried like this: .where(sqlneptun_user.primary_email IN (${sql.join(encryptedEmails, sql, )})) same issue with raw sql, seems like functions do not work in postgres IN

const encryptedEmails = emails.map(email => encryptColumn(email));
  const fetchedUsers = await db.execute<ReadChatConversationShare>(sql`SELECT id FROM neptun_user WHERE primary_email IN (${sql.join(encryptedEmails, sql`, `)})`);

const encryptedEmails = emails.map(email => encryptColumn(email));
  const fetchedUsers = await db.execute<ReadChatConversationShare>(sql`SELECT id FROM neptun_user WHERE primary_email IN (${sql.join(encryptedEmails, sql`, `)})`);

Gaming

Programming

How do I dynamically insert multiple encrypted values?