Alternatives to SQLAlchemy for your project - Prisma case

Although I use a lot SQLAlchemy in my daily work, I'm not satisfied by its api that I found a little difficult compared to the Django ORM with which I started my python journey! Django ORM is probably the best ORM IMHO in the python ecosystem. Unfortunately it is tight to the Django project and cannot be used elsewhere. So I decided to look for SQLAlchemy alternatives and starting with this article, I will present you the ones I liked the most.

For data engineers / data scientists this library can be particularly useful for you because it has a cleaner api to fetch data from databases.

Introduction

Prisma is a recent TypeScript ORM taking a different approach from its predecessors and focusing on type safety thanks to TypeScript.

But don't worry in this tutorial we will not talk about TypeScript, I will present you the unofficial python client created for this project. To present it briefly, I will borrow his words:

Prisma Client Python is a next-generation ORM built on top of Prisma that has been designed from the ground up for ease of use and correctness.

Installation

To install it you can use pip or poetry with the following command:

$ pip install prisma
# or
$ poetry add prisma

The prisma python client comes with a CLI that actually embeds the official prisma CLI with some additional commands. To be sure it is installed, type the following in your shell:

$ prisma -h

◭  Prisma is a modern DB toolkit to query, migrate and model your database (https://prisma.io)

Usage

  $ prisma [command]

Commands

            init   Setup Prisma for your app
        generate   Generate artifacts (e.g. Prisma Client)
              db   Manage your database schema and lifecycle
         migrate   Migrate your database
          studio   Browse your data with Prisma Studio
          format   Format your schema
...

Note: you may notice that it downloads some binaries when you first invoke this command. This is normal it fetches the node prisma cli and engines used by prisma. 😁

Prisma schema file

In Prima, it all starts with a schema.prisma file where you define your business models and relations between them. After that you will invoke a prisma CLI command to generate the client. This is different from ORMs like Django ORM and SQLAlchemy which use the Active Record
pattern where we define model classes with data and methods to interact with the database. Here Prisma (at least the python library) is in charge of defining the api to manipulate the database as well as the different data models that will be involved in the operations.

So let's start with this schema (saved it in a file called schema.prisma):

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

generator client {
  provider  = "prisma-client-py"
}

model User {
  id         String   @id @default(uuid())
  firstname  String
  lastname   String
  is_admin   Boolean  @default(false)
  email      String   @unique
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
  posts      Post[] // not represented in the database

  @@map("user")
}

model Post {
  id         String   @id @default(uuid())
  title      String
  content    String
  published  Boolean  @default(false)
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
  // user field will not be represented in the database
  // To link a post to its author we precise which field of this table
  // is a foreign key (user_id) and on which field of the other table
  // it points (id)
  user       User     @relation(fields: [user_id], references: [id])
  user_id    String

  @@map("post")
}

Ok, let's break down what we have.

datasource

In this section, we define how to connect to a database. We provide:

• a provider: in our case it is sqlite but prisma supports MySQL, MariaDB, PostgreSQL, SQL Server, CockroachDB and MongoDB.
• an url: the connection string with all the information necessary to connect. For sqlite it is quite simple, we pass a filename path prefixed with file:. For other databases, refer to prisma documentation.

Note: At the moment of writing this article CockroachDB is not supported by the python client, but it should not take long.

Also, you can use environment variables to not reveal secrets in your prisma schema file. For example, you can replace the url value in the previous example with env("DATABASE_URL"). The prisma client will know that it needs to fetch database connection string from an environment variable called DATABASE_URL.

generator

In this section, we specify the script that will generate a python client we can use for our queries.
For our python library, the value is prisma-client-py but you can create a custom generator if you want.
Warning! It is an advanced topic. 🙂

model

And we finally have the model definition section. In our example, we defined two models User and Post. These two models will represent two tables in the database db.sqlite and the attributes defined in each of them will
represent table columns.
To know all the types available in prisma, refer to this
section of the official documentation. There are not many of them.

Some notes:

• To define a primary field, we use the attribute @id and we ensure that the value is filled with an uuid using the attribute @default combined with the function uuid()
• In the User model, the is_admin boolean field is assigned a default value of false. This means that every user created will not be an admin. The other value possible for this boolean field is true of course.
• In the User model, the email field is assigned a @unique attribute which means that we can't have the same email twice in the user table.
• In User and Post models, the created_at date time field is assigned a default attribute with function now() which means that each time we create a record, the field will be automatically filled with the current timestamp.
• In User and Post models, the updated_at date time field is assigned an @updatedAt attribute which means each time we update a record, the field will be automatically filled with the current timestamp.
• We defined a 1-n relation between Post and User models which is represented by the user and posts fields. These fields are not represented in the database but at prisma level to easily manage relations.
• The @@map model attribute is used to define the name of the table in the database. If we don't define it, by default prisma will use the model name.

Basic Usage

Now that we have a schema file we can create the database and generate the client with this command:

$ prisma db push

Normally, you should have an output similar to the following:

Prisma schema loaded from schema.prisma
Datasource "db": SQLite database "db.sqlite" at "file:db.sqlite"

SQLite database db.sqlite created at file:db.sqlite
...
✔ Generated Prisma Client Python (v0.6.6) to ...

Create a python file with the following content. This is the minimum to use the python client.

import asyncio
from prisma import Prisma


async def main() -> None:
    db = Prisma()
    await db.connect()
    await db.user.find_first()

    await db.disconnect()


if __name__ == '__main__':
    asyncio.run(main())

If you run it, it should return nothing since there is no record in the database but at least everything is fine :)

