รู้จักกับ ORM ตัวช่วย query ฐานข้อมูลกัน

version: "3.7"

services:
  db:
    image: mysql:latest
    container_name: mysql_db
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: tutorial
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
    networks:
      - my_network

  phpmyadmin:
    image: phpmyadmin/phpmyadmin:latest
    container_name: phpmyadmin
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_USER: root
      PMA_PASSWORD: root
    ports:
      - "8080:80"
    depends_on:
      - db
    networks:
      - my_network

networks:
  my_network:
    driver: bridge

volumes:
  mysql_data:
    driver: local

├── docker-compose.yml
├── index.js -- ไฟล์หลักที่เราจะทำกัน
├── package.json

{
  "name": "orm-express-example",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "python3 -m http.server --directory src 8888",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.18.2",
    "mysql2": "^3.6.0",
    "sequelize": "^6.32.1"
  },
  "devDependencies": {
    "nodemon": "^3.0.1"
  }
}

const express = require("express");
const mysql = require("mysql2/promise");
const { Sequelize, DataTypes } = require("sequelize");

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

const port = 8000;

let conn = null;

// function init connection mysql
const initMySQL = async () => {
  conn = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "tutorial",
  });
};

// use sequenlize
const sequelize = new Sequelize("tutorial", "root", "root", {
  host: "localhost",
  dialect: "mysql",
});

/* เราจะเพิ่ม code ส่วนนี้กัน */

// Listen
app.listen(port, async () => {
  await initMySQL();
  // await sequelize.sync()

  console.log("Server started at port 8000");
});

// ทั้งสร้างและ validation
const User = sequelize.define(
  "users",
  {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      valipublishDate: {
        isEmail: true,
      },
    },
  },
  {},
);

// เพิ่ม table address เข้ามา
const Address = sequelize.define(
  "addresses",
  {
    address1: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  },
  {},
);

// ประกาศ relation แบบปกติ
// User.hasMany(Address)

// relation แบบผูกติด (จะสามารถลบไปพร้อมกันได้) = พิจารณาเป็น case by case ไป
User.hasMany(Address, { onDelete: "CASCADE" });

Address.belongsTo(User);

// Listen
app.listen(port, async () => {
  await initMySQL();
  await sequelize.sync(); // ปลด comment บรรทัดนี้ออก

  console.log("Server started at port 8000");
});

app.get("/api/users", async (req, res) => {
  try {
    // แบบ Query แบบเก่า
    // const [result] = await conn.query('SELECT * from users')

    // query ผ่าน model แทน
    const users = await User.findAll();
    res.json(users);
  } catch (err) {
    console.error(err);
  }
});

app.post("/api/users", async (req, res) => {
  try {
    const data = req.body;
    // แบบ Query แบบเก่า
    // const [result] = await conn.query('INSERT INTO users SET ?', data)

    // ท่า Model
    const users = await User.create(data);
    res.json(users);
  } catch (err) {
    console.error(err);
    res.json({
      message: "something went wrong",
      error: err.errors.map((e) => e.message),
    });
  }
});

app.get("/api/users/:id/address", async (req, res) => {
  try {
    const userId = req.params.id;
    // แบบ Query แบบเก่า
    // const [result] = await conn.query('SELECT users.*, addresses.address1 FROM users LEFT JOIN addresses on users.id = addresses.userId WHERE users.id = ?', userId)

    const result = await User.findAll({
      where: { id: userId },
      include: {
        model: Address,
      },
      raw: true,
    });

    res.json(result);
  } catch (err) {
    console.error(err);
    res.json(err);
  }
});

app.put("/api/users/:id", async (req, res) => {
  try {
    const data = req.body;
    const userId = req.params.id;
    // แบบ Query แบบเก่า
    //  const result = await conn.query('UPDATE users SET ? WHERE id = ?', [data, id])

    // ท่า Model
    const users = await User.update(
      {
        name: data.name,
        email: data.email,
      },
      {
        where: { id: userId },
      },
    );

    // ใช้ upsert (insert or update) สำหรับการใส่ข้อมูล
    for (let i = 0; i < data.addresses.length; i++) {
      let cAddressData = data.addresses[i][i];
      cAddressData.userId = userId;
      const address = await Address.upsert(cAddressData);
    }

    // [2] ใช้ if, else แยกเคส ซึ่ง upsert มีค่าเทียบเท่ากับท่าล่างนี้
    // for (let i = 0; i < data.addresses.length; i++) {
    //   const updateAddress = data.addresses[i]
    //   const updateObj = {}

    //   if (updateAddress.id) {
    //     await Address.update({
    //       address1: updateAddress.address1
    //     }, {
    //       where: {
    //         id: updateAddress.id,
    //         userId // userId: userId
    //       }
    //     })
    //   } else {
    //     await Address.create({
    //       ...updateAddress,
    //       userId
    //     })
    //   }
    // }

    res.json({
      message: "update complete!",
      users,
    });
  } catch (err) {
    console.error(err);
    res.json({
      message: "something went wrong",
      error: err.errors.map((e) => e.message),
    });
  }
});

app.delete("/api/users/:id", async (req, res) => {
  try {
    const userId = req.params.id;
    // แบบ Query แบบเก่า
    // const [result] = await conn.query('DELETE FROM users WHERE id = ?', [id])

    // query ผ่าน model แทน
    const result = await User.destroy({
      where: { id: userId },
    });

    res.json({
      message: "delete successful",
      result,
    });
  } catch (err) {
    console.error(err);
    res.json(err);
  }
});

version: "3"

services:
  postgres:
    image: postgres:latest
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: mydatabase
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4:latest
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: adminpassword
    ports:
      - "5050:80"
    depends_on:
      - postgres
    volumes:
      - pgadmin_data:/var/lib/pgadmin

volumes:
  postgres_data:
  pgadmin_data:

const sequelize = new Sequelize("mydatabase", "user", "password", {
  host: "localhost",
  dialect: "postgres",
  logging: false, // Set true if you want to see SQL query logs
});