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.