Sql Common Laravel Package

Getting Started

• Do not install this package as your primary database layer. amphp/sql-common is an interface/abstraction package. It cannot open a database connection by itself.
• Install a concrete driver instead:

  composer require amphp/mysql
  # or
  composer require amphp/postgres
  

  Add amphp/sql-common explicitly only when you type-hint its interfaces, build a custom driver, or write reusable database-agnostic code.
• First files to read:
  • src/SqlExecutor.php — the core query/prepare/execute contract.
  • src/SqlConnectionPool.php — pooling, transactions, connection lifecycle.
  • src/SqlConnection.php — a single connection.
  • src/SqlResult.php / SqlStatement.php / SqlTransaction.php — result, prepared statement, transaction contracts.
  • src/ConnectionConfig.php — driver configuration base.
  • src/Common/ — shared pool implementation helpers used by the MySQL/Postgres drivers.
• Minimal first use case: inject the driver's pool as Amp\Sql\SqlConnectionPool and run a query through the common interface.

  use Amp\Sql\SqlConnectionPool;
  
  final class UserRepository
  {
      public function __construct(private SqlConnectionPool $pool) {}
  
      public function find(int $id): ?array
      {
          $result = $this->pool->execute(
              'SELECT * FROM users WHERE id = ?', // Postgres uses $1
              [$id],
          );
  
          foreach ($result as $row) {
              return $row;
          }
  
          return null;
      }
  }
  

• Use it from inside an Amp context (event loop/fiber). The concrete driver creates and configures the pool; your application code depends on the shared interfaces.

Implementation Patterns

• Depend on the narrowest interface. Query-only services should type-hint Amp\Sql\SqlExecutor. Use SqlConnectionPool only when you need transactions, connection acquisition, pool stats, or shutdown control.
• Prefer pool-level execute() for one-shot queries. It acquires and releases a connection for you. Use getConnection() only for explicit transactions or session-level state.
• Use prepared statements for repeated queries. Prepare on a connection and reuse the SqlStatement; do not share a statement across pool connections because it is bound to its originating connection.
• Transaction pattern:

  $transaction = $pool->beginTransaction();
  
  try {
      $transaction->execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [$amount, $from]);
      $transaction->execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [$amount, $to]);
      $transaction->commit();
  } catch (\Throwable $e) {
      $transaction->rollback();
      throw $e;
  }
  

  Keep the transaction scope small and never share a SqlTransaction across concurrent coroutines.
• Use isolation levels through the shared enum/constants (Amp\Sql\SqlTransactionIsolation) when the driver supports them, rather than driver-specific constants.
• Wrap/decorate SqlExecutor for cross-cutting concerns. Logging, metrics, retry policies, and query timing can be implemented by decorating the shared interface without coupling to MySQL or Postgres.
• Build driver-agnostic repositories/services around SqlExecutor, but keep dialect-specific SQL in the driver-specific implementation or configuration layer. The shared package does not normalize SQL dialects or placeholders.
• Custom drivers: implement SqlConnection, SqlStatement, SqlResult, and SqlTransaction; provide a SqlConnector; and reuse the Amp\Sql\Common\* pool helpers where possible.
• Testing: fake SqlExecutor, SqlStatement, and SqlResult for unit tests. Use real driver integration tests for SQL correctness, pooling, and transaction behavior.

Gotchas and Tips

• This is not an ORM or query builder. It provides contracts and shared pool mechanics only. Schema management, migrations, and SQL portability remain your responsibility.
• Placeholder syntax is driver-specific. amphp/mysql commonly uses ?; amphp/postgres commonly uses $1, $2, etc. Do not assume SQL written against one driver will run unchanged on the other.
• Pool exhaustion is easy to cause. Avoid holding a connection while awaiting unrelated I/O. Do not call getConnection() and then wait on another pooled connection in the same logical operation.
• Results are lazy and often single-pass. Consume or explicitly discard a SqlResult before reusing the connection. Some metadata such as row count may not be available until the result is consumed.
• Transactions need explicit cleanup. Always commit() or rollback(). Relying on destructor behavior is fragile, especially under cancellation and long-running workers.
• Catch the shared exception hierarchy (Amp\Sql\SqlException, ConnectionException, QueryError, TransactionError) rather than only driver-specific exception classes. Inspect available query/error context when debugging failed statements.
• Respect the async runtime. Blocking calls (sleep(), blocking file/network I/O) stall the event loop. In Amp v3/fiber-based code, synchronous-looking code still runs cooperatively; do not introduce blocking operations.
• Version alignment matters. Keep amphp/sql-common on a major version compatible with your MySQL/Postgres driver. Mixing major versions can produce interface mismatches.
• Close pools during shutdown. Long-running workers should close the pool and avoid reusing connections after shutdown begins.
• No built-in logging or query telemetry. Add a decorator around SqlExecutor/SqlConnectionPool if you need query logs, timings, or metrics.
• Configuration objects are immutable-style. Use withDatabase()-style helpers where provided instead of mutating shared config. Never log connection configs containing passwords.
• Extension point: implement SqlConnector and extend the shared Common pool classes for custom transports or test doubles; use decorators rather than modifying vendor code for observability.