simply learn-full-stack-7

November 21, 2022

Simply Learn Full-Stack React & Node.js

Let's jump right in!

All the edits we need to make are on the server. We're gonna use Prisma ORM and SqlLite DB for convenience. We need to install these in node-server

Install the Prisma client which express will use to connect to our database:

npm i -S @prisma/client

Next install Prisma, also on the server:

npm i -D prisma

-D is shorthand for --save-dev. This saves the package under devDependencies

Under node-server create a new folder prisma

In folder prisma, create a new file schema.prisma. Set the contents to:

1datasource db {
2  provider = "sqlite"
3  url      = "file:./data.db?connection_limit=1"
4}
5
6generator client {
7  provider = "prisma-client-js"
8}
9
10model Note {
11  id        String @id @default(cuid())
12  title     String
13  content   String
14  authorId  String
15  lang      String
16  isLive    Boolean
17  category  String
18
19  createdAt DateTime @default(now())
20  updatedAt DateTime @updatedAt
21
22  author    Author  @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)
23}
24
25model Author {
26  id        String @id @default(cuid())
27  username  String @unique
28
29  createdAt DateTime @default(now())
30  updatedAt DateTime @updatedAt
31
32  notes    Note[]
33}

We have two tables here:

Note
Author

To generate the SqlLite database file run this command from node-server folder:

npx prisma db push

Now to generate the DB entities run:

npx prisma generate

In node-server create a new folder: models. Inside node-server/models create 3 new files:

db.js
author.model.js
note.model.js

Edit db.js to:

1const { PrismaClient } = require("@prisma/client")
2
3let prisma;
4
5if (process.env.NODE_ENV === "production") {
6  prisma = new PrismaClient()
7} else {
8  const {__db__} = global;
9
10  if (__db__) {
11    prisma = __db__
12  } else {
13    prisma = new PrismaClient({
14      log: [
15        {
16          emit: "event",
17          level: "query",
18        },
19        "info",
20        "warn",
21        "error",
22      ],
23    });
24
25    prisma.$on("query", ({query, duration}) => {
26      console.log(`\x1b[36mprisma:query\x1b[0m ${query}`);
27      console.log(`Took: ${duration}ms`)
28    });
29
30    global.__db__ = prisma
31  }
32
33  prisma.$connect();
34}
35
36module.exports = {
37  prisma
38}

In development environments, this creates a single prisma instance and stores it as a global and logs SQL queries to the console.

Edit author.model.js to:

1const { prisma } = require("./db")
2
3async function getAuthor(id) {
4  return prisma.author.findUnique({ where: { id } });
5}
6
7async function getAuthorByName(username) {
8  return prisma.author.findUnique({ where: { username } });
9}
10
11async function createAuthor(
12  author
13) {
14  return prisma.author.create({
15    data: author
16  });
17}
18
19module.exports = {
20  getAuthor,
21  getAuthorByName,
22  createAuthor,
23}

Edit note.model.js to:

1const { prisma } = require("./db")
2
3async function getNotes() {
4  return prisma.note.findMany();
5}
6
7async function getNote(id) {
8  return prisma.note.findUnique({ where: { id } });
9}
10
11async function createNote(
12  note
13) {
14  return prisma.note.create({
15    data: note
16  });
17}
18
19async function updateNote(
20  note
21) {
22  return prisma.note.update({
23    data: note,
24  });
25}
26
27module.exports = {
28  getNotes,
29  getNote,
30  createNote,
31  updateNote,
32}

That finishes our data access layer. These ORM functions can now be used in our controllers to access data.

First we need to create a script to seed our database. In the prisma folder, create a new file seed.js:

1const { PrismaClient } = require("@prisma/client")
2const prisma = new PrismaClient();
3
4async function seed() {
5  // Blitz everything!
6  await prisma.note.deleteMany();
7  await prisma.author.deleteMany();
8
9  const author = await prisma.author.create({
10    data: {
11      username: 'neohed'
12    },
13  });
14
15  await prisma.note.create({
16    data: {
17      title: 'A New Note',
18      content: 'This note is retrieved from the database!',
19      authorId: author.id,
20      lang: 'en',
21      isLive: true,
22      category: '',
23    },
24  });
25
26  console.log(`Database has been seeded. 🌱`)
27}
28
29seed()
30  .then(() => {
31    console.log('Prisma seed function in prisma/seed.js executed!')
32  })
33  .catch((e) => {
34    console.error(e);
35    process.exit(1)
36  })
37  .finally(async () => {
38    await prisma.$disconnect()
39  })

Now we need to reference this script from package.json. Edit package.json to this:

1{
2  "name": "server",
3  "version": "1.0.0",
4  "description": "",
5  "main": "index.js",
6  "scripts": {
7    "start": "node index.js",
8    "test": "echo \"Error: no test specified\" && exit 1"
9  },
10  "keywords": [],
11  "author": "",
12  "license": "ISC",
13  "dependencies": {
14    "@prisma/client": "^4.0.0",
15    "body-parser": "^1.20.0",
16    "cors": "^2.8.5",
17    "express": "^4.18.1",
18    "morgan": "^1.10.0"
19  },
20  "devDependencies": {
21    "prisma": "^4.0.0"
22  },
23  "prisma": {
24    "seed": "node prisma/seed.js"
25  }
26}

Now run the seed script, execute this:

npx prisma db seed

This will run the seed.js script and populate the database with one author and one note record.

And finally, edit note.controller.js to:

1const authorRepo = require('../models/author.model');
2const noteRepo = require('../models/note.model');
3
4async function getNote(req, res) {
5  const notes = await noteRepo.getNotes();
6  //HACK return top 1 note
7  const { authorId, ...noteRest } = notes[0];
8  const { username } = await authorRepo.getAuthor(authorId);
9
10  res.json({ note: {
11      ...noteRest,
12      author: username
13    }
14  });
15}
16
17async function postNote(req, res) {
18  const {body} = req;
19  const {id, title, content, author, lang, isLive, category} = body;
20
21  console.log('Server received data:');
22  console.log({id, title, content, author, lang, isLive, category})
23
24  res
25    .status(200)
26    .json({
27      message: 'Ok'
28    })
29}
30
31module.exports = {
32  getNote,
33  postNote
34}

If you run your server and client now, you should see different data, loaded from the SqlLite database! You will also see the SQL queries logged in your server console.

Next CRUD operations, ...

Code repo: Github Repository