Question 89535 in PostgreSQL Question posted in
The official documentation can be found here.

Is there a way to automatically convert any field of type int to a hashed string? – Postgresql

I am looking for a way that is not too hacky to hash all ids before returning to user, is there a way to do so? I’ve searched all over docs and couldn’t find anything that handles all scenarios.

I am using Postgres + Prisma ORM and have multiple models with relational fields. Ideally I would achieve the desired behavior using Prisma.

I have tried Jordan reply and my code looks like this.

const getPrismaClient = () => {
  const client = new PrismaClient();
  client.$extends({
    result: {
      blog: {
        // We basically create a virtual `hashedId` field
        hashedId: {
          needs: { id: true },
          compute(client) {
            return intToHashedString(client.id);
          },
        },
      },
    },
  });
  client.blog
    .findMany({
      select: { hashedId: true },
    })
    .then((r) => {
      console.log(r);
    });
  return client;
};

but it throws this error

error - unhandledRejection: PrismaClientValidationError: 
Invalid `prisma.blog.findMany()` invocation:
{
  select: {
    hashedId: true,
    ~~~~~~~~
?   id?: true,
?   title?: true,
?   slug?: true,
?   image?: true,
?   description?: true,
?   views?: true,
?   authorId?: true,
  clientVersion: '4.11.0'
}
Tags:

2

Answers


  1. 0

    There are undocumented, but largely safe-to-use hashing functions for every built-in data type in Postgres. Those are used for hash partitioning and indexing, among other things. For integer (int4) input:

    hashint4(int) → int
hashint4extended(int, bigint) → bigint

    The second form requires Postgres 12 or later, additionally takes a seed as 2nd parameter, and produces a larger bigint hash to make collisions less likely.

    Or, to avoid hash collisions almost certainly, go even bigger with the good old md5() function to get strings of 32 characters (matching a UUID). Related:

    But md5() takes text, so you have to cast your integer input.
    Examples:

    SELECT hashint4(my_id)            AS my_id_int4_hash
     , hashint4extended(my_id, 0) AS my_id_int8_hash
     , hashint4(my_id)            AS my_id_md5_hash
FROM   tbl
LIMIT  10;

    Cast numeric results to text with ::text if you insist on "strings".

    Related (consider the chapter "Probability of a hash collision?"):

    You might create a (MATERIALIZED) VIEW of your table with hashed values to save some typing and computation (though the functions are very performant).

    Login or Signup to reply.
  2. 0

    You can use Prisma Client results extension (They’re still under a feature flag, so at your own risk).

    First enable the feature

    // schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["clientExtensions"]
}

    And then, here is how you could use it:

    import { PrismaClient } from '@prisma/client';
import { scrypt } from 'node:crypto';

const prisma = new PrismaClient();

const xprisma = prisma.$extends({
  result: {
    client: {
      // We basically create a virtual `hashedId` field
      hashedId: {
        needs: { id: true },
        compute(client: { id: string }) {
          return new Promise<string>((resolve, reject) => {
            // I used scrypt here as an example
            scrypt(client.id, 'salt', 64, (err, hashed) => {
              if (err) return reject(err);
              return resolve(hashed.toString('hex'));
            })
          })
        }
      }
    }
  }
});

//                    ,--- Using xprisma, instead of prisma
//                   v
const client = await xprisma.client.findFirst({
  where: { id: '092a5486-9ca2-4c4c-85e6-da7cedb281a5' },
  // hashedId is then autocompleted in available fields
  select: { hashedId: true },
});

    You’ll need to handle performance issues, but it should answer your need.

    EDIT: Note that prisma.$extends returns a new PrismaClient which will include your extensions. Otherwise, it won’t compile. Here’s why I kept the doc’s convention of using xprisma.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search