DEV Community

Cover image for Full-Stack React & Node.js - Database!
Dave
Dave

Posted on

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
Enter fullscreen mode Exit fullscreen mode

Next install Prisma, also on the server:

npm i -D prisma
Enter fullscreen mode Exit fullscreen mode

-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[]
}
Enter fullscreen mode Exit fullscreen mode

We have two tables here:

  • Note
  • Author

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

npx prisma db push
Enter fullscreen mode Exit fullscreen mode

Now to generate the DB entities run:

npx prisma generate
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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,
}
Enter fullscreen mode Exit fullscreen mode

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,
}
Enter fullscreen mode Exit fullscreen mode

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()
  })
Enter fullscreen mode Exit fullscreen mode

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"
  }
}
Enter fullscreen mode Exit fullscreen mode

Now run the seed script, execute this:

npx prisma db seed
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)