Express - MariaDB adatbázis

• Szerző: Sallai András
• Copyright © 2023, Sallai András
• Szerkesztve: 2024
• Licenc: CC Attribution-Share Alike 4.0 International
• Web: https://szit.hu

mysql

pnpm install mariadb

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
}

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
        });
    },

Küldjünk egy HTTP klienssel egy felhasználót:

http post localhost:8000/api/employees 
name='aaaa' city='Szeged' salary=357

Indítsuk el a MariaDB-t:

mysql

Megnézhetjük a létező adatbázisokat:

> show databases;

Kapcsolódjunk az emp adatbázishoz:

> use emp

Nézzük meg milyen kollekcióink vannak:

> show tables;

> select * from employees;

A kimenet ehhez hasonló kell legyen:

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'
            });                
        })
    },

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'
        });
    },

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'
        });
    }