Lecture 20 - Full-Stack Web Application (Part 2)

Content

What is Database?
SQL vs. NoSQL
MongoDB Atlas
Prisma ORM
File Uploading

See the slide click here

This slide contains information about Database, SQL vs. NoSQL, MongoDB Atlas service.

Repositories

Frontend

https://github.com/cpe207-2568/lecture20-2568-frontend-starter

Backend

https://github.com/cpe207-2568/lecture20-2568-backend-starter

Note that the scripts section in the package.json file contains the following scripts.

  ...
  "scripts": {
      "dev": "npx nodemon --exec tsx src/index.ts",
      "build": "npx tsc",
      "serve": "node dist/index.js",
      
      "prisma:generate": "prisma generate",
      "prisma:push": "prisma db push",
      "prisma:seed": "node prisma/seed.js"
  },
  ...

We will use those scripts to run the following commands later on.

// Creates PrismaClient object for database connection
pnpm run prisma:generate

// Syncing Prisma database schema with the target database (MongoDB)
pnpm run prisma:push

// Insert some seeded data
pnpm run prisma:seed

Prisma ORM

Install Prisma ORM

pnpm install @prisma/client
pnpm install -D prisma

Initialize Prisma with the following command. This command creates a prisma directory with a prisma/schema.prisma file.

npx prisma init

Now, creates database schema for your application in the prisma/schema.prisma file.

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL") // เช่น mongodb+srv://user:pass@cluster/dbname
}

// =======================
// Model: User
// =======================
model User {
  id       String  @id @default(auto()) @map("_id") @db.ObjectId
  userId   String  @unique
  username String  @unique
  password String

  tasks    Task[]  @relation("UserTasks")  // one-to-many

  @@map("users")
}

// =======================
// Model: Task
// =======================
model Task {
  id          String   @id @default(auto()) @map("_id") @db.ObjectId
  taskId      String   @unique
  title       String
  description String?
  dueDate     DateTime?
  doneAt      DateTime?
  isDone      Boolean   @default(false)
  filename    String?
  createAt    DateTime  @default(now())
  updateAt    DateTime  @updatedAt

  user        User      @relation("UserTasks", fields: [userId], references: [userId])
  userId      String

  @@map("tasks")
}

Create .env file and add DATABASE_URL variable and configures its value as the MongoDB Atlas connection string.

DATABASE_URL=mongodb+srv://<mongo_user>:<mongo_password>@<mongo_server>/<database_name>?retryWrites=true&w=majority&appName=Cluster0

Next step is to generate the Prisma Client object with the following command. This command reads your schema and generate a type-safe database client (PrismaClient) in the node_modules directory.

npx prisma generate

or 

pnpm run prisma:generate

Finally, we can sync the database schema (prisma/schema.prisma) with the database (MongoDB Atlas) with this command.

npx prisma db push

or

pnpm run prisma:push

To seed the database with some data, executes the prisma/seed.js.

node prisma/seed.js

or

pnpm run prisma:seed

Browse and refresh your database again, you should see the seeded data.

File Uploading

For this feature, we can use multer package. multer is a middleware for handling multipart/form-data. It is primarily used for uploading files

Install package dependencies:

pnpm install multer
pnpm install -D @types/multer

Create a directory in your project's root to store the uploaded files.

// for PowerShell
md uploads

// for macOS and Linux
mkdir uploads

Update the `Todo` routes.

Creates a multer configuration

Storage directory location
Filename pattern

...
import multer from 'multer';
import path from 'path';
import fs from 'fs';

// --- Multer Configuration for File Uploads ---
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, 'uploads/'); // Specify the directory to save files
  },
  filename: (req, file, cb) => {
    // Create a unique filename to avoid overwrites
    cb(null, Date.now() + path.extname(file.originalname));
  },
});

const upload = multer({ storage: storage });
...

Then use upload.single("file") as a middleware to handle a single file upload from a field name file.

// CREATE a new todo for a user
// POST /api/v2/todos
app.post('/todos', upload.single('file'), async (req: Request, res: Response) => {
  try {
    const { title, authorId } = req.body;

    if (!title || !authorId) {
      return res.status(400).json({ error: 'Title and authorId are required' });
    }

    const newTodo = await prisma.todo.create({
      data: {
        title,
        authorId,
        // If a file was uploaded, req.file will be defined. Save its path.
        filePath: req.file ? req.file.path : null,
      },
    });
    res.status(201).json(newTodo);
  } catch (error) {
    console.error(error); // Log the error for debugging
    res.status(500).json({ error: 'Could not create todo' });
  }
});

To test the endpoing with Insomnia.

potikanond/README.md