Full-Stack React & Node.js - Database!
Tags: react, javascript, node, database
Published: 2022-06-29 18:30:25

---
series: 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:
```shell
npm i -S @prisma/client
```

Next install Prisma, also on the server:
```shell
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:

```text
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:

```shell
npx prisma db push
```

Now to generate the DB entities run:

```shell
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:

```javascript
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:

```javascript
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:

```javascript
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**:

```javascript
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:

```json
{
  "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:

```shell
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:

```javascript
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](https://github.com/neohed/node-react-stack)