এক্সপ্রেস অ্যাপে ডেটাবেজ মাইগ্রেশন এবং ORM

npm install prisma --save-dev
npx prisma init --datasource-provider sqlite

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

npm install dotenv

DATABASE_URL="file:../notes.db"

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

// 🧍 User model
model User {
  id         Int      @id @default(autoincrement())
  username   String   @unique
  password   String
  role       String   // admin | tenant | user
  tenant     Tenant?  @relation(fields: [tenantId], references: [id])
  tenantId   Int?
  notes      Note[]
  created_at DateTime @default(now())
}

// 🏢 Tenant model
model Tenant {
  id         Int      @id @default(autoincrement())
  name       String   @unique
  users      User[]
  notes      Note[]
  created_at DateTime @default(now())
}

// 🗒️ Note model
model Note {
  id         Int      @id @default(autoincrement())
  title      String
  content    String
  user       User     @relation(fields: [userId], references: [id])
  userId     Int
  tenant     Tenant?  @relation(fields: [tenantId], references: [id])
  tenantId   Int?
  created_at DateTime @default(now())
}

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default prisma;

npx prisma migrate dev --name init

import express from "express";
import prisma from "../db.js";
import { authenticate } from "../middleware/auth.js";

const router = express.Router();

// ✅ All routes require authentication
router.use(authenticate);

/**
 * 📘 Create Note
 * - Regular users can create notes under their tenant
 * - Tenants and Admins can also create (optional)
 */
router.post("/", async (req, res) => {
  const { title, content } = req.body;
  const { id, tenantId } = req.user;

  try {
    const note = await prisma.note.create({
      data: {
        title,
        content,
        userId: id,
        tenantId: tenantId || null,
      },
    });

    res.json(note);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to create note" });
  }
});

/**
 * 📖 Get All Notes
 * - Admin: see all notes
 * - Tenant: see all notes under their tenant
 * - User: see only their own notes
 */
router.get("/", async (req, res) => {
  const { role, id, tenantId } = req.user;

  try {
    let notes;

    if (role === "admin") {
      notes = await prisma.note.findMany({
        orderBy: { created_at: 'desc' },
        include: {
          user: {
            select: { id: true, username: true, role: true }
          },
          tenant: {
            select: { id: true, name: true }
          }
        }
      });
    } else if (role === "tenant") {
      notes = await prisma.note.findMany({
        where: { tenantId: tenantId },
        orderBy: { created_at: 'desc' },
        include: {
          user: {
            select: { id: true, username: true, role: true }
          }
        }
      });
    } else {
      notes = await prisma.note.findMany({
        where: { userId: id },
        orderBy: { created_at: 'desc' }
      });
    }

    res.json(notes);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch notes" });
  }
});

/**
 * 🔍 Get Single Note
 * - Admin: can access any note
 * - Tenant: can access notes within their tenant
 * - User: only their own notes
 */
router.get("/:id", async (req, res) => {
  const { role, id, tenantId } = req.user;
  const noteId = parseInt(req.params.id);

  try {
    let whereClause = { id: noteId };

    if (role === "tenant") {
      whereClause.tenantId = tenantId;
    } else if (role === "user") {
      whereClause.userId = id;
    }
    // Admin can access any note, so no additional where clause needed

    const note = await prisma.note.findFirst({
      where: whereClause,
      include: {
        user: {
          select: { id: true, username: true, role: true }
        },
        tenant: {
          select: { id: true, name: true }
        }
      }
    });

    if (!note) return res.status(404).json({ error: "Note not found" });
    res.json(note);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch note" });
  }
});

/**
 * ✏️ Update Note
 */
router.put("/:id", async (req, res) => {
  const { title, content } = req.body;
  const { role, id, tenantId } = req.user;
  const noteId = parseInt(req.params.id);

  try {
    let whereClause = { id: noteId };

    if (role === "tenant") {
      whereClause.tenantId = tenantId;
    } else if (role === "user") {
      whereClause.userId = id;
    }
    // Admin can update any note

    const updatedNote = await prisma.note.updateMany({
      where: whereClause,
      data: {
        title,
        content,
      },
    });

    if (updatedNote.count === 0) {
      return res.status(404).json({ error: "Note not found or access denied" });
    }

    res.json({ message: "Note updated successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to update note" });
  }
});

