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
