ORM, Prisma, and How You Should Build Your Next Backend Database Project

Prisma is a premier object-relational mapping (ORM) tool in the Node.js ecosystem. It's powerful yet simple and easy to use. A great team also supports it, and it has enjoyed widespread adoption by the Node.js community. Prisma has earned such an excellent reputation that we incorporated it into our tech stack for Amplication. Not only do we use Prisma for our generated applications, but we also use it in our internal tech stack.

In this article, we'll closely examine some of Prisma's features and the team that supports it. Prisma has become as crucial to Amplication as Node itself, and I highly recommend you consider it for your next Node project.

Introducing Prisma

Prisma is one of the most popular ORMs in the node ecosystem, and over the last two years, it has seen consistent growth in the community. Much of this has to do with the team supporting the product. Prisma is open-source and licensed under Apache-2.0, making it very flexible for your projects—an impressive array of investors, such as key members from Heroku, Netlify, and GraphQL, back it. Support for Prisma is ongoing and robust, as the team continuously makes improvements with regular releases that address bugs, performance issues, and other enhancements.

One of the most important considerations when choosing a tech stack is ensuring that any application or package I use is well supported. No matter how performant or sophisticated a package is, if it's under-supported, it can add untold hours to your project as you try to fit it into an edge use case or work out a bug. Prisma's popularity and open-source status mean that not only is it supported by a fantastic internal team, but the community support is incredible, too. There are plenty of well-educated and experienced Prisma developers on the usual forums.

Prisma is a package that invites new users. It is well-documented and well-suited for your first or 50th Node project. Their documentation contains comprehensive information on the features available. You can also access several guides, including introductory "how-tos," steps for deploying applications that use Prisma, and instructions on migrating from another ORM. The guides are thorough, up-to-date, and easy to follow. In addition, Prisma's team has made sure your experience using their ORM is as painless as possible.

Why Use Prisma?

In the simplest case, Prisma can access your database as an ORM. As part of its suite of products, Prisma offers a "Client API" that can make writing even the most complex database operations simple. But where Prisma shines is in its ability to handle complex querying operations.

Prisma's API lets you easily traverse relationships. Below is an example of an application accessing a database from the Prisma Client tutorial. First, the application accesses an author's profile by using the navigation properties from the blog post to the author and finally to the author's profile:

const authorProfile: Profile | null = await prisma.post
  .findUnique({ where: { id: 1 } })
  .author()
  .profile();

It also makes pagination a breeze by exposing arguments for order, limits, and cursors. Below you can see an example where you can use the client to take five posts from the database by starting from the post with id=2:

// Find the next 5 posts after post id 2
const paginatedPosts3: Post[] = await prisma.post.findMany({
  take: 5,
  cursor: {
    id: 2,
  },
});

It also allows for aggregate queries such as sum and count:

// Group users by country
const groupUsers = await prisma.User.groupBy({
  by: ["country"],
  _count: {
    id: true,
  },
});

Along with these features, Prisma's client also facilitates transactions, includes middleware and the execution of raw questions, and helps make logging simple.

But to limit Prisma's capabilities to just reading or writing data would be a major disservice. Another great aspect of Prisma is how it handles migrations. Prisma supports SDL-first and code-first approaches to modeling your data structure in code. I suggest the code-first approach to writing your database if creating a new application. Still, creating your Prisma schema using the database for existing databases can be easier.

If you decide to work with the code-first approach, you can use Prisma Migrate to create the tables in your database. Start by writing your schema definition using Prisma's markup language. The following is a sample from the Prisma tutorial:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

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

With your schema set up, you can prepare your database by selecting one of the providers supported by Prisma. In the example below, I'm using PostgreSQL, but there are several available options like MySQL, SQLite, MongoDB, CockroachDB, and Microsoft SQL Server:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

With your schema and provider set, Prisma is now able to convert your schema into executable code to create your database:

-- CreateTable
CREATE TABLE "User" (
  "id" SERIAL,
  "name" VARCHAR(200) NOT NULL,
  PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Post" (
  "id" SERIAL,
  "title" VARCHAR(150) NOT NULL,
  "published" BOOLEAN NOT NULL DEFAULT true,
  "authorId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

It's just that simple. If you switch from PostgreSQL to MySQL (or any other provider), change your provider and rebuild your migration. If you need to create seed data, you can configure your application to know where your seed data is and use the Prisma client to insert any data you need.

Using Prisma With Amplication

Of course, you need to be able to get the data from your database to your User. Amplication will create a schema.prisma file from the configuration you inputted into the UI. This file will contain all the fields, types, and relationships necessary to create your databases. Each entity you make in the UI will also generate a series of classes. Since Amplication is built on NestJS, we generate a module, a resolver, and a service to facilitate handling queries to the API for the entity.

First, the service is where we use Prisma to query or modify data in the database. We generated typed classes to represent the arguments for each query, such as CustomerFindManyArguments. Then we create wrapper methods for Prisma such as findMany, which calls the customer collection's findMany function from the Prisma client and passes the instance of CustomerFinderManyArguments to it. The Prisma client uses these arguments to filter your data or use pagination.

Amplication will then generate each entity's REST API controllers and GraphQL resolvers. First, we generate the typed classes; we use the same entity definition you provided to build prisma.schema. This helps sync the API and database regarding required fields and types. Then we can use those typed classes to inform Swagger of the structure of the application's API. Finally, we register the modules to the application and then the application to Swagger. You'll notice that the fields in your Args classes are decorated with APIProperty. This gives Swagger all the details it needs to create your API documentation.

A closer look at the CustomerFinderManyArguments class reveals that we also use decorators from the @nestjs/graphql. By doing this, we can provide GraphQL of the available fields in your data. We then generate a resolver to select what the application will expose via GraphQL. The resolvers depend on an entity service generated with your application.

Finally, Amplication will generate Swagger documentation for your API that fully represents your end-to-end data transfer.

You can review the CustomerServiceBase in our sample application for a closer look.

Wrapping Up

Now you can see why Prisma has become integral to the Amplication stack. It is simple and easy to use but powerful and fully featured. The team supporting the product has done a fantastic job of enhancing it, too. As a result, Prisma will be a part of our stack for the foreseeable future, and we recommend you make it a part of yours as well.

If you're excited about Amplication and its potential to simplify your development workflow, please consider showing your support by starring our repository on GitHub. Your 🌟 will help us reach more developers and continue to improve the platform.