1. Intro

Prisma is ORM that converts relational database into type safe model in your application.

2. Workflow

Step 1. Create database tables with Alembic

1. create the database structure with alembic. Alembic is the main tool to run the database migration in our application. I understand we could do this with prisma too but alembic brings more power and flexibility with python syntax and raw sql queries.

alembic revision -m "create table posts"
alembic upgrade head

Step 2. install prisma

To get prisma working for both python and javascript. you should use this version.

prisma-client-py: 0.15.0
prisma: 5.19.0

Install prisma for both python and javascript https://www.prisma.io/

npm install prisma

https://prisma-client-py.readthedocs.io/en/stable/

Python

pip install prisma

Step 3. generate the prisma schema file

npx prisma db pull

This will read the database and pull the table information prisma/schema.prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

generator js {
  provider = "prisma-client-js"
  previewFeatures = ["views"]
}

model alembic_version {
  version_num String @id(map: "alembic_version_pkc") @db.VarChar(32)

  @@ignore
}


model posts {
  id              String         @id @db.Uuid
  name            String
  contents        String
  url             String         @unique
  source_id       String         @db.Uuid
  tag_count       Int            @default(0)
  image_count     Int            @default(0)
  hit             Int            @default(0)
  meta            Json?          @default("{}")
  published_at    DateTime       @db.Timestamptz(6)
  created_at      DateTime       @default(now()) @db.Timestamptz(6)
  updated_at      DateTime       @default(now()) @db.Timestamptz(6)
  post_categories PostCategory[]
  post_images     PostImage[]
  post_tags       PostTag[]
  source          Source         @relation(fields: [source_id], references: [id], onDelete: Cascade)

  @@index([source_id], map: "idx_posts_source_id")
}

4. modify schema.prisma file as you need

I usually convert the model name to be CamelCase and singular and keep the database name to be snakecase and plural. I need to map the names with @@map keyword

model Post {
  ...

  @@map("posts")
}

5. when the models look good, you run prisma generate to create the prisma client

npx prisma generate

This will generate the client for both javascript and python.

6. Use in javascript

import prisma from "@/lib/prisma"

const posts = await prisma.post.findMany({
  take: 10,
  orderBy: {
    created_at: 'desc'
  }
})

7. Use in python

from src.db.prisma import prisma, requires_connection

posts = await prisma.post.findMany({
  take: 10,
  orderBy: {
    created_at: 'desc'
  }
})

3. Conclusion

You can use prisma both in javascript and python to deal with your models in database easily.