NodeJS et SQLite3

SQLite3 est une bibliothèque C (la version 3 du nom) qui implémente un moteur de base de données sans serveur. Une base de donnée SQLite est implémentée par un simple fichier. A l'heure actuelle, SQLite est l'un des moteurs de base de données les plus simples à installer, à manipuler et à administrer, ce qui en fait un très bon candidat pour l'utilisation dans un site web. Pour en savoir plus sur les usages de SQLite, consulter cette page. Néanmoins, le site dit lui-même:

"Le site web SQLite utilise bien-sûr lui-même SQLite et à l'heure de l'écriture de ce texte (2015) il traite entre 400.000 et 500.000 requêtes HTTP par jour, dont 15-20% sont des pages dynamiques utilisant la base de donnée. Chaque page fait en moyenne 200 requêtes SQL. Bien que la base de donnée se trouve sur une seule machine virtuelle partageant un serveur physique avec 23 autres MV, la charge moyenne reste en dessous de 0,1 la plupart du temps."

Le fin mot de l'histoire est que SQLite est une bonne solution de base donnée dans le contexte d'une serveur web à traffic raisonnable.

Installation

Pour l'installer:

npm install sqlite3

SQLite en deux mots

En bref, une base de donnée SQL est une collection de tableaux (tables) auxquels on accède grâce à leur nom. Outre son nom, un tableau est défini par ses colonnes: à chaque colonne on associe un nom et un type. Les données du tableau consistent en les lignes du tableau.

Par exemple

$ sqlite3 test.db3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> create table message(id INT, nom TEXT, content TEXT);
sqlite> insert into message values(0,"Bob","Hello");
sqlite> insert into message values(1,"Alice","Goodbye");
sqlite> insert into message values(2,"Charlie","Good afternoon");
sqlite> select * from message; -- les commentaires sont comme ça
0|Bob|Hello
1|Alice|Goodbye
2|Charlie|Good afternoon
sqlite>

Ici, on trois commandes:

CREATE TABLE : pour créer une table. On donne le nom de la table et une liste de nom de colonnes avec leur type. Outre TEXT et INT, on peut aussi choisir FLOAT par exemple (voir la doc pour tous les choix possibles)
INSERT INTO nomdelatable VALUES(lesvaleursseparéespardesvirgules) : pour remplir la table
SELECT * FROM nomdelatable : pour lister le contenu de la table
Les commandes peuvent être en majuscule ou en minuscule
On peut faire des commentaires en commençant par "--"

Notez que les commandes se terminent par un point virgule (";").

La sélection peut aussi être filtrée. Par exemple, avec la table qui précède:

sqlite> select * from message where id = 0 ;
0|Bob|Hello
sqlite> select content from message where id = 0 ;
Hello
sqlite> select nom from message where id = 0 ;
Bob
sqlite> select nom,content from message where id = 0 ;
Bob|Hello
sqlite> select max(id) from message
2
sqlite> select * from message where id > 0 ;
1|Alice|Goodbye
2|Charlie|Good afternoon
sqlite> select * from message where nom like "%i%";
1|Alice|Goodbye
2|Charlie|Good afternoon
sqlite> select * from message where nom like "%i%" and id > 1;
2|Charlie|Good afternoon
sqlite> select * from message where nom like "%i%" and not id > 1;
1|Alice|Goodbye

Notes:

L'étoile ("*") dans le "select" sélectionne toutes les colonnes. On peut aussi mettre une ou plusieurs colonnes entre virgules.
On peut aussi mettre une fonction (comme "max") pour récupérer une information sur la liste des valeurs dans une colonne.
On peut filtrer sur des opérateurs de comparaisons sur les nombres
On peut aussi filtrer par pattern sur un champ de texte avec "like". Le symbole "%" est remplacé par n'importe quelle séquence de caractères
On peut combiner les assertions booléennes avec "and", mais aussi "or", et "not"

Outre l'insertion et les requêtes, on peut modifier une ligne dans un tableau avec update

