# Prisma

Prisma (opens new window) is an open-source (opens new window) ORM for Node.js and TypeScript. It is used as an alternative to writing plain SQL, or using another database access tool such as SQL query builders (like knex.js) or ORMs (like TypeORM and Sequelize (opens new window)). Prisma currently supports PostgreSQL, MySQL, SQL Server and SQLite.

While Prisma can be used with plain JavaScript, it embraces TypeScript and provides a level to type-safety that goes beyond the guarantees other ORMs in the TypeScript ecosystem. You can find an in-depth comparison of the type-safety guarantees of Prisma and TypeORM here (opens new window).

See issue for more details: #1389 (opens new window)

Note

If you want to get a quick overview of how Prisma works, you can follow the Quickstart (opens new window) or read the Introduction (opens new window) in the documentation (opens new window).

# Prisma generator for Ts.ED Premium sponsors

If you are a premium sponsor, or you want to become one, you can ask Ts.ED team (opens new window) to get your access to the private packages @tsedio/prisma.

This package generates enums, and classes compatible with the Ts.ED decorators like Returns and extends possibilities about the prisma.schema. It also generates PrismaService and Repositories!

You should follow this tutorial to create your project properly. You will have more details regarding the installation of the @tsedio/prisma package.

# Getting started

In this tutorial, you'll learn how to get started with Ts.ED and Prisma from scratch. You are going to build a sample Ts.ED application with a REST API that can read and write data in a database.

For the purpose of this guide, you'll use a SQLite (opens new window) database to save the overhead of setting up a database server.

Note that you can still follow this guide, even if you're using PostgreSQL or MySQL – you'll get extra instructions for using these databases at the right places.

Note

If you already have an existing project and consider migrating to Prisma, you can follow the guide for adding Prisma to an existing project (opens new window). If you are migrating from TypeORM, you can read the guide Migrating from TypeORM to Prisma (opens new window).

# Create a Ts.ED project

To get started, install the Ts.ED CLI and create your app skeleton with the following commands:

npm install -g @tsed/cli
tsed init tsed-prisma
1
2

From the CLI, select the following features: Swagger, Eslint, Jest.

# Set up Prisma

Start by installing the Prisma CLI as a development dependency in your project:

cd tsed-prisma
npm install --save-dev prisma
1
2

In the following steps, we'll be using the Prisma CLI (opens new window). As a best practice, it's recommended to invoke the CLI locally by prefixing it with npx:

npx prisma
1

Now create your initial Prisma setup using the init command of the Prisma CLI:

npx prisma init
// OR
yarn prisma init
1
2
3
  • schema.prisma: Specifies your database connection and contains the database schema,
  • .env: A dotenv file, typically used to store your database credentials in a group of environment variables.

# Set the database connection

Your database connection is configured in the datasource block in your schema.prisma file. By default it's set to postgresql, but since you're using a SQLite database in this guide you need to adjust the provider field of the datasource block to sqlite:

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

generator client {
  provider = "prisma-client-js"
}
1
2
3
4
5
6
7
8

Now, open up .env and adjust the DATABASE_URL environment variable to look as follows:

DATABASE_URL="file:./dev.db"
1

SQLite databases are simple files; no server is required to use a SQLite database. So instead of configuring a connection URL with a host and port, you can just point it to a local file which in this case is called dev.db. This file will be created in the next step.

