This activity guides you through building a simple CRUD API using Prisma and Express. It requires a basic understanding of relational database schemas, how to translate them into the equivalent Prisma schemas, and how to perform CRUD operations with Prisma Client.
The solution branch contains documented solution code. The commit history of that branch follows the instructions below.
- Define Prisma schema according to the provided database schema.
- Write a seed script to initialize the database with Prisma Migrate.
- Write Express routes that perform CRUD operations via Prisma Client.
Expand to see DBML
Table Author {
id Serial [pk]
name String
}
Table Book {
id Serial [pk]
title String
authorId Int
}
Ref: "Book"."authorId" > "Author"."id"
- Fork and clone this repo. Work in your local repository!
- Install the Prisma CLI.
npm install prisma --save-dev
- Initialize Prisma to use sqlite.
npx prisma init --datasource-provider sqlite
- In the generated
.env
file, setDATABASE_URL
to"file:books.db"
. - Add models to your
schema.prisma
file according to the database schema above. - Create and run the initial migration.
npx prisma migrate dev --name init
- Explore the created database. You should see two empty models:
Author
andBook
.
npx prisma studio
- If you made a mistake in your
schema.prisma
, instead of running another migration, you can instead usedb push
to sync your database with the schema. This is useful while prototyping.
npx prisma db push
- Install Prisma Client, which we will use to interact with the database.
npm install @prisma/client
- Create and export a new
PrismaClient
inprisma/index.js
.const { PrismaClient } = require('@prisma/client'); const prisma = new PrismaClient(); module.exports = prisma;
- In
prisma/seed.js
, seed 20 authors into the database. Each author should have 3 corresponding books. Refer to the docs on how to create related records.const prisma = require('../prisma'); const seed = async () => { // TODO: Create 20 authors with 3 books each }; seed() .then(async () => await prisma.$disconnect()) .catch(async (e) => { console.error(e); await prisma.$disconnect(); process.exit(1); });
- Update
package.json
to configure Prisma's integrated seeding functionality."prisma": { "seed": "node prisma/seed.js" }
- Use Prisma Migrate to completely reset and seed the database.
npx prisma migrate reset
- Note: this is designed to be used in development only! Another option is
npx prisma db seed
, but that will not clear existing data.reset
is simpler to use (for now).
- Note: this is designed to be used in development only! Another option is
- Confirm that the database is correctly seeded with authors and books.
npx prisma studio
- Install Express and create a server with two main routers:
/authors
and/books
. - Create the following
/authors
routes. These routes should use the Prisma Client CRUD operations to read and write from the database.GET /authors
- returns an array of all authorsPOST /authors
- creates a new author with the information provided in the request bodyGET /authors/:id
- returns a single author with the specified idPUT /authors/:id
- overwrites the author with the information provided in the request bodyDELETE /authors/:id
- deletes the author with the specified id
- Add the following
/authors
routes; these routes handle the relationship between authors and books.GET /authors/:id/books
- get all books written by the specified authorPOST /authors/:id/books
- creates a new book as provided in the request body with the specified author
- Create the following
/books
routes.GET /books
- returns an array of all booksGET /books/:id
- returns a single book with the specified idPUT /books/:id
- overwrites the book with the information provided in the request bodyDELETE /books/:id
- deletes the book with the specified id
You now have a fully working CRUD API!