Prisma ORM is an open-source query builder for Node.js. It consists of three parts: Prisma Client for querying relational databases, a migration tool, called Prisma Migrate, and Prisma Studio, a graphical user interface for viewing and editing data.
Read here the five reasons why we love to use Prisma in our projects.
- Typescript support
- Abstraction from database-specific details
- Database migration at our command
- Filtering, sorting and pagination
- Open source
Typescript support
We've been utilizing Typescript in all of our projects for a few years now. Typing our code reduces the likelihood of errors because it is verified inside the editor as we code. It also autocompletes your code while typing.
Data querying is now also type-safe because Prisma auto-generates types based on the database schema. There is no need to manually update associated types even if the schema is changed because Prisma takes care of this automatically. This marriage between Prisma and Typescript is a great fit with our current technology stack and way of working.
Abstraction from database-specific details
When one of our clients asked us to redesign their website, we took advantage of the chance to rewrite the entire site in Next.js 13. Although the original code was written in PHP and plain JavaScript, a later addition already used Next.js. Two separate applications, each with its own database, MySQL and SQLite, respectively. As a result, we decided to merge these as well and move all the data into a single MySQL database. Fortunately, we were already using Prisma ORM to retrieve the data, making the database migration a breeze.
While relational databases and RDBMSs are commonly used for data storage and management, it comes as no surprise that a wide range of implementations have resulted from it. Differences in implementation can be seen in areas such as data types, functions, syntax, and so on. Despite the fact that SQL is regarded as the standard query language for relational databases, each database vendor put their own flavor to it. You might think of them as dialects of the same language. In MySQL, for example, the function to get the current date is CURDATE(), whereas in SQLite it is DATE('now').
Thankfully, while configuring our database schema with Prisma ORM, we don't need to be aware of these distinctions. Any specific implementation or language variation will be taken care of for us. As a result, we can query the data without taking into account any of the database specifics. This is a significant advantage when working with many clients on various projects and accessing various databases on a daily basis.
In this case, it allowed us to switch not just between databases, but also between database suppliers, without having to rewrite any data fetching functions or code. It was simply a matter of replacing the previous data source with the new one and including @map attributes. This last step is only required if you want to use underscores when naming database columns.
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model Result {
id Int @id @default(autoincrement())
date DateTime
discipline String
member String
score Float?
timeInt Int? @map(name: "time_int")
category String
isIndoor Boolean @map(name: "is_indoor")
year Int
gender String
@@index([year])
@@map(name: "result")
}
Database migration at our command
Clients usually have a clear idea of what they want to achieve and where they want to go in the coming weeks and months when they launch a new website. However, as organizations and technologies evolve, so will their digital and data requirements. We are frequently asked to make improvements and add new features to existing projects. In some cases, this may also require changes to the database.
As mentioned before, Prisma has us covered when it comes to switching data sources. Using Prisma Migrate, we can also quickly change the tables and columns without leaving our code editor. There is no need to write and execute SQL commands. Simply edit the database schema and execute prisma migrate dev in the terminal.
When we are through with our changes, we push our code along with the updated schema. And, because we already deploy code changes to all environments automatically, the necessary migrations are executed simultaneously by running prisma migrate deploy.
Filtering, sorting and pagination
For this use case, we needed a way to retrieve the top result for each member from more than 900,000 records. The results also need to be filtered, paginated and ranked from best to worst. We can accomplish all of this using the Prisma Client, so we don't need to develop these functionalities ourselves. Here is the resulting query:
import {NextApiRequest, NextApiResponse} from 'next'
import prisma from '../../database/prisma'
export default async function handler(
req: NextApiRequest,
res: NextApiResponse
) {
const {filters, page, resultsPerPage} = req.body
const filterConditions = Object.keys(filters).reduce((prev, curr) => {
return {...prev, [curr]: {equals: filters[curr]}}
}, {})
const results = await prisma.result.findMany({
distinct: ['member'],
skip: ((page && page - 1) || 0) * resultsPerPage,
take: resultsPerPage,
where: {
OR: [{score: {not: 0}}, {timeInt: {not: 0}}],
...filterConditions
},
orderBy: [
{score: 'desc'},
{timeInt: 'asc'},
{date: 'asc'}
]
})
res.status(200).json(results)
}Summary
The Typescript support of Prisma provides significant added value. Knowing at all times what data to expect brings peace of mind during development. We have been using Typescript in all our projects. The fact that tools like Prisma also support it allows us to further extend our way of working. For completeness, a downside of using Prisma is that you need to learn another (query) language. However, that is just a minor drawback compared to the many benefits it brings.