With PostgreSQL and MySQL, you need to configure the connection URL to point to the database server. You can learn more about the required connection URL format here (opens new window).

    # Create two database tables with Prisma Migrate

    In this section, you'll create two new tables in your database using Prisma Migrate (opens new window). Prisma Migrate generates SQL migration files for your declarative data model definition in the Prisma schema. These migration files are fully customizable so that you can configure any additional features of the underlying database or include additional commands, e.g. for seeding.

    Add the following two models to your schema.prisma file:

    model User {
      id    Int     @default(autoincrement()) @id
      email String  @unique
      name  String?
      posts Post[]
    }
    
    model Post {
      id        Int      @default(autoincrement()) @id
      title     String
      content   String?
      published Boolean? @default(false)
      author    User?    @relation(fields: [authorId], references: [id])
      authorId  Int?
    }
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15

    With your Prisma models in place, you can generate your SQL migration files and run them against the database. Run the following commands in your terminal:

    npx prisma migrate dev --name init
    
    1

    TIP

    Add the previous command to your scripts in the package.json:

    {
      "scripts": {
        "prisma:migrate": "npx prisma migrate dev --name init"
      }
    }
    
    1
    2
    3
    4
    5

    This prisma migrate dev command generates SQL files and directly runs them against the database. In this case, the following migration file was created in the existing prisma directory:

    $ tree prisma
    prisma
    ├── dev.db
    ├── migrations
    │   └── 20210507100915_init
    │       └── migration.sql
    └── schema.prisma
    
    1
    2
    3
    4
    5
    6
    7

    # Install and generate Prisma Client

    Prisma Client is a type-safe database client that's generated from your Prisma model definition. Because of this approach, Prisma Client can expose CRUD (opens new window) operations that are tailored specifically to your models.

    To install Prisma Client in your project, run the following command in your terminal:

    $ npm install @prisma/client
    
    1

    Note that during installation, Prisma automatically invokes the prisma generate command for you. In the future, you need to run this command after every change to your Prisma models to update your generated Prisma Client.

    The prisma generate command reads your Prisma schema and updates the generated Prisma Client library inside node_modules/@prisma/client.

    WARNING

    The generated interfaces cannot be used with @Returns to describe the Swagger documentation! You have to use the premium package @tsedio/prisma

    TIP

    Add the previous command to your scripts in the package.json:

    {
      "scripts": {
        "prisma:generate": "npx prisma generate"
      }
    }
    
    1
    2
    3
    4
    5

    # Configure Ts.ED prisma client Premium sponsors

    Now our prisma.schema is ready for the basic usage. It's time to configure @tsedio/prisma.

    See our dedicated guide here.

    The client will generate all enums, classes, repositories and PrismaService for the Ts.ED framework!

    # Create the PrismaService and PostService

    You're now able to send database queries with Prisma Client. If you want to learn more about building queries with Prisma Client, check out the API documentation (opens new window).

    When setting up your Ts.ED application, you'll want to abstract away the Prisma Client API for database queries within a service. To get started, you can create a new PrismaService that takes care of instantiating PrismaClient and connecting to your database.

    Inside the src/services directory, create a new file called PrismaService.ts and add the following code to it:

    import { Injectable, OnInit, OnDestroy } from '@tsed/common';
    import { PrismaClient } from '@prisma/client';
    
    @Injectable()
    export class PrismaService extends PrismaClient implements OnInit, OnDestroy {
      async $onInit() {
        await this.$connect();
      }
    
      async $onDestroy() {
        await this.$disconnect();
      }
    }
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13

    # Create UserService

    Next, you can write services that you can use to make database calls for the User and Post models from your Prisma schema.

    Still inside the src/services directory, create a new file called UsersRepository.ts and add the following code to it:

    import {Inject, Injectable} from "@tsed/di";
    import {Prisma, User} from "@prisma/client";
    import {PrismaService} from "./PrismaService";
    
    @Injectable()
    export class UserRepository {
      @Inject()
      prisma: PrismaService;
    
      async findUnique(args: Prisma.UserFindUniqueArgs): Promise<User | null> {
        return this.prisma.user.findUnique(args);
      }
    
      async findMany(args?: Prisma.UserFindManyArgs): Promise<User[]> {
        return this.prisma.user.findMany(args);
      }
    
      async create(args: Prisma.UserCreateArgs): Promise<User> {
        return this.prisma.user.create(args);
      }
    
      async update(args: Prisma.UserUpdateArg): Promise<User> {
        return this.prisma.user.update(args);
      }
    
      async delete(args: Prisma.UserDeleteArgs): Promise<User> {
        return this.prisma.user.delete(args);
      }
    }
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29

    Notice how you're using Prisma Client's generated types to ensure that the methods that are exposed by your service are properly typed. You therefore save the boilerplate of typing your models and creating additional interface or DTO files.

    Now do the same for the Post model.

    Still inside the src directory, create a new file called PostsRepository.ts and add the following code to it:

    import {Post, Prisma} from "@prisma/client";
    import {Inject, Injectable} from "@tsed/di";
    import {PrismaService} from "./PrismaService";
    
    @Injectable()
    export class PostsRepository {
      @Inject()
      prisma: PrismaService;
    
      async findUnique(args: Prisma.PostFindUniqueArgs): Promise<Post | null> {
        return this.prisma.post.findUnique(args);
      }
    
      async findMany(args?: Prisma.PostFindManyArgs): Promise<Post[]> {
        return this.prisma.post.findMany(args);
      }
    
      async create(args: Prisma.PostCreateArgs): Promise<Post> {
        return this.prisma.post.create(args);
      }
    
      async update(args: Prisma.PostUpdateArg): Promise<Post> {
        return this.prisma.post.update(args);
      }
    
      async delete(args: Prisma.PostDeleteArgs): Promise<Post> {
        return this.prisma.post.delete(args);
      }
    }
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29

    Your UsersRepository and PostsRepository currently wrap the CRUD queries that are available in Prisma Client. In a real world application, the service would also be the place to add business logic to your application. For example, you could have a method called updatePassword inside the UsersRepository that would be responsible for updating the password of a user.

    # Create controllers

    Finally, you'll use the services you created in the previous sections to implement the different routes of your app.

    Now we have to create controllers to expose your business to our consumers. So create the following controllers in src/controllers directory:

      Now start the server and open the Swagger documentation to test your REST API!

      # Summary

      In this tutorial, you learned how to use Prisma along with Ts.ED to implement a REST API. The controller implementing the routes of the API is calling a PrismaService which in turn uses Prisma Client to send queries to a database to fulfill the data needed by incoming requests.

      Last Updated: 9/17/2021, 8:57:54 AM

      Other topics