Chapter 11 - Persist the slideshows with Prisma¶

In this chapter, you will persist (= save) the slideshows you create in a SQLite with the help of Prisma.

Steps¶

Install, initialize and configure Prisma¶

Install Prisma in your project.

In a terminal, execute the following command(s).
npm install --save prisma @prisma/client

Switch to the src directory.

In a terminal, execute the following command(s).
1	`cd src`

Initialize Prisma with SQLite provider in the src directory.

In a terminal, execute the following command(s).
npx prisma init --datasource-provider sqlite

A new prisma directory has been created in the src directory. It contains the (empty) schema that will be used to generate the database and the migrations.

Switch back to the parent directory.

In a terminal, execute the following command(s).
1	`cd ..`

Delete unnecessary files. You'll come back to these files and their meaning in future chapters.

In a terminal, execute the following command(s).
1 2 3	`rm .env rm .gitignore`

Update the src/prisma/schema.prisma file to set the SQLite database file in your working directory.

src/prisma/schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite" // (1)!
  url      = "file:../../mediaplayer.db" // (2)!
}

SQLite will be used for the database.
The database will be saved in your working directory.

Install Prisma extension in the Dev Container¶

Find the Handlebars Prisma.prisma extension in Visual Studio Code extensions.

Install the extension. Once installed, click on the little Manage gear () and select the Add to devcontainer.json. Your devcontainer.json should looks like that.

.devcontainer/devcontainer.json
// See https://containers.dev/implementors/json_reference/ for configuration reference
{
	"name": "Create a media player application project",
	"build": {
		"dockerfile": "Dockerfile"
	},
	"remoteUser": "node",
	"postCreateCommand": "./.devcontainer/npm-global-without-sudo.sh",
	"customizations": {
		"vscode": {
			"extensions": ["andrejunges.Handlebars", "Prisma.prisma"]
		}
	}
}

Create the Slide model to the Prisma schema¶

Update the src/prisma/schema.prisma Prisma schema to add the Slide model from the Slide type you defined in TypeScript. The new property id is the assignment of an ID to a slide.

src/prisma/schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = "file:../../mediaplayer.db"
}

// Enums are not supported in SQLite.
// https://www.prisma.io/docs/reference/database-reference/database-features#misc
//
// An issue is opened to add support for this.
// https://github.com/prisma/prisma/issues/2219
// enum MediaType {
//   IMAGE
//   VIDEO
// }

model Slide {
  id       String @id @default(uuid()) // (1)!
  src      String
  type     String // Enums are not support in SQLite
  alt      String
  interval Int? // (2)!

  @@map(name: "slides") // (3)!
}

The ID of each slide is an UUID.
The interval is optional.
Name the table slides (instead of Slide) to follow SQL conventions.

Create the first database migration to create the Slide table¶

Create the first database migration.

A database migration is an SQL script that changes the database to be up-to-date with the new requirements.

In a terminal, execute the following command(s).
npx prisma migrate dev --name mediaplayer --schema src/prisma/schema.prisma

A new src/prisma/migrations directory has been created with a new migration.

Open the file migration.sql. It should be like this.

-- CreateTable
CREATE TABLE "slides" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "src" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "alt" TEXT NOT NULL,
    "interval" INTEGER
);

It creates the slide table in the database, thanks to Prisma.

Add the Slideshow model to the Prisma schema¶

Update the src/prisma/schema.prisma Prisma schema to add the Slideshow model from the Slideshow type you defined in TypeScript.

src/prisma/schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = "file:../../mediaplayer.db"
}

// Enums are not supported in SQLite.
// https://www.prisma.io/docs/reference/database-reference/database-features#misc
//
// An issue is opened to add support for this.
// https://github.com/prisma/prisma/issues/2219
// enum MediaType {
//   IMAGE
//   VIDEO
// }

model Slide {
  id          String @id @default(uuid())
  slideshowId String @map(name: "slideshow_id") // (1)!
  src         String
  type        String // Enums are not support in SQLite
  alt         String
  interval    Int?

  slideshow Slideshow @relation(fields: [slideshowId], references: [id]) // (2)!

  @@map(name: "slides")
}

model Slideshow {
  id     String  @id @default(uuid())
  slides Slide[] // (3)!

  @@map(name: "slideshows")
}

Add the foreign key and name it slideshow_id.
Add the relation to the Slideshow model.
The slides are an array of Slide models.

Create the second database migration to create the Slideshow table¶

Create the second database migration.

In a terminal, execute the following command(s).
npx prisma migrate dev --name mediaplayer --schema src/prisma/schema.prisma

A second migration has been created in the src/prisma/migrations.

Open the file migration.sql. It should be like this.

/*
  Warnings:

  - Added the required column `slideshow_id` to the `slides` table without a default value. This is not possible if the table is not empty.

*/
-- CreateTable
CREATE TABLE "slideshows" (
    "id" TEXT NOT NULL PRIMARY KEY
);

-- RedefineTables
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_slides" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "slideshow_id" TEXT NOT NULL,
    "src" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "alt" TEXT NOT NULL,
    "interval" INTEGER,
    CONSTRAINT "slides_slideshow_id_fkey" FOREIGN KEY ("slideshow_id") REFERENCES "slideshows" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);
INSERT INTO "new_slides" ("alt", "id", "interval", "src", "type") SELECT "alt", "id", "interval", "src", "type" FROM "slides";
DROP TABLE "slides";
ALTER TABLE "new_slides" RENAME TO "slides";
PRAGMA foreign_key_check;
PRAGMA foreign_keys=ON;

It creates the tables slideshows and update the old slides tables with the new fields needed to create the relationship between the tables slideshows and slides.

Integrate Prisma with NestJS¶

Create the Prisma service¶

Create the PrismaService file with the following content.

src/prisma/prisma.service.ts
import { INestApplication, Injectable, OnModuleInit } from "@nestjs/common";
import { PrismaClient } from "@prisma/client";

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
	async onModuleInit() {
		// (1)!
		await this.$connect();
	}

	async enableShutdownHooks(app: INestApplication) {
		// (2)!
		process.on("beforeExit", async () => {
			await app.close();
		});
	}
}

NestJS will connect to the database on initialization.
NestJS will be closed once the connection to the database is closed as well.

Create the Prisma module¶

Create the PrismaModule file with the following content.

src/prisma/prisma.module.ts
import { Module } from "@nestjs/common";
import { PrismaService } from "./prisma.service";

@Module({
	providers: [PrismaService],
	exports: [PrismaService],
})
export class PrismaModule {}

Update the App module to include the Prisma module¶

Update the AppModule to make the Prisma module available for the entire application.

src/app.module.ts
import { Module } from "@nestjs/common";
import { AppController } from "./app.controller";
import { AppService } from "./app.service";
import { PrismaModule } from "./prisma/prisma.module";

@Module({
	imports: [PrismaModule],
	controllers: [AppController],
	providers: [AppService],
})
export class AppModule {}

Update the main file¶

Update the main file to get the Prisma service and add the shutdown hook. This will allow to close the database and the application gracefully.

src/main.ts
import { NestFactory } from "@nestjs/core";
import { NestExpressApplication } from "@nestjs/platform-express";
import { join } from "path";
import { AppModule } from "./app.module";
import { handlebars } from "hbs";
import { PrismaService } from "./prisma/prisma.service";

async function bootstrap() {
	const app = await NestFactory.create<NestExpressApplication>(AppModule);

	app.setBaseViewsDir(join(__dirname, "..", "views"));
	app.useStaticAssets(join(__dirname, "..", "public"));
	app.setViewEngine("hbs");

	handlebars.registerHelper("eq", (v1, v2) => v1 === v2);

	const prismaService = app.get(PrismaService);
	await prismaService.enableShutdownHooks(app);

	await app.listen(3000);
}

bootstrap();

Include Prisma files in NestJS output¶

Update NestJS configuration so it includes Prisma files when running or building the application.

nest-cli.json
{
	"$schema": "https://json.schemastore.org/nest-cli",
	"collection": "@nestjs/schematics",
	"sourceRoot": "src",
	"compilerOptions": {
		"deleteOutDir": true,
		"assets": ["**/*.prisma", "**/*.sql", "**/*.toml"],
		"watchAssets": true
	}
}

Use Prisma in the service¶

Now that Prisma is available in the application, you can inject it anywhere using dependency injection as follow.

The Map made earlier is now removed as everything is saved in the database. The unnecessary imports are deleted and Prisma is used to access the database.

Warning

At this time of writing, the Prisma Extension seems to have problems to reload the TS server within the Dev Container as stated in this GitHub issue. You can manually reload the TS server with View > Command Palette... and execute TypeScript: Restart TS server to update the Prisma types if you have issues to see the right types.

src/app.service.ts
import { Injectable } from "@nestjs/common";
import { PrismaService } from "./prisma/prisma.service"; // (1)!
import { CreateSlideshow } from "./types/create-slideshow.type";
import { UpdateSlideshow } from "./types/update-slideshow.type";

@Injectable()
export class AppService {
	constructor(private readonly prisma: PrismaService) {} // (2)!

