Hello 👋! In this guide, we will learn how to build an API connected to a database using Prisma.

First, let's create a new virtual environment: python -m venv env and activate it. On windows, you can do .\env\Scripts\activate.

Now, let's install the packages we need for this project:

pip install flask prisma

After that is done, create a new file called app.py, and let's put some boilerplate in order to check our flask app works.

from flask import Flask

app = Flask(__name__)

@app.route('/', methods=['GET'])
def index():
  return {
    "ping": "pong"
  }


if __name__ == "__main__":
  app.run(debug=True, port=5000, host='0.0.0.0')

Now, if you start your app with python app.py and go to http://127.0.0.1:5000/ you should see your endpoint response 🎉.

Database model

Let's get to the prisma part. First, we need to model our database. In our example, let's have users and posts. I will be using sqlite, but if you want to use something else, like postgres, you will need to change the provider and url, for example:

datasource db {
  provider = "postgresql"
  url      = "postgresql://postgres_user:postgres_password@localhost:5432/db_name"
}

Create a file called schema.prisma and inside it put the following:

datasource db {
  provider = "sqlite"
  url      = "file:database.db"
}

generator db {
  provider  = "prisma-client-py"
  interface = "sync"
}

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  published Boolean  @default(false)
  title     String
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

Here, we have a one to many relationship between user and post. In order to learn more about how to build relationships in Prisma schema, you can check the official js documentation.

Now, let's create the database with Prisma. You can do it by creating a migration using:

prisma migrate dev

and providing a name for the migration, for example, init. After that, you will have a sqlite database inside the root folder.

Python and Prisma

Now, on the python side. Let's register Prisma inside Flask.

from flask import Flask
from prisma import Prisma, register

db = Prisma()
db.connect()
register(db)
app = Flask(__name__))

# rest of the file
...

Routes

I like to keep routes organized, so let's create a folder called routes and inside it, a file called user.py with the following content:

from flask import Blueprint, request
from prisma.models import User

user_blueprint = Blueprint('user', __name__)

@user_blueprint.route('/', methods=['GET','POST'])
def list_create():
  if request.method == 'GET':
    users = User.prisma().find_many(include={'posts': True})
    return {
      "data": [user.dict() for user in users]
    }

  if request.method == 'POST':
    data = request.json

    if data is None:
      return

    name = data.get('name')
    email = data.get('email')

    if name is None or email is None:
      return {"error": "You need to provide name and email"}

    user = User.prisma().create(data={'email': email, 'name': name})

    return dict(user)

After that, we need to register the blueprint inside app.py.

from flask import Flask
from prisma import Prisma, register
from routes.user import user_blueprint
# from routes.post import post_blueprint

db = Prisma()
db.connect()
register(db)

app = Flask(__name__)

@app.route('/', methods=['GET'])
def index():
  return {
    "ping": "pong"
  }

app.register_blueprint(user_blueprint, url_prefix='/user')
# app.register_blueprint(post_blueprint, url_prefix='/post')

if __name__ == "__main__":

  app.run(debug=True, port=5000, threaded=True)

And now, if we hit the endpoint http://127.0.0.1:5000/user with a POST method with the following payload:

{
    "name": "Mihai2",
    "email": "mihai2@test.com"
}

We will receive the created user:

{
    "data": [
        {
            "createdAt": "Fri, 01 Jul 2022 07:25:34 GMT",
            "email": "mihai2@test.com",
            "id": 1,
            "name": "Mihai2",
            "posts": null
        }
    ]
}

Now, let me explain a bit the prisma part. Every model that we created in our prisma.schema is now available in prisma.models and can be used like this:

from prisma.models import User

By using that model, we can make specific queries like create, update, find_many and so on. You can check all the queries here and the model based access here. If the request method is GET, we are retrieving every user from the database and after that, we use list comprehension in order to format it as a list of dictionaries ( we need to do it because of Flask since we can't just return that model). If the request is POST, we create that record in the database and return it as a dictionary. Prisma provides a very good autocomplete, so if you're using VSCode, ctrl + space is your best friend 😀 and most of the time, you will not even need the documentation.

Now, let's do the post routes. Similarly, in the routes folder we create a post.py file with a blueprint.

from flask import Blueprint, request
from prisma.models import Post

post_blueprint = Blueprint('post', __name__)

@post_blueprint.route('/', methods=['GET','POST'])
def list_create():
  if request.method == 'GET':
    posts = Post.prisma().find_many()
    return {
      "data": [post.dict(exclude={'author'}) for post in posts]
    }

  if request.method == 'POST':
    data = request.json

    if data is None:
      return

    title = data.get('title')
    published = data.get('published')
    authorId = data.get('authorId')

    if title is None or published is None or authorId is None:
      return {"error": "You need to provide title, published and authorId"}

    post = Post.prisma().create(data={'title': title, 'authorId': authorId, 'published': published })


    return post.dict()

@post_blueprint.route('/<int:id>', methods=['GET','PUT', 'DELETE'])
def view_update_delete(id):
  if request.method == 'GET':

    post = Post.prisma().find_unique(where={'id': id}, include={'author': True})
    if post is None:
      return {'error': 'Post doesn`t exist'}, 404

    return post.dict()

  if request.method == 'PUT':
    data = request.json

    if data is None:
      return

    title = data.get('title')
    published = data.get('published')
    authorId = data.get('authorId')

    if title is None or published is None or authorId is None:
      return {"error": "You need to provide title, published and authorId"}

    post = Post.prisma().update(where={'id': id }, include={'author': True}, data={'title': title, 'published': published, 'author': {'connect': {'id': authorId}}})

    if post is None:
      return {'error': 'Post doesn`t exist'}, 404

    return post.dict()

  if request.method == 'DELETE':
    post = Post.prisma().delete(where={'id': id})
    if post is None:
      return {'error': 'Post doesn`t exist'}, 404

    return post.dict(exclude={'author'})

And add the blueprint to app.py.

from routes.post import post_blueprint
app.register_blueprint(post_blueprint, url_prefix='/post')

For the post routes, we created a full CRUD API. The list and create endpoints are the same as for the user. Additionally, we created the view_update_delete function that handles 3 types of requests:

• GET 127.0.0.1:5000/post/1 -> we retrieve a record from the database with the specific id and return it.
• PUT 127.0.0.1:5000/post -> we update a record in the database with the specific id and the payload provided and return the updated record. Payload example:

  {
    "title": "Post1 updated",
    "published": false,
    "authorId": 1
  }
  

• DELETE 127.0.0.1:5000/post/1 -> we delete that record from the database and return it.

And that's it 🎉! Now you can start the app and maybe add more functionalities because it is easy to interact with a database with Prisma.

I really enjoyed using Prisma in javascript, and I am glad that Robert created a client for python. 🔥🔥

If you have any questions, we can discuss them in the comments. 💬