Guide

Tech Stack

For this simple CRUD API, the recommended tech stack is:

Backend Framework: Node.js with Express.js

Lightweight and easy to use for building REST APIs.
Database: PostgreSQL

Structured and relational, ideal for handling amount and date data.
ORM: Sequelize

Simplifies database interactions with PostgreSQL.
ontainerization: Docker

Ensures portability and ease of deployment.

Folder Structure (Backend Only)

In this setup, you will have a backend-only project that exposes the API, and the React frontend (which will be in a separate project) will consume these APIs.

/my-app
  /server                  # Backend (Node.js with Express and MongoDB)
    /controllers           # API endpoint controllers
    /models                # Mongoose models
    /routes                # API routes
    /config                # Database configuration
    server.js              # Main entry point of the backend server
    package.json           # Backend dependencies
    .env                   # Environment variables (e.g., MongoDB URI)
  README.md                # Project description

- src/
  - models/
    - record.model.js
  - routes/
    - record.routes.js
  - controllers/
    - record.controller.js
  - config/
    - db.config.js
  - server.js
- Dockerfile
- docker-compose.yml

Backend Setup

1. Install Dependencies

Go to the backend directory (/server) and initialize the project with:

cd /server
npm init -y
npm install express mongoose cors dotenv

2. Folder Structure and Backend Code

server.js: Entry point for the Express server.
controllers/recordController.js: Controller logic for CRUD operations.
models/Record.js: Mongoose schema for records.
routes/recordRoutes.js: Define API routes.

7. Running the Backend

node server.js

CODE

Database Configuration (src/config/db.config.js)

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("crud_db", "postgres", "password", {
  host: "localhost",
  dialect: "postgres",
});

module.exports = sequelize;

Model Definition (src/models/record.model.js)

const { DataTypes } = require("sequelize");
const sequelize = require("../config/db.config");

const Record = sequelize.define("Record", {
  amount: {
    type: DataTypes.FLOAT,
    allowNull: false,
  },
  date: {
    type: DataTypes.STRING,
    allowNull: false,
  },
});

module.exports = Record;

Controller (src/controllers/record.controller.js)

const Record = require("../models/record.model");

// Create a new record
exports.createRecord = async (req, res) => {
  try {
    const { amount, date } = req.body;
    const record = await Record.create({ amount, date });
    res.status(201).json(record);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

// Get all records
exports.getRecords = async (req, res) => {
  try {
    const records = await Record.findAll();
    res.status(200).json(records);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

// Get a single record by ID
exports.getRecordById = async (req, res) => {
  try {
    const record = await Record.findByPk(req.params.id);
    if (!record)
      return res.status(404).json({ message: "Record not found" });
    res.status(200).json(record);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

// Update a record by ID
exports.updateRecord = async (req, res) => {
  try {
    const { amount, date } = req.body;
    const record = await Record.findByPk(req.params.id);
    if (!record)
      return res.status(404).json({ message: "Record not found" });

    record.amount = amount;
    record.date = date;
    await record.save();

    res.status(200).json(record);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

// Delete a record by ID
exports.deleteRecord = async (req, res) => {
  try {
    const record = await Record.findByPk(req.params.id);
    if (!record)
      return res.status(404).json({ message: "Record not found" });

    await record.destroy();

    res.status(204).send();
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

Routes (src/routes/record.routes.js)

const express = require("express");
const router = express.Router();
const controller = require("../controllers/record.controller");

router.post("/", controller.createRecord);
router.get("/", controller.getRecords);
router.get("/:id", controller.getRecordById);
router.put("/:id", controller.updateRecord);
router.delete("/:id", controller.deleteRecord);

module.exports = router;

Server Setup (src/server.js)

const express = require("express");
const sequelize = require("./config/db.config");
const recordRoutes = require("./routes/record.routes");

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

// Routes
app.use("/api/records", recordRoutes);

// Start server and connect to DB
sequelize
  .sync()
  .then(() => {
    console.log("Database connected!");
    app.listen(3000, () => console.log("Server running on port 3000"));
  })
  .catch((err) => console.error("Database connection failed:", err));

Dockerfile

FROM node:16

WORKDIR /app

COPY package.json package-lock.json ./
RUN npm install

COPY . .

EXPOSE 3000

CMD ["node", "src/server.js"]

Docker Compose (docker-compose.yml)

version: "3.8"

services:
  app:
    build: .
    ports:
      - "3000:3000"
    depends_on:
      - db

  db:
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: crud_db
    ports:
      - "5432:5432"

How to Run

Build and start the services:
```
docker-compose up --build
```
Access the API at http://localhost:3000/api/records.

This setup provides a fully functional CRUD API using Node.js, Express, PostgreSQL, Sequelize, and Docker

Extra

DATABASE

CREATE DATABASE testdb;
\list
# or
\l
# is same

Listing users in PostgreSQL

Using the \du Command

The simplest way to list all users is by using the \du command in the psql terminal.
This will show a list of all roles in the database, including users, their attributes (such as superuser status), and their assigned roles.

\du

This will display a table with the following columns:

Role Name: The name of the user or role.
Attributes: Whether the role is a superuser, can create databases, or has login privileges.
Member Of: Any other roles this user belongs to.

List of Roles

Role name	Attributes	Member of
postgres	Superuser, Create role, Create DB, Replication, Bypass RLS	{}
bishow	Create DB	{}
myuser	Create DB	{postgres}

To Check if user has password or not

To check if a PostgreSQL user (like bishow) has a password set, you can query the pg_shadow system catalog table, which stores user information including the password hash (if set).

Querying the pg_shadow Table

The pg_shadow table contains user information, including password data. To check if the user bishow has a password set, you can run the following query:
```
SELECT usename, passwd FROM pg_shadow WHERE usename = 'bishow';
```
- usename: The username.
- passwd: The hashed password (if set) or an empty string if no password is set.

When you run the command:

sudo -u postgres psql

You are switching to the postgres user and logging into the PostgreSQL database as the postgres user (which is typically the superuser for PostgreSQL). Here's a breakdown:

sudo -u postgres: This part of the command tells sudo to run the following command (psql) as the postgres user, which is usually the default superuser in PostgreSQL.
psql: This is the PostgreSQL interactive terminal that connects to the database.

Once you're inside the psql terminal, you'll be logged in as the postgres PostgreSQL user, not as your system's current user (e.g., bishow or any other user you might have logged in as on the operating system).

To verify this inside the PostgreSQL terminal, you can run the following SQL command:

SELECT current_user;

This will return the PostgreSQL user you're currently logged in as.

postgres=# SELECT current_user;
 current_user
--------------
 postgres
(1 row)

In this example, the current PostgreSQL user is postgres.