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 file tsconfig.json 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: "hello@exmaple.com",
      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 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. DISTINCT 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, does some magic to generate a query. Prisma.sql 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 replace all variables with placeholders. The resulting SQL is: Prisma.sql SELECT DISTINCT
    (name)
FROM
    Account
WHERE
    meta ->> "$.?" = ?
LIMIT ?; And it's time to move to the . We are still going to use query parameters for JSON field values and limit, but JSON field name will be concatenated with the query. $queryRawUnsafe 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 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. distinct

Read My Stories

Hire Me

Buy Me a Coffee

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

A Prisma ORM Review

Too Long; Didn't Read

People Mentioned

Company Mentioned

Aleksei Kankov

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

A Prisma ORM Review

Too Long; Didn't Read

People Mentioned

Company Mentioned

Aleksei Kankov

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES