Advanced Usage

Note

Adapted from the Prisma Client Go documentation

In the quickstart, we have created a simple post model and ran a few queries. However, Prisma and the Python client are designed to work with relations between models.

We already created a post model, such as for a blog. Let's assume we want to add comments to a post, and connect these models in a way so we can rely on SQL's foreign keys and the Python client's ability to work with relations.

So let's introduce a new comment model with a 1 to many relationship with the Post model:

model Post {
  ...
  comments Comment[]
}

model Comment {
  id         String   @default(cuid()) @id
  created_at DateTime @default(now())
  content    String
  post       Post @relation(fields: [post_id], references: [id])
  post_id    String
}

Full Prisma Schema

datasource db {
  // could be postgresql or mysql
  provider = "sqlite"
  url      = "file:dev.db"
}

generator db {
  provider             = "prisma-client-py"
  recursive_type_depth = 5
}

model Post {
  id         String    @id @default(cuid())
  created_at DateTime  @default(now())
  updated_at DateTime  @updatedAt
  title      String
  published  Boolean
  desc       String?
  comments   Comment[]
}

model Comment {
  id         String   @id @default(cuid())
  created_at DateTime @default(now())
  content    String
  post       Post     @relation(fields: [post_id], references: [id])
  post_id    String
}

Whenever you make changes to your model, migrate your database and re-generate your prisma code:

# apply migrations
prisma migrate dev --name "add comment model"
# generate
prisma generate

In order to create comments, we can either create a post, and then connect that post when creating a comment or create a post while creating the comment.

post = await db.post.create({
    'title': 'My new post',
    'published': True,
})
print(f'post: {post.json(indent=2)}\n')

first = await db.comment.create({
    'content': 'First comment',
    'post': {
        'connect': {
            'id': post.id,
        },
    },
})
print(f'first comment: {first.json(indent=2)}\n')

second = await db.comment.create({
    'content': 'Second comment',
    'post': {
        'connect': {
            'id': post.id,
        },
    },
})
print(f'second comment: {second.json(indent=2)}\n')

Alternative method

first = await db.comment.create(
    data={
        'content': 'First comment',
        'post': {
            'create': {
                'title': 'My new post',
                'published': True,
            },
        },
    },
    include={'post': True}
)
second = await db.comment.create({
    'content': 'Second comment',
    'post': {
        'connect': {
            'id': first.post.id
        }
    }
})

Now that a post and comments have been created, you can query for them as follows:

# find all comments on a post
comments = await db.comment.find_many(
    where={
        'post_id': post.id
    }
)
print(f'comments of post with id {post.id}')
for comment in comments:
    print(comment.json(indent=2))

# find at most 3 comments on a post
filtered = await db.comment.find_many(
    where={
        'post_id': post.id
    },
    take=3
)
print(f'filtered comments of post with id {post.id}')
for comment in filtered:
    print(comment.json(indent=2))

Prisma also allows you to fetch multiple things at once. Instead of doing complicated joins, you can fetch a post and a few of their comments in just a few lines and with full type-safety:

# fetch a post and 3 of its comments
post = await db.post.find_unique(
    where={
        'id': post.id,
    },
    include={
        'comments': {
            'take': 3,
        },
    },
)