Well, if you don't want the async interface, for example you want to adopt it in a synchronous web framework, or you are a data engineer / data scientist who is not used to work in this paradigm, you can generate the client with a synchronous interface. First, you must modify the generator section of the schema file to insert the interface information. It will look like the following:

// I just put the interesting part, not the whole file
generator client {
  provider  = "prisma-client-py"
  interface = "sync"
}

Now re-generate the client with:

$ prisma db push

And you should be able to run the following script:

from prisma import Prisma


def main() -> None:
    db = Prisma()
    db.connect()
    db.user.find_first()

    db.disconnect()


if __name__ == '__main__':
    main()

We are good now! In the following examples I will use asynchronous examples, but all you have to do to make it work
with your synchronous client is to remove async / await keywords. 😉

Ok, let's create some users and posts.

import asyncio

from prisma import Prisma


async def main() -> None:
    db = Prisma()
    await db.connect()
    user_1 = await db.user.create(
        data={
            'firstname': 'Kevin',
            'lastname': 'Bogard',
            'email': 'kevin@bogard.com',
            'posts': {
                'create': [
                    {
                        'title': 'Prisma is awesome',
                        'content': 'This is the truth!'
                    },
                    {
                        'title': 'Do you know the Bogard family?',
                        'content': "If not, you probably don't know King of Fighters!"
                    }
                ]
            }
        }
    )
    user_2 = await db.user.create(
        data={
            'firstname': 'Rolland',
            'lastname': 'Beaugosse',
            'email': 'rolland@beaugosse.fr',
            'posts': {
                'create': [
                    {
                        'title': 'Prisma is awesome',
                        'content': 'you should use it!'
                    },
                    {
                        'title': 'What other ORM do you use?',
                        'content': 'Tell me in the comments'
                    }
                ]
            }
        }
    )
    print(user_1)
    print(user_2.json(indent=2))

    await db.disconnect()


if __name__ == '__main__':
    asyncio.run(main())

You notice that we can create a user and its related posts in one query if we want. The query is straightforward thanks to prisma which manages the relations in a implicit way for us.

The result of this query is the user model created, it is a pydantic model, so
you can use its methods to introspect the data. if you don't know pydantic, it is a powerful library for data validation. I have a tutorial on some of its features.

Hidden powers of pydantic. Discover some key features of pydantic… | by Kevin Tewouda | Medium

Kevin Tewouda ・ Dec 27, 2022 ・

lewoudar.Medium

If you are curious you can look the definition of the generated models in the module prisma.models.

Note: if you don't target sqlite, you can create multiple objects in a batch like this

# not possible in sqlite
users = await db.user.create_many(
    data=[
        {'firstname': 'Kevin', 'lastname': 'Bogard', 'email': 'kevin@bogard.com'},
        {'firstname': 'Rolland', 'lastname': 'Beaugosse', 'email': 'rolland@beaugosse.com'},
    ]
)

Now let's see how to query objects with prisma and the python client.

# I don't put all the imports, you have them in the previous example :)
from datetime import datetime, timedelta

# returns the first user of the database
user = await db.user.find_first()
print(user.json(indent=2))

# returns the user where email is rolland@beaugosse.com and include post information
user = await db.user.find_unique(
    where={'email': 'rolland@beaugosse.fr'},
    include={'posts': True}
)
print(user.json(indent=2))

