CRUD API Menggunakan Express.js dengan Database MySQL + Sequelize ORM di Server GNU/Linux

Assalamualaikum Wr.Wb..

Berikut contoh lengkap CRUD API menggunakan Express.js dengan fitur:

- CRUD (Create, Read, Update, Delete)

- Upload image & PDF

- Keamanan upload (sanitize, MIME check, file size, rename aman)

- Proteksi SQL Injection, RCE, LFI, XSS

- Pagination pada list data

Contoh ini memakai MySQL + Sequelize ORM

(ORM otomatis membantu mitigasi keretanan SQLi).

1. Instalasi Dependency

npm init -y

npm install express sequelize mysql2 multer express-validator helmet cors xss-clean express-rate-limit dotenv uuid

2. Struktur Folder Project

project/

│

├── app.js

├── .env

├── uploads/

│   ├── images/

│   └── files/

│

├── config/

│   └── database.js

│

├── models/

│   └── Document.js

│

├── routes/

│   └── document.routes.js

│

├── controllers/

│   └── document.controller.js

│

├── middlewares/

│   ├── upload.middleware.js

│   └── sanitize.middleware.js

3. Berikut contoh file .env yang dipakai sesuai project ini:

# APP

PORT=3000

NODE_ENV=development

# DATABASE

DB_HOST=localhost

DB_NAME=crud_upload_db

DB_USER=root

DB_PASS=password_db

# SECURITY

RATE_LIMIT_MAX=100

RATE_LIMIT_WINDOW=15

Keterangan Variabel dari file .env :

- PORT → port aplikasi Express

- DB_HOST → host database MySQL

- DB_NAME → nama database

- DB_USER → user database

- DB_PASS → password database

- RATE_LIMIT_MAX → maksimal request per IP

- RATE_LIMIT_WINDOW → menit (digunakan di rate limiter)

Struktur Tabel: documents SQL (MySQL)

CREATE TABLE documents (

  id INT AUTO_INCREMENT PRIMARY KEY,

  title VARCHAR(255) NOT NULL,

  description TEXT,

  image VARCHAR(255),

  file VARCHAR(255),

  createdAt DATETIME NOT NULL,

  updatedAt DATETIME NOT NULL

) ENGINE=InnoDB;

Penjelasan Tiap Kolom table sebagai berikut:

| Kolom         | Tipe         | Keterangan                  |

| ------------- | ------------ | --------------------------- |

| `id`          | INT (PK)     | Primary key, auto increment |

| `title`       | VARCHAR(255) | Judul dokumen               |

| `description` | TEXT         | Deskripsi dokumen           |

| `image`       | VARCHAR(255) | Nama file image (jpg/png)   |

| `file`        | VARCHAR(255) | Nama file PDF               |

| `createdAt`   | DATETIME     | Waktu data dibuat           |

| `updatedAt`   | DATETIME     | Waktu terakhir update       |

4. Konfigurasi Database di folder (config/database.js)

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

const sequelize = new Sequelize(

  process.env.DB_NAME,

  process.env.DB_USER,

  process.env.DB_PASS,

  {

    host: process.env.DB_HOST,

    dialect: 'mysql',

    logging: false,

  }

);

module.exports = sequelize;

5. Buat Model di folder (models/Document.js)

const { DataTypes } = require('sequelize');

const sequelize = require('../config/database');

const Document = sequelize.define('Document', {

  id: {

    type: DataTypes.INTEGER,

    primaryKey: true,

    autoIncrement: true

  },

  title: {

    type: DataTypes.STRING(255),

    allowNull: false

  },

  description: {

    type: DataTypes.TEXT,

    allowNull: true

  },

  image: {

    type: DataTypes.STRING(255),

    allowNull: true

  },

  file: {

    type: DataTypes.STRING(255),

    allowNull: true

  }

}, {

  tableName: 'documents',

  timestamps: true

});

module.exports = Document;

6. Buat Middleware Keamanan Upload

di folder (middlewares/upload.middleware.js)

const multer = require('multer');

const path = require('path');

const { v4: uuidv4 } = require('uuid');

const allowedMime = [

  'image/jpeg',

  'image/png',

  'application/pdf'

];

const storage = multer.diskStorage({

  destination: (req, file, cb) => {

    if (file.mimetype.startsWith('image')) {

      cb(null, 'uploads/images');

    } else {

      cb(null, 'uploads/files');

    }

  },

  filename: (req, file, cb) => {

    const safeName =

      uuidv4() + path.extname(file.originalname).toLowerCase();

    cb(null, safeName);

  }

});

const fileFilter = (req, file, cb) => {

  if (!allowedMime.includes(file.mimetype)) {

    return cb(new Error('File type not allowed'), false);

  }

  cb(null, true);

};

module.exports = multer({

  storage,

  fileFilter,

  limits: {

    fileSize: 5 * 1024 * 1024 // 5MB

  }

});

