{"id":1585,"date":"2020-08-22T10:11:18","date_gmt":"2020-08-22T08:11:18","guid":{"rendered":"http:\/\/blogperso.union31.fr\/?p=1585"},"modified":"2020-08-23T10:06:11","modified_gmt":"2020-08-23T08:06:11","slug":"node-js-modules-externes-bdd","status":"publish","type":"post","link":"https:\/\/blogperso.union31.fr\/?p=1585","title":{"rendered":"Node.js : modules externes Bases De Donn\u00e9es"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Sommaire<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#I_Base_de_donnees_Mysql\" >I Base de donn\u00e9es Mysql<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#I1_Introduction\" >I.1 Introduction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#I2_Exemples\" >I.2 Exemples<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#I21_Exemple_avec_le_module_Mysql_un_select_puis_renvoi_json_de_la_requete\" >I.2.1 Exemple avec le module Mysql : un select puis renvoi json de la requete<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#I22_Exemple_avec_le_module_Mysql2_un_select_puis_renvoi_json_de_la_requete\" >I.2.2 Exemple avec le module Mysql2 : un select puis renvoi json de la requ\u00eate<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#II_Base_de_donnees_MongoDb\" >II Base de donn\u00e9es MongoDb<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#II1_Introduction\" >II.1 Introduction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#II2_Exemples\" >II.2 Exemples<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blogperso.union31.fr\/?p=1585\/#II21_Module_Mongodb_selection_dun_jeu_de_donnees\" >II.2.1 Module Mongodb : s\u00e9lection d&rsquo;un jeu de donn\u00e9es<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"I_Base_de_donnees_Mysql\"><\/span>I Base de donn\u00e9es Mysql<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"I1_Introduction\"><\/span>I.1 Introduction<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Il existe plusieurs modules permettant de faire des requ\u00eates SQL sur un serveur Mysql.<\/p>\n\n\n\n<p>Ci-dessous une liste de modules les plus populaires :<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table class=\"has-subtle-light-gray-background-color has-background\"><tbody><tr><td>mysql<\/td><td>Le plus connu<\/td><td><a href=\"https:\/\/www.npmjs.com\/package\/mysql\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.npmjs.com\/package\/mysql<\/a><\/td><\/tr><tr><td>mysql2<\/td><td>Se veut ax\u00e9 sur la performance et utilise les promises<\/td><td><a href=\"https:\/\/www.npmjs.com\/package\/mysql2\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.npmjs.com\/package\/mysql2<\/a><\/td><\/tr><tr><td><\/td><td><\/td><td><\/td><\/tr><tr><td><\/td><td><\/td><td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"I2_Exemples\"><\/span>I.2 Exemples<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Bien lire les exemples fournit sur le site NPm du module mysql avant.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"I21_Exemple_avec_le_module_Mysql_un_select_puis_renvoi_json_de_la_requete\"><\/span>I.2.1 Exemple avec le module Mysql : un select puis renvoi json de la requete<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>Ce premier exemple se veut \u00ab\u00a0asynchrone\u00a0\u00bb de l&rsquo;ouverture de la requ\u00eate \u00e0 la fin de la requ\u00eate. Il g\u00e8re \u00e9galement le retour d&rsquo;erreur.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\nconst express \t\t= require('express')\nconst mysql \t\t= require(\"mysql\")\n\nconst app = express()  \t\t\/\/ Instancie l'application express\napp.set('view engine', 'ejs'); \t\/\/ associe le moteur de template Ejs \u00e0 Express\nconst port = 8080\n\n\nconst paramsBdd = {\n  host     : 'localhost',\n  user     : 'root',\n  password : '',\n  database : 'open_food'\n}\n\n\n\/\/ Accueil\napp.get(\"\/\",(req,res)=> {\n\n\tlet requete = 'SELECT * FROM `oo_additifs` where commentaire like \"%interdit%\"'\n\tget_donnees(paramsBdd,requete, (error,requete,results)=> {\n\t\tif (error) {\n\t\t\t\/\/ Affiche erreur\n\t\t\tres.send(\"Erreur BDD :\" + error) \/\/ ne pas faire en prod :)\n\t\t} else {\n\t\t\t\/\/ Affiche r\u00e9sultat requete sous forme json\n\t\t\tres.json(results)\n\t\t}\n\t})\n\n})\n\n\/\/ Ecoute serveur\napp.listen(port, () => {\n  console.log(`Ecoute sur le port:${port} avec Express`)\n})\n\n\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\/\/  --> event callback(error,requete,results)\n\nfunction get_donnees(params,requete,callback) {\n\n\tvar connection = mysql.createConnection(params);\n\n\tconnection.connect((err)=> {\n\t\tif (err) {\n\t\t    console.error('error connexion MYSQL  : ' + err.stack)\n\t\t    \/\/ --> renvoie erreur\n\t\t    callback(error=err,undefined,undefined)\n\t\t    return\n\t\t}\n\n\t\tconsole.log('Connexion MYSQL OK as id ' + connection.threadId)\n\t\tconnection.query( requete, (error, results, fields)=> {\n\t\t\tif (error) {\n\t\t\t\t\/\/ --> renvoie erreur\n\t\t\t\tcallback(error=error,undefined,undefined)\n\t\t\t\treturn\n\t\t\t} \n\t\t \t\/\/ --> renvoie le r\u00e9sultat de la requ\u00eate\n\t\t \tcallback(error = undefined,requete = requete,res = results)\n\t\t \tconsole.log('Requete : ', requete)\n\t\t \t\n\t\t\t\/\/ ferme la connexion\n\t\t\tconnection.end((error)=>{\n\t\t\t\t  if (error) {\n\t\t\t\t  \tconsole.error('Error end mysql : ' + error)\n\t\t\t\t  } \n\t\t\t});\n\t\t});\n\t});\n}\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"I22_Exemple_avec_le_module_Mysql2_un_select_puis_renvoi_json_de_la_requete\"><\/span>I.2.2 Exemple avec le module Mysql2 : un select puis renvoi json de la requ\u00eate<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p><strong>Pour info <\/strong>:  le m\u00eame code de l&rsquo;exemple ci-dessus fonctionne avec  le module mysql2. Ainsi pour la documentation il faut se r\u00e9f\u00e9rer au module \u00ab\u00a0mysql\u00a0\u00bb pour Node<\/p>\n\n\n\n<p>Le code ci-dessous montre l&rsquo;utilisation de ce module en utilisant les promises. Seule le contenu de la fonction \u00ab\u00a0get_donnees\u00a0\u00bb change.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>const express \t\t= require('express')\nconst path \t\t\t= require('path')\nconst bodyParser \t= require('body-parser')\nconst mysql \t\t= require(\"mysql2\/promise\")\n\nconst app = express()  \t\t\t\/\/ Instancie l'application express\napp.set('view engine', 'ejs'); \t\/\/ associe le moteur de template Ejs \u00e0 Express\nconst port = 8080\n\n\nconst paramsBdd = {\n  host     : 'localhost',\n  user     : 'root',\n  password : '',\n  database : 'open_food'\n}\n\n\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\/\/ Enregistrement Middleware Express\n\n\/\/ enregistrement du middleware bodyparser\napp.use(bodyParser.json()); \/\/ support json encoded bodies\napp.use(bodyParser.urlencoded({ extended: true })); \/\/ support encoded bodies\n\n\n\/\/ Accueil\napp.get(\"\/\",(req,res)=> {\n\n\tlet requete = 'SELECT * FROM `oo_additifs` where commentaire like \"%interdit%\"'\n\t\n\tget_donnees(paramsBdd,requete, (error,requete,results)=> {\n\t\tif (error) {\n\t\t\t\/\/ Affiche erreur\n\t\t\tres.send(\"Erreur BDD :\" + error) \/\/ ne pas faire en prod ;)\n\t\t} else {\n\t\t\t\/\/ Affiche r\u00e9sultat requete sous forme json\n\t\t\tres.json(results&#91;0])\n\t\t}\n\t})\n\n})\n\n\/\/ Ecoute serveur\napp.listen(port, () => {\n  console.log(`Ecoute sur le port:${port} avec Express`)\n})\n\n\n\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\/\/  \nasync function get_donnees(params,requete,callback) {\n\n    try{\n\t\/\/ create the connection\n\tconst connection = await mysql.createConnection(params)\n\t\/\/ query database\n\tconst tab_resultat = await connection.execute(requete);\n        \/\/ callback pour renvoyer le r\u00e9sultat\n\tcallback (null,requete,tab_resultat)\n    } catch(err) {\n        \/\/ si erreur callback envoy\u00e9 avec l'erreur\n    \tcallback(err,undefined,undefined)\n    }\n\n}<\/code><\/pre>\n\n\n\n<p>Plus simple \u00e0 \u00e9crire et \u00e0 lire !!!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"II_Base_de_donnees_MongoDb\"><\/span>II Base de donn\u00e9es MongoDb<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"II1_Introduction\"><\/span>II.1 Introduction<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Comme pour Mysql, il existe plusieurs modules pour se connecter sur une base de donn\u00e9es NOSQL MongoDB :<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table class=\"has-subtle-light-gray-background-color has-background\"><tbody><tr><td>mongodb<\/td><td>Le module officiel<\/td><td><a href=\"https:\/\/www.npmjs.com\/package\/mongodb\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.npmjs.com\/package\/mongodb<\/a><\/td><\/tr><tr><td>mongoose<\/td><td>Apporte l&rsquo;utilisation sch\u00e9ma et mod\u00e8le, des casting, etc. <\/td><td><a href=\"https:\/\/www.npmjs.com\/package\/mongoose\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.npmjs.com\/package\/mongoose<\/a><br><a href=\"https:\/\/mongoosejs.com\/\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/mongoosejs.com\/<\/a><\/td><\/tr><tr><td>Mongojs<\/td><td>Se veut simple \u00e0 utiliser &#8230;<\/td><td><a href=\"https:\/\/www.npmjs.com\/package\/mongojs\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.npmjs.com\/package\/mongojs<\/a><\/td><\/tr><tr><td><\/td><td><\/td><td><\/td><\/tr><tr><td><\/td><td><\/td><td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"II2_Exemples\"><\/span>II.2 Exemples<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"II21_Module_Mongodb_selection_dun_jeu_de_donnees\"><\/span>II.2.1 Module Mongodb : s\u00e9lection d&rsquo;un jeu de donn\u00e9es<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>Ce exemple montre comment interroger une base de donn\u00e9es dans MongoDb et r\u00e9cup\u00e9rer un \u00e9l\u00e9ment :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n\nconst express \t\t= require('express')\nconst MongoClient \t= require('mongodb').MongoClient;\n\nconst app = express()  \t\t\t\/\/ Instancie l'application express\nconst port = 8080\n\n\n\/\/ Accueil\napp.get(\"\/\",(req,res)=> {\n\n\t\n\tget_donnees( (error,results)=> {\n\t\tif (error) {\n\t\t\t\/\/ Affiche erreur\n\t\t\tres.send(\"Erreur BDD :\" + error) \/\/ ne pas faire en prod ;)\n\t\t} else {\n\t\t\t\/\/ Affiche r\u00e9sultat requete sous forme json\n\t\t\tres.json(results)\n\t\t}\n\t})\n})\n\n\/\/ Ecoute serveur\napp.listen(port, () => {\n  console.log(`Ecoute sur le port:${port} avec Express !!!`)\n})\n\n\n\n\/*\n*\t\n*\/\nfunction get_donnees(callback) {\n\n\t\/\/ Connection URL\n\tconst url = 'mongodb:\/\/localhost:27017';\n\t\/\/ Database Name\n\tconst dbName = 'open_food_facts';\n\n\t\/\/Connexion au serveur\n\tMongoClient.connect(url,{'useUnifiedTopology' : 'true'}, function(err, client) {\n\t  if (err) {\n\t  \t\t\/\/ erreur : on renvoi via callback\n\t  \t\tcallback(err,undefined)\n\t  } else {\n\t\t  \/\/ se connecte \u00e0 la base de donn\u00e9es\n\t\t  const db = client.db(dbName);\n  \t\t  \/\/ Se place sur la collection \"produit\"\n  \t\t  const collection = db.collection('produit');\n  \t\t  \/\/ Effectue une recherche\n  \t\t  collection.find({'_id' : '000000001124'}).toArray(function(err, docs) {\n\t\t\t if (err) {\n\t\t\t \t\/\/ erreur : on renvoie\n\t\t\t \tcallback(err,undefined)\n\t\t\t } else {\n\t\t\t \t\/\/ produit trouv\u00e9 : on renvoie\n\t\t\t\tcallback(undefined,docs)\n\t\t\t\t\/\/ ferme la connection\n\t\t\t\tclient.close();\n\t\t\t }\n\t\t  });\n\t  }\t\n\t});\n\n}\n\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I Base de donn\u00e9es Mysql I.1 Introduction Il existe plusieurs modules permettant de faire des requ\u00eates SQL sur un serveur Mysql. Ci-dessous une liste de modules les plus populaires : mysql Le plus connu https:\/\/www.npmjs.com\/package\/mysql mysql2 Se veut ax\u00e9 sur<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1585","post","type-post","status-publish","format-standard","hentry","category-_dev"],"_links":{"self":[{"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=\/wp\/v2\/posts\/1585","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1585"}],"version-history":[{"count":33,"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=\/wp\/v2\/posts\/1585\/revisions"}],"predecessor-version":[{"id":1622,"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=\/wp\/v2\/posts\/1585\/revisions\/1622"}],"wp:attachment":[{"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogperso.union31.fr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}