Alternatives to SQLAlchemy for your project — Prisma case

Prisma is a new kind of ORM focusing on code security with type hints

Photo by Campaign Creators on Unsplash

Although I use lot SQLAlchemy in my daily work, I’m not satisfied with its API I found it 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 the 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):

base prisma schema

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 the 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 a 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 are represented by the user and posts fields. These fields are not represented in the database but at the 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.

basic async client code

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 if you are a data engineer/data scientist who is not used to working 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:

basic Prisma sync client

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.

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 an 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

If you are curious you can look at 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.

Example of queries with Prisma

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 generates 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 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 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 to 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 to 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.

extended schema with a new profile model

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:

simple crud of profile model

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:

extended schema with a new category model

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.

simple crud of the category model

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: As 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 to 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 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! 😉

This article was originally published on dev.to

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 😉