Aller au contenu

Langage SQL avancé

Introduction

SQL (Structured Query Language) est un langage de programmation utilisé pour gérer et manipuler des bases de données relationnelles.

Base de données relationnelle

Les caractéristiques principales des bases de données relationnelles incluent :

  • Tables : Les données sont organisées en tables composées de lignes et de colonnes.
  • Relations : Les tables peuvent être liées entre elles par des clés primaires et étrangères.
  • Intégrité des données : Les bases de données relationnelles utilisent des contraintes pour assurer la validité et la cohérence des données.

Types de bases de données relationnelles

  • Base de données sans serveur:
    • Fichier plat: CSV, JSON, XML, SQLite, H2
    • Mémoire: H2, SQLite
  • Base de données avec serveur: MySQL, PostgreSQL, Oracle DB, SQL Server, H2

On peut observer que certaines bases de données supportent plusieurs types d'utilisation, comme H2.

SQLite

SQLite est une base de données relationnelle légère et intégrée qui ne nécessite pas de serveur pour fonctionner. Elle est souvent utilisée pour les applications mobiles, les applications de bureau et les projets de développement rapide.

Quelques spécificités de SQLite:

  • Les tables ont une clé primaire auto-incrémentée par défaut appelée rowid. Une colonne en autoincrement explicite n'est pas recommandée (source).
  • Les fichiers de base données utilisent une extension .db ou .sqlite.
  • Pas de support pour les procédures stockées, définition de fonctions (à part via du C), les vues ou les déclencheurs complexes.

PostgreSQL

PostgreSQL est une base de données relationnelle open-source puissante et robuste qui prend en charge une large gamme de fonctionnalités avancées, telles que les transactions, les vues, les procédures stockées et les types de données personnalisés.

Préparation

Ce fichier compose permet de lancer une base de données PostgreSQL et une Web UI d'administration appelée adminer. Pour le lancer, il suffit de se placer à la racine du projet et d'exécuter la commande suivante:

docker-compose up
# ou avec podman-compose
podman-compose up
compose.yml 
# Use postgres/example user/password credentials

services:
  db:
    image: postgres
    restart: always
    shm_size: 128mb
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Installer les extensions VSCode suivantes pour interagir avec la base de données: SQLTools et son driver SQLTools PostgreSQL.

spécificités de PostgreSQL

  • Support des enums (types de données énumérés) pour restreindre les valeurs possibles d'une colonne à un ensemble défini de valeurs.
  • Support du JSON et du JSONB (JSON encodé en binaire au lieu de texte) pour le stockage de données semi-structurées (peut donc remplacer une BDD NoSQL).
Définition d'une table utilisant du JSONB et d'une enum 
DROP VIEW IF EXISTS last_talk_titles;
DROP FUNCTION IF EXISTS can_login;
DROP TABLE IF EXISTS users;
DROP TYPE IF EXISTS user_status;

CREATE TYPE user_status AS ENUM (
  'not_validated',
  'validated',
  'inactive'
);

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255),
  internal_name TEXT UNIQUE,
  PASSWORD VARCHAR(100),
  status user_status,
  details JSONB
);

INSERT INTO users (email, internal_name, password, status, details) VALUES (
  'hello@email.com',
  'hello',
  'secret',
  'validated', '{
  "conferences": ["devoxx", "DevLille"],
  "lastTalk": {
    "title": "blazor Doom",
    "keywords": ["wasm", "dotnet", "JavaScript"]
  }
}');
INSERT INTO users (email, internal_name, password, status, details) VALUES (
  'toto@email.com',
  'toto',
  'secret',
  'inactive',
  '{
  "lastTalk": {
    "title": "WebAI",
    "keywords": ["Web", "AI", "JavaScript"]
  }
}');

select * from users;
Exemple de JSONB

Documentation

-- Sélection
select * from users;

