PHPackages debuss-a/stored-procedures - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. debuss-a/stored-procedures ActiveLibrary[Database & ORM](/categories/database) debuss-a/stored-procedures ========================== A dedicated implementation to deal with stored procedures (with PDO). 0.1.0(1mo ago)00MITPHPPHP ^8.3CI passing Since Mar 24Pushed 1mo agoCompare [ Source](https://github.com/debuss/stored-procedures)[ Packagist](https://packagist.org/packages/debuss-a/stored-procedures)[ RSS](/packages/debuss-a-stored-procedures/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (1)Dependencies (3)Versions (2)Used By (0) Stored Procedures ================= [](#stored-procedures) A PHP package that makes working with **stored procedures** clean and effortless. Built for teams where direct SQL queries are forbidden and all database access goes through stored procedures. Pass a `PDO` instance, call your procedure, get results β€” the package handles the driver-specific syntax, parameter binding, and output parameter retrieval for you. Features -------- [](#features) - 🐬 **MySQL**, 🐘 **PostgreSQL** and πŸͺŸ **SQL Server** support out of the box - Driver auto-detection from your `PDO` connection - Fluent `Procedure` builder with `input()` / `output()` / `inout()` chaining - Convenient query methods: `query`, `queryFirst`, `querySingle` + `OrDefault` variants - `OUT` and `INOUT` parameter support (driver-specific handling is abstracted away) - Immutable `ResultSet` with multi-row-set navigation, iteration, and counting - Named parameter validation to prevent SQL injection - Specific exception hierarchy for clean error handling - Requires only `ext-pdo` β€” no ORM, no query builder, no framework dependency - Tested with [Pest](https://pestphp.com) + [Mockery](https://github.com/mockery/mockery) Requirements ------------ [](#requirements) - PHP **8.3+** - `ext-pdo` - One of: `ext-pdo_mysql`, `ext-pdo_pgsql`, `ext-pdo_sqlsrv` (or `ext-pdo_dblib`) Note For PostgreSQL, version 14+ is required. Installation ------------ [](#installation) ``` composer require debuss-a/stored-procedures ``` Quick Start ----------- [](#quick-start) ``` use StoredProcedures\SqlConnection; // 1. Wrap any PDO instance $pdo = new PDO('mysql:host=127.0.0.1;dbname=demo', 'root', 'secret', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, ]); $connection = new SqlConnection($pdo); // 2. Call a stored procedure $result = $connection->query('sp_fetch_countries_by_first_letter', [ 'p_letter' => 'F', ]); // 3. Iterate results foreach ($result as $row) { echo "{$row->iso} β€” {$row->name}\n"; } ``` Usage ----- [](#usage) ### Querying [](#querying) ``` // All rows $result = $connection->query('sp_get_all_users'); $rows = $result->rows(); // First (or only) row set $count = count($result); // Row count $empty = $result->isEmpty(); // Boolean check // First row (throws NoResultsException if empty) $row = $connection->queryFirst('sp_find_user', ['p_id' => 42]); // First row or default $row = $connection->queryFirstOrDefault('sp_find_user', ['p_id' => 0], $default); // Exactly one row (throws if 0 or 2+ rows) $row = $connection->querySingle('sp_get_user_by_email', ['p_email' => 'a@b.com']); // Exactly one row or default $row = $connection->querySingleOrDefault('sp_get_user_by_email', ['p_email' => '?'], $default); ``` ### Executing (INSERT / UPDATE / DELETE) [](#executing-insert--update--delete) ``` $result = $connection->execute('sp_insert_user', [ 'p_name' => 'Alice', 'p_email' => 'alice@example.com', ]); echo $result->getAffectedRows(); // e.g. 1 ``` ### Output Parameters [](#output-parameters) #### MySQL / SQL Server [](#mysql--sql-server) OUT and INOUT parameters are retrieved automatically via the `ResultSet`: ``` use StoredProcedures\{SqlParameter, DbType, ParameterMode}; $result = $connection->execute('sp_insert_and_return_id', [ new SqlParameter('p_name', 'Alice', DbType::STRING), new SqlParameter('p_id', null, DbType::INTEGER, ParameterMode::OUT), ]); $newId = $result->getOutputValue('p_id'); ``` #### PostgreSQL [](#postgresql) PostgreSQL functions return OUT parameters as **columns in the result set** β€” no special handling needed: ``` $result = $connection->query('fn_insert_user_returning_id', [ 'p_name' => 'Alice', ]); $newId = $result->rows()[0]->p_id; // OUT column ``` ### Fluent Procedure Builder [](#fluent-procedure-builder) For a more readable syntax, use the fluent builder: ``` // Query $result = $connection ->procedure('sp_search_users') ->input('p_name', 'Alice') ->input('p_active', true, DbType::BOOLEAN) ->all(); // Execute with OUT $result = $connection ->procedure('sp_insert_user') ->input('p_name', 'Alice') ->output('p_id', DbType::INTEGER) ->all(); echo $result->getOutputValue('p_id'); // Execute with INOUT $result = $connection ->procedure('sp_lookup_country_name') ->input('p_iso', 'FR') ->inout('p_name', '', DbType::STRING, 100) ->all(); echo $result->getOutputValue('p_name'); // "France" // Shorthand for affected-rows-only calls $affected = $connection ->procedure('sp_delete_user') ->input('p_id', 42) ->execute(); // Returns int ``` ### Multi-Result-Set Procedures [](#multi-result-set-procedures) Some stored procedures return more than one `SELECT`. The `ResultSet` gives you indexed access: ``` $result = $connection->query('sp_dashboard_data'); $users = $result->rowSet(0); // First SELECT $orders = $result->rowSet(1); // Second SELECT $count = $result->rowSetCount(); // 2 $all = $result->getAllRowSets(); // Nested array // foreach always iterates the first row set foreach ($result as $row) { /* ... */ } ``` > **Note:** Multi-row-set support requires MySQL or SQL Server. PostgreSQL functions return a single result set. ### Parameter Shorthand [](#parameter-shorthand) You can pass a named associative array instead of `SqlParameter` objects β€” types are auto-detected: ``` $result = $connection->query('sp_search', [ 'p_name' => 'Alice', // DbType::STRING 'p_age' => 30, // DbType::INTEGER 'p_active' => true, // DbType::BOOLEAN 'p_score' => 9.5, // DbType::FLOAT 'p_notes' => null, // DbType::NULL ]); ``` For explicit control, use `SqlParameter` directly: ``` use StoredProcedures\{SqlParameter, DbType}; $result = $connection->query('sp_search', [ new SqlParameter('p_date', '2024-01-01', DbType::DATE), new SqlParameter('p_blob', $binary, DbType::BINARY), ]); ``` Supported Drivers ----------------- [](#supported-drivers) DriverPDO nameSyntax generatedOUT/INOUT mechanism**MySQL**`mysql``CALL \`sp`(:in, @out)``@user_variables` + `SELECT @var`**PostgreSQL**`pgsql``SELECT * FROM fn(:in)`OUT returned as result columns**SQL Server**`sqlsrv` / `dblib``{CALL [sp](:in, :out)}``PDO::PARAM_INPUT_OUTPUT` bindingThe correct driver is resolved automatically from `PDO::ATTR_DRIVER_NAME`. Exceptions ---------- [](#exceptions) All exceptions extend `StoredProcedureException` for easy catch-all handling: ExceptionThrown when`StoredProcedureException`Base class β€” invalid procedure/parameter names, numeric array keys, etc.`NoResultsException``queryFirst()` or `querySingle()` returns zero rows`MultipleResultsException``querySingle()` returns more than one row`UnsupportedDriverException`PDO driver is not `mysql`, `pgsql`, `sqlsrv`, or `dblib```` use StoredProcedures\Exception\{NoResultsException, MultipleResultsException}; try { $row = $connection->querySingle('sp_find_user', ['p_id' => 42]); } catch (NoResultsException) { // 0 rows } catch (MultipleResultsException) { // 2+ rows } ``` Testing ------- [](#testing) ``` # Run the test suite ./vendor/bin/pest --parallel # Run with mutation testing XDEBUG_MODE=coverage ./vendor/bin/pest --mutate --min=80 --parallel ``` Docker (demo databases) ----------------------- [](#docker-demo-databases) The repository includes a `docker-compose.yml` with MySQL, PostgreSQL, and SQL Server containers β€” each pre-loaded with a `countries` table, seed data, and example stored procedures/functions. ``` docker compose up -d # Run the demo scripts php docker/mysql/mysql.php php docker/pgsql/pgsql.php php docker/sqlsrv/sqlsrv.php ``` License ------- [](#license) MIT Β© Alexandre DebusschΓ¨re The package is licensed under the MIT license. See [License File](https://github.com/debuss-a/stored-procedures/blob/master/LICENSE.md) for more information. ### Health Score 35 β€” LowBetter than 80% of packages Maintenance90 Actively maintained with recent releases Popularity0 Limited adoption so far Community6 Small or concentrated contributor base Maturity38 Early-stage or recently created project Bus Factor1 Top contributor holds 100% of commits β€” single point of failure How is this calculated?**Maintenance (25%)** β€” Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** β€” Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** β€” Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** β€” Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Unknown Total 1 Last Release 46d ago ### Community Maintainers ![](https://www.gravatar.com/avatar/0e1a70117520fe10a630d61c750bbe6888d174e9903825e741470f818ee2c5d1?d=identicon)[debuss-a](/maintainers/debuss-a) --- Top Contributors [![debuss](https://avatars.githubusercontent.com/u/2537607?v=4)](https://github.com/debuss "debuss (1 commits)") --- Tags databasepdoprocedurestored ### Code Quality TestsPest Static AnalysisPHPStan Type Coverage Yes ### Embed Badge ![Health badge](/badges/debuss-a-stored-procedures/health.svg) ``` [![Health](https://phpackages.com/badges/debuss-a-stored-procedures/health.svg)](https://phpackages.com/packages/debuss-a-stored-procedures) ``` ### Alternatives [doctrine/dbal Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management. 9.7k578.4M5.6k](/packages/doctrine-dbal)[ifsnop/mysqldump-php PHP version of mysqldump cli that comes with MySQL 1.3k5.5M69](/packages/ifsnop-mysqldump-php)[nette/database πŸ’Ύ Nette Database: layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle. 5656.7M231](/packages/nette-database)[dibi/dibi Dibi is Database Abstraction Library for PHP 5013.8M120](/packages/dibi-dibi)[aura/sqlquery Object-oriented query builders for MySQL, Postgres, SQLite, and SQLServer; can be used with any database connection library. 4572.9M34](/packages/aura-sqlquery)[envms/fluentpdo FluentPDO is a quick and light PHP library for rapid query building. It features a smart join builder, which automatically creates table joins. 925511.7k13](/packages/envms-fluentpdo) PHPackages Β© 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)