Prisma to interact with my DB

This post was initially published on Medium.

I’ve been using Prisma for a few months, and I’ve recently given a talk about it, let’s try to recap some takeaways here.

You might prefer to go over the slides, here you go.

Interacting with my database

Prisma belongs to the ORM family (Object-Relational Mapping). It can be seen as an extra layer between your server and your database and its goal is to facilitate your job each time you want to interact with your DB.

Depending on your project, using this extra layer can bring you some benefits, but there are at least two other ways to interact with your database:

Option 1. Native driver

As I’m using PostgreSQL, it would be node-postgres. By choosing this solution, you will be in total control of the SQL sent to your database, and you’ll end up writing your queries like this:

Option 2. A query builder

A query builder will help you catch some typos and even more if you’re using TypeScript. The most popular one is knex, and you’ll end up expressing your queries using their “helper” functions. Here is a simple example:

Option 3. A complete and full-featured framework

Here are some we can think of.

Note: Some of these tools use knex as a query builder under the hood.

Package scores come from snyk.io (example for Prisma), they take into account a few aspects such as popularity, maintenance, security and community. I don’t know what it’s worth but at least it gives another metric along with Github stars (which is not a real metric, yes you’re right).
You can also check moiva.io 📊.

Comparing these frameworks is not an easy job (unless you have unlimited time to try them all!). The following article helped me get a better idea of each of them: https://www.sitepoint.com/javascript-typescript-orms/

Prisma team also did a good job comparing SQL, query builders, and ORMs: https://www.prisma.io/dataguide/types/relational/comparing-sql-query-builders-and-orms

Long story short, I went on with Prisma.

Our database schema

Let’s get to it. The first thing we notice when starting with Prisma is the schema we have to define. It uses a specific syntax and it goes something like this:

(Prisma 2 syntax here, see next screenshot for Prisma ≥ v3)

Most of it should be self-explanatory 🤞.

Here is Prisma v3 equivalent:

Then to inject this structure to your database, you’ll need to:

Set the value of the environment variable DATABASE_URL;
Have a database that listens to this URL, obviously;
Run the following Prisma CLI command: prisma db push

Good news if you already have an existing project and your database structure in place, you can infer this schema with prisma db pull.

To recap:

Schema in other ORMs

You might have used ORMs before. I personally remember Hibernate in Java, it was using annotations on top of class attributes. In the JavaScript world, TypeORM follows the same idea:

So that is a big difference with Prisma.

Very subjective opinion, but I really like that Prisma does not force me to express my entities as JavaScript classes. Since it uses a separate file to define the schema, it serves as a better separation between two worlds: the relational world and the object-oriented world.

To go further on that subject, you can read about the object-relational impedance mismatch or what the Prisma team says about it.

Generate Prisma DB client

Now that we have our database schema all described in a prisma/schema.prisma file, we can ask Prisma CLI to generate our DB client: prisma generate

We end up with two new files within our node_modules folder:

👉 index.js

findFirst(), findMany(), updateOne(), etc.

We find in this file all necessary helper functions that we’ll use to write queries. These will always return JavaScript objects (POJOs). And for edge cases, like when you want to take advantage of some PostgreSQL extensions, we still can write raw SQL queries: prismaClient.$queryRaw('<SQL>').

👉 index.d.ts

All TypeScript types corresponding to our entities. Some basic examples:

Use Prisma DB client

Now that our DB client has been generated, let’s use it into our app.

Basic example of using the newly generated Prisma client

In a more serious project, you might want to instantiate your Prisma client only once and then use dependency injection to pull it wherever you need.

Tests

Two solutions are documented:
(I haven’t tried the first one.)

1. By completely mocking Prisma client: https://www.prisma.io/docs/guides/testing/unit-testing

2. By using a test database:

Prior to your test script, be sure to set a different database URL:
DATABASE_URL=postgresql://test-user:test-password@localhost:5432/db-test

And then clean all tables between each test:
TRUNCATE TABLE \"${dbSchemaName}\".\"${tablename}\" CASCADE;

You can find more info here: https://www.prisma.io/docs/concepts/components/prisma-client/crud#deleting-all-data-with-raw-sql–truncate

Prisma migration tool

Migrations are one of the big subjects you’ll have to deal with during your development phase. Prisma won’t be your silver bullet, you will still need to gain knowledge about good practices (the expand and contract pattern, “Ensure the new changes work before you drop anything”, etc.), but Prisma CLI does include all necessary commands.

Here is the standard way:

Don’t forget to add all your migration.sql scripts to your version control system.

Prisma Studio

Prisma also includes a minimalist admin UI on top of your database.

I’ve tried it plus a few other tools and have kept using TablePlus in the end.

What I like about Prisma

➕➕ TypeScript-first.

➕➕ Dynamic project, regular releases with good communication, huge community on Slack, Prisma Day 2021, etc.
(Like any other tool, we don’t know if it will still be that dynamic in a few years!)

➕➕ A wide and well-written documentation. For example, I was looking for a place to host my database and I ended up reading a Prisma post about it!

➕➕ Community seems to have a positive opinion on the tool.https://cdn.embedly.com/widgets/media.html?type=text%2Fhtml&key=a19fcc184b9711e1b4764040d3dc5c07&schema=twitter&url=https%3A//twitter.com/kentcdodds/status/1400893865196879873&image=https%3A//i.embed.ly/1/image%3Furl%3Dhttps%253A%252F%252Fabs.twimg.com%252Ferrors%252Flogo46x38.png%26key%3Da19fcc184b9711e1b4764040d3dc5c07

Caveats

One time, I ended up outside “the right migration flow” and I had to manually mark some migration scripts as “done”. Once again the documentation was helpful but it took me some time to understand what was going on.
Composite primary keys in your schema.prisma tend to generate not-so-easy-to-use TypeScript types. But Prisma team has worked on that when releasing v3 so you’ll have more control on how to name these constraints.
Using enums in your schema can be too much of a constraint compared to defining your enums in your code. Once again that’s a personal tradeoff: more flexibility with enums in JS/TS vs more security with DB-level enums.