How to connect rows in a many-to-many (implicit join table) relation efficiently?

[CarterRoll]

Question

I am trying to connect rows for two tables that have a many-to-many relation. Both tables will have thousands of rows that need to be joined together. It would be incredibly inefficient to loop over one table to do a createOrConnect with the other. It is not possible to access relations in a createMany, so that is out of the options. I tried to use an executeRawUnsafe to directly create rows in the implicit join table, but that gives me an error saying that the table does not exist.

Is there a way to directly write to an implicit join table? This would solve my issue. I don't need to worry about SQL injections, as all input values are coming from the database. I do not want to create an explicit join table, because my system uses a lot of introspection to provide accurate data to the frontend, and this join table should not be a part of that introspection.

[nurul3101]

You’re right that you can’t use createMany with relations, so the recommended workaround is to loop create calls and run them together in a $transaction.

Something like this:

type PostInput = {
  title: string
  categoryNames: string[]
}

async function createPostsWithCategories(postsData: PostInput[]) {
  // Build an array of create() calls
  const createOperations = postsData.map((post) =>
    prisma.post.create({
      data: {
        title: post.title,
        categories: {
          connect: post.categoryNames.map((name) => ({ name })),
        },
      },
    })
  )

  // Run all creates in a single transaction
  return prisma.$transaction(createOperations)
}

async function main() {
  const postsData: PostInput[] = [
    {
      title: 'Post 1',
      categoryNames: ['Category1', 'Category2'],
    },
    {
      title: 'Post 2',
      categoryNames: ['Category2', 'Category3'],
    },
    // ...more posts
  ]

  const createdPosts = await createPostsWithCategories(postsData)
  console.log(`Created ${createdPosts.length} posts`)
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

[CarterRoll]

Is this safe and efficient to do with potentially thousands of create queries? Also, I am already forced to use an interactive transaction for other parts of this database action, so using a sequential transaction will break my ACIDity. This is really not a solution that works well for my use case.

[CarterRoll]

I spent the day testing this solution and rewriting my queries around using sequential transactions, and I've confirmed that the query is correct, but I get a prisma:error Invalid string length error spammed into my terminal. I've verified that the query is correct and works in an interactive transaction, but it's 25,000 upserts, each with >100 connects, totaling around 19 million rows in the join table.

The reason this is happening is because of Prisma's built-in prepared statement limit of 32,767. Batching them in transactions that total under this limit for the implicit join table allows the query to run, but it takes around 30 minutes. If I could access the implicit join table, this could be done more efficiently.

[nurul3101]

For inserting millions of rows efficiently, you could batch your INSERT statements using $executeRawUnsafe with multi-row VALUES clauses, staying under the parameter limit per batch. Something like chunking into batches of a few thousand rows each and running those inside an interactive transaction.

That said, since implicit join table internals aren't part of Prisma's public API, there's always a small risk that naming conventions could change across major versions.

[SX110903]

tio el problema es que prisma nombra las tablas implicitas con un guion bajo delante, tipo si tienes ModelA y ModelB la tabla se llama _ModelAToModelB (en orden alfabetico). puedes usar executeRaw directamente sobre esa tabla:

await prisma.$executeRaw`
  INSERT INTO _ModelAToModelB (A, B)
  SELECT a.id, b.id FROM tableA a, tableB b
  WHERE -- tu condicion
  ON CONFLICT DO NOTHING
`

si no sabes el nombre exacto de la tabla mira en tu base de datos directamente, suelen estar ordenadas alfabeticamente los dos modelos. con esto te ahorras el bucle y lo haces en una sola query, que para miles de filas es lo unico que tiene sentido

[Felipeness]

Prisma's implicit join table naming convention is _ModelAToModelB — both model names in alphabetical order, prefixed with an underscore. The columns are always "A" (first model alphabetically) and "B" (second).

So for this schema:

model Post {
  tags Tag[]
}

model Tag {
  posts Post[]
}

The table is _PostToTag (P before T). Your table was probably not found because you used something like PostTag or post_tag — Prisma always uses the _AToB pattern with PascalCase.

You can verify with:

SELECT table_name FROM information_schema.tables WHERE table_name LIKE _%;

For the bulk insert, I'd go with unnest over string interpolation — avoids SQL injection and parameter limit issues:

const postIds = pairs.map((p) => p.postId);
const tagIds = pairs.map((p) => p.tagId);

await prisma.$executeRawUnsafe(
  `INSERT INTO "_PostToTag" ("A", "B")
   SELECT * FROM unnest($1::int[], $2::int[])
   ON CONFLICT DO NOTHING`,
  postIds,
  tagIds
);

If you want something simpler (and the dataset is not huge), string interpolation works too:

const values = pairs
  .map((p) => `(${p.postId}, ${p.tagId})`)
  .join(", ");

await prisma.$executeRawUnsafe(
  `INSERT INTO "_PostToTag" ("A", "B") VALUES ${values} ON CONFLICT DO NOTHING`
);

Honestly, if you are going to do a lot of raw SQL against join tables, consider switching to an explicit join model:

model PostTag {
  postId Int
  tagId  Int
  post   Post @relation(fields: [postId], references: [id])
  tag    Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])
}

Then you get createMany with skipDuplicates: true, proper type safety, and no guessing about table names.

[github-actions[bot]]

Hi,

As we have not heard back from you, we are closing this discussion to keep our discussions organized.

Feel free to start a new discussion if this remains relevant.

Thank you for being part of the community!