	async /* (3)! */ getSlideshows() {
		return await this.prisma.slideshow.findMany({
			// (4)!
			include: {
				// (5)!
				slides: {
					select: {
						alt: true,
						interval: true,
						src: true,
						type: true,
					},
				},
			},
		});
	}

	async getSlideshow(slideshowId: string) {
		const slideshow = await this.prisma.slideshow.findFirst({
			where: {
				// (6)!
				id: {
					equals: slideshowId,
				},
			},
			include: {
				slides: {
					select: {
						alt: true,
						interval: true,
						src: true,
						type: true,
					},
				},
			},
		});

		return slideshow;
	}

	async createSlideshow(createSlideshow: CreateSlideshow) {
		const newSlideshow = await this.prisma.slideshow.create({
			data: {
				// (7)!
				slides: {
					create: createSlideshow.slides,
				},
			},
			include: {
				slides: {
					select: {
						alt: true,
						interval: true,
						src: true,
						type: true,
					},
				},
			},
		});

		return newSlideshow;
	}

	async updateSlideshow(slideshowId: string, updateSlideshow: UpdateSlideshow) {
		const updatedSlideshow = await this.prisma.slideshow.update({
			where: {
				id: slideshowId,
			},
			data: {
				slides: {
					deleteMany: {}, // (8)!
					create: updateSlideshow.slides,
				},
			},
			include: {
				slides: {
					select: {
						alt: true,
						interval: true,
						src: true,
						type: true,
					},
				},
			},
		});

		return updatedSlideshow;
	}

	async deleteSlideshow(slideshowId: string) {
		await this.prisma.slideshow.delete({
			// (9)!
			where: {
				id: slideshowId,
			},
		});
	}
}

Import the PrismaService.
Inject the PrismaService. Thanks to NestJS and the definition in the AppModule, the service is now available in your AppService. You can use the service with the this.prisma keyword.
As Prisma is asynchrone, the async keyword tells that the function is asynchrone as well.
The PrismaService is used to get the slideshows from the database.
We ask Prisma to include the slides from the slideshow as well. Without this, Prisma would only return the slideshows with their IDs but without the slides.
Define the WHERE condition. In this case, we ask Prisma to get the slideshow with the ID asked by the user.
Define the data of the slideshow to create. We omit the ID as Prisma will generate it for use and we set the slides from the user input.
Delete all the slides of the previous slideshow before creating a new slideshow with its new slides.
The suppression of the slideshow in the database throws an error instead of the boolean previously used.

Update the controller¶

src/app.controller.ts
import {
	Get,
	Controller,
	Render,
	Param,
	Res,
	Post,
	Body,
	Patch,
	Delete,
	NotFoundException,
	HttpCode,
	Redirect,
} from "@nestjs/common";
import { Response } from "express";
import { AppService } from "./app.service";
import { CreateSlideshow } from "./types/create-slideshow.type";
import { UpdateSlideshow } from "./types/update-slideshow.type";

@Controller()
export class AppController {
	constructor(private readonly appService: AppService) {}

	@Get()
	@Redirect("/slideshows")
	root() {}

	@Get("/slideshows")
	@Render("index")
	async getSlideshows() {
		const slideshows = await this.appService.getSlideshows();
		// (1)!
		return { slideshows: slideshows };
	}

	@Get("/slideshows/:id")
	async getSlideshow(@Res() res: Response, @Param("id") id: string) {
		const slideshow = await this.appService.getSlideshow(id);

		if (!slideshow) {
			return res.redirect("/slideshows");
		}

		return res.render("slideshow", { slideshow: slideshow });
	}

	@Get("/api/slideshows")
	async getSlideshowsApi() {
		const slideshows = await this.appService.getSlideshows();
		// (2)!
		return slideshows;
	}

	@Get("/api/slideshows/:id")
	async getSlideshowApi(@Param("id") id: string) {
		const slideshow = await this.appService.getSlideshow(id);

		if (!slideshow) {
			throw new NotFoundException();
		}

		return slideshow;
	}

	@Post("/api/slideshows")
	async createSlideshowApi(@Body() createSlideshow: CreateSlideshow) {
		const newSlideshow = await this.appService.createSlideshow(createSlideshow);

		return newSlideshow;
	}

	@Patch("/api/slideshows/:id")
	async updateSlideshowApi(
		@Param("id") id: string,
		@Body() updateSlideshow: UpdateSlideshow
	) {
		try {
			const updatedSlideshow = await this.appService.updateSlideshow(
				id,
				updateSlideshow
			);

			return updatedSlideshow;
		} catch (error) {
			throw new NotFoundException();
		}
	}

	@Delete("/api/slideshows/:id")
	@HttpCode(204)
	async deleteSlideshowApi(@Param("id") id: string) {
		try {
			// (3)!
			await this.appService.deleteSlideshow(id);
		} catch (error) {
			throw new NotFoundException();
		}
	}
}

