Cosa sono le stored procedure e quando dovresti utilizzarle?

Question

Accepted Answer

Una **stored procedure** è un insieme di istruzioni SQL (e logica procedurale) salvate nel database ed eseguite per nome. Esegue la logica *all'interno* del database piuttosto che nel codice dell'applicazione. Hanno benefici reali ma anche compromessi significativi, quindi quando utilizzarle è una genuina decisione di design.

## Una stored procedure

```sql
-- define a procedure that encapsulates logic in the database
CREATE PROCEDURE transfer_funds(from_id INT, to_id INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- can include logic: conditionals, loops, error handling, transactions
END;
$$;

CALL transfer_funds(1, 2, 100);   -- execute by name
```

Una stored procedure raggruppa SQL e logica procedurale (variabili, condizionali, cicli, gestione degli errori) in una routine di database denominata e riutilizzabile. Le funzioni sono simili ma restituiscono un valore.

## Potenziali benefici

```text
✓ Performance — runs IN the database (no round-trips for multi-step logic); precompiled
✓ Reduced network traffic — one call vs many queries from the app
✓ Reusability — shared logic callable from multiple applications/clients
✓ Security — grant EXECUTE on a procedure without granting table access; reduces
  injection surface (parameterized)
✓ Consistency — centralize critical logic so all clients use the same implementation
```

## I compromessi significativi (perché molti le evitano oggi)

```text
✗ Business logic in the DATABASE → harder to version-control, test, and maintain
  (logic split between app code and the database)
✗ Database-specific (PL/pgSQL vs T-SQL vs PL/SQL) → less portable, vendor lock-in
✗ Harder to debug than application code; weaker tooling
✗ Scaling — the database is often the hardest tier to scale; pushing logic there
  can increase its load
✗ Modern apps often prefer logic in the application layer (testable, portable, versioned)
```

## Quando utilizzarle

```text
✓ Performance-critical data-intensive operations (heavy multi-step DB logic, batch jobs)
✓ Logic that MUST be enforced at the DB level across multiple clients
✓ Reducing round-trips for operations involving many queries
✗ AVOID putting general business logic in procedures by default — modern practice
  usually favors the application layer (better testing, versioning, portability)
```

## Perché è importante

Comprendere le stored procedure — inclusi i loro compromessi — è una conoscenza preziosa perché rappresentano una genuina scelta architettonica con benefici e svantaggi reali, e sapere *quando* utilizzarle (e quando no) riflette un giudizio maturo.

Le stored procedure possono offrire **benefici di performance** (eseguire logica multi-step all'interno del database evita round-trip di rete), traffico di rete ridotto, riutilizzabilità tra client, sicurezza (concedere execute senza accesso alle tabelle) e coerenza centralizzata — rendendole appropriate per operazioni critiche in termini di performance e ad alto utilizzo di dati, elaborazione batch e logica che deve essere applicata a livello di database su più applicazioni.

Tuttavia, comprendere i **compromessi significativi** è altrettanto importante e riflette la pratica moderna: mettere la logica di business nel database rende più difficile il controllo di versione, i test e la manutenzione (dividendo la logica tra app e database), ti lega al linguaggio procedurale specifico di un database (PL/pgSQL, T-SQL, PL/SQL — riducendo la portabilità), è più difficile da debugare e aggiunge carico al tier del database (spesso il più difficile da scalare).

Per questi motivi, **lo sviluppo di applicazioni moderno spesso favorisce il mantenimento della logica di business nel livello applicativo** (testabile, portabile, controllato da versione) piuttosto che nelle stored procedure.

L'insight chiave è il **giudizio di quando utilizzarle** — appropriato per esigenze specifiche critiche in termini di performance o di applicazione a livello di DB, ma generalmente evitato come default per la logica di business.

Poiché le stored procedure sono comuni in molti sistemi (soprattutto enterprise/legacy), e poiché comprendere sia i loro benefici che i loro compromessi (e la tendenza moderna verso la logica a livello di applicazione) è ciò che consente decisioni architettoniche valide, comprendere le stored procedure è una conoscenza preziosa di livello senior che dimostra la capacità di pesare la logica lato database rispetto a quella lato applicazione — una considerazione di design significativa e un argomento che riflette maturità architettonica.