Theo's Typesafe Cult•2y ago

Prisma CreateMany with a relation (connect)

Hi everyone, in Prisma is there a way to createMany with relation - using connect i assume? Basically this a million times: I read the documentation and there doesn't seem to be "nested createMany" but i think that's not that im doing (or is it? seems like this is too common an operation to not have a create-many)...

    const result = await prisma.posts.create({
      data: {
        user: {
          connect: {
            id: user.id,
          },
        },
        ...postData,
      },
    });

    const result = await prisma.posts.create({
      data: {
        user: {
          connect: {
            id: user.id,
          },
        },
        ...postData,
      },
    });

22 Replies

Leonidas•2y ago

I have never found a solution for this with Prisma (neither for createMany nor updateMany). Always had to resort to raw queries if performance mattered. But in case someone else goes my route i would highly recommend to use zod to evaluate wether the sql query has the desired format. Since a schema migration could lead to breaking changes with this escape hatch - a small unit test for the raw sql query is VERY useful here

fotofloOP•2y ago

hi Leonidas, sorry can you explain the last message? which escape hatch? the rawquery? (btw i highly reccomend using TDD for backend stuff like this, it will save you tons of time @Leonidas ) all tests fail immediately as soon as you change the schema and also, you write code much faster... so many benifits to TDD

Leonidas•2y ago

Yeah prisma is awesome, it provides you a „graphQL“ layer when querying your database - which makes it typesafe But for highly optimized queries or cases where prisma does not support the feature you are needing, you can also write good old regular sql

Leonidas•2y ago

https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access

Prisma

Raw database access (Reference)

Learn how you can send raw SQL and MongoDB queries to your database using the raw() methods from the Prisma Client API.

Leonidas•2y ago

Since these regular sql queries provide no type safety, i recommend to add a small unti test with zod schema validation. Just to make sure the type casting actually matches the data you get. Cause these will be really annoying bugs, the cost of writing a unit test is justified here imo

Leonidas•2y ago

In my scenario i needed to traverse a tree structure (modeled with a relational database). With (regular) prisma i would have needed to recursively query my database one element after another. With a custom sql query i only needed one database call. For anyone wondering about how to do this with sql: common table expressions are a powerful sql language feature for this. https://www.postgresql.org/docs/current/queries-with.html

PostgreSQL Documentation

7.8. WITH Queries (Common Table Expressions)

7.8. WITH Queries (Common Table Expressions) 7.8.1. SELECT in WITH 7.8.2. Recursive Queries 7.8.3. Common Table Expression Materialization 7.8.4. Data-Modifying Statements …

Pluto•2y ago

Prisma docs have clearly mentioned that you cannot use connect inside batch queries. If your user and post is a mapping table and performance isn't your top concern ,

primsa.$transaction( async(tx)=>{

const posts = await tx.createmany() // without connecting users

posts.forEach((post)=> {
 tx.userPostMapping.create({
  data : {
   postId : post.id , 
   userId : user.id
  }
 })
})

primsa.$transaction( async(tx)=>{

const posts = await tx.createmany() // without connecting users

posts.forEach((post)=> {
 tx.userPostMapping.create({
  data : {
   postId : post.id , 
   userId : user.id
  }
 })
})

It's a bad solution probably and I've been trying to find better one. Still no luck. Ofcourse apart from raw sql Sorry if it's not much relevant to your question

Leonidas•2y ago

The awful things is that you quickly reach your connection limit when you invoke one db call for each entity to update. When using normal prisma (no raw sql) i would atleast recommend to batch your db updates. So that only a fixed amount of posts are updated in parallel. But raw sql would be the best option here, if you have to process alot of entities (posts in your example)

Leonidas•2y ago

https://github.com/koskimas/kysely Kysely is a tool to get type safety with raw sql

GitHub

GitHub - koskimas/kysely: A type-safe typescript SQL query builder

A type-safe typescript SQL query builder. Contribute to koskimas/kysely development by creating an account on GitHub.

Leonidas•2y ago

Since i have never used kysely i am not sure if the provided type safety is worth the effort to set this tool up (and if it even supports all the things i need). Therefore i will stick to raw sql with a unit test for the few times i actually need it.

Igal•2y ago

and if it even supports all the things i need

what are the things you need?

Leonidas•2y ago

Recursive common table statements in my example. The general question would be: is every sql feature supported by kysely? I have to again state that i have not tried kysely, but it seems highly unlikely Kysely looks like a wonderful tool. I am not questioning the value it brings. I am questioning the value it brings when you already use prisma for you queries and simply want an escape hatch for using more powerful features of sql when optimizing a query According to kysely‘s docs a recursive CTE can be accomplished in kysely (withRecursive). That looks really promising! I should definitely try out Kysely the next time I encounter such an issue✌🏼 @fotoflo i would argue that raw sql is Prismas only option that solves your „createMany with relation“ issue. If you are looking for a type safe approach with semi-raw sql queries Kysely is my recommendation. It can generate its internal types from retrospection (just like Prisma), so using Kysely complementary to Prisma should work (atleast in theory)

Igal•2y ago

Recursive CTEs are supported.

Igal•2y ago

You can use Kysely as a query builder without executing the queries. https://koskimas.github.io/kysely/types/InferResult.html

InferResult | kysely

Documentation for kysely

Igal•2y ago

Also check this write up about Primsa + Kysely https://dev.to/nexxeln/typesafe-database-queries-on-the-edge-5bbn

DEV Community 👩‍💻👨‍💻

Typesafe Database Queries on the Edge

What is the Edge? Edge computing is the new hottest thing in the web dev ecosystem, and...

fotofloOP•2y ago

great stuff guys thank you will read in depth @Igals and @Leonidas... as soon as my current tests pass (connection pool issue happening all the time now..)

fotofloOP•2y ago

https://github.com/prisma/prisma/discussions/17472

GitHub

How to createMany with a relation? Looping CREATE and getting "to...

hello! Let me see if i got this straight - I have what i believe to be a very simple issue: I create a batch of reports scores I create a bunch of scores in the batch Because you can't use ...

fotofloOP•2y ago

this is too simple to be like this... asking a question in the prisma discussions

Leonidas•2y ago

Nice! Thank you for sharing this. Setting one-to-one and one-to-many relations is supported by regular Prisma with this method☝🏼 Only for many-to-many relations raw SQL or Kysely would be needed

fotofloOP•2y ago

W00t my question got an answer, giving it a go. @Leonidas did you try it yet?

Leonidas•2y ago

I haven‘t, but i am convinced it works. That is exactly what i do when writing raw sql for this - just way simpler and typesafe😅

fotofloOP•2y ago

it worked

Gaming

Programming

Prisma CreateMany with a relation (connect)