Prisma TypeScript ORM: An Introduction and Exploration

Introduction

Prisma has been one of the most popular ORM tools in the JavaScript/TypeScript ecosystem for quite some time now. This is for pretty good reasons. There’s a lot to like about Prisma, but just like everything else it comes with it’s own set of trade-offs.

When developing software applications you always want to pick the right tool for the job, especially when it comes to your database technologies. This post aims to provide a broad introduction to Prisma. I’ll highlight some of the things that it does well and point out certain things that it might not. In the end, the decision on whether or not you should use it is up to you. This post is a great starting point in your due diligence journey.

Prisma from 1000ft

What is Prisma?

Prisma is a modern ORM that supports multiple SQL databases, MongoDB, and CockroachDB (with the help of an external package). It offers a range of features that make working with databases easier and more productive, including a CLI tool, schemas, a type-safe query builder, migrations, and a GUI for inspecting your database and schema.

With Prisma, you define your database schema using Prisma's schema definition language (SDL), and Prisma generates a type-safe query builder API based on that schema. This means that you can write database operations in a more type-safe and developer-friendly way. This includes autocompletion, syntax highlighting, and error checking.

How does it work?

Prisma abstracts away the underlying database operations and provides a unified and consistent API for working with different databases (typically SQL based). This API builds up your SQL/Mongo queries for you, so you don't have to write them manually.

Along with filtering, paginating, and sorting your data, the API allows you to specify the fields you want to be returned as well as any related data you want to fetch (similar to a JOIN operation in SQL). If you’ve ever tried writing raw SQL before, or using a database driver directly, you understand just how amazing this is.

This makes it easier to write complex database operations, and reduces the likelihood of errors. It also drastically reduces the amount of code you need to write to have data that is ready to provide to any clients of your server application.

Prisma in practice

The last section was a bit of a teaser. Let’s get an idea of what this looks like in practice using a basic blog application as the premise for our example code. Here's the Prisma schema:

// schema.prisma

model User {
  id       Int      @id @default(autoincrement())
  name     String
  email    String   @unique
  posts    Post[]
}

model Post {
  id       Int      @id @default(autoincrement())
  title    String
  content  String
  published Boolean @default(false)
  author   User     @relation(fields: [authorId], references: [id])
  authorId Int
}

In this schema, we have a one-to-many relationship between User and Post. A user can have many posts, but a post can only have one author. Based on this schema, you can perform common database operations using Prisma's query builder.

Example queries:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// Create a new user
const newUser = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@example.com'
  }
})

// Create a new post for a user
const newPost = await prisma.post.create({
  data: {
    title: 'My First Post',
    content: 'Hello, world!',
    published: true,
    author: {
      connect: { id: newUser.id }
    }
  }
})

// Get all posts, including their authors
const postsWithAuthors = await prisma.post.findMany({
  include: {
    author: true
  }
})

// Get all posts by a specific user
const userPosts = await prisma.user.findUnique({
  where: { id: newUser.id }
}).posts()

// Update a post
const updatedPost = await prisma.post.update({
  where: { id: newPost.id },
  data: { title: 'My Updated Post' }
})

// Delete a user and all of their posts
const deletedUser = await prisma.user.delete({
  where: { id: newUser.id },
  include: { posts: true }
})

This is a really nice and friendly API in my opinion. But sometimes you need an escape hatch to drill back down to raw query operations. Prisma has that covered as well:

const users = await prisma.$queryRaw`SELECT * FROM User`

Where does it shine?

Type-safe query builder API

One of the biggest advantages of Prisma is its type-safety when you are working within a TypeScript application. The API that Prisma provides is already easy to use, but the type completion and suggestions really make it shine.

Easy seeding

Prisma is also great for writing powerful seed scripts. Prisma's CLI tool allows you to easily write scripts that populate your database with sample data.

Migrations

Prisma's migrations tooling is particularly powerful and easy to use, allowing developers to make changes to their schema and keep their database up to date with minimal effort.

Potential gotchas and trade-offs

While Prisma is a powerful and versatile ORM, it does have some limitations and trade-offs. For example, some features and APIs are only available for certain databases. The API docs are pretty proactive about telling you when a particular feature is only supported by certain databases though.

Another potential issue with Prisma is its performance. Depending on the requirements for your application and the complexity of your schema, this could be something you need to consider. While Prisma does make it easy to fetch related data from other tables in your queries, it doesn’t use traditional JOIN operation’s to do this. It runs additional queries for each relation included in your query. You can configure logging for the Prisma client to inspect what queries it’s running under the hood if you want more details on this.

Additional reading and resources

If you're considering Prisma for your project, the Concepts page is a great resource that covers a lot of material from a higher-level systems design standpoint. You can also check out this blog post that compares Prisma to other ORMs and query builders.

There are some good open-source application examples that use Prisma. Examples like these can help you understand how to use Prisma in practice and see how it can be integrated with other tools and frameworks.

• https://github.com/kentcdodds/kentcdodds.com
• https://github.com/poulainv/tottem
• https://github.com/calcom/cal.com

There are also some full-stack frameworks that come integrated with Prisma like RedwoodJS and Blitz.js. With these frameworks, you can quickly get up and running with Prisma, and benefit from its features and capabilities without having to worry about integrating Prisma into your application manually. This can save you time and effort, and allow you to focus on building your application's features and functionality.

Conclusion

Prisma is a powerful and versatile ORM that offers a range of features that make working with databases easier and more productive. Its type-safe API and schema definition language can help you write database operations in a more developer-friendly way, while its CLI tool and GUI make it easier to manage your database and schema.

However, Prisma also has some limitations and trade-offs, such as potential performance issues and database-specific features. When choosing a database and ORM for your project, it's important to consider your requirements and use case specifically.

Is Prisma right for you? Ultimately, the decision is yours. But if you're looking for a quality and reliable ORM for your JavaScript or TypeScript project, Prisma is definitely worth considering.