Alternatives to SQLAlchemy for your project — Prisma case

Illustration of a database
Photo by Campaign Creators on Unsplash

Introduction

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

Installation

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

$ pip install prisma
# or
$ poetry add prisma
$ 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
...

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.

base prisma schema

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.

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.

  • 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
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 ...
basic async client code
// I just put the interesting part, not the whole file
generator client {
provider = "prisma-client-py"
interface = "sync"
}
$ prisma db push
basic prisma sync client
# 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'},
]
)
Example of queries with prisma

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"
}
$ prisma db pull

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"
# be sure the url in the datasource section of your prisma file
# has the correct value
$ prisma migrate deploy
...
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)
...
}
$ prisma migrate dev --name "add views field in post model"

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

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
  • 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.
$ prisma migrate dev --name "add profile model"
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
$ prisma migrate dev --name "add category model"
simple crud of category model

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).

--

--

Déserteur camerounais résidant désormais en France. Passionné de programmation, sport, de cinéma et mangas. J’écris en français et en anglais dû à mes origines.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Kevin Tewouda

Kevin Tewouda

69 Followers

Déserteur camerounais résidant désormais en France. Passionné de programmation, sport, de cinéma et mangas. J’écris en français et en anglais dû à mes origines.