A Prisma ORM Review

Today my plan is to go through Prisma ORM's basic setup and usage and explain what I like about this ORM and what I don't like. I have a lot of experience working with different ORMs and plain queries for relational databases, and I will be reviewing with this background knowledge.

I'm going to use TypeScript language and SQLite database, as well as yarn package manager in my examples. I don't expect you to know them, but I'm going to provide a pretty self-explanatory code or some comments.

Installing Prisma to a new project

For the most part, I'm going to follow official docs

Let's start by creating an empty project and initializing it

mkdir prisma_orm_review
cd prisma_orm_review
yarn init  -y
yarn add -D typescript ts-node @types/node

Adding tsconfig.json file

touch tsconfig.json

{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true
  }
}

Adding Prisma

yarn add prisma

Initializing Prisma for MySQL

npx prisma init --datasource-provider mysql

Data modeling

Prisma ORM takes a bit unusual approach to data modeling. You have to define your models in a separate file and use specific to the ORM syntax. It's not a big deal, but it's worth mentioning. The syntax is pretty much self-explanatory.

model Account {
  id              Int       @id @default(autoincrement())
  parentAccountId Int?
  parentAccount   Account?  @relation("Parent", fields: [parentAccountId], references: [id])
  childAccounts   Account[] @relation("Parent")
  email           String    @unique
  name            String?
  meta            Json
  isActive        Boolean   @default(false)
  createdAt       DateTime  @default(now())
  posts           Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  account   Account @relation(fields: [accountId], references: [id])
  accountId Int
}

Based on that schema, Prisma ORM will generate a client to work with the database. Also, it's possible to generate migrations for the database.

npx prisma migrate dev --name init

Easy Level Queries

This script will create an account with one post. It's possible to create a nested structure to create the account and the post simultaneously, which is very lovely.

I do recommend enabling query logs in the dev environment. This will help you identify problems with your queries. Also, you can see the queries used under the hood.

script.ts

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
  log: ["query", "info", "warn", "error"],
});

async function main() {
  // create an account
  const accountWithPost = await prisma.account.create({
    data: {
      email: "[email protected]",
      name: "John Doe",
      meta: {
        firstName: "John",
        lastName: "Doe",
        age: 30,
      },
      isActive: true,
      posts: {
        create: {
          title: "Post #1",
          content: "Hello world",
          published: true,
        },
      },
    },
  });
  console.log(accountWithPost);

  // select active accounts
  const activeAccounts = await prisma.account.findMany({
    where: {
      isActive: true,
    },
  });
  console.group(activeAccounts);
}

main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

This case is pretty simple. In the end, we have a couple of SQL queries to create an account and a post and one query to select created account. Also, it wraps everything into one transaction. No surprises here.

BEGIN;
INSERT
INTO
    cta.Account (id, email, name, meta, isActive, createdAt)
VALUES
    (?, ?, ?, ?, ?, ?);
INSERT
INTO
    cta.Post (id, title, content, published, accountId)
VALUES
    (?, ?, ?, ?, ?);
SELECT
    cta.Account.id,
    cta.Account.parentAccountId,
    cta.Account.email,
    cta.Account.name,
    cta.Account.meta,
    cta.Account.isActive,
    cta.Account.createdAt
FROM
    cta.Account
WHERE
    cta.Account.id = ?
LIMIT ? OFFSET ?;
COMMIT;

Next query will select all active accounts from the database. In our database, we have only one account, so I do not worry about pagination. But in real life, you will have a lot of accounts, so pagination is a must.

SELECT
    cta.Account.id,
    cta.Account.parentAccountId,
    cta.Account.email,
    cta.Account.name,
    cta.Account.meta,
    cta.Account.isActive,
    cta.Account.createdAt
FROM
    cta.Account
WHERE
    cta.Account.isActive = ?

Raw SQL Queries

In the next step, let's select the first 10 distinct account names.

async function main() {
  const result = await prisma.account.findMany({
    select: {
      name: true,
    },
    where: {},
    distinct: ["name"],
    take: 10,
    skip: 0,
  });

  console.log(result);
}

The code looks pretty simple. But I see a surprise in the logs. The generated query looks like this:

SELECT
    cta.Account.id,
    cta.Account.name
FROM
    cta.Account
WHERE
    1 = 1
ORDER BY
    cta.Account.id ASC

As you can see, offset and limit are not used. This is because Prisma does not use DISTINCT in the query and filters data in memory. It's not a problem for small databases but for large ones, it's a performance killer. Imagine filtering in memory millions of records just to find the first 10.

In this case, we have to jump to the raw SQL query.

async function main() {
  const limit = 10;
  const result = await prisma.$queryRaw<Array<{ name: string }>>(
    Prisma.sql`SELECT DISTINCT
                   (name)
               FROM
                   Account
               LIMIT ${limit}`
  );

  console.log(result);
}

Generated query:

select distinct(name) from Account limit ?

At the first glance, it looks like an example from a book called "SQL injections in practice." But it's not. Under the hood, Prisma.sql does some magic to generate a query.

The output of this

console.log(Prisma.sql`select distinct(name) from Account limit ${limit}`);

{
  text: 'select distinct(name) from Account limit $1',
  sql: 'select distinct(name) from Account limit ?',
  values: [ 10 ]
}

All variables in the query will be replaced by placeholders. Therefore, it's safe to use variables in the query.

Raw SQL Unsafe

At this step, let's add more complexity to our previous query by filtering by fields in metadata. To make our life a bit more complex, the names of the fields are not predefined and will be provided from the frontend.

async function main() {
  const limit = 10;
  const jsonFieldName = "firstName";
  const jsonFieldValue = "John";

  const result = await prisma.$queryRaw<Array<{ name: string }>>(
    Prisma.sql`SELECT DISTINCT
        (name)
    FROM
        Account
    WHERE
        meta ->> "$.${jsonFieldName}" = ${jsonFieldValue}
    LIMIT ${limit};
`
  );
}

Turns out, this query does not work because Prisma.sql replace all variables with placeholders. The resulting SQL is:

SELECT DISTINCT
    (name)
FROM
    Account
WHERE
    meta ->> "$.?" = ?
LIMIT ?;

And it's time to move to the $queryRawUnsafe. We are still going to use query parameters for JSON field values and limit, but JSON field name will be concatenated with the query.

async function main() {
  const limit = 10;
  const jsonFieldName = "firstName";
  const jsonFieldValue = "John";

  const result = await prisma.$queryRawUnsafe<Array<{ name: string }>>(
    `SELECT DISTINCT
        (name)
    FROM
        Account
    WHERE
        meta ->> "$.${jsonFieldName}" = ?
    LIMIT ?;
`,
    jsonFieldValue,
    limit
  );

  console.log(result);
}

Resulting query:

SELECT DISTINCT
    (name)
FROM
    Account
WHERE
    meta ->> "$.firstName" = ?
LIMIT ?;

And this one looks pretty good. I do not recommend using such queries in production. It will be slow.

Conclusion

In general, Prisma ORM is a good choice for your project. It lacks some features of other ORMs; for example, performing distinct in the memory is not acceptable. But it's not a big deal because you can jump the lower levels such as raw SQL queries.