Installation de SQLite
SQLite est un logiciel qui permet de créer et d’intéragir avec une base de données SQL. Cette base est stockée dans un fichier, et le code qui permet d’exécuter les requêtes SQL est directement embarqué dans une librairie TypeScript.
On peut installer SQLite avec npm et l’utiliser dans un projet TypeScript: npm i sqlite3 puis npm i --save-dev @types/sqlite3.
Exécution d’une première requête SQL
Dans le fichier index.ts, on va initialiser une base de données SQLite qui sera stockée dans db.sqlite, puis exécuter une première requête SELECT:
Cette première requête SQL va récupérer un entier aléatoire avec la fonction SQL RANDOM()
import { Database } from 'sqlite3'; // Ouverture de la base de données depuis le fichier `db.sqlite` const db = new Database('db.sqlite'); // Récupération d'un entier aléatoire entre -99 et +99 db.get( 'SELECT RANDOM() % 100 as result', (_, res) => console.log(res) );
Avec cette requête, on a pu tester que la base de données SQLite a été créée avec succès et permet l’exécution de requêtes SQL ainsi que la récupération de leur résultat.
Création d’un schéma de base de données
L’objectif de notre base va être de stocker les articles publiés sur un blog. Pour cela, on va créer une table dans notre base de données avec les colonnes nécessaires à la représentation d’un article en base:
CREATE TABLE IF NOT EXISTS articles ( id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(200) NOT NULL, description TEXT NOT NULL )
La table articles comporte trois colonnes:
- id (identifiant unique) est un entier unique et la clef primaire de la table
- titre de l’article, une chaîne de 200 caractères au maximum
- la description de l’article
On stocke le schéma de la table articles dans le fichier sql/articles.sql, puis on utilise TypeScript pour lancer cette requête:
import fs from 'fs'; // Création de la table articles db.exec(fs.readFileSync(__dirname + '/sql/articles.sql').toString());
SQLite fournit une documentation complète des standards SQL qu’elle implémente, notamment la création d’une table.
Insertion de données dans la base
Maintenant qu’on a créé le schéma de la base de données, on va insérer quelques lignes pour tester SQLite. Chaque ligne correspond à un article.
INSERT OR REPLACE INTO articles VALUES (1, 'Premier article', 'Neque porro quisquam est qui'), (2, 'Second article', 'ipsum quia dolor sit amet'), (3, 'Dernier article', 'dolorem consectetur, adipisci velit')
Le mot clef OR REPLACE permet de traiter le cas où un des identifiants d’articles qu’on veut insérer (ici 1, 2 et 3) sont déjà présents dans la base. Le mot clef OR REPLACE demande à SQLite de remplacer un éventuel article déjà présent par l’article que l’on souhaite insérer.
De la même façon que pour la création de la table, on stocke cette requête dans le dossier sql et on l’exécute avec TypeScript:
// Insertion d'exemples db.exec(fs.readFileSync(__dirname + '/sql/exemple-articles.sql').toString());
Lecture de données
Maintenant que trois articles ont été insérés dans db.sqlite, on va pouvoir interroger la base de données pour récupérer des informations sur les articles qu’elle contient.
Par exemple, on peut récupérer les titres des deux articles de la base dont la description est la plus longue:
// Récupération des titres des deux premiers articles db.all( 'SELECT titre FROM articles ORDER BY LENGTH(description) DESC LIMIT 2', (_, res) => console.log(res) );
la méthode all de l’objet Database de la librairie SQLite permet de récupérer chacune des deux lignes résultat dans une liste. Chaque élément du tableau sera un objet Javascript qui contiendra la sélection de la requête (ici le titre):
[
{
"titre": "Dernier article"
},
{
"titre": "Premier article"
}
]
Mise à jour des données
Une fois qu’un article est dans la base, on peut le modifier. Le titre de l’article 3 était ‘dernier article’. On peut le modifier pour que le titre de cet article soit maintenant ‘Troisième article’ avec une requête SQL UPDATE:
// Mise à jour du titre de l'article d'id 3 db.exec(`UPDATE articles SET titre='Troisième article' WHERE id=3`); // Récupération du nouveau titre de l'article d'id 3 db.get( 'SELECT titre FROM articles WHERE id=3', (_, res) => console.log(res) );
Requêtes paramétrisables
On a vu comment mettre à jour l’article d’id 3. Mais comment peut on adapter la requête pour passer un id arbitraire ?
On serait tenté de formater une chaîne de caractères WHERE id=${id}, mais cela pose un problème de sécurité et permet l’injection SQL.
Pour paramétrer une requête, on va utiliser les requêtes préparées. Une requête préparée consiste à indiquer la structure de la requête et ses paramètres au driver SQLite dans un premier temps avec db.prepare , puis de fournir les valeurs des paramètres dans un second temps.
// Mise à jour du titre de l'article d'id 3 const statement = db.prepare( `UPDATE articles SET titre='Troisième article' WHERE id=?` ) statement.run([3]);
Suppression de données
On a décidé que l’article 3 n’était pas pertinent. On peut le supprimer avec DELETE et en précisant son id dans la clause WHERE:
// Suppression du nouveau titre du troisième article const delete_statement = db.prepare( `DELETE FROM articles WHERE id=?` ) delete_statement.run([3]);
Conclusion
SQLite est une base de données SQL contenue dans un seul fichier, ce qui permet par exemple de pouvoir rapidement tester une base de données SQL, ou encore d’embarquer une base de données sur une machine n’ayant qu’un accès limité au réseau par exemple.
SQLite supporte la plupart des standards, fonctions et opérations SQL, dont les commandes de création de table: insertion, sélection, mise à jour, suppression.
TypeScript permet d’intéragir avec toutes sortes de bases de données, dont SQLite.
Utiliser une base de données SQLite avec TypeScript