ray / aura-sql-module
aura/sql module for Ray.Di
Installs: 328 187
Dependents: 6
Suggesters: 0
Security: 0
Stars: 8
Watchers: 5
Forks: 15
Open Issues: 0
Requires
- php: ^8.4
- ext-pdo: *
- aura/sql: ^6.0
- aura/sqlquery: ^3.0
- doctrine/annotations: ^1.11 || ^2.0
- pagerfanta/pagerfanta: ^3.5 || ^4.7
- psr/log: ^1.1 || ^2.0 || ^3.0
- ray/aop: ^2.17
- ray/di: ^2.18
- rize/uri-template: ^0.4
Requires (Dev)
- bamarni/composer-bin-plugin: ^1.4
- phpunit/phpunit: ^12.1
- 2.x-dev
- 1.x-dev
- 1.14.1
- 1.14.0
- 1.13.3
- 1.13.2
- 1.13.1
- 1.13.0
- 1.12.0
- 1.11.3
- 1.11.2
- 1.11.1
- 1.11.0
- 1.10.0
- 1.9.1
- 1.9.0
- 1.8.1
- 1.8.0
- 1.7.1
- 1.7.0
- 1.6.5
- 1.6.4
- 1.6.3
- 1.6.2
- 1.6.1
- 1.6.0
- 1.5.0
- 1.4.6
- 1.4.5
- 1.4.4
- 1.4.3
- 1.4.2
- 1.4.1
- 1.4.0
- 1.3.0
- 1.2.2
- 1.2.1
- 1.2.0
- 1.1.1
- 1.1.0
- 1.0.3
- 1.0.2
- 1.0.1
- 1.0.0
- dev-php8.4-tools
- dev-detached2
- dev-detached
- dev-php8.4
- dev-license/copyright-to-2025
- dev-dep-ver
- dev-profiler
This package is auto-updated.
Last update: 2025-05-28 12:17:07 UTC
README
Installation
composer require ray/aura-sql-module
Getting started
Module install
use Ray\Di\AbstractModule; use Ray\AuraSqlModule\AuraSqlModule; use Ray\AuraSqlModule\AuraSqlQueryModule; class AppModule extends AbstractModule { protected function configure() { $this->install( new AuraSqlModule( 'mysql:host=localhost;dbname=test', 'username', 'password', 'slave1,slave2,slave3', // optional slave server list $options, // optional key=>value array of driver-specific connection options $queries // Queries to execute after the connection. ) ); } }
Use AuraSqlEnvModule to get the value from the environment variable each time at runtime, instead of specifying the value directly.
$this->install( new AuraSqlEnvModule( 'PDO_DSN', // getenv('PDO_DSN') 'PDO_USER', // getenv('PDO_USER') 'PDO_PASSWORD', // getenv('PDO_PASSWORD') 'PDO_SLAVE', // getenv('PDO_SLAVE') $options, // optional key=>value array of driver-specific connection options $queries // Queries to execute after the connection. ) );
Replication
Installing AuraSqlReplicationModule
using a connection locator
for master/slave connections.
use Ray\Di\AbstractModule; use Ray\AuraSqlModule\AuraSqlModule; use Aura\Sql\ConnectionLocator; use Aura\Sql\Connection; class AppModule extends AbstractModule { protected function configure() { $locator = new ConnectionLocator; $locator->setWrite('master', new Connection('mysql:host=localhost;dbname=master', 'id', 'pass')); $locator->setRead('slave1', new Connection('mysql:host=localhost;dbname=slave1', 'id', 'pass')); $locator->setRead('slave2', new Connection('mysql:host=localhost;dbname=slave2', 'id', 'pass')); $this->install(new AuraSqlReplicationModule($locator)); } }
You will now have a slave db connection when using HTTP GET, or a master db connection in other HTTP methods.
Multiple DB
You may want to inject different connection destinations on the same DB interface with #[Named($qualifier)]
attribute.
Two modules are provided. NamedPdoModule
is for non replication use. and AuraSqlReplicationModule
is for replication use.
#[Inject] public function setLoggerDb(#[Named('log_db')] ExtendedPdoInterface $pdo) { // ... }
with no replication
Use NamedPdoModule
to inject different named Pdo
instance for non Replication use.
For instance, This module install log_db
named Pdo
instance.
class AppModule extends AbstractModule { protected function configure() { $this->install(new NamedPdoModule('log_db', 'mysql:host=localhost;dbname=log', 'username', 'password')); } }
Or
class AppModule extends AbstractModule { protected function configure() { $this->install(new NamedPdoEnvModule('log_db', 'LOG_DSN', 'LOG_USERNAME', 'LOG_PASSWORD')); } }
with replication
You can set $qualifier
in 2nd parameter of AuraSqlReplicationModule.
class AppModule extends AbstractModule { protected function configure() { $this->install(new AuraSqlReplicationModule($locator, 'log_db')); } }
Transaction
Any method marked with #[Transactional]
will have a transaction started before, and ended after it is called.
use Ray\AuraSqlModule\Annotation\WriteConnection; // important use Ray\AuraSqlModule\Annotation\Transactional; // important class User { public $pdo; #[WriteConnection, Transactional] public function write() { // $this->pdo->rollback(); when exception thrown. } }
Query Builder
Aura.SqlQuery provides query builders for MySQL, Postgres, SQLite, and Microsoft SQL Server. Following four interfaces are bound and can be injected via constructor:
Aura\SqlQuery\Common\SelectInterface
Aura\SqlQuery\Common\InsertInterface
Aura\SqlQuery\Common\UpdateInterface
Aura\SqlQuery\Common\DeleteInterface
use Aura\SqlQuery\Common\SelectInterface; use Aura\Sql\ExtendedPdoInterface; class UserRepository { public function __construct( private readonly SelectInterface $select, private readonly ExtendedPdoInterface $pdo ) {} public function findById(int $id): array { $statement = $this->select ->distinct() // SELECT DISTINCT ->cols([ // select these columns 'id', // column name 'name AS namecol', // one way of aliasing 'col_name' => 'col_alias', // another way of aliasing 'COUNT(foo) AS foo_count' // embed calculations directly ]) ->from('users AS u') // FROM these tables ->where('id = :id') ->getStatement(); return $this->pdo->fetchAssoc($statement, ['id' => $id]); } }
Multiple Query Builders
use Aura\SqlQuery\Common\SelectInterface; use Aura\SqlQuery\Common\InsertInterface; use Aura\SqlQuery\Common\UpdateInterface; use Aura\Sql\ExtendedPdoInterface; class UserService { public function __construct( private readonly SelectInterface $select, private readonly InsertInterface $insert, private readonly UpdateInterface $update, private readonly ExtendedPdoInterface $pdo ) {} public function createUser(array $userData): int { $statement = $this->insert ->into('users') ->cols($userData) ->getStatement(); $this->pdo->perform($statement, $this->insert->getBindValues()); return (int) $this->pdo->lastInsertId(); } public function updateUser(int $id, array $userData): bool { $statement = $this->update ->table('users') ->cols($userData) ->where('id = :id') ->bindValue('id', $id) ->getStatement(); return $this->pdo->perform($statement, $this->update->getBindValues()); } }
Pagination
Pagination service is provided for both ExtendedPdo
raw sql and Select
query builder.
ExtendedPdo
use Ray\AuraSqlModule\Pagerfanta\AuraSqlPagerFactoryInterface; use Aura\Sql\ExtendedPdoInterface; class UserListService { public function __construct( private readonly AuraSqlPagerFactoryInterface $pagerFactory, private readonly ExtendedPdoInterface $pdo ) {} public function getUserList(int $page): Page { $sql = 'SELECT * FROM users WHERE active = :active'; $params = ['active' => 1]; $pager = $this->pagerFactory->newInstance($this->pdo, $sql, $params, 10, '/?page={page}&category=users'); return $pager[$page]; } }
Select query builder
use Ray\AuraSqlModule\Pagerfanta\AuraSqlQueryPagerFactoryInterface; use Aura\SqlQuery\Common\SelectInterface; use Aura\Sql\ExtendedPdoInterface; class ProductListService { public function __construct( private readonly AuraSqlQueryPagerFactoryInterface $queryPagerFactory, private readonly SelectInterface $select, private readonly ExtendedPdoInterface $pdo ) {} public function getProductList(int $page, string $category): Page { $select = $this->select ->from('products') ->where('category = :category') ->bindValue('category', $category); $pager = $this->queryPagerFactory->newInstance($this->pdo, $select, 10, '/?page={page}&category=' . $category); return $pager[$page]; } }
An array access with page number returns Page
value object.
/* @var Pager \Ray\AuraSqlModule\Pagerfanta\Page */ // $page->data // sliced data // $page->current; // $page->total // $page->hasNext // $page->hasPrevious // $page->maxPerPage; // (string) $page // pager html
It is iterable.
foreach ($page as $item) { // ... }
View
The view template can be changed with binding. See more at Pagerfanta.
use Pagerfanta\View\Template\TemplateInterface; use Pagerfanta\View\Template\TwitterBootstrap3Template; use Ray\AuraSqlModule\Annotation\PagerViewOption; $this->bind(TemplateInterface::class)->to(TwitterBootstrap3Template::class); $this->bind()->annotatedWith(PagerViewOption::class)->toInstance($pagerViewOption);
Profile
To log SQL execution, install AuraSqlProfileModule
.
It will be logged by a logger bound to the PSR-3 logger. This example binds a minimal function logger created in an anonymous class.
class DevModule extends AbstractModule { protected function configure() { // ... $this->install(new AuraSqlProfileModule()); $this->bind(LoggerInterface::class)->toInstance( new class extends AbstractLogger { /** @inheritDoc */ public function log($level, $message, array $context = []) { $replace = []; foreach ($context as $key => $val) { if (! is_array($val) && (! is_object($val) || method_exists($val, '__toString'))) { $replace['{' . $key . '}'] = $val; } } error_log(strtr($message, $replace)); } } ); } }