Alternatives to SQLAlchemy for your project - Prisma case
Tags: python, database, orm, prisma
Published: 2022-08-08 17:34:00

Although I use a lot [SQLAlchemy](https://www.sqlalchemy.org/) in my daily work, I'm not satisfied by its api that I found a little difficult compared to the [Django ORM](https://docs.djangoproject.com/en/4.0/topics/db/queries/) with which I started my python journey! Django ORM is probably the best [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) 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](https://www.prisma.io/) is a recent TypeScript ORM taking a different [approach](https://www.prisma.io/docs/concepts/overview/prisma-in-your-stack/is-prisma-an-orm) from its predecessors and focusing on type safety thanks to [TypeScript](https://www.typescriptlang.org/).

But don't worry in this tutorial we will not talk about TypeScript, I will present you the *unofficial* [python client](https://prisma-client-py.readthedocs.io/en/stable/) 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](https://medium.com/analytics-vidhya/poetry-finally-an-all-in-one-tool-to-manage-python-packages-3c4d2538e828) with the following command:

```shell
$ pip install prisma
# or
$ poetry add prisma
```

The prisma python client comes with a CLI that actually embeds the official [prisma CLI](https://www.prisma.io/docs/concepts/components/prisma-cli) with some additional commands. To be sure it is installed, type the following in your shell:

```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](https://github.com/prisma/prisma-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](https://guides.rubyonrails.org/active_record_basics.html)
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`):

```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](https://www.cockroachlabs.com/) and [MongoDB](https://www.mongodb.com/).
- 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](https://www.prisma.io/docs/concepts/database-connectors).

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](https://prisma-client-py.readthedocs.io/en/stable/reference/custom-generators/) 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](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#model-field-scalar-types) of the official documentation. There are not many of them.

Some notes:

- To define a primary field, we use the [attribute](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#attributes) `@id` and we ensure that the value is filled with an uuid using the attribute `@default` combined with the [function](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#attribute-functions) `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()](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#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](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#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](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#map-1) 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:

```shell
$ prisma db push
```

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

```shell
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.

```python
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:

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

Now re-generate the client with:

```shell
$ prisma db push
```

And you should be able to run the following script:

```python
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.

```python
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](https://pydantic-docs.helpmanual.io/) 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.

{% embed https://lewoudar.medium.com/hidden-powers-of-pydantic-558a8109e45 %}

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

```python
# 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.

```python
# 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](https://prisma-client-py.readthedocs.io/en/stable/reference/operations/) and the official [documentation](https://www.prisma.io/docs/concepts/components/prisma-client/crud).

## 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:

```prisma
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:

```shell
$ 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](https://www.prisma.io/docs/concepts/components/introspection) 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:

```shell
$ 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:

```shell
# 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](https://docs.djangoproject.com/en/4.1/topics/migrations/) or [alembic](https://alembic.sqlalchemy.org/en/latest/), 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:

```prisma
...
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`:

```shell
$ 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](https://www.prisma.io/docs/concepts/components/prisma-migrate) 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](https://condor.depaul.edu/gandrus/240IT/accesspages/relationships.htm).

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.

```prisma
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:

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

And starts to play with it:

```python
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:

```prisma
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:

```shell
$ 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.

```python
# 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](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations#explicit-many-to-many-relations) 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](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/self-relations) 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](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete) data.
- Auto-completion feature to write queries faster on editors supporting the [Language Server Protocol](https://microsoft.github.io/language-server-protocol/)
and [pyright](https://github.com/microsoft/pyright) like [VS Code](https://code.visualstudio.com/). Unfortunately, [Pycharm](https://www.jetbrains.com/pycharm/) has limited support for `TypedDict` and it doesn't work for [now](https://youtrack.jetbrains.com/issue/PY-54151/TypedDict-completion-at-callee-does-not-work-for-methods).
- 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](https://www.oracle.com/database/). 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](https://github.com/prisma/prisma/issues/2853) 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](https://docs.djangoproject.com/en/4.0/ref/models/expressions/) in Django. There is an ongoing [issue](https://github.com/prisma/prisma/issues/5048) if you want to follow this topic. The workaround is to use [raw queries](https://www.prisma.io/docs/guides/database/troubleshooting-orm/help-articles/comparing-columns-through-raw-queries).
- There are some inconsistencies in database support where some fields are implemented in some databases but not others like the [JSON](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#json) field which is not implemented on sqlite (there is an ongoing issue [here](https://github.com/prisma/prisma/issues/3786)) and even where it is implemented, the way to query [differs](https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields) 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](https://github.com/RobertCraigie/prisma-client-py)).
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](https://lewoudar.substack.com/) 😉