# lists posts from the first user
posts = await db.post.find_many(
    where={
        'user': {
            'is': {
                'email': 'kevin@bogard.com',
            }
        }
    }
)
for post in posts:
    print(post.json(indent=2))

# find posts
# where title contains "Prisma" or creation date is less than 1 hour
# order by creation date descendant
# and include user information
posts = await db.post.find_many(
    where={
        'OR': [
            {
                'title': {
                    'contains': 'Prisma'
                }
            },
            {
                'created_at': {
                    'gt': datetime.utcnow() - timedelta(hours=1)
                }
            }
        ]
    },
    include={'user': True},
    order={'created_at': 'desc'}
)
for post in posts:
    print(post.json(indent=2))

The queries made with prisma are really simple to follow and powerful. To know more about what you can do, check the prisma client reference and the official documentation.

Introspection

What if you already have a database in place and want to take advantage of prisma? Prisma got your back with a command which introspects the database and generate the models in your schema file. Concretely, you have to define a base file with the datasource and generator sections like this:

datasource db {
  provider = "sqlite"
  // replace the url with correct one in your case
  url      = "file:db.sqlite"
}

generator client {
  provider  = "prisma-client-py"
}

and run the command:

$ prisma db pull

Prisma will automatically populate the models with the metadata retrieved from the database. Of course, it is not
perfect, and you will probably have to adjust some information but it is a good start. More information on prisma introspection can be found in this section of the official documentation.

migrations

Another great feature of prisma is its migration system. This allows for a smoother database experience allowing us to make incremental changes in our web application for example. When you create / update / delete models, you can create a migration file that you will apply later on your production database. For example, since we have created two models so far, we can create a migration file with the following command:

$ prisma migrate dev --name "create user and post models"

Note: you should notice that the CLI asks you the permission to continue because it will erase all the data in your local
database. This is normal since it is our first migration and it is only done on our local database.

Now we have a folder migrations with sub-folders containing your different migration files. Once we have done all
the necessary tests and are sure about our changes, we can apply it on our production database with the following command:

# be sure the url in the datasource section of your prisma file
# has the correct value
$ prisma migrate deploy

Note: unlike other migration systems like Django migrations or alembic, you don't have a system to upgrade or downgrade the database.
You always update the database with a new migration. So if you want to roll back a migration, you must create a new migration file.

Now re-create some users and posts (you can use our first example). And let's say we want now to count the number of times a post is viewed, so we add this in our model:

...
model Post {
  id         String   @id @default(uuid())
  title      String
  content    String
  published  Boolean  @default(false)
  // we have a new field to count the number of times
  // a post is viewed
  views      Int      @default(0)
  ...
}

You can create a new migration with prisma migrate:

$ prisma migrate dev --name "add views field in post model"

Note: the database is not erased this time because we already have a migration in place. 😉

More information about migrations can be found in this section of the official documentation.

relations

Probably the best feature for me in prisma is that we can easily query deeply nested and linked models thanks to how it handles relations in the background for us. In this regard, it supports the following relations:

• 1-n (one-to-many) relation
• 1-1 (one-to-one) relation
• n-m (many-to-many) relation
• self-relations where a field model references itself

if these terms do not speak to you (the first three especially), I invite you to read this article.

We have already seen the one-to-many relation where a user has 0 to many posts and a post has only one user. Now let's see how to define one-to-one and many-to-many relations.

one-to-one relation

Let's say we want to extend the user information in a profile table, we will add the following model in the schema and also update the User model.

model User {
 ...
 // we add an optional profile field
  profile    Profile?

  @@map("user")
}

model Profile {
  id      String @id @default(uuid())
  bio     String
  user    User   @relation(fields: [user_id], references: [id])
  // the @unique attribute is what make the difference between a
  // 1-n relation and a 1-1 relation
  user_id String @unique

  @@map("profile")
}

It is not really different from how we define the user<->post relation except for two things:

• the profile field in the User model has a ? sign next to the type because we want it to be optional. This is because we already have some users in the database, so if this field is required, it will be possible to update the database. Of course, if it is a new project, you can make it mandatory if you want. I just want to say that it is not related to the definition of a one-to-one relation.
• in the Profile model, the user_id field is marked as @unique. If you look the Post model and how we define the same field, you will notice that it is the only difference, this attribute is what creates the one-to-one relation between User and Profile.

