Parking lot system design using Prisma and Node.js with a CRUD approach

To design a parking lot system using Prisma and Node.js with a CRUD (Create, Read, Update, Delete) based approach, we'll outline the system components, database schema using Prisma, and basic CRUD operations. This example assumes a simplified parking lot management system where we manage parking spots and vehicles.

System Components

1. Prisma: ORM (Object-Relational Mapping) tool for database interactions.
2. Node.js: Backend server to handle HTTP requests and interact with Prisma.
3. Express.js: Framework for building web APIs in Node.js.
4. Database: PostgreSQL (used in this example, but Prisma supports various databases).

Database Schema with Prisma

In schema.prisma, define the models for ParkingSpot and Vehicle:

// schema.prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model ParkingSpot {
  id        Int      @id @default(autoincrement())
  number    Int      @unique
  occupied  Boolean  @default(false)
  vehicle   Vehicle? @relation(fields: [vehicleId], references: [id])
  vehicleId Int?
}

model Vehicle {
  id          Int           @id @default(autoincrement())
  plateNumber String        @unique
  color       String
  model       String
  parkingSpot ParkingSpot[]
}

CRUD Operations in Node.js using Prisma

Initialize Prisma Client

Install Prisma CLI and Prisma Client:

npm install @prisma/cli @prisma/client

Initialize Prisma Client in your Node.js application:

// prisma.js
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

module.exports = prisma;

CRUD Operations Example

Implement CRUD operations for managing parking spots and vehicles:

// parkingService.js
const prisma = require('./prisma');

async function createParkingSpot(number) {
  const parkingSpot = await prisma.parkingSpot.create({
    data: {
      number,
      occupied: false,
    },
  });
  return parkingSpot;
}

async function getParkingSpotById(id) {
  const parkingSpot = await prisma.parkingSpot.findUnique({
    where: { id },
  });
  return parkingSpot;
}

async function updateParkingSpot(id, data) {
  const updatedParkingSpot = await prisma.parkingSpot.update({
    where: { id },
    data,
  });
  return updatedParkingSpot;
}

async function deleteParkingSpot(id) {
  const deletedParkingSpot = await prisma.parkingSpot.delete({
    where: { id },
  });
  return deletedParkingSpot;
}

module.exports = {
  createParkingSpot,
  getParkingSpotById,
  updateParkingSpot,
  deleteParkingSpot,
};

Integrating with Express.js

Set up routes to handle HTTP requests:

// index.js
const express = require('express');
const app = express();
const prisma = require('./prisma');
const parkingService = require('./parkingService');

app.use(express.json());

// Create parking spot
app.post('/parking-spots', async (req, res) => {
  const { number } = req.body;
  try {
    const parkingSpot = await parkingService.createParkingSpot(number);
    res.json(parkingSpot);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Failed to create parking spot' });
  }
});

// Get parking spot by id
app.get('/parking-spots/:id', async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    const parkingSpot = await parkingService.getParkingSpotById(id);
    res.json(parkingSpot);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Failed to fetch parking spot' });
  }
});

// Update parking spot by id
app.put('/parking-spots/:id', async (req, res) => {
  const id = parseInt(req.params.id);
  const { number, occupied, vehicleId } = req.body;
  try {
    const updatedParkingSpot = await parkingService.updateParkingSpot(id, {
      number,
      occupied,
      vehicleId,
    });
    res.json(updatedParkingSpot);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Failed to update parking spot' });
  }
});

// Delete parking spot by id
app.delete('/parking-spots/:id', async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    const deletedParkingSpot = await parkingService.deleteParkingSpot(id);
    res.json(deletedParkingSpot);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Failed to delete parking spot' });
  }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});