select email, details->>'lastTalk' as lastTalk from users;
select details || '{"favoriteFood": "Pizza"}' from users;
-- Préciser un chemin (doubler la flèche convertir le résultat en texte)
select email, details->'lastTalk'->>'title' as lastTitle from users;
select email, details#>>'{lastTalk, title}' as lastTitle from users;
-- Accès par index
select email, details->'conferences'->>0 as firstconf from users;
select email, details#>>'{conferences, 0}' as firstconf from users;
select email, details#>>'{lastTalk, keywords, 0}' as first_keyword from users;
select email, details#>'{lastTalk, keywords}'->>0 as first_keyword from users;

-- Update

UPDATE users SET details = jsonb_set(details, '{lastTalk, duration}', '50') WHERE email = 'hello@email.com';
select email, details#>>'{lastTalk, duration}' from users;

-- WHere
SELECT email, details FROM users WHERE details->'lastTalk'@>'{"title": "WebAI"}';
SELECT email, details FROM users WHERE details->'lastTalk'->'title' ? 'WebAI';
-- Item in array
SELECT email, details FROM users WHERE details#>'{lastTalk, keywords}' ? 'wasm';
  • Support des fonctions définies par l'utilisateur pour effectuer des opérations spécifiques sur les données.
Exemple de fonction 
DROP FUNCTION IF EXISTS can_login;

CREATE FUNCTION can_login(u users)
RETURNS BOOLEAN as $$
BEGIN
  if u.status in ('inactive') THEN
    RETURN FALSE;
  END IF;
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT can_login(u.*) from users as u; 
SELECT can_login(u.*), * from users as u;
SELECT can_login(u.*), email from users as u;
select email, status from users as u where can_login(u);
  • Support des vues pour créer des tables virtuelles basées sur des requêtes SQL. Les vues sont recalculées à chaque fois qu'elles sont utilisées, ce qui permet de garantir que les données affichées sont toujours à jour.
Exemple de vue 
DROP VIEW IF EXISTS last_talk_titles;

CREATE VIEW last_talk_titles 
AS SELECT email, details#>>'{"lastTalk", "title"}' AS talk FROM users;

SELECT * FROM last_talk_titles;

PostgREST

PostgREST est un serveur web qui expose une API RESTful basée sur une base de données PostgreSQL. Il permet de créer rapidement des API pour interagir avec les données stockées dans PostgreSQL, en utilisant des requêtes SQL pour définir les endpoints et les opérations disponibles.

  • Lancer une instance de postgres.
  • Installer PostgREST en suivant ce guide
  • Créer une base de données pour accueillir les données de l'API en suivant ce guide.
BDD qui accueillera les données de l'API 
create schema api;

--  endpoint /todos
create table api.todos (
  id int primary key generated by default as identity,
  done boolean not null default false,
  task text not null,
  due timestamptz
);
insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

-- Make a role to use for anonymous web requests. When a request comes in, PostgREST will switch into this role in the database to run queries.
create role web_anon nologin;
-- The web_anon role has permission to access things in the api schema, and to read rows in the todos table.
grant usage on schema api to web_anon;
grant select on api.todos to web_anon;
-- It’s a good practice to create a dedicated role for connecting to the database:
create role authenticator noinherit login password 'mysecretpassword';
-- authenticator can switch to the web_anon role:
grant web_anon to authenticator;
  • Créer un fichier de configuration pour PostgREST (qu'on peut appeler postgrest-api.conf) avec le contenu suivant:
Configuration de PostgREST 
db-uri = "postgres://authenticator:mysecretpassword@localhost:5432/postgres"
db-schemas = "api"
db-anon-role = "web_anon"
  • Lancer le serveur PostgREST en utilisant la commande suivante postgrest postgrest-api.conf
  • Tester l'API avec un curl http://localhost:3000/todos.

Row Level Security (RLS)

Row Level Security (RLS) est une fonctionnalité de PostgreSQL qui permet de contrôler l'accès aux données au niveau des lignes d'une table. Avec RLS, les administrateurs de la base de données peuvent définir des politiques de sécurité qui restreignent l'accès aux données en fonction des rôles et des permissions des utilisateurs.

Recherche full-text

Postgres supporte la recherche full-text, qui permet de rechercher des mots ou des phrases dans des colonnes de texte. Cette fonctionnalité est similaire à ce que proposer des moteurs de recherche comme Elasticsearch, mais elle est intégrée directement dans la base de données, ce qui peut simplifier l'architecture de l'application.

Il est possible d'utiliser deux approches pour la recherche full-text dans Postgres:

  • Utiliser les opérateurs de recherche full-text intégrés, tels que to_tsvector, @@ et to_tsquery, pour effectuer des recherches sur des colonnes de texte.
  • utiliser une extension de recherche full-text tierce, telle que pg_trgm ou pg_search.

La fonctionnalité GENERATED ALWAYS AS permet de créer des colonnes générées automatiquement à partir d'autres colonnes de la table. Cela peut être utilisé pour créer une colonne de recherche full-text qui est automatiquement mise à jour chaque fois que les données de la table sont modifiées.

Alternativement, on peut aussi créer un index GIN de type tsvector sur une colonne textuelle pour améliorer les performances des recherches full-text avec tsvector.

Exemple de recherche full-text avec les opérateurs intégrés 
DROP TABLE IF EXISTS stories;

CREATE TABLE stories(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   body TEXT,
   author TEXT
);


INSERT INTO stories(title, body, author) VALUES
('PostgreSQL tsvector demo', 'This is a demo of the tsvector data type in PostgreSQL. It allows you to store and search text data efficiently.', 'John Doe'),
('Another story', 'This is another story about PostgreSQL and its features.', 'Jane Smith'),
('Full-text search', 'PostgreSQL provides powerful full-text search capabilities using tsvector and tsquery.', 'Alice Johnson');

-- Create a tsvector column for the body of the stories
SELECT to_tsvector('english', body) AS body_vector FROM stories;

-- Search for stories that contain the words 'demo' and 'tsvector'
SELECT * FROM stories WHERE to_tsvector('english', body) @@ to_tsquery('english', 'demo & tsvector');
-- Search for stories that contain either 'demo' or 'tsvector'
SELECT * FROM stories WHERE to_tsvector('english', body) @@ to_tsquery('english', 'demo | tsvector');
-- Search for stories that do not contain the word 'tsvector'
SELECT * FROM stories WHERE to_tsvector('english', body) @@ to_tsquery('english', '!tsvector');
-- Search for stories that contain the exact phrase 'demo of the tsvector'
SELECT * FROM stories WHERE to_tsvector('english', body) @@ to_tsquery('english', '''demo of the tsvector''');
-- Search for stories that contain either 'PostgreSQL' or the exact phrase 'demo of the tsvector', and also contain the word 'capabilities'
SELECT * FROM stories WHERE to_tsvector('english', body) @@ to_tsquery('english', '(PostgreSQL | ''demo of the tsvector'') & capabilities');
-- websearch_to_tsquery allows for browser-like search syntax, so we can search for stories that contain 'demo' and 'tsvector' using web search syntax
SELECT * FROM stories WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'demo tsvector');

-- Create a GIN index (index type optimized for full-text search) on the body column for faster search
CREATE INDEX idx_body_vector ON stories USING GIN (to_tsvector('english',body));

SELECT * FROM stories WHERE to_tsvector('english', body) @@ to_tsquery('english', 'demo & tsvector');

-- We can alternatively create an ALWAYS GENERATED column for the tsvector of the body
ALTER TABLE stories ADD COLUMN body_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

-- Now we can search using the generated column
SELECT * FROM stories WHERE body_vector @@ to_tsquery('english', 'demo & tsvector');

Sources:

Supabase

Supabase est une plateforme de développement d'applications qui fournit une suite d'outils pour créer des applications web et mobiles. Elle utilise PostgreSQL comme base de données principale et offre des fonctionnalités telles que l'authentification, le stockage de fichiers, les fonctions serverless et les API en temps réel.

Sources