Express blog - Atelier fil route

database.sql

CREATE TABLE author
(
    id INTEGER NOT NULL
    AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR
    (50) NOT NULL
);

    CREATE TABLE `post`
    (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    title VARCHAR
    (128) NOT NULL,
    slug VARCHAR
    (128) NOT NULL,
    content LONGTEXT NOT NULL,
    datepost DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    active tinyint
    (1) NOT NULL DEFAULT "1",
    FOREIGN KEY
    (author_id) REFERENCES author
    (id),
    FOREIGN KEY
    (category_id) REFERENCES category
    (id)
);

    CREATE TABLE category
    (
        id INTEGER NOT NULL
        AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR
        (50) NOT NULL
);

index.js

const express = require("express");
const app = express();
const port = 3000;
const bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

const indexRouter = require("./routes/index");
const postsRouter = require("./routes/posts");

app.use("/", indexRouter);
app.use("/posts", postsRouter);

app.listen(port, () => {
  console.log(`App listening at http://localhost:${port}`);
});

mysql.js

const mysql = require("mysql");
const dotenv = require("dotenv").config();

module.exports = mysql.createConnection({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
});

posts.js

const express = require("express");
const router = express.Router();
const db = require("../database/mysql");

router.get("/", (req, res) => {
  db.query("SELECT * FROM post", (err, results) => {
    if (err) {
      res.status(500).json({
        error: err.message,
        sql: err.sql,
      });
    } else {
      res.json(results);
    }
  });
});

router.get("/slug/:slug", (req, res) => {
  let mySlug = req.params.slug;
  db.query("SELECT * FROM post WHERE slug = ?", [mySlug], (err, results) => {
    if (err) {
      res.status(500).json({
        error: err.message,
        sql: err.sql,
      });
    } else {
      res.json(results);
    }
  });
});

router.get("/title", (req, res) => {
  db.query("SELECT title FROM post", (err, results) => {
    if (err) {
      res.status(500).json({
        error: err.message,
        sql: err.sql,
      });
    } else {
      res.json(results);
    }
  });
});

router.get("/title-content", (req, res) => {
  db.query(
    `SELECT id, title FROM post WHERE title LIKE '%${req.query.title}%'`,
    (err, results) => {
      if (err) {
        res.status(500).send(`An error occurred ${err.message}`);
      } else {
        res.json(results);
      }
    }
  );
});

router.get("/content-start", (req, res) => {
  db.query(
    `SELECT id, title, content FROM post WHERE content LIKE '${req.query.content}%'`,
    (err, results) => {
      if (err) {
        res.status(500).send(`An error occurred ${err.message}`);
      } else {
        res.json(results);
      }
    }
  );
});

router.get("/date-sup", (req, res) => {
  db.query(
    `SELECT id, title, datepost FROM post WHERE datepost > DATE(?)`,
    [req.query.datepost],
    (err, results) => {
      if (err) {
        res.status(500).send(`An error occurred ${err.message}`);
      } else {
        res.json(results);
      }
    }
  );
});

router.get("/order", (req, res) => {
  db.query(
    `SELECT id, title, datepost FROM post ORDER BY datepost ?`,
    [req.query.order],
    (err, results) => {
      if (err) {
        res.status(500).send(`An error occurred ${err.message}`);
      } else {
        res.json(results);
      }
    }
  );
});

router.post("/", (req, res) => {
  const formData = req.body;
  db.query("INSERT INTO post SET ?", formData, (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).send("Erreur lors de la sauvegarde d'un post");
    } else {
      res.status(200).send(`Votre billet "${formData.title}" a été publié !`);
    }
  });
});

router.put("/:id", (req, res) => {
  const idPost = req.params.id;
  const formData = req.body;

  db.query("UPDATE post SET ? WHERE id = ?", [formData, idPost], (err) => {
    if (err) {
      res.status(500).send("Erreur lors de la modification d'un post");
    } else {
      res.status(200).send(`Le post "${idPost}" a été mis à jour !`);
    }
  });
});

router.put("/:id/toggle", (req, res) => {
  const idPost = req.params.id;

  db.query("UPDATE post SET active = !active WHERE id = ?", [idPost], (err) => {
    if (err) {
      res.status(500).send("Erreur lors de la modification d'un post");
    } else {
      res.status(200).send(`Le post "${idPost}" a été togglé !`);
    }
  });
});

router.delete("/delete/:id", (req, res) => {
  const idPost = req.params.id;
  db.query("DELETE FROM post WHERE id = ?", [idPost], (err) => {
    if (err) {
      res.status(500).send("Erreur lors de la suppression d'un post");
    } else {
      res.status(200).send(`Le post "${idPost}" a été supprimé !`);
    }
  });
});

router.delete("/all-unactives", (req, res) => {
  db.query("DELETE FROM post WHERE active = 0", (err) => {
    if (err) {
      res
        .status(500)
        .send("Erreur lors de la suppression d'un post", err.message);
    } else {
      res.status(200).send(`Les posts inactifs ont été supprimés !`);
    }
  });
});

module.exports = router;