Prisma

Introduction

• It is an ORM that supports multiple database driver and contains data migration tool for migrating the data model to database via sql file to make sure the data consistency between data model and db schema

• Its pattern is to grouping different data model together as a single file (schema first) , rather than separate it one by one (code first)

Commands

prisma db seed

• To execute the custom logic of seed file, the seed file will also be executed after prisma migrate dev or prisma migrate deployis successful

prisma migrate reset

• For development only, to delete all the data and table and rerun all the migration sql file again

prisma migrate dev

• For development only

• Reruns the existing migration history in the shadow database (second, temporary database that is created and deleted automatically)

• After re-run, it will compare end state of history of shadow database and current state of database

• It will use the checksum column of prisma migration table behind the scene

• If detected there are diffs between state, it means that schema drift (edited or deleted migration file, or a manual changes to the database schema) are detected, you will be asked to reset your data

• Applies pending migrations to the shadow database (for example, new migrations created by colleagues)

• If it detects changes to the prisma schema, it generates a new migration from these changes

prisma migrate deploy

• For production only

• To apply the new migration sql file only, if it is failed , it will block the execution of other new sql file

prisma migrate diff \
 --from-empty \
 --to-schema-datamodel ./prisma/schema.prisma \
 --script > ./prisma/migrations/000000000000_squashed_migrations/migration.sql

• It will compare the differences of from and to and generate the changes into one sql file

prisma migrate resolve \
 --applied 000000000000_squashed_migrations

• Marked the sql file history status as resolved, so that the sql file will be new and executed by deployment command

prisma migrate resolve --rolled-back test

• Marked the sql file history status as rolled back

• It is needed to fix back the sql file manually

• After rerun deployment command, the failed file will be re-run again

npx prisma db execute --file ./down.sql --schema prisma/schema.prisma

• Directly run the sql file to the database without creating any prisma history

Extension

• By using extension, you can declare the custom method to the model and apply global logic to each query, ... (AOP)

• Doc: https://www.prisma.io/docs/orm/prisma-client/client-extensions

import { Logger } from '@nestjs/common';
import { Prisma, PrismaClient } from '@prisma/client';

export class PrismaExtendClient extends PrismaClient<Prisma.PrismaClientOptions,
 'query'> {
  private logger = new Logger(PrismaExtendClient.name);
  constructor(options?: ConstructorParameters<typeof PrismaClient>[0]) {
    super(options);
    // adding logging to each query
    this.$on('query', (e) => {
      this.logger.debug(`executed ${e.query} ${e.params}`);
    });
    return this.$extends({
      query: {
        group: {
          // override and extend the behaviours 
          async $allOperations({ model, operation, args, query }) {
            if (
              operation === 'count' ||
              operation === 'findMany' ||
              operation === 'findFirst' ||
              operation === 'updateMany'
            ) {
              args.where = { isDeprecated: false, ...args.where };
            }
            return query(args);
          },
        },
        modelFile: {
          async $allOperations({ model, operation, args, query }) {
            if (
              operation === 'findMany' ||
              operation === 'findFirst' ||
              operation === 'updateMany' ||
              operation === 'findUnique'
            ) {
              args.where = { ...args.where, deletedAt: null };
            }
            return query(args);
          },
        },
      },
    }) as this;
  }
}