How to generate unique number getting from postgres sql in jest tests running concurrently

i have an issue with unique constraint on one of my fields. I'm adding records to database to be able to check by tests is my code working as expected. One of table field is unique number that is provided from outside (it's not related to some other table in the same database), i need to generate this unique number for each test, but i met with unique constraint issue. I have following function:

export const findMinUniqueUserId = async (): Promise<number> => {
    const subscriptions = await prisma.$queryRaw<Subscription[]>(`
            SELECT "userId"
            FROM public."Subscriptions"
            ORDER BY "userId" DESC
            LIMIT 1
        `);

    const firstFreeUserId = (subscriptions[0]?.userId || 0) + 1;

    return firstFreeUserId;
};

that returns the first minimum free "userId" field. I have also the following tests:

describe("Test 1", () => {
    it("should do something", async () => {
        const draftSub = {
            userId: await findMinUniqueUserId()
            ...some other fields
        }

        await prisma.subscription.create({
            data: draftSub
        })
        ...some other test stuff
    })
})

And the second one:

describe("Test 2", () => {
    it("should do something", async () => {
        const draftSub = {
            userId: await findMinUniqueUserId()
            ...some other fields
        }

        await prisma.subscription.create({
            data: draftSub
        })
        ...some other test stuff
    })
})

Sometimes i'm getting an error:

Unique constraint failed on the fields: (`userId`)

I've heard that each of test suit (describe block) works on seperate worker thread, i was trying to prepare some kind of singleton class, that can helps me but i think each instance of class is creating in separete worker thread, so generated userId is not unique.

This is what i was trying with singleton class:

export class UserIdManager {
    private static instance: UserIdManager
    private static userIdShiftBeforeDatabaseCall = 0
    private static minFreeUserIdAfterDatabaseCall = 0

    private constructor() {
        return;
    }

    private static async init() {
        this.minFreeUserIdAfterDatabaseCall = await findMinUniqueUserId();
    }

    public static async reserveMinFreeUserId() {
        let minFreeUserId = UserIdManager.userIdShiftBeforeDatabaseCall;
        UserIdManager.userIdShiftBeforeDatabaseCall++;
        if (!UserIdManager.instance) {
            UserIdManager.instance = new UserIdManager();
            await this.init();
        }

        minFreeUserId += UserIdManager.minFreeUserIdAfterDatabaseCall;
        return minFreeUserId;
    }

}

But i realize that it doesn't help me with multithreading. I've used this, but with the same result:

....
const draftSub = {
            userId: await UserIdManager.reserveMinFreeUserId()
            ...some other fields
        }
....

So, the question is how to generate unique number for each test. When i pass --runInBand option to jest everything is working correctly, but it takes much more time.

What you are using is the typical MAX()+1 method of assigning unique values. Unfortunately this is a virtual guarantee you will get duplicate values for your unique value. This is a result of the Multi-Version Concurrency Control (MVCC) nature of Postgres. In a MVCC database the actions taken by one session cannot be seen by another session until the first session commits. Thus when multiple sessions access max()+1 they each get the same result. The first one to commit succeeds, the second fails. The solution to this is creating a sequence and let Postgres assign the unique value, it will not assign the same value twice regardless how many sessions access the sequence concurrently. The cost however being your values will contain gaps - accept it, get over it, and move on. You can have the sequence generated by defining your userid as a generated identity (Postgres10 or later) or as serial for older versions.

create table subscriptions ( id generated always as identity ...) -- for versions  Postgres 10 or later

or

create table subscriptions ( id serial ...)   -- for versions prior to Postgers 10

With either of those in place get rid of your findMinUniqueUserId function. You may also want to look into insert...returning... functionality