Now you can upgrade the database with the command:

$ prisma migrate dev --name "add profile model"

And starts to play with it:

user = await db.user.find_first()
profile = await db.profile.create(
    data={
        'bio': "I'm cool",
        'user': {
            # we link the profile to its user
            'connect': {
                'id': user.id
            }
        }
    }
)
print(profile.json(indent=2))

# we get the same profile
profile = await db.profile.find_first(
    where={
        'user': {
            'is': {
                'email': user.email
            }
        }
    },
    include={'user': True}
)
print(profile.json(indent=2))

many-to-many relation

Now, let's say we want to attach some categories to a post, we can update the schema to something like this:

model Post {
  ...
  categories Category[] @relation(references: [id])

  @@map("post")
}

model Category {
  id    String @id @default(uuid())
  name  String
  posts Post[] @relation(references: [id])

  @@map("category")
}

And migrate the database with the following command:

$ prisma migrate dev --name "add category model"

If you look at the migration file, you will notice that prisma creates a glue table to handle this relation. Now we can do the following stuff thanks to prisma.

# we create a user, post and category together!
user = await db.user.create(
    data={
        'firstname': 'foo',
        'lastname': 'bar',
        'email': 'foo@bar.com',
        'posts': {
            'create': [
                {
                    'title': 'Prisma is so good',
                    'content': 'oh yeah!',
                    'categories': {
                        'create': [
                            {'name': 'prisma'},
                            {'name': 'orm'}
                        ]
                    }
                }
            ]
        }
    }
)
print(user.json(indent=2))

# we fetch the categories
categories = await db.category.find_many(
    where={
        'posts': {
            'every': {
                'title': {
                    'contains': 'Prisma'
                }
            }
        }
    },
    include={'posts': True}
)
for category in categories:
    print(category.json(indent=2))

Sometimes, you want to add some attributes in an n-m relation, in that case you have to manually create the intermediate table. For more information, see this section of the official documentation.

I won't explain the self-relation either because this tutorial is already long! Feel free to learn more in this section of the documentation.

cli

I want to summarize some commands you will often use when working with prisma.

• prisma format: Like the name suggests, it formats the schema file and also validates its content.
• prisma db pull: Fetch models from the database and generate the client.
• prisma generate: Generates the client, useful in a pull strategy where you want to modify models without pushing the changes in the database.
• prisma db push: When you are testing an application, this is the command you should use to reset the database and generate the client.
• prisma migrate dev --name <name>: To use after db push when you are sure of your changes. It will create a migration file, apply it on your local environment and generate the client.
• prisma migrate deploy: Apply a migration file on a production environment.
• prisma py version: Show debugging information about the python client, prisma version supported, binaries, etc...

Summary

Ok, at the end of this tutorial, I will list the advantages and drawbacks I see with prisma.

advantages

• Simple and neat api to CRUD data.
• Auto-completion feature to write queries faster on editors supporting the Language Server Protocol and pyright like VS Code. Unfortunately, Pycharm has limited support for TypedDict and it doesn't work for now.
• Migration system.
• Introspection feature to generate models and clients by reading metadata on the database (this is really cool!).
• Support many relational databases, even a relatively new one I just discovered while learning prisma, CockroachDB. It also supports MongoDB which is a NoSQL database.

drawbacks

• Even if it supports many relational databases, there is one notable absence in the list: Oracle. It can be restrictive if you are working for a bank or a large corporation that is used to working with this database. There is an ongoing issue if you want to follow this topic.
• We can't compare two fields of the same table, something we can do with the F expressions in Django. There is an ongoing issue if you want to follow this topic. The workaround is to use raw queries.
• There are some inconsistencies in database support where some fields are implemented in some databases but not others like the JSON field which is not implemented on sqlite (there is an ongoing issue here) and even where it is implemented, the way to query differs from one database to another. This is not what I expect from an ORM. It should be database agnostic.
• We don’t have mixins support for models to avoid repeating fields between models like datetime fields (created_at, updated_at).

Anyway, prisma is a relatively new project, and it is very pleasant to use. I encourage you to give it a try and support the python client (at least a star on GitHub).
For me who doesn't like SQL, being able to retrieve models from the database and make queries with prisma is a breath of fresh air. 🤣

This is all for this tutorial, stay tuned for the next ORM we will explore together! 😉

If you like my article and want to continue learning with me, don’t hesitate to follow me here and subscribe to my newsletter on substack 😉