شرح انشاء API لتطبيق store book باستخدام sql في NodeJS
في هذا الجزء سوف نشرح معكم كيف تقوم بعمل api لتطبيق متجر كتب بسيط جدا باستخدام sql ويمكنك استخدام الدرس السابق حتى تقوم بعمل الجداول التي سوف تعمل عليها وايضا تهيئة سيرفر محلي يتم تخزين به البيانات في جهازك والامر بسيط جدا كما شرحنا لكم في الدرس السابق وفي هذا الدرس سوف يكون استكمالا للدرس السابق ولكن عليك بتثبيت بعض المكتبات الضروريه للعمل والتي نقدمها لكم بالاسفل حاول ان تقوم بتثبيتها في جهازك حتى تبدء العمل بدون اي مشاكل وانشاء apis .
add packages:
"dependencies": {
"babel-polyfill": "^6.26.0",
"body-parser": "^1.20.1",
"cors": "^2.8.5",
"dotenv": "^16.0.3",
"express": "^4.18.2",
"memorystorage": "^0.12.0",
"pg": "^8.8.0",
"randomstring": "^1.2.3",
"sequential-ids": "^0.0.0"
}
للحصول على كود select او الاضافة او الحذف او غيره من الجدول الخاص بك يمكنك استخدام الامر التالي وهو الانتقال الى الجدول ومن ثم عمل generate sql واختر العمليه التي ترغب بها .
هنا قمنا بعمل تحديد للعناصر سوف يخرج لك كود يمكنك نسخه وبعدها لصقه هنا في المحرر حتى تقوم بتهيئته بشكل صحيح وبعدها يمكنك ان تكمل وهنا قمنا بتعديل شكل الكود وايضا تحويله الى upper case .
Application structuring
هذا هو شكل الملفات والهيكلة التي سوف نتبعها في تكوين التطبيق الخاص بنا
BD Folder
هذا الملف يكون المسؤول عن التعامل مع ملفات sql في اول ملف والخاص بال pool وبداخله تخبر التطبيق الخاص بك بملفات الpostgres التي قمنا بعملها في الدرس السابق بداخل .env
dbQuery وهذا يساعدك في الحصول على البيانات sql من query بشكل مباشر دون الحاجه لكتابة الكود في كل مره وتستطيع الحصول على بيانات او الحصول وارسال بيانات
queries هذا الكود المسؤول عن التعامل مع الاكواد من postgres كل ما تحتاجه هو الانتقال الى الجدول ومن ثم عمل تحديد للعنصر الذي ترغب به من select , delete , update وغيره .
db.folder
// # ------ connection.js ------------------------------------------------
var pool = require('./pool');
// تم عمل هذه الداله لتسهيل الوصول للبيانات من database
// بدلا من كتابة select * from * ... فقط نحصل على البيانات من خلال هذه الداله بشكل مباشر من ال database .
exports.dbQuery = (queryText, queryParams) => {
return new Promise((resolve , reject) => {
pool.query(queryText, queryParams)
.then(res => {
resolve(res);
})
.catch(err => {
reject(err);
})
});
}
// # ------ connection.js ------------------------------------------------
const { Pool } = require('pg')
const dotenv = require("dotenv").config()
const database_Config = {
host: process.env.host,
user: process.env.user,
database: process.env.database,
password: process.env.password,
port: process.env.port,
connectionTimeoutMillis: 300,
idleTimeoutMillis: 200,
max: 20,
}
const pool = new Pool(
database_Config
)
pool.on('connect', (client) => {
console.log("datatbase is connect")
})
pool.on('remove', (client) => {
console.log("data base is remove")
})
// # ------ queries.js ------------------------------------------------
exports.quaryList={
GET_STORE_LIST_QUARY:"SELECT store_id, store_name, store_address, store_code, created_on, created_by FROM bms.store;",
GET_STORE_CODE:"SELECT STORE_CODE FROM BMS.STORE WHERE STORE_CODE =$1;",
GET_BOOK_ID:"SELECT book_id FROM BMS.book WHERE book_id =$1;",
SAVE_STORE_QUARY:"INSERT INTO bms.store (store_name, store_address, store_code, created_on, created_by) VALUES($1, $2, $3, $4, $5);",
UPDATE_STORE_QUARY:"UPDATE bms.store SET store_name=$1, store_address=$2, store_code=$3, created_on=$4, created_by=$5 WHERE store_id=$6;",
DELETE_STORE_QUARY:"DELETE FROM bms.store WHERE store_id=$1;",
GET_BOOK_LIST_QUARY:"SELECT book_id, book_title, book_descreption, book_publisher, book_author, book_pages, store_code, created_on, created_by FROM bms.book;",
GET_BOOK_DETAILS_QUARY:"SELECT BOOK_ID, BOOK_TITLE, BOOK_DESCREPTION, BOOK_PUBLISHER, BOOK_AUTHOR, BOOK_PAGES, BOOK.STORE_CODE, STORE.STORE_NAME ,STORE.STORE_ADDRESS ,BOOK.CREATED_ON, BOOK.CREATED_BY FROM BMS.BOOK INNER JOIN BMS.STORE ON BOOK.STORE_CODE = STORE.STORE_CODE WHERE BOOK_ID =$1;",
SAVE_BOOK_QUARY:"INSERT INTO bms.book (book_title, book_descreption, book_publisher, book_author, book_pages, store_code, created_on, created_by) VALUES($1, $2, $3, $4, $5, $6, $7, $8);",
UPDATE_BOOK_QUARY:"UPDATE BMS.BOOK SET BOOK_TITLE=$1, BOOK_DESCREPTION=$2, BOOK_PUBLISHER=$3, BOOK_AUTHOR=$4, BOOK_PAGES=$5, STORE_CODE=$6, CREATED_ON=$7, CREATED_BY=$8 WHERE BOOK_ID=$9; ",
DELETE_BOOK_QUARY:"DELETE FROM bms.book WHERE book_id=$1;"
}
Models Folder
هذا الملف يعبر عن constructor الخاص بكل Model فنحن هنا نستخدم 2 واحد لل book والاخر لل store .
models.folder
// # ------ store.model.js ------------------------------------------------
exports.Store = class Store {
constructor(storeId , StoreName , code , address){
this.storeId = storeId;
this.StoreName = StoreName;
this.code = code;
this.address = address;
}
}
// # ------ book.model.js ------------------------------------------------
exports.Book = class Book {
constructor(bookId , title , isbn , description , publisher , author , pages){
this.bookId = bookId;
this.title = title;
this.isbn = isbn;
this.description = description;
this.publisher = publisher;
this.author = author;
this.pages = pages;
}
}
Route Folder
هذا الملف يستخدم عمل end point الخاصه بالتطبيق حتى تتمكن من عمل نوع البيانات والعمليه التي تحدث عند اختيار النوع وال end point بسهوله تامه .
route.js
// # ------ store.route.js ------------------------------------------------
var express = require('express');
const router = express.Router();
var cntroller = require('../controller/store.controller')
router.get("/stores" , cntroller.getStoreList);
router.post("/stores/save" , cntroller.saveStore);
module.exports = router
// # ------ book.route.js ------------------------------------------------
var express = require('express');
const router = express.Router();
var bookCtrl = require('../controller/book.controller');
router.get("/books" , bookCtrl.getBookList);
router.get("/books/details/:bookId" , bookCtrl.getBookDetails);
router.post("/books/save" , bookCtrl.saveBook);
router.put("/books/update" , bookCtrl.updateBook);
router.delete("/books/delete/:bookId" , bookCtrl.deleteBook);
module.exports = router
Utils Folder
هذا الملف قمنا بعمل ملف لانشاء نمط رقمي بشكل منظم مثل 001 و 002 وهكذا والملف الاخر كان لتوليد كلمات عشوائيه من 5 احرف تحتوي على الحروف التي توجد في charset .
utils.folder
// # ------ utility.js ------------------------------------------------
var sequential = require("sequential-ids");
// generate numbers by order sort
var generator = new sequential.Generator({
digits: 3,
// format
restore: "000"
});
generator.start();
module.exports = generator
// # ------ generator.js ------------------------------------------------
var randomstring = require("randomstring");
exports.generateStoreCode = () => {
return randomstring.generate({
length: 5,
charset: 'alphabetic',
capitalization : 'uppercase'
});
}
Controller Folder
هذا الجزء هو اساس التطبيق فهنا كل شيئ يحدث ويكون باسم controller حيث يمكن تنفيذ او وظيفه من خلال هذا المكان او بمعنى اخر logic العمليه تحدث هنا .
controller.folder
// # ------ store.controller.js ------------------------------------------------
var queries = require('../db/queries');
var dbConnection = require('../db/connection');
var util = require('../utils/utility');
exports.getStoreList = async (req , res) => {
try {
// تنفيذ عملية ارجاع البيانات
var storeListQuery = queries.queryList.GET_STORE_LIST_QUERY;
// الحصول على النتيجة من قواعد البياناتا
var result = await dbConnection.dbQuery(storeListQuery);
// حتى يعرض الصفوف فقط
return res.status(200).send(JSON.stringify(result.rows));
} catch (err) {
console.log("Error : " + err);
return res.status(500).send({error : 'Failed to list store'});
}
}
exports.saveStore = async (req , res) => {
try {
var createdBy = "admin";
var createdOn = new Date();
// req.body
var storeName = req.body.storeName;
var address = req.body.address;
console.log("storeName : " + storeName + " ----- address : " + address)
if(!storeName || !address){
return res.status(500).send({ error: 'store name and address are required , can not empty' })
}
let storeCode= util.generateStoreCode();
values =[storeName , storeCode , address , createdBy , createdOn];
var saveStoreQuery = queries.queryList.SAVE_STORE_QUERY;
await dbConnection.dbQuery(saveStoreQuery , values);
return res.status(201).send("Successfully store created ");
} catch (err) {
console.log("Error : " + err);
return res.status(500).send({error : 'Failed to save store'});
}
}
// # ------ book.controller.js ------------------------------------------------
const quaries = require('../db/queries');
const dbConnection = require('../db/connection');
exports.getBookList = async (req, res) => {
try {
var BookQuary = quaries.quaryList.GET_BOOK_LIST_QUARY
var result = await dbConnection.dbQuery(BookQuary)
return res.status(200).send(JSON.stringify({ status: 200, message: "success get all data from book", data: result.rows }))
} catch (error) {
console.log(error)
return res.status(500).send({ status: 500, message: "faild to list book database" })
}
}
exports.getBookDetails = async (req, res) => {
try {
var id = req.params.id
var BookQuary = quaries.quaryList.GET_BOOK_DETAILS_QUARY
var result = await dbConnection.dbQuery(BookQuary, [id])
return res.status(200).send(JSON.stringify({ status: 200, message: "success get Details data from book", data: result.rows }))
} catch (error) {
return res.status(500).send({ status: 500, message: "faild to list book database" })
}
}
exports.saveBook = async (req, res) => {
try {
var bookTitle = req.body.title
var bookDescreption = req.body.descreption
var bookPublisher = req.body.publisher
var bookAuthor = req.body.author
var bookPages = req.body.pages
var storeCode = req.body.storeCode
var createdOn = new Date();
var createdBy = "admin"
if (!bookTitle || !bookPublisher || !bookAuthor || !storeCode) {
return res.status(422).send({ status: 422, message: "book Publisher ,book Author, store Code ,and book Title are require " })
}
try {
var storeQuary = quaries.quaryList.GET_STORE_CODE
var result = await dbConnection.dbQuery(storeQuary,[storeCode])
if(result.rows.length===0){
return res.status(422).send({ status: 422, message: "sore code not valiade" })
}
} catch (error) {
return res.status(422).send({ status: 422, message: "faild to get store code from database " })
}
values = [bookTitle, bookDescreption, bookPublisher, bookAuthor, bookPages, storeCode, createdOn, createdBy]
var addStoreQuary = quaries.quaryList.SAVE_BOOK_QUARY
await dbConnection.dbQuery(addStoreQuary, values)
return res.status(200).send(JSON.stringify({ status: 200, message: "success to add book" }))
} catch (error) {
console.log(error)
return res.status(500).send({ status: 500, message: "faild to add book database" })
}
}
exports.updateBook = async (req, res) => {
try {
var bookTitle = req.body.title
var bookDescreption = req.body.descreption
var bookPublisher = req.body.publisher
var bookAuthor = req.body.author
var bookPages = req.body.pages
var bookId = req.body.bookId
var storeCode = req.body.storeCode
var createdOn = new Date();
var createdBy = "admin"
if (!bookId||!bookTitle || !bookPublisher || !bookAuthor || !storeCode) {
return res.status(422).send({ status: 422, message: "book id, book Publisher ,book Author, store Code ,and book Title are require " })
}
try {
var storeQuary = quaries.quaryList.GET_BOOK_ID
var result = await dbConnection.dbQuery(storeQuary,[bookId])
if(result.rows.length===0){
return res.status(422).send({ status: 422, message: "id not valiade" })
}
} catch (error) {
return res.status(422).send({ status: 422, message: "faild to get id from database " })
}
values = [bookTitle, bookDescreption, bookPublisher, bookAuthor, bookPages, storeCode, createdOn, createdBy,bookId]
var updateStoreQuary = quaries.quaryList.UPDATE_BOOK_QUARY
await dbConnection.dbQuery(updateStoreQuary, values)
return res.status(200).send(JSON.stringify({ status: 200, message: "success to update book" }))
} catch (error) {
console.log(error)
return res.status(500).send({ status: 500, message: "faild to update book database" })
}
}
exports.deleteBook = async (req, res) => {
try {
var bookId = req.params.id
if (!bookId) {
return res.status(422).send({ status: 422, message: "book id are require " })
}
try {
var storeQuary = quaries.quaryList.GET_BOOK_ID
var result = await dbConnection.dbQuery(storeQuary,[bookId])
if(result.rows.length===0){
return res.status(422).send({ status: 422, message: "id not valiade" })
}
} catch (error) {
console.log(error)
return res.status(422).send({ status: 422, message: "faild to get id from database " })
}
var deleteStoreQuary = quaries.quaryList.DELETE_BOOK_QUARY
await dbConnection.dbQuery(deleteStoreQuary, [bookId])
return res.status(200).send(JSON.stringify({ status: 200, message: "success to delete book" }))
} catch (error) {
console.log(error)
return res.status(500).send({ status: 500, message: "faild to delete book database" })
}
}