Keamanan upload yang diterapkan ini sebagai berikut:

- MIME whitelist

- Rename file (mencegah RCE & LFI)

- Size limit

- Tidak memakai nama file user

- Folder terpisah file images dan file pdf

7. Buat Middleware Sanitasi & XSS

di folder (middlewares/sanitize.middleware.js)

const { body, query, param } = require('express-validator');

exports.documentValidation = [

  body('title').trim().escape(),

  body('description').optional().trim().escape(),

  query('page').optional().isInt({ min: 1 }),

  query('limit').optional().isInt({ min: 1, max: 100 }),

  param('id').optional().isInt()

];

8. Buat Controller isi api CRUD nya

di folder (controllers/document.controller.js)

const Document = require('../models/Document');

/**

 * CREATE

 */

exports.create = async (req, res) => {

  const image = req.files?.image?.[0]?.filename || null;

  const file = req.files?.file?.[0]?.filename || null;

  const data = await Document.create({

    title: req.body.title,

    description: req.body.description,

    image,

    file

  });

  res.json(data);

};

/**

 * READ + PAGINATION

 */

exports.findAll = async (req, res) => {

  const page = parseInt(req.query.page) || 1;

  const limit = parseInt(req.query.limit) || 10;

  const offset = (page - 1) * limit;

  const { rows, count } = await Document.findAndCountAll({

    limit,

    offset,

    order: [['id', 'DESC']]

  });

  res.json({

    total: count,

    page,

    totalPage: Math.ceil(count / limit),

    data: rows

  });

};

/**

 * READ BY ID

 */

exports.findOne = async (req, res) => {

  const data = await Document.findByPk(req.params.id);

  if (!data) return res.status(404).json({ message: 'Not found' });

  res.json(data);

};

/**

 * UPDATE

 */

exports.update = async (req, res) => {

  const doc = await Document.findByPk(req.params.id);

  if (!doc) return res.status(404).json({ message: 'Not found' });

  const image = req.files?.image?.[0]?.filename;

  const file = req.files?.file?.[0]?.filename;

  await doc.update({

    title: req.body.title,

    description: req.body.description,

    image: image || doc.image,

    file: file || doc.file

  });

  res.json(doc);

};

/**

 * DELETE

 */

exports.remove = async (req, res) => {

  const doc = await Document.findByPk(req.params.id);

  if (!doc) return res.status(404).json({ message: 'Not found' });

  await doc.destroy();

  res.json({ message: 'Deleted' });

};

9. Buat Routes di folder (routes/document.routes.js)

const express = require('express');

const router = express.Router();

const upload = require('../middlewares/upload.middleware');

const sanitize = require('../middlewares/sanitize.middleware');

const controller = require('../controllers/document.controller');

router.post(

  '/',

  upload.fields([

    { name: 'image', maxCount: 1 },

    { name: 'file', maxCount: 1 }

  ]),

  sanitize.documentValidation,

  controller.create

);

router.get('/', sanitize.documentValidation, controller.findAll);

router.get('/:id', sanitize.documentValidation, controller.findOne);

router.put(

  '/:id',

  upload.fields([

    { name: 'image', maxCount: 1 },

    { name: 'file', maxCount: 1 }

  ]),

  sanitize.documentValidation,

  controller.update

);

router.delete('/:id', sanitize.documentValidation, controller.remove);

module.exports = router;

10. Buat untuk App Utama di file (app.js)

require('dotenv').config();

const express = require('express');

const helmet = require('helmet');

const cors = require('cors');

const xss = require('xss-clean');

const rateLimit = require('express-rate-limit');

const sequelize = require('./config/database');

const documentRoutes = require('./routes/document.routes');

const app = express();

app.use(helmet());                 // XSS, clickjacking, CSP

app.use(cors());

app.use(express.json());

app.use(xss());                    // XSS sanitization

app.use(rateLimit({

  windowMs: 15 * 60 * 1000,

  max: 100

}));

app.use('/uploads', express.static('uploads')); // readonly

app.use('/api/documents', documentRoutes);

sequelize.sync().then(() => {

  app.listen(3000, () => console.log('Server berjalan di port 3000'));

});

Proteksi Keamanan yang Sudah Aktif ini sebagai berikut:

| Ancaman        | Penanganan                      |

| -------------- | ------------------------------- |

| SQL Injection  | Sequelize ORM                   |

| XSS            | helmet + xss-clean + escape     |

| RCE            | Rename file, no exec, whitelist |

| LFI            | Path fixed, no user path        |

| File Tampering | MIME whitelist + size limit     |

| Brute Force    | Rate limit                      |

Contoh Request List Data dengan Pagination

GET /api/documents?page=1&limit=10

oke mungkin itu saja semoga bermanfaat...

Wasalamualaikum Wr.Wb...