Stored Procedure Bundle Laravel Package

Product Decisions This Supports

• Database Abstraction for Stored Procedures: Enables standardized execution of PostgreSQL stored procedures across microservices or monolithic Symfony applications, reducing ad-hoc SQL in business logic.
• Multi-Database Support: Facilitates integration with legacy systems (e.g., admin panels, reporting tools) or polyglot persistence (e.g., API vs. analytics DBs) without duplicating connection logic.
• Build vs. Buy: Justifies not reinventing wheel for PostgreSQL stored procedure orchestration, especially if the team lacks deep pgfunc expertise.
• Performance-Critical Workloads: Ideal for batch processing, ETL pipelines, or high-frequency transactions where stored procedures are already optimized (e.g., complex aggregations, bulk updates).
• Roadmap for Database-Centric Features:
  • Phase 1: Replace raw Doctrine queries with stored procedures for read-heavy operations (e.g., dashboards).
  • Phase 2: Offload write-heavy logic (e.g., inventory updates) to the database layer.
  • Phase 3: Implement database-driven workflows (e.g., event sourcing via stored procs).

When to Consider This Package

• Avoid if:
  • Your team uses MySQL or another non-PostgreSQL database (this is PostgreSQL-only via pgfunc).
  • You need active maintenance (last release: 2018; no dependents).
  • Your use case requires complex transaction management (e.g., distributed XA transactions) beyond basic stored proc calls.
  • You’re already using Doctrine DBAL or Laravel’s Query Builder extensively for similar patterns.
  • Security concerns: The bundle lacks recent audits; validate stored procedures for SQL injection risks.
• Look elsewhere if:
  • You need multi-language support (e.g., calling stored procs from Go/Python).
  • Your database schema is highly dynamic (e.g., schema migrations require frequent stored proc updates).
  • You prefer ORM-first approaches (e.g., Doctrine entities) over procedural SQL.

How to Pitch It (Stakeholders)

For Executives: "This bundle lets us leverage PostgreSQL’s stored procedures—pre-optimized SQL logic—to accelerate performance-critical operations (e.g., reporting, batch jobs) while reducing application-layer complexity. By standardizing connections and execution, we can cut development time for database-heavy features by 30%+ and future-proof our architecture for polyglot persistence. The trade-off? Minimal upfront cost (MIT license) and a focus on PostgreSQL, which aligns with our [existing stack]."

For Engineering: *"This wraps pgfunc to let us call stored procedures from Symfony with zero boilerplate. Key wins:

• Unified connection management: Configure once in stored_procedure.yaml, reuse across services.
• PostgreSQL superpowers: Offload logic like complex joins, bulk inserts, or aggregations to the DB layer.
• Legacy integration: Seamlessly talk to admin/API databases without duplicating connection logic. Caveats: No active maintenance (but pgfunc is stable); validate stored procs for security. Let’s prototype this for [high-impact use case] first."*

For Developers: *"Imagine replacing this:

$conn = Doctrine::getConnection();
$result = $conn->executeQuery('SELECT * FROM complex_join(...)');

With this:

# config/packages/stored_procedure.yaml
stored_procedure:
    connections:
        analytics:
            dbname: '%env(ANALYTICS_DB)%'

Then in code:

$proc = $this->get('stored_procedure.manager')->getConnection('analytics');
$results = $proc->execute('complex_join', [$param1, $param2]);

No more raw SQL strings, easier testing, and DB-optimized logic. Downside: Only PostgreSQL, and the bundle is stale—but pgfunc is solid."*