Comment implémenter le NoSQL dans PostgreSQL avec SQLAlchemy et Python ?

Lors des dernières évolutions d’une application que nous maintenons, le client souhaitait pouvoir gérer différents formulaires évolutifs sans nécessiter de nouvelles livraisons de l’application. La solution devait lui permettre de créer et modifier ces formulaires directement depuis une interface Web d’administration, tout en diffusant facilement les modèles mis à jour aux utilisateurs. C’est dans ce contexte que nous avons exploré l’intégration de fonctionnalités NoSQL dans PostgreSQL en utilisant Python et SQLAlchemy.

Pourquoi choisir NoSQL avec PostgreSQL ?

Le défi majeur de cette évolution était de trouver une solution qui permette à nos algorithmes de traiter et stocker une structure de données variable tout en appliquant des contraintes d’intégrité aux données saisies par les utilisateurs.

La solution que nous avons décidé de mettre en place est d’utiliser une structure de données commune entre frontend et backend, qui est capable de déclarer des contraintes de validation. Cette structure est gérée en respectant le format Components Object du standard openapi.

Niveau stockage, nous avions plusieurs possibilités pour enregistrer les formulaires et les données saisies par les utilisateurs :

• Enregistrer les données sur disque au format JSON
• Utiliser une base de données NoSQL comme MongoDB
• Utiliser le type JSON ou JSONB supporté par PostgreSQL

Même si MongoDB semblait initialement le choix le plus adapté, nous avons opté pour PostgreSQL afin de tirer parti des compétences de l’équipe actuelle et de sa haute disponibilité. En effet, la haute disponibilité du serveur PostgreSQL est assurée par la DSI de l’entreprise, ce qui nous évite de maintenir nous même un cluster MongoDB.

Proof of concept de la solution NoSQL avec PostgreSQL et SQLAlchemy

Maintenant que la solution est présentée, nous allons nous concentrer sur l’implémentation de la solution pour notre backend développé avec Python.

Monter un serveur PostgreSQL

Si vous n’avez pas de serveur PostgreSQL à disposition, je vous propose de monter votre environnement avec Docker Compose.

Le Compose file suivant nous permet de déclarer une serveur PostgreSQL avec une base de données application accessibles sur le port 5432 de la machine. Il déclare également un serveur Web pgAdmin nous permettant de gérer la base de données depuis un navigateur Web.

version: "3.9"

services:
  postgres:
    image: 'postgres:16'
    ports:
      - '5432:5432'
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: application
  pgadmin:
    image: dpage/pgadmin4
    depends_on:
      - postgres
    ports:
      - "9080:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@domain.com
      PGADMIN_DEFAULT_PASSWORD: admin

Pour lancer les conteneurs, il ne nous reste qu’à exécuter la commande suivante :

$ docker compose up -d

Création du schéma

Pour initialiser le schéma de la base, nous allons nous connecter à pgAdmin, ou utiliser le client en ligne de commande psql. Pour notre exemple, nous n’allons créer qu’une table « nosql » avec le champ « jdata » déclaré en tant que type JSON. La requête SQL est la suivante :

CREATE TABLE nosql (
    id SERIAL PRIMARY KEY,
    jdata JSON
)

Tester le client psycopg2

Avant d’implémenter la solution avec SQLAlchemy, nous allons tester quelques requêtes avec le client psycopg2.

Installer psycopg2

La première étape consiste à installer le package Python :

$ python -m venv venv
$ . venv/bin/activate # <1>
$ pip install psycopg2-binary
Collecting psycopg2-binary
...
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.9.9

Ajouter des enregistrements et filtrer des données JSON

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import json
import pprint
import random

import psycopg2


if __name__ == "__main__":
    with psycopg2.connect("dbname=application user=postgres password=postgres host=localhost port=5432") as conn:
        with conn.cursor() as cur:
            # Nettoyage des données
            cur.execute("DELETE FROM nosql WHERE true")

            # Ajout de données aléatoires
            for i in range(10):
                cur.execute("""
                INSERT INTO nosql(jdata)
                VALUES (%s)
                """, (json.dumps({'name': random.choice(['foo', 'bar', 'baz']), 'id': i}),))

            # Sélection des données
            cur.execute("""
            SELECT id, jdata
            FROM nosql
            WHERE jdata ->> 'name' IN ('foo', 'bar')
                AND CAST(jdata ->> 'id' AS INTEGER) % 2 = 0
            """)

            pprint.pprint([{'id': id_, 'data': jdata} for id_, jdata in cur.fetchall()])

Ce script affiche le résultat suivant :

$ python main.py
[{'data': {'id': 0, 'name': 'foo'}, 'id': 31},
 {'data': {'id': 2, 'name': 'foo'}, 'id': 33},
 {'data': {'id': 4, 'name': 'foo'}, 'id': 35},
 {'data': {'id': 6, 'name': 'foo'}, 'id': 37}]

Cet exemple injecte des données aléatoires en fournissant un objet JSON sérialisé sous forme de str. En revanche, lors de leur extraction, psycopg2 parse automatiquement les données JSON en nous retournant l’objet sous forme de dict.

Comme vous pouvez le constater, PostgreSQL nous permet d’utiliser des clauses WHERE complexes pour évaluer les données au sein de l’objet JSON. Cela signifie qu’il est possible de s’approcher du fonctionnement des queries MongoDB en utilisant un serveur de base de données initialement conçu pour le traitement de données structurées.

Implémentation avec SQLAlchemy

Installation de SQLAlchemy

Passons maintenant à l’implémentation de la solution en utilisant les fonctionnalités de l’ORM SQLAlchemy. Nous commençons par installer le package via pip.

$ pip install SQLAlchemy
Collecting SQLAlchemy
...
Installing collected packages: typing-extensions, greenlet, SQLAlchemy
Successfully installed SQLAlchemy-2.0.31 greenlet-3.0.3 typing-extensions-4.12.2

Gestion des données JSON grâce à SQLAlchemy

Le script suivant utilise les fonctionnalités de l’ORM SQLAlchemy et a pour objectif de reproduire le comportant du script de test utilisant psycopg2.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pprint
import random

from sqlalchemy import (
    create_engine,
    and_,
    Column,
    Integer,
)
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy.orm import (
    declarative_base,
    Session,
)


# Déclaration du model

Base = declarative_base()


class NoSQL(Base):

    __tablename__ = "nosql"

    id = Column(Integer, primary_key=True)
    jdata = Column(JSON)


# Main
if __name__ == "__main__":
    engine = create_engine('postgresql://postgres:postgres@localhost:5432/application')

    with Session(engine) as session:
        # Nettoyage des données
        session.query(NoSQL).delete()

        # Ajout de données aléatoires
        for i in range(10):
            item = NoSQL(jdata={'name': random.choice(['foo', 'bar', 'baz']), 'id': i})

            session.add(item)
        session.commit()

        # Sélection des données
        query = session.query(NoSQL).filter(
            and_(
                NoSQL.jdata['name'].astext.in_(('foo', 'bar')),
                NoSQL.jdata['id'].astext.cast(Integer) % 2 == 0,
            )
        )

        pprint.pprint([{'id': item.id, 'data': item.jdata} for item in query.all()])

Je considère que vous avez maîtrisez déjà les bases de l’ORM SQLAlchemy. Je vous présente seulement les nouveautés liées au type JSON de PostgreSQL.
Au niveau de l’entité NoSQL, SQLAlchemy nous permet de déclarer l’attribut jdata comme étant une Column(JSON). SQLAlchemy s’attend donc à ce que la table déclare une colonne de type JSON en base de données.

Cet attribut nous permet d’utiliser des expressions telles que NoSQL.jdata["name"] dans une expression filter afin d’accéder aux propriétés des objets JSON. Dans notre exemple, nous utilisons plus spécifiquement l’expression NoSQL.jdata['name'].astext qui permet d’utiliser l’opérateur ->> dans la requête SQL sous-jacente.

Vérifier la fiabilité de SQLAlchemy

En exécutant ce script, nous obtenons le résultat suivant dans le terminal :

$ python orm.py
[{'data': {'id': 2, 'name': 'foo'}, 'id': 73},
 {'data': {'id': 6, 'name': 'bar'}, 'id': 77},
 {'data': {'id': 8, 'name': 'foo'}, 'id': 79}]

Pour vérifier que le résultat affiché est correct, nous allons exécuter la requête PostgreSQL de l’exemple avec psycopg2 :

SELECT id, jdata
FROM nosql
WHERE jdata ->> 'name' IN ('foo', 'bar')
    AND CAST(jdata ->> 'id' AS INTEGER) % 2 = 0

Cette requête retourne les données suivantes qui sont identiques à celles retournées par la requête générée par SQLAlchemy.

Conclusion

Parfois, la meilleure solution technique n’est pas toujours la plus adaptée pour votre application. Par exemple, pour notre client, l’ajout d’un nouveau produit à l’environnement applicatif peut avoir des conséquences négatives sur la maintenabilité ou augmenter les besoins en compétences (en développement, comme pour l’exploitation). Dans ce cas, la mise en place de solutions alternatives, bien que moins adaptées, peut être justifiée si elles répondent à nos besoins.

En résumé, cette solution permet de gérer des données flexibles grâce à PostgreSQL tout en limitant les coûts et les compétences nécessaires. Cela illustre l’importance de choisir une technologie en fonction des contraintes opérationnelles et non seulement de ses caractéristiques techniques.