/**
 * ❌ Delete Note
 */
router.delete("/:id", async (req, res) => {
  const { role, id, tenantId } = req.user;
  const noteId = parseInt(req.params.id);

  try {
    let whereClause = { id: noteId };

    if (role === "tenant") {
      whereClause.tenantId = tenantId;
    } else if (role === "user") {
      whereClause.userId = id;
    }
    // Admin can delete any note

    const deletedNote = await prisma.note.deleteMany({
      where: whereClause,
    });

    if (deletedNote.count === 0) {
      return res.status(404).json({ error: "Note not found or access denied" });
    }

    res.json({ message: "Note deleted successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to delete note" });
  }
});

export default router;

import express from "express";
import bcrypt from "bcryptjs";
import jwt from "jsonwebtoken";
import prisma from "../db.js";
import { authenticate, authorizeRoles } from "../middleware/auth.js";

const router = express.Router();
const JWT_SECRET = "s9d8f7s9df7s9dadsfdf7s9df7s9df"; // use env in prod

// 🧱 Register Tenant (only admin can do this)
router.post("/register-tenant", authenticate, authorizeRoles("admin"), async (req, res) => {
  const { tenantName, username, password } = req.body;

  const hashedPassword = await bcrypt.hash(password, 10);

  try {
    // Create tenant and tenant user in a transaction
    const result = await prisma.$transaction(async (tx) => {
      // Create the tenant
      const tenant = await tx.tenant.create({
        data: {
          name: tenantName,
        },
      });

      // Create the tenant admin user
      const user = await tx.user.create({
        data: {
          username,
          password: hashedPassword,
          role: "tenant",
          tenantId: tenant.id,
        },
      });

      return { tenant, user };
    });

    res.json({ 
      message: "Tenant created", 
      tenantId: result.tenant.id,
      userId: result.user.id 
    });
  } catch (err) {
    console.error(err);
    res.status(400).json({ error: "Tenant or username already exists" });
  }
});

// 👤 Register Regular User (only tenant admin can do this)
router.post("/register", authenticate, authorizeRoles("tenant"), async (req, res) => {
  const { username, password } = req.body;

  const hashedPassword = await bcrypt.hash(password, 10);

  try {
    const user = await prisma.user.create({
      data: {
        username,
        password: hashedPassword,
        role: "user",
        tenantId: req.user.tenantId,
      },
    });

    res.json({ message: "User created", id: user.id });
  } catch (err) {
    console.error(err);
    res.status(400).json({ error: "Username already exists" });
  }
});

// 🔑 Login (works for all roles)
router.post("/login", async (req, res) => {
  const { username, password } = req.body;

  try {
    const user = await prisma.user.findUnique({
      where: { username },
      include: {
        tenant: {
          select: { id: true, name: true }
        }
      }
    });

    if (!user) {
      return res.status(400).json({ error: "Invalid credentials" });
    }

    const valid = await bcrypt.compare(password, user.password);
    if (!valid) {
      return res.status(400).json({ error: "Invalid credentials" });
    }

    const token = jwt.sign(
      { 
        id: user.id, 
        role: user.role, 
        tenantId: user.tenantId,
        username: user.username 
      },
      JWT_SECRET,
      { expiresIn: "1h" }
    );

    res.json({ 
      token, 
      role: user.role,
      user: {
        id: user.id,
        username: user.username,
        role: user.role,
        tenant: user.tenant
      }
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Login failed" });
  }
});

export default router;

import { PrismaClient } from "@prisma/client";
import bcrypt from "bcryptjs";

const prisma = new PrismaClient();

async function main() {
  // Check if superadmin already exists
  const existing = await prisma.user.findUnique({
    where: { username: "superadmin" },
  });

  if (!existing) {
    const hashedPassword = await bcrypt.hash("password", 10);

    await prisma.user.create({
      data: {
        username: "superadmin",
        password: hashedPassword,
        role: "admin", // superadmin role
      },
    });

    console.log("✅ Superadmin created");
  } else {
    console.log("ℹ️ Superadmin already exists");
  }
}

main()
  .catch((e) => console.error(e))
  .finally(async () => {
    await prisma.$disconnect();
  });

 "scripts": {
    "dev": "nodemon index.js",
    "seed": "node prisma/seed.js"
  },

// 🗒️ Note model
model Note {
  id         Int      @id @default(autoincrement())
  title      String
  content    String
  user       User     @relation(fields: [userId], references: [id])
  userId     Int
  tenant     Tenant?  @relation(fields: [tenantId], references: [id])
  tenantId   Int?
  created_at DateTime @default(now())
}

// 🗒️ Note model
model Note {
  id         Int      @id @default(autoincrement())
  title      String
  content    String
  tags       String?
  user       User     @relation(fields: [userId], references: [id])
  userId     Int
  tenant     Tenant?  @relation(fields: [tenantId], references: [id])
  tenantId   Int?
  created_at DateTime @default(now())
}

npx prisma migrate dev --name add_tags_field

npx prisma generate

/**
 * 📘 Create Note
 * - Regular users can create notes under their tenant
 * - Tenants and Admins can also create (optional)
 */
router.post("/", async (req, res) => {
  const { title, content, tags } = req.body;
  const { id, tenantId } = req.user;

  try {
    const note = await prisma.note.create({
      data: {
        title,
        content,
        userId: id,
        tags,
        tenantId: tenantId || null,
      },
    });

    res.json(note);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to create note" });
  }
});

{
  "title": "Note with tags",
  "content": "this is a note with tags!",
    "tags": "prisma,express"
}

npm init -y
npm install express dotenv @prisma/client
npm install prisma --save-dev

npx prisma init --datasource-provider sqlite

import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  engine: "classic",
  datasource: {
    url: env("DATABASE_URL"),
  },
});

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model Note {
  id         Int      @id @default(autoincrement())
  title      String
  content    String
  tags       String?   // optional field
  created_at DateTime @default(now())
}

DATABASE_URL="file:./dev.db"

npx prisma migrate dev --name init

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();
export default prisma;

import express from "express";
import prisma from "./db.js";

const app = express();
app.use(express.json());
const PORT = 3000;

// 📝 Create Note
app.post("/notes", async (req, res) => {
  const { title, content, tags } = req.body;
  const note = await prisma.note.create({
    data: { title, content, tags },
  });
  res.json(note);
});

// 📋 Get All Notes
app.get("/notes", async (req, res) => {
  const notes = await prisma.note.findMany({ orderBy: { created_at: "desc" } });
  res.json(notes);
});

// 🔍 Get Single Note
app.get("/notes/:id", async (req, res) => {
  const note = await prisma.note.findUnique({ where: { id: Number(req.params.id) } });
  if (!note) return res.status(404).json({ error: "Note not found" });
  res.json(note);
});

// ✏️ Update Note
app.put("/notes/:id", async (req, res) => {
  const { title, content, tags } = req.body;
  try {
    const note = await prisma.note.update({
      where: { id: Number(req.params.id) },
      data: { title, content, tags },
    });
    res.json(note);
  } catch {
    res.status(404).json({ error: "Note not found" });
  }
});

// ❌ Delete Note
app.delete("/notes/:id", async (req, res) => {
  try {
    await prisma.note.delete({ where: { id: Number(req.params.id) } });
    res.json({ message: "Note deleted" });
  } catch {
    res.status(404).json({ error: "Note not found" });
  }
});

// Start server
app.listen(PORT, () => console.log(`Server running on http://localhost:${PORT}`));

POST http://localhost:3000/notes
Content-Type: application/json

{
  "title": "First Note",
  "content": "This is a simple note",
  "tags": "learning,prisma"
}

npx prisma studio