Drizzle TeamDT
Drizzle Team•14mo ago
Anthony

Race condition executing a common pattern (select or insert)

I'm having trouble understanding how to execute a common pattern in Postgres and I'm using Drizzle as an ORM adapted. The common pattern is this: I want to fetch a single row by some query, for example

let record = await db.query.records.findFirst({
    where: eq(records.phoneNumber, '+15555555555'),
    orderBy: desc(records.createdAt)
});


But if that record doesn't exist I want to create a new record and use that one later on in the file.

let record = await db.query.records.findFirst({
    where: eq(records.phoneNumber, '+15555555555'),
    orderBy: asc(records.createdAt)
});

if (!record) {
    const [newRecord] = await db.insert(records)
        .values({
            phoneNumber: '+15555555555'
        })
        .returning();
    
    record = newRecord;
}


Now, the thing is I am doing this in an API route that might be called multiple times at the same time and I want to avoid race conditions so I wrapped this in a transaction. 

const record = await db.transaction(async (tx) => {
    // First try to find the existing record
    const existingRecord = await tx.query.records.findFirst({
        where: eq(records.phoneNumber, '+15555555555'),
        orderBy: asc(records.createdAt)
    });

    if (existingRecord) {
        return existingRecord;
    }

    // If no record exists, create a new one
    const [newRecord] = await tx.insert(records)
        .values({
            phoneNumber: '+15555555555'
        })
        .returning();

    return newRecord;
});


Now the problem is that even though this is wrapped in a transaction, race conditions can still occur where 2 users call my API endpoint at the exact same time, and 2 new records are created. How do I update my API endpoint to avoid this scenario using Drizzle syntax or PostgreSQL?
Was this page helpful?