[[:oktatas:web:back-end_framework:express|< Express]]

====== Express - MariaDB adatbázis ======

  * **Szerző:** Sallai András
  * Copyright (c) 2023, Sallai András
  * Szerkesztve: 2024
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC Attribution-Share Alike 4.0 International]]
  * Web: https://szit.hu

===== Kliens =====

  mysql

===== Függőségek =====

  pnpm install mariadb


===== Dolgozók adatbázisban =====

<code javascript app/models/mariadb.js>
const mariadb = require('mariadb');

const connectonString = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    connectionLimit: 5
}

const pool = mariadb.createPool(connectonString)

async function getConnection() {
    return await pool.getConnection();    
}

async function getEmployees(conn) {
    const sql = 'select * from employees';
    return await conn.query(sql);
}

async function getEmployee(conn, id) {
    const sql = 'select * from employees where id=?';
    return await conn.query(sql, [id]);
}

async function createEmployee(conn, emp) {
    const sql = 'insert into employees ' +
        '(name, city, salary) ' +
        'values (?, ?, ?)';
    return await conn.query(sql,
        [emp.name, emp.city, emp.salary]);
}

async function deleteEmployee(conn, id) {
    const sql = 'delete from employees ' +
        'where id=?';
    return await conn.query(sql, [id]);
}

async function updateEmployee(conn, emp, id) {
    const sql = 'update employees set ' +
        'name=?, city=?, salary=? ' +
        'where id=?';
    return await conn.query(sql, [emp.name, emp.city, emp.salary, id]);
}

module.exports = {    
    getConnection,
    getEmployees,
    getEmployee,
    createEmployee,
    deleteEmployee,
    updateEmployee
}

</code>

==== Dolgozó hozzáadása ====

<code javascript app/controllers/employeecontrollers.js>
    async store(req, res) {
        console.log(req.body);
        if(!req.body.name) {
            res.status(400);
            res.json({ 
                success: false, 
                message: 'Bad request'
            });
        }

        const conn = await getConnection();
        const result = await createEmployee(conn, req.body);
        const id = Number(result.insertId)
        const employee = await getEmployee(conn, id);
        res.status(201)        
        await res.json({
            success: true,
            message: employee
        });
    },
</code>



==== Ellenőrzés ====

Küldjünk egy HTTP klienssel egy felhasználót:
<code>
http post localhost:8000/api/employees 
name='aaaa' city='Szeged' salary=357
</code>

Indítsuk el a MariaDB-t:
  mysql

Megnézhetjük a létező adatbázisokat:
<code>
> show databases;
</code>

Kapcsolódjunk az emp adatbázishoz:
<code>
> use emp
</code>

Nézzük meg milyen kollekcióink vannak:
<code>
> show tables;
</code>


<code>
> select * from employees;
</code>


A kimenet ehhez hasonló kell legyen:

<code>

</code>

==== Dolgozók lekérdezése ====

<code javascript app/controllers/employeecontrollers.js>
    async index(req, res) {
        const sql = 'select * from employees';
        await pool.query(sql, (err, rows) => {
            res.json({ 
                success: true,
                data: rows,
                message: 'Ok'
            });                
        })
    },
</code>

==== Dolgozó törlése ====

<code javascript app/controllers/employeecontrollers.js>
    async delete(req, res) {
        if(!req.params.id) {
            res.status(400);
            res.json({ 
                success: false, 
                message: 'Bad request'
            });
        }
        const id = req.params.id;
        const conn = await getConnection();
        const result = await deleteEmployee(conn, id);

        res.status(200);
        res.json({
            success: true,
            data: result.affectedRows,
            message: 'Ok'
        });
    },
</code>

==== Dolgozó frissítése ====

<code javascript app/controllers/employeecontrollers.js>
    async update(req, res) {
        if(!req.params.id || !req.body.name) {
            res.status(400);
            res.json({
                success: false,
                message: 'Bad request'
            });
        }
        
        const id = req.params.id;

        const conn = await getConnection();
        await updateEmployee(conn, req.body, id);
        const employee = await getEmployee(conn, id);

        res.status(200);
        res.json({
            success: true,
            body: employee,
            message: 'Ok'
        });
    }

</code>