Full-Stack React & Node.js - Database!

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:

datasource db {
  provider = "sqlite"
  url      = "file:./data.db?connection_limit=1"
}

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

model Note {
  id        String @id @default(cuid())
  title     String
  content   String
  authorId  String
  lang      String
  isLive    Boolean
  category  String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  author    Author  @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)
}

model Author {
  id        String @id @default(cuid())
  username  String @unique

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  notes    Note[]
}

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:

const { PrismaClient } = require("@prisma/client")

let prisma;

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient()
} else {
  const {__db__} = global;

  if (__db__) {
    prisma = __db__
  } else {
    prisma = new PrismaClient({
      log: [
        {
          emit: "event",
          level: "query",
        },
        "info",
        "warn",
        "error",
      ],
    });

    prisma.$on("query", ({query, duration}) => {
      console.log(`\x1b[36mprisma:query\x1b[0m ${query}`);
      console.log(`Took: ${duration}ms`)
    });

    global.__db__ = prisma
  }

  prisma.$connect();
}

module.exports = {
  prisma
}

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:

const { prisma } = require("./db")

async function getAuthor(id) {
  return prisma.author.findUnique({ where: { id } });
}

async function getAuthorByName(username) {
  return prisma.author.findUnique({ where: { username } });
}

async function createAuthor(
  author
) {
  return prisma.author.create({
    data: author
  });
}

module.exports = {
  getAuthor,
  getAuthorByName,
  createAuthor,
}

Edit note.model.js to:

const { prisma } = require("./db")

async function getNotes() {
  return prisma.note.findMany();
}

async function getNote(id) {
  return prisma.note.findUnique({ where: { id } });
}

async function createNote(
  note
) {
  return prisma.note.create({
    data: note
  });
}

async function updateNote(
  note
) {
  return prisma.note.update({
    data: note,
  });
}

module.exports = {
  getNotes,
  getNote,
  createNote,
  updateNote,
}

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:

const { PrismaClient } = require("@prisma/client")
const prisma = new PrismaClient();

async function seed() {
  // Blitz everything!
  await prisma.note.deleteMany();
  await prisma.author.deleteMany();

  const author = await prisma.author.create({
    data: {
      username: 'neohed'
    },
  });

  await prisma.note.create({
    data: {
      title: 'A New Note',
      content: 'This note is retrieved from the database!',
      authorId: author.id,
      lang: 'en',
      isLive: true,
      category: '',
    },
  });

  console.log(`Database has been seeded. 🌱`)
}

seed()
  .then(() => {
    console.log('Prisma seed function in prisma/seed.js executed!')
  })
  .catch((e) => {
    console.error(e);
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

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

{
  "name": "server",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "node index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "@prisma/client": "^4.0.0",
    "body-parser": "^1.20.0",
    "cors": "^2.8.5",
    "express": "^4.18.1",
    "morgan": "^1.10.0"
  },
  "devDependencies": {
    "prisma": "^4.0.0"
  },
  "prisma": {
    "seed": "node prisma/seed.js"
  }
}

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:

const authorRepo = require('../models/author.model');
const noteRepo = require('../models/note.model');

async function getNote(req, res) {
  const notes = await noteRepo.getNotes();
  //HACK return top 1 note
  const { authorId, ...noteRest } = notes[0];
  const { username } = await authorRepo.getAuthor(authorId);

  res.json({ note: {
      ...noteRest,
      author: username
    }
  });
}

async function postNote(req, res) {
  const {body} = req;
  const {id, title, content, author, lang, isLive, category} = body;

  console.log('Server received data:');
  console.log({id, title, content, author, lang, isLive, category})

  res
    .status(200)
    .json({
      message: 'Ok'
    })
}

module.exports = {
  getNote,
  postNote
}

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 we will finish up the form and add a few missing features...

Code repo: Github Repository