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).

• Kit Prisma

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

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

The @tsed/prisma will be automatically installed. 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!

TIP

You can become a sponsor and help us to improve this module. A dedicated tiers is available on Github sponsors here (opens new window). By choosing this options, your feature/fix request treated in top priority.

Set up Prisma

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

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

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

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

npx prisma init
// OR
yarn prisma init

• 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"
}

generator tsed {
  provider = "tsed-prisma"
}

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

DATABASE_URL="file:./dev.db"

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).

Prisma client configuration

After installation, you need to update your schema.prisma file and then add a new generator section below the client one:

generator client {
  // ...
}

generator tsed {
  provider = "tsed-prisma"
}

Then after running npx prisma generate, this will emit the generated Ts.ED classes and Enums to @tsedio/prisma/.schema in node_modules folder.

You can also configure the default output folder, e.g.:

generator tsed {
  provider = "tsed-prisma"
  output   = "../prisma/generated/tsed"
}

By default, when the output path contains node_modules, the generated code is transpiled - containing *.js and *.d.ts files that are ready to use (import) in your code. However, if you explicitly choose another folder in output config, the generated code will be emitted as raw TS files which you can use and import as your other source code files.

You can overwrite that by explicitly setting emitTranspiledCode config option:

generator tsed {
  provider           = "tsed-prisma"
  output             = "../prisma/generated/tsed"
  emitTranspiledCode = true
}

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?
}

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

TIP

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

{
  "scripts": {
    "prisma:migrate": "npx prisma migrate dev --name init"
  }
}

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

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

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"
  }
}

Generate Models and Repositories

By using the Prisma client and the @tsed/prisma module, you'll be able to generate models, enums and repositories each time you use the following command:

npm run prisma:generate

@tsed/prisma let you adding extra decorators on your datamodel property to generate the Ts.ED model. Here is an example of Prisma datamodel with some decorators:

model User {
  /// @TsED.Groups("!creation")
  /// Comment
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now())
  /// @TsED.Email()
  /// @TsED.Description("User email. This email must be unique!")
  email       String   @unique
  weight      Float?
  is18        Boolean?
  name        String?
  successorId Int?
  successor   User?    @relation("BlogOwnerHistory", fields: [successorId], references: [id])
  predecessor User?    @relation("BlogOwnerHistory")
  role        Role     @default(USER)
  posts       Post[]
  keywords    String[]
  biography   Json
  /// @TsED.Ignore(ctx.endpoint === true)
  ignored    String
}

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

enum Role {
  USER
  ADMIN
}

Based on our previous example, Prisma Client will generate the following UserModel:

import {User} from "../client";
import {Integer, Required, Property, Groups, Format, Email, Description, Allow, Enum, CollectionOf} from "@tsed/schema";
import {Role} from "../enums";
import {PostModel} from "./PostModel";

export class UserModel implements User {
  @Property(Number)
  @Integer()
  @Required()
  @Groups("!creation")
  id: number;

  @Property(Date)
  @Format("date-time")
  @Required()
  createdAt: Date;

  @Property(String)
  @Required()
  @Email()
  @Description("User email. This email must be unique!")
  email: string;

  @Property(Number)
  @Allow(null)
  weight: number | null;

  @Property(Boolean)
  @Allow(null)
  is18: boolean | null;

  @Property(String)
  @Allow(null)
  name: string | null;

  @Property(Number)
  @Integer()
  @Allow(null)
  successorId: number | null;

  @Property(() => UserModel)
  @Allow(null)
  predecessor: UserModel | null;

  @Required()
  @Enum(Role)
  role: Role;

  @CollectionOf(() => PostModel)
  @Required()
  posts: PostModel[];

  @CollectionOf(String)
  @Required()
  keywords: string[];

  @Property(Object)
  @Required()
  biography: any;

  @TsED.Ignore((value: any, ctx: any) => ctx.endpoint === true)
  ignored: string;
}

And, the following repository:

import {isArray} from "@tsed/core";
import {deserialize} from "@tsed/json-mapper";
import {Injectable, Inject} from "@tsed/di";
import {PrismaService} from "../services/PrismaService";
import {Prisma, User} from "../client";
import {UserModel} from "../models";

@Injectable()
export class UsersRepository {
  @Inject()
  protected prisma: PrismaService;

  get collection() {
    return this.prisma.user;
  }

  get groupBy() {
    return this.collection.groupBy.bind(this.collection);
  }

  protected deserialize<T>(obj: null | User | User[]): T {
    return deserialize<T>(obj, {type: UserModel, collectionType: isArray(obj) ? Array : undefined});
  }

  async findUnique(args: Prisma.UserFindUniqueArgs): Promise<UserModel | null> {
    const obj = await this.collection.findUnique(args);
    return this.deserialize<UserModel | null>(obj);
  }

  async findFirst(args: Prisma.UserFindFirstArgs): Promise<UserModel | null> {
    const obj = await this.collection.findFirst(args);
    return this.deserialize<UserModel | null>(obj);
  }

  async findMany(args?: Prisma.UserFindManyArgs): Promise<UserModel[]> {
    const obj = await this.collection.findMany(args);
    return this.deserialize<UserModel[]>(obj);
  }

  async create(args: Prisma.UserCreateArgs): Promise<UserModel> {
    const obj = await this.collection.create(args);
    return this.deserialize<UserModel>(obj);
  }

  async update(args: Prisma.UserUpdateArgs): Promise<UserModel> {
    const obj = await this.collection.update(args);
    return this.deserialize<UserModel>(obj);
  }

  async upsert(args: Prisma.UserUpsertArgs): Promise<UserModel> {
    const obj = await this.collection.upsert(args);
    return this.deserialize<UserModel>(obj);
  }

  async delete(args: Prisma.UserDeleteArgs): Promise<UserModel> {
    const obj = await this.collection.delete(args);
    return this.deserialize<UserModel>(obj);
  }

  async deleteMany(args: Prisma.UserDeleteManyArgs) {
    return this.collection.deleteMany(args);
  }

  async updateMany(args: Prisma.UserUpdateManyArgs) {
    return this.collection.updateMany(args);
  }

  async aggregate(args: Prisma.UserAggregateArgs) {
    return this.collection.aggregate(args);
  }
}

It will be the same for all the models that you declare in the schema.prisma!

Ts.ED decorators usage in Prisma

The generator parse prisma command to find extra Ts.ED decorators. You can use any @tsed/schema decorators from Ts.ED by adding a comment with the following format /// @TsED.Decorator. See example above:

model User {
  /// @TsED.Groups("!creation")
  /// Comment
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now())
  /// @TsED.Email()
  /// @TsED.Description("User email. This email must be unique!")
  email       String   @unique
}

Output:

export class UserModel implements User {
  @Property(Number)
  @Integer()
  @Required()
  @Groups("!creation")
  id: number;

  @Property(String)
  @Required()
  @Email()
  @Description("User email. This email must be unique!")
  email: string;
}

Create controllers

Finally, you'll use the repository you generated 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!

Inject PrismaService

@tsed/prisma package generate also the PrismaService. You can inject this service as following:

import {Injectable, Inject} from "@tsed/di";
import {PrismaService} from "@tsed/prisma";

@Injectable()
export class MyService {
  @Inject()
  protected prisma: PrismaService;
}

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 our Repositories generated with the Prisma Client and uses the generated PrismaService to interact with the database.