সার্ভারে পোস্টগ্রেস এবং এডমিন ইন্টারফেস ইন্সটল

sudo apt update && sudo apt upgrade -y

sudo apt install postgresql postgresql-contrib -y

sudo systemctl status postgresql

sudo systemctl start postgresql

sudo systemctl enable postgresql

sudo -i -u postgres

psql

CREATE DATABASE myappdb;

\c myappdb

You are now connected to database "myappdb" as user "postgres".

CREATE TABLE users (
    id SERIAL PRIMARY KEY,         -- Auto-incrementing ID
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, email, password)
VALUES
    ('alice', '[email protected]', 'password123'),
    ('bob', '[email protected]', 'secret456'),
    ('charlie', '[email protected]', 'mypassword');

SELECT * FROM users;

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myuser;

\c myappdb

-- Grant privileges on all existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- Grant privileges on all sequences (needed for SERIAL columns)
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- Grant privileges automatically on any future tables and sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO myuser;

\l

\q

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

sudo nano /etc/postgresql/16/main/pg_hba.conf

host    all             all             0.0.0.0/0               scram-sha-256

sudo ufw allow 5432/tcp
sudo ufw reload

#
# Setup the repository
#

# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

#
# Install pgAdmin
#

# Install for both desktop and web modes:
sudo apt install pgadmin4

# Install for desktop mode only:
sudo apt install pgadmin4-desktop

# Install for web mode only: 
sudo apt install pgadmin4-web 

# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

sudo systemctl restart apache2

sudo apt update
sudo apt install apache2 php php-pgsql -y

sudo mkdir -p /var/www/html/adminer
sudo wget "https://www.adminer.org/latest.php" -O /var/www/html/adminer/index.php

sudo chown -R www-data:www-data /var/www/html/adminer
sudo chmod -R 755 /var/www/html/adminer

npm init -y

npm install express @prisma/client
npm install -D prisma nodemon

npx prisma init

DATABASE_URL="postgresql://myuser:mypassword@localhost:5432/myappdb?schema=public"

npx prisma db pull

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

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

model users {
  id         Int       @id @default(autoincrement())
  username   String    @unique @db.VarChar(50)
  email      String    @unique @db.VarChar(100)
  password   String    @db.VarChar(255)
  created_at DateTime? @default(now()) @db.Timestamp(6)
}

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

npx prisma studio

const express = require("express");
const { PrismaClient } = require("@prisma/client");
const bcrypt = require("bcrypt");

const app = express();
const prisma = new PrismaClient();

app.use(express.json());

// --------------------
// CREATE NEW USER
// --------------------
app.post("/users", async (req, res) => {
  try {
    const { username, email, password } = req.body;

    // Optional: hash password before saving
    const hashedPassword = await bcrypt.hash(password, 10);

    const user = await prisma.users.create({
      data: {
        username,
        email,
        password: hashedPassword,
      },
    });

    res.json({ id: user.id, username: user.username, email: user.email });
  } catch (err) {
    console.error(err);
    if (err.code === "P2002") {
      // Unique constraint failed
      return res.status(400).json({ error: "Username or email already exists" });
    }
    res.status(500).json({ error: "Something went wrong" });
  }
});

// --------------------
// GET ALL USERS
// --------------------
app.get("/users", async (req, res) => {
  try {
    const users = await prisma.users.findMany({
      select: { id: true, username: true, email: true, created_at: true },
      orderBy: { id: "desc" },
    });
    res.json(users);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Something went wrong" });
  }
});

// --------------------
// GET SINGLE USER BY ID
// --------------------
app.get("/users/:id", async (req, res) => {
  const { id } = req.params;
  try {
    const user = await prisma.users.findUnique({
      where: { id: Number(id) },
      select: { id: true, username: true, email: true, created_at: true },
    });

    if (!user) return res.status(404).json({ error: "User not found" });
    res.json(user);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Something went wrong" });
  }
});

// --------------------
// DELETE USER
// --------------------
app.delete("/users/:id", async (req, res) => {
  const { id } = req.params;
  try {
    await prisma.users.delete({
      where: { id: Number(id) },
    });
    res.json({ message: "User deleted successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Something went wrong" });
  }
});

// --------------------
// START SERVER
// --------------------
const PORT = 3000;
app.listen(PORT, () => {
  console.log(`🚀 Server running on port ${PORT}`);
});

npm install bcrypt

node server.js

curl -X POST http://localhost:3000/users \
-H "Content-Type: application/json" \
-d '{"username":"Your name","email":"[email protected]","password":"123456"}'