🍪 🖼️ Vues SQL : les bonnes pratiques et leurs limites

Temps de lecture : 6 minutes

Hello les petits Biscuits !

Bienvenue sur la 36ème édition de Ruby Biscuit.
Vous êtes maintenant 591 abonnés 🥳

Maintenant Ruby biscuit, c’est aussi votre meilleur allié pour recruter des devs Ruby !
Si vous n’avez pas encore rejoint le club, RDV sur https://recrutement.rubybiscuit.fr

Bonne lecture.

---

Chez Capsens, j’ai récemment dû créer un dashboard affichant des données agrégées provenant de plusieurs tables : nombre de contributions, montants collectés, statut des projets, etc. La requête SQL sous-jacente était assez lourde et utilisée à plusieurs endroits de l’application. J’en ai donc profité pour tester les vues SQL, une solution qui permet d’améliorer significativement les performances de la requête.

Une vue SQL, c’est un peu comme une « fausse table » : elle ne stocke pas directement des données, mais mémorise une requête SQL complexe pour qu’on puisse l’utiliser comme si c’était une table classique. Super pratique pour éviter les répétitions et simplifier la logique métier dans vos requêtes !

Dans cet article, je vous propose de découvrir ces fameuses vues SQL, comment les utiliser (notamment avec la gem Scenic) et quelles sont leurs limites.

Les deux types de vues SQL :

Il existe deux types de vues SQL : les vues non matérialisées (classiques) et les vues matérialisées. Voyons la différence :

1. Les vues non matérialisées (classiques)

Ce sont juste des requêtes sauvegardées qui sont recalculées chaque fois que vous y accèdez.

Avantages :

• Simplifient ton code Ruby en évitant les requêtes complexes répétitives.

• Centralisent la logique SQL.

Inconvénients :

• Peuvent être lentes car recalculées à chaque consultation.

Quand choisir une vue non matérialisée ? 🚦

Vous pouvez envisager une vue non matérialisée quand :

• Les requêtes SQL sont modérément complexes et fréquemment utilisées.

• Les données doivent toujours être à jour en temps réel (par exemple, si vous affichez un stock de produits en temps réel dans un dashboard).

• Vous voulez centraliser la logique SQL afin de garder un code plus clair, cohérent et maintenable.

Exemple pratique avec Scenic, l’ami des vues SQL dans Rails 🚙

Scenic est une gem Ruby qui simplifie considérablement la gestion des views SQL dans Rails, qu'elles soient matérialisées ou non. Elle permet une intégration propre et lisible dans ton application Rails, avec une gestion native des migrations.

Créer une vue non matérialisée :

rails generate scenic:view active_users

Cette commande génère automatiquement :

• Un fichier SQL : db/views/active_users_v01.sql

• Une migration pour créer ta vue

Exemple du fichier SQL :

SELECT users.*
FROM users
WHERE users.active = true
ORDER BY users.created_at DESC;

Migration Rails générée :

class CreateActiveUsers < ActiveRecord::Migration[7.0]
  def change
    create_view :active_users
  end
end

On peut même associer un model à cette vue ! Et donc y stocker nos méthodes, scopes et même les associations !

class ActiveUser < ApplicationRecord
  self.table_name = 'active_users'

  scope :recent, -> { order(created_at: :desc) }
end

2. Les vues matérialisées

Elles sauvegardent les résultats physiquement dans la base de données et doivent être rafraîchies régulièrement ou sur demande.

Avantages :

• Beaucoup plus rapides, surtout pour les requêtes lourdes.

• Réduisent la charge sur la base de données.

Inconvénients :

• Prennent plus d’espace de stockage.

• Peuvent afficher des données légèrement périmées si elles ne sont pas rafraîchies fréquemment.

Quand choisir une vue matérialisée ? 🚦

Vous pouvez envisager une vue matérialisée quand :

• Vous avez as des requêtes très coûteuses que vous utilisez souvent (rapports, statistiques, etc.).

• Les données n’ont pas besoin d'être mises à jour en temps réel.

Exemple pratique (toujours avec Scenic 🚙) :

Créer une vue matérialisée :

rails generate scenic:view latest_orders --materialized

Cette commande génère automatiquement :

• Un fichier SQL : db/views/latest_orders_v01.sql

• Une migration pour créer ta vue

Exemple du fichier SQL :

SELECT orders.*, users.email AS user_email
FROM orders
JOIN users ON users.id = orders.user_id
ORDER BY orders.created_at DESC
LIMIT 100;

Migration Rails générée :

class CreateLatestOrders < ActiveRecord::Migration[7.0]
  def change
    create_view :latest_orders, materialized: true
  end
end

Modèle associé à cette vue :

class LatestOrder < ApplicationRecord
  self.primary_key = :id
  self.table_name = 'latest_orders'

  belongs_to :user, foreign_key: :user_id

  scope :recent, -> { order(created_at: :desc) }

  def self.refresh
    Scenic.database.refresh_materialized_view(table_name, concurrently: true)
  end
end

Important : si vous voulez rafraîchir la vue sans bloquer ton application (concurrently: true), il faut impérativement ajouter un index unique :

class AddIndexToLatestOrders < ActiveRecord::Migration[7.0]
  def change
    add_index :latest_orders, :id, unique: true
  end
end

Attention ! L’option concurrently: true exige PostgreSQL ≥ 9.4 et un index unique.

Versionner une vue SQL avec Scenic 🔄

Si vous souhaitez faire évoluer ta vue existante, Scenic permet facilement de gérer différentes versions :

rails generate scenic:view latest_orders --materialized --replace

Cette commande génère une nouvelle version du fichier SQL (latest_orders_v02.sql) ainsi qu'une migration adaptée.

Bonus : Tester tes vues SQL 🧪

Voici comment vous pouvez tester facilement votre vue SQL avec RSpec par exemple :

# spec/models/latest_order_spec.rb
require 'rails_helper'

RSpec.describe LatestOrder, type: :model do
  describe '.recent' do
    subject { described_class.recent }

    let(:user) { create(:user) }
    let!(:order1) { create(:order, user: user, created_at: 1.day.ago) }
    let!(:order2) { create(:order, user: user, created_at: 1.hour.ago) }

    it 'returns orders ordered by created_at: :desc' do
      described_class.refresh # obligatoire pour une vue matérialisée
      expect(subject.pluck(:id)).to eq(
        [order2.id, order1.id]
      )
    end
  end
end

Voilà ! Vous connaissez maintenant les bonnes pratiques des vues SQL avec Rails, comment les versionner et même comment les tester facilement. À vous de jouer avec Scenic ! 🚀

— Christopher