sqlite> update message set content = "Nope" where id = 0 ;
sqlite> select * from message;
0|Bob|Nope
1|Alice|Goodbye
2|Charlie|Good afternoon

On peut aussi simplement supprimer une ou plusieurs lignes:

sqlite> delete from message where nom like "%i%";
sqlite> select * from message;
0|Bob|Nope

(ici on a supprimé deux lignes)

Finalement, on peut aussi effacer un tableau complètement avec "drop table"

sqlite> drop table message;
sqlite> select * from message;
Error: no such table: message

(Argh ! Le tableau a disparu !)

La bibliothèque sqlite3 avec Node.js

Une fois installée avec npm, on peut utiliser la librairie, comme d'habitude, en créant au début du fichier javascript un objet javascript qui va contenir toutes les fonctions:

var sq = require('sqlite3');
sq.verbose(); // pour obtenir des informations sur l'exécution des
              // requêtes SQL (utile pour le débug)

On est prêt à créer une connexion sur une base de données. Pour les besoins du tutoriel, nous allons re-créer la base de donnée du dessus qui a été malencontreusement effacée. Mais on va utiliser un nom plus malin.

Donc, dans la console, dans le répertoire du fichier javascript:

$ sqlite3 touslesmessages.db3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> create table message(id INT, nom TEXT, content TEXT);
sqlite> insert into message values(0,"Bob","Hello");
sqlite> insert into message values(1,"Alice","Goodbye");
sqlite> insert into message values(2,"Charlie","Good afternoon");

On peut maintenant rajouter dans notre fichier javascript

var db = new sq.Database(__dirname + '/touslesmessages.db3');

et on a un objet db qui est lié à la base de données.

Comment l'utiliser ? Il y a essentiellement trois méthodes utiles:

db.each("COMMANDE SQL", function(err,row) { ... })
- Exécute la commande sql
- Sur chaque ligne retournée, applique (en séquence) la fonction.
- L'objet row a pour attributs les noms de colonnes et commes valeurs... les valeurs.
db.all("COMMANDE SQL", function(err,rows) { ... })
- Pareil, mais retourne la liste des lignes et applique la fonction dessus (l'argument rows).
db.run("COMMANDE SQL")
- Pour quand on ne souhaite pas faire quelque chose après (par exemple, après une insertion ou une modification de ligne

Exemple simple

Un exemple complet (qui ne lance pas de serveur) pourrait être:

var sq = require('sqlite3');
sq.verbose(); 

var db = new sq.Database(__dirname + '/touslesmessages.db3');

db.each("SELECT * FROM message", function(err, row) {
    if (err) {
        console.log(err);
    } else {
        console.log(row.nom + " a écrit '" + row.content + "'");
    }
});

Sauvez ce programme comme "message.js" et exécutez-le avec la commande

nodejs message.js

Admirez le résultat.

Vous pouvez facilement imaginer comment demander au code de rajouter ou d'effacer une ligne dans la base de données.

Note

On peut maintenant évidemment transformer ce code en un service web avec express, et c'est l'objectif du TD6.

Avec des paramètres externes

La fonction run peut être utilisée avec des paramètres dans la commandes SQL: on met des "?" à la place de ce que l'on veut mettre, et donner en deuxième argument de run un tableau. Par exemple:

db.run("INSERT INTO message VALUES(?,?,?)", [3,"David","I am here"]);

va effectivement faire

db.run("INSERT INTO message VALUES(3,\"David\",\"I am here\")");

L'intérêt est que

vous n'avez pas besoin d'échapper les caractères problématiques: javascript le fait pour vous
la commande SQL est écrite une fois pour toute

Les autres fonctions peuvent aussi prendre des paramètres externes. Par exemple:

db.each("SELECT * FROM message WHERE id > ?", [2],
  function(err, row) {
    if (err) {
        console.log(err);
    } else {
        console.log(row.nom + "(" + row.id + ") a écrit '" + row.content + "'");
    }
  });