Prisma multiple create queries with nested connectOrCreate throws unique constraint failed

Question

I'm trying to create a model where there's a many-to-many relation between 2 types, Movie and Genre. I retrieve a list of movies from an external data source asynchronously, create each in my own database if it's not already created, and return the movie.

const retrieveList = async () => {
   const results = await API.getMovies();
   return results.map((item) => getMovieItem(item.id));
}

const getMovieItem = async (movieId) => {
    // see if is already in database
    const movie = await prisma.movie.findUnique({
        where: { tmdbId: movieId },
    });
    if (movie) {
        return movie;
    } else {
        // create and return if doesn't exist
        const details = await API.getDetails(movieId);
        const genresData = details.genres.map((genre) => ({
            create: {
                name: genre.name,
            },
            where: {
                name: genre.name
            },
        }));
        return prisma.movie.create({
            data: {
                title: details.title,
                description: details.overview,
                genres: {
                    connectOrCreate: genresData,
                },
            },
            select: {
                tmdbId: true,
                id: true,
                title: true,
                description: true,
                //...
            },
        });
    }
};

the problem is that there seems to be some sort of inconsistency where if the connection to the external API is slow enough in some runs, everything seems to be working fine; but also if it's fast enough, it throws the error:

Invalid `prisma.movie.create()` invocation:
  Unique constraint failed on the fields: (`name`)"

likely because it is trying to create a genre that's already created instead of connecting it.

Danila · Accepted Answer · 2022-06-13 21:13:05Z

5

As the docs states:

Multiple connectOrCreate queries that run as concurrent transactions can result in a race condition. ...

So if you request 2 movies of the same genre at the same time and this genre is not yet exist then both queries would try to create this genre and only the first one will succeed.

Docs recommends catching for specific error:

To work around this scenario, we recommend catching the unique violation exception (PrismaClientKnownRequestError, error P2002) and retrying failed queries.

You can also create all the genres at first, for example:

// Some abstract function which extracts all the genres
const genres = gatherGenres(results);

await prisma.genres.createMany({
    data: genres,
    // Do nothing on duplicates
    skipDuplicates: true,
});

And then create all the movies and connect them to genres, because genres have already been created.

Also, small nitpick, you function retrieveList is not awaitable because it returns an array of promises, and to actually wait until all the promises are done you need to use Promise.all on it

const retrieveList = async () => {
   const results = await API.getMovies();
   return results.map((item) => getMovieItem(item.id));
}

// ...

// You can't just await it ❌
await retrieveList()

// Need to use Promise.all ✅
await Promise.all(retrieveList())

edited Jun 13, 2022 at 21:13

answered Jul 29, 2021 at 20:45

Danila

19k2 gold badges57 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shayan Shojaei Over a year ago

thanks for the answer. so if I were to go with the second method of gathering all the possible genres in the database, beforehand; do you have any suggestions as to where it would be appropriate to execute the query in an apollo-server (graphql) application?

Danila Over a year ago

I would do this: get all the movies from api, gather all genres and create them, and then create all the movies (and connect them to genres). Just do it all inside your retrieveList function, just be careful with await

Collectives™ on Stack Overflow

Prisma multiple create queries with nested connectOrCreate throws unique constraint failed

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related