Node.js & SQLite

Présentation de SQLite

Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou Orale, SQLite ne reproduit pas le schéma habituel client-serveur mais est directement intégrée aux programmes.
À la différence de MySQL, il est pas nécessaire d'installer un serveur web tel WAMP sur votre PC, pour utiliser SQLite.
Une base de donnée SQLite (déclarations, tables, index et données) se traduit par un fichier fichier d'extension .bd.
Pour manipuler ce fichier on peut installer et utiliser une application : DB browser.

DB browser

DB Browser pour SQLite est un utilitaire pour créer/modifier/consulter une BD SQlite. C'est l'équivalent pour SQLite de PHPMyAdmin pour MySQL.
Si vous avez visité mon tutoriel sur Python, vous savez que le langage Python privilégie l'emploi de BD de type SQLite et vous avez lu tout le chapitre consacré à l'interface DB browser.
Afin de rafraîchir votre connaissance de SQLite et de DB browser je vous invite à relire ce chapitre : SQLite & DB browser

Grâce à DB browser, j'avais créé une BD intitulée 'animaux.db' comprenant deux tables : taxons & especes.
Une espèce appartient à un taxon (groupe d'espèces ayant un ancêtre commun) et un seul.
Ces deux tables peuvent être reliées entre elles puisqu'elles ont un champ commun nommé code.
Cette colonne commune est clé primaire dans la table "taxons" et clé étrangère dans la table "especes".
Il y a donc entre ces deux tables une contrainte d'intégrité référentielle : on ne peut saisir dans le champ "especes.code" que des valeurs qui existent dans la colonne "taxons.code".

Tout d'abord je crée dans le dossier "documents" un répertoire nommé "node_sqlite".
Dans ce nouveau dossier je copie la base de type SQLite "animaux.db" évoquée quelques lignes plus haut.

Nouvelle base SQLite

Tout d'abord je crée dans le dossier "documents" un répertoire nommé "node_sqlite".

Création de la nouvelle base

Ouvrez l'utilitaire DB brower et créer une nouvelle BD SQLite dans le dossier "documents\node_sqlite".
Nommez cette nouvelle base "test.db" et créez en son sein une table nommées "essai".
Avec l'éditeur de définition de table générez le code SQL suivant : CREATE TABLE essai(id INTEGER,nom VARCHAR(30), prenom VARCHAR(30))
Fermez la base !

Programmes Node pour remplir cette table

Ce programme, comme tous les programmes de ce chapitre, sera stocké dans "documents\node_sqlite".
J'ai mis "programmes" au pluriel car je vous propose plusieurs versions.

Version 1

Le code : const sqlite3 = require('sqlite3'); var db = new sqlite3.Database('test.db'); console.log("ajouts de nouveaux enr. dans la table test.essai"); var req1 ="INSERT INTO essai VALUES (1,'poulain', 'henri')" db.run (req1); var req2 ="INSERT INTO essai (nom, prenom) VALUES('Laval', 'Pierre')"; db.run(req2); var req3 ="INSERT INTO essai(nom, prenom) VALUES ('Durand','paul'), ('Dubois', 'jacques'),('Delpierre', 'marie')" db.run(req3); db.close;

Il faut bien sûr lancer le pilote "sqlite3" qu'il faut avoir installé avec npm.
L'instruction var db = new sqlite3.Database('test.db') : crée OU ouvre (si la base existe déjà) la base 'test.db'.
On peut appliquer à l'objet "db" de nombreuses méthodes : db.run(), db.close, db.each(), ...

dans "req1" je renseigne tous les champs
dans "req2" je renseigne uniquement les champs nom et prenom
dans "req3" je renseigne nom & prenom pour 3 nouvelles lignes

Surtout n'oubliez pas de fermer la base.

Le rendu :

L'exécution de ce script provoque l'insertion de 5 lignes dans la table "tsst.essai".
Remarqu : la colonne ID est vide sauf pour la première insertion.

Suite :

Ouvrez la base via DB browser et renseignez les champs ID dans la table "essai".

Version 2

Dans un véritable programme d'insertion de lignes dans une table, l'utilisateur saisit dans des variables et ce sont les contenus de ces variables qui doivent remplir les champs du nouvel enregistrement.
Cependant il faut alors prendre quelques précautions ; on ne peut argumenter directement VALUES avec les noms des variables. Nous avions déjà rencontré ce problème dans le cadre de la manipulation d'une base SQLite via Python.

Le script : const sqlite3 = require('sqlite3'); const prompt = require('prompt-sync')(); var vid = prompt("saisir l'id !"); var vnom = prompt("saisir nom !"); var vprenom = prompt("saisir prenom !"); var db = new sqlite3.Database('test.db'); console.log("ajouts d'une nouvelle ligne dans la table test.essai"); var requete ="INSERT INTO essai(id,nom,prenom) VALUES (?,?,?)"; db.run(requete,[vid,vnom,vprenom]); db.close;

Il faut activer le module "prompt-sync" pour effectuer des saisies au clavier.
Notez la syntaxe particulière de la requête SQL INSERT et de l'instruction d'exécution de cette requête.

Le rendu :

Lancez plusieurs fois ce programme mais en faisant varier id, nom et prenom.

Remarque : si dans la définition de la table "essai" nous avions défini la colonne ID en "autoincrement", le remplissage de ce champ serait automatique.

Version 3

Le script doit permettre à l'utilisateur d'ajouter autant de lignes qu'il le souhaite.
Le script (extrait) : ... console.log("tapez 999 en guise d'id pour sortir de la boucle d'ajout de ligne"); for (i =1; i&l;10;i++) { var vid = prompt("saisir l'id !"); if (vid ==999) break; var vnom = prompt("saisir nom !"); var vprenom = prompt("saisir prenom !"); var db = new sqlite3.Database('test.db'); console.log("ajouts d'une nouvelle ligne dans la table test.essai"); var requete ="INSERT INTO essai(id,nom,prenom) VALUES (?,?,?)"; db.run(requete,[vid,vnom,vprenom]); } ...

Ce script permet d'ajouter dix lignes à la table mais si l'utisateur veut en saisir moins, il lui suffit de taper 999 en guise d'ID pour quitter la boucle de saisie.

Remplir la table via un formulaire de saisie

L'idéal pour un utilisateur est de renseigner un formulaire donc avec des contrôles de saisie.
Si vous avez lu mon tutoriel sur PHP vous savez qu'il est relativement simple de créer une page d'extension .php qui ajoute une ligne après saisies des données via un formulaire HTML.
Code HTML & PHP pour ajouter une ligne à une table MySQL
Malheureusement c'est beaucoup moins simple en Node.js qu'en PHP.
La solution dans l'environnement Node.js fait appel à des notions qui n'ont pas été encore évoquées ..

Parcourir la table

J'aborde maintenant le programme Node.js pour lire la table 'essai'.

Alors qu'il faut associer la méthode db.run avec des requêtes de mise à jour de la base (INSERT, UPDATE, etc.), on associe aux requêtes SELECT trois méthodes possibles : db.each, db.all & db.get.

Le code avec la méthode "db.each"

var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('test.db');
db.each("SELECT * FROM essai", function(erreur, ligne) {
    if (erreur) console.log(erreur);
    else   console.log(ligne.nom + " " + ligne.prenom);
});
db.close;

L'instruction db.each () permet de parcourir une table/vue.
À chaque passage dans cette boucle le contenu de l'enregistrement courant est affecté à l'objet "ligne".

Le rendu

Dans la console Node, les enregistrements de la table 'test.essai' s'affichent par valeurs croissantes d'ID.

Le code avec la méthode "db.all"

Je peux utiliser la méthode db.all qui retourne la totalité de la table et la méthode db.get qui retourne la première ligne.
Le code (extrait) : ... db.all("SELECT * FROM essai", function(erreur,lignes) { if (erreur) console.log(erreur); else console.log(lignes); }); ...

Le rendu :
Toute la table est affichée sous forme d'un tableau au format JSON : { id: 1, nom: 'poulain', prenom: 'henri' }, { id: 2, nom: 'Laval', prenom: 'Pierre' }, { id: 3, nom: 'Durand', prenom: 'paul' }, { id: 4, nom: 'Dubois', prenom: 'jacques' }, { id: 5, nom: 'Delpierre', prenom: 'marie' }, { id: 15, nom: 'Gaulle', prenom: 'Charles' }, { id: 10, nom: 'darche', prenom: 'pat' }, { id: 10, nom: 'macron', prenom: 'emmanuel' }

Le code avec la méthode "db.get"

Cette méthode retourne la première ligne de la table au format JSON.
Le rendu :
{ id: 1, nom: 'poulain', prenom: 'henri' }

Retour sur la base "animaux.db"

Les scripts qui suivent concernent la base 'animaux.db' qui a déjà été présentée dans le cadre du tutoriel python.
Je duplique le fichier 'animaux.db" dans le dossier "documents\node_sqlite".

Parcourir la table 'especes'

Le code

var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('animaux.db');
db.each("SELECT id, nom_espece, code  FROM especes", function(err, ligne) {
    console.log(ligne.id + " - " + ligne.nom_espece + " " + ligne.code);
  });
db.close;

Le code est très proche de l'exemple précédent.

Le rendu

Les enregistrements de la table 'animaux.especes' s'affichent dans la console Node.

Afficher le rendu d'une requête SELECT dans le navigateur

Il serait souhaitable que le résultat d'une requête sélection s'affiche sous forme d'une jolie page web dans le navigateur.

Le programme Node

var http =require("http"); 
var sqlite3 =require("sqlite3"); 
var db = new sqlite3.Database('animaux.db');

// préparation HTML 
var HTML ="<!DOCTYPE html><html><meta charset = 'utf-8'><body style ='font-size : 20px ; width : 900px ; margin : auto; '>"; 
HTML+= "<h3 style ='text-align : center ;'>Liste des espèces</h3>"; 
HTML +="<p>"; 
var req_sql = "SELECT nom_espece  FROM especes ORDER BY nom_espece" ; 
db.each(req_sql, function(err, ligne) {
    console.log(ligne.nom_espece);
	HTML+= ligne.nom_espece + "</br>" ;
  });

console.log("tapez 'localhost:8080' dans navigateur");
db.close ; 
HTML+= "</p></body></html>"; 
var pour_navigateur = function(req,result)
{ result.writeHead(200, {'Content-Type': 'text/html'}); result.end(HTML); }

// instanciation du serveur 
var serveur = http.createServer(pour_navigateur);
serveur.listen(8080);

créer un serveur web
générer du code HTML
envoyer ce code HTML via le serveur dans le navigateur

Le rendu

Reconnaissez que l'affichage est satisfaisant et pourtant les modifications du programme sont marginales.
Si vous avez lu le chapitre précédent, vous retrouvez la même astuce pour un bel affichage dans le navigateur : générér du code HTML et le stocker dans une variable nommée HTML, retourner le contenu de cette variable au navigateur.

Parcourir une vue

Je rappelle que dans le langage SQL, une requête sélection complexe peut être sauvegardée sous forme d'une vue ('view').
Je vous conseille de lire ou relire dans le chapitre en lien ce qui conerne les vues : SQLite et DB browser

La vue4

Cet objet 'view' est construit avec la requête sélection suivante :
CREATE VIEW "vue4" AS select * from especes, taxons where especes.code = taxons.code

Il s'agit donc d'une extraction de données appartenant à deux tables : especes & taxons.
Dans cette vue on associe à chaque ligne de 'especes' une ligne de 'taxons' grâce aux champs de jointure (nommé "code" dans chaque table).
Je rappelle que la colonne "taxons.code" est clé primaire et que la colonne "especes.code est "clé étrangère".
Cette vue comprend entre autre les champs 'nom_espece' & nom_taxon respectivement des tables 'especes' et 'taxons'.

Le script

var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('animaux.db');
db.each("SELECT * from vue4", function(err, ligne) {
    console.log(ligne.nom_espece + " - " + ligne.nom_taxon);
  });
db.close;

Notez que l'on manipule la vue comme une table.

Le rendu

Affichage dans la console Node d'une liste avec pour chaque espèce le nom du taxon correspondant.

Traduction

Tutoriel sur JS natif, Vue.js, jQuery.js & Node.js

JavaScript natif

Framework Vue.js

Framework jQuery.js

Node.js

Tutoriel JavaScript - recherche

L'auteur : Patrick Darcheville

Node.js & SQLite

Présentation de SQLite

DB browser

Nouvelle base SQLite

Création de la nouvelle base

Programmes Node pour remplir cette table

Version 1

Version 2

Version 3

Remplir la table via un formulaire de saisie

Parcourir la table

Le code avec la méthode "db.each"

Le rendu

Le code avec la méthode "db.all"

Le code avec la méthode "db.get"

Retour sur la base "animaux.db"

Parcourir la table 'especes'

Le code

Le rendu

Afficher le rendu d'une requête SELECT dans le navigateur

Le programme Node

Le rendu

Parcourir une vue

La vue4

Le script

Le rendu