The code to convert the Map to an array is removed as Prisma returns an array from the database.
The code to convert the Map to a standard JavaScript object is removed as Prisma returns an object from the database.
The try catch block replaces the boolean previously used.

Try out the API¶

Ensure the Prisma client is up-to-date with the following command.

In a terminal, execute the following command(s).
# Run all the migrations
npx prisma migrate dev --name mediaplayer --schema src/prisma/schema.prisma

# Generate the client
npx prisma generate --schema src/prisma/schema.prisma

Start the application.

Create a slideshow.

Once done, stop your NestJS application with Ctrl+C in your terminal.

Restart your NestJS application.

Try to access the slideshows.

You should now see the slideshows are still there! They have been successfully persisted!

Save the Prisma commands in the Node project¶

Migrations are really useful to keep up the databases updates during the development process. As migrations need to be created quite often, let's save the required commands in the package.json as new scripts.

You'll then be able to run the migration command with npm run prisma:migrate:dev and the generate command with npm run prisma:generate:dev!

package.json
{
	"name": "create-a-media-player-application",
	"version": "0.0.1",
	"description": "",
	"author": "",
	"private": true,
	"license": "UNLICENSED",
	"scripts": {
		"build": "nest build",
		"format": "prettier --write \"src/**/*.ts\" \"test/**/*.ts\"",
		"start": "nest start",
		"start:dev": "npm run prisma:migrate:dev && nest start --watch", // (1)!
		"start:debug": "npm run prisma:migrate:dev && nest start --debug --watch",
		"start:prod": "node dist/main",
		"lint": "eslint \"{src,apps,libs,test}/**/*.ts\" --fix",
		"test": "jest",
		"test:watch": "jest --watch",
		"test:cov": "jest --coverage",
		"test:debug": "node --inspect-brk -r tsconfig-paths/register -r ts-node/register node_modules/.bin/jest --runInBand",
		"test:e2e": "jest --config ./test/jest-e2e.json",
		"prisma:migrate:dev": "prisma migrate dev --name mediaplayer --schema src/prisma/schema.prisma",
		"prisma:generate:dev": "prisma generate --schema src/prisma/schema.prisma"
	},
	"dependencies": {
		"@nestjs/common": "^9.0.0",
		"@nestjs/core": "^9.0.0",
		"@nestjs/platform-express": "^9.0.0",
		"@prisma/client": "^4.9.0",
		"hbs": "^4.2.0",
		"reflect-metadata": "^0.1.13",
		"rxjs": "^7.2.0",
		"uuid": "^9.0.0"
	},
	"devDependencies": {
		"@nestjs/cli": "^9.0.0",
		"@nestjs/schematics": "^9.0.0",
		"@nestjs/testing": "^9.0.0",
		"@types/express": "^4.17.13",
		"@types/hbs": "^4.0.1",
		"@types/jest": "29.2.4",
		"@types/node": "18.11.18",
		"@types/supertest": "^2.0.11",
		"@types/uuid": "^9.0.0",
		"@typescript-eslint/eslint-plugin": "^5.0.0",
		"@typescript-eslint/parser": "^5.0.0",
		"eslint": "^8.0.1",
		"eslint-config-prettier": "^8.3.0",
		"eslint-plugin-prettier": "^4.0.0",
		"jest": "29.3.1",
		"prettier": "^2.3.2",
		"prisma": "^4.9.0",
		"source-map-support": "^0.5.20",
		"supertest": "^6.1.3",
		"ts-jest": "29.0.3",
		"ts-loader": "^9.2.3",
		"ts-node": "^10.0.0",
		"tsconfig-paths": "4.1.1",
		"typescript": "^4.7.4"
	},
	"jest": {
		"moduleFileExtensions": ["js", "json", "ts"],
		"rootDir": "src",
		"testRegex": ".*\\.spec\\.ts$",
		"transform": {
			"^.+\\.(t|j)s$": "ts-jest"
		},
		"collectCoverageFrom": ["**/*.(t|j)s"],
		"coverageDirectory": "../coverage",
		"testEnvironment": "node"
	}
}

Ensure all migrations are up-to-date in development before starting the application.

Summary¶

Congrats! Your application now persists all the slideshows in the database.

Prisma speeds up the development of the database thanks to its schema and the migrations.

The Prisma client generated from the schema ensures the types of your inputs are correct thanks to TypeScript.

When restarting the application, there is no more data loss.

SQLite helps to develop simple applications as only a file is required to store the database. For more robust applications, another kind of database is recommended, such as PostgreSQL. However, for development, SQLite is perfectly fine.