README

👁 codecov
👁 Type Coverage
👁 Continuous Integration

An Aura.Sql Module for Ray.Di

Versions

Two lines are maintained. Pick by how your application declares metadata — the required PHP version follows from the Aura.Sql major you depend on, not from this package directly.

• 1.13.x — use this if your application still relies on Doctrine annotations (@Transactional, @Named in docblocks). Depends on ray/di ^2.13, which ships doctrine/annotations. Supports Aura.Sql 4 and 5.
• 1.x (current) — PHP attributes only (#[Transactional]). Depends on ray/di ^2.16+, which dropped doctrine/annotations. Supports Aura.Sql 5 (PHP 8.1–8.3) and Aura.Sql 6 (PHP 8.4+, covering the PDO BC break introduced in PHP 8.4).

Composer resolves the matching Aura.Sql major automatically from your PHP version, so you normally don't need to think about it — the decisive question is annotations vs attributes.

To pin to the maintenance line, use a tilde or wildcard constraint — ^1.13 would also match 1.14+ and pull you onto the 1.x line:

{
 "require": {
 "ray/aura-sql-module": "~1.13.1"
 }
}

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));
 }
 }
 );
 }
}