> ## Documentation Index
> Fetch the complete documentation index at: https://suvera.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Configuring Database Access with PdbcTemplate in Winter Boot

> Set up datasources, inject PdbcTemplate, execute typed queries, map rows to PPA entities, and manage multi-tenant connections in Winter Boot.

Winter Boot's database layer wraps PHP's native PDO (and optional OCI) extensions behind the `PdbcTemplate` interface, giving you a clean, injection-friendly API for executing SQL without managing connections, prepared statements, or result-set iteration yourself. Each configured datasource automatically produces a named `PdbcTemplate` bean and a transaction manager bean you can inject anywhere in your application.

<Tip>
  Need full ORM entities and repositories instead of raw SQL? The [Winter Doctrine module](/modules/doctrine) plugs Doctrine ORM and DBAL into the same datasource configuration.
</Tip>

## Datasource Configuration

Declare datasources in `application.yml` under the top-level `datasource` key. You may configure as many datasources as you need — mark exactly one as the primary with `isPrimary: true`.

```yaml application.yml theme={null}
datasource:
    -   name: defaultdb
        isPrimary: true
        url: "sqlite::memory:"
        username: myuser
        password: mypassword
        connection:
            persistent: false
            errorMode: ERRMODE_EXCEPTION
            columnsCase: CASE_NATURAL
            idleTimeout: 600
            autoCommit: true

    -   name: admindb
        url: "mysql:host=localhost;port=3307;dbname=testdb"
        username: adminuser
        password: adminpassword
        connection:
            persistent: true
            errorMode: ERRMODE_EXCEPTION
            columnsCase: CASE_NATURAL
            idleTimeout: 300
            autoCommit: true
```

### Top-Level Datasource Properties

<ParamField body="isPrimary" type="bool" default="false">
  Mark this datasource as the primary. The primary datasource's `PdbcTemplate` is injected with a plain `#[Autowired]` (no qualifier needed).
</ParamField>

<ParamField body="validationQuery" type="string" default="''">
  SQL statement used to validate connections before use (e.g. `SELECT 1`).
</ParamField>

<ParamField body="driverClass" type="string" default="PdoDataSource">
  Fully-qualified class name of a custom `DataSource` implementation.
</ParamField>

### Connection Options Reference

<ParamField body="persistent" type="bool" default="false">
  Use persistent PDO connections.
</ParamField>

<ParamField body="errorMode" type="string" default="ERRMODE_EXCEPTION">
  PDO error mode. One of `ERRMODE_SILENT`, `ERRMODE_WARNING`, or `ERRMODE_EXCEPTION`.
</ParamField>

<ParamField body="columnsCase" type="string" default="CASE_NATURAL">
  Column name case folding. One of `CASE_NATURAL`, `CASE_LOWER`, or `CASE_UPPER`.
</ParamField>

<ParamField body="idleTimeout" type="int" default="600">
  Seconds before an idle connection is recycled.
</ParamField>

<ParamField body="autoCommit" type="bool" default="true">
  Enable auto-commit for non-transactional operations.
</ParamField>

<ParamField body="timeoutSecs" type="int" default="30">
  Connection-level statement timeout in seconds.
</ParamField>

<ParamField body="rowsPrefetch" type="int" default="100">
  Number of rows to prefetch. Applies to the OCI driver only.
</ParamField>

***

## Injecting PdbcTemplate

Winter Boot automatically registers a `PdbcTemplate` bean for every configured datasource. Use a plain `#[Autowired]` to inject the primary datasource's template, or target a specific datasource by the `<datasource-name>-template` bean name.

```php UserRepository.php theme={null}
use dev\winterframework\pdbc\PdbcTemplate;
use dev\winterframework\stereotype\Autowired;
use dev\winterframework\stereotype\Service;

#[Service]
class UserRepository {

    // Injected from the primary datasource (defaultdb)
    #[Autowired]
    private PdbcTemplate $pdbc;

    // Injected from the named datasource "admindb"
    #[Autowired("admindb-template")]
    private PdbcTemplate $adminPdbc;
}
```

<Tip>
  The `-template` suffix is part of Winter Boot's automatic bean-naming convention. For every datasource named `<name>`, the framework registers a bean under `<name>-template`. You only need the suffix when injecting a **non-primary** template.
</Tip>

***

## PdbcTemplate Methods

Every method accepts either a plain PHP `array` (positional `?` or named `:name` placeholders) or a typed `BindVars` collection as its bind-variable argument.

<AccordionGroup>
  <Accordion title="execute — Run any SQL statement">
    Execute any SQL statement and optionally receive the underlying `PreparedStatement` in a callback.

    | Parameter   | Type                              | Description                                        |
    | ----------- | --------------------------------- | -------------------------------------------------- |
    | `$sql`      | `string`                          | SQL statement to execute                           |
    | `$bindVars` | `array\|BindVars`                 | Optional bind variables                            |
    | `$action`   | `PreparedStatementCallback\|null` | Optional callback receiving the prepared statement |

    **Returns:** `mixed` — the result of the callback, or the raw execution result.

    ```php theme={null}
    // Simple DML
    $this->pdbc->execute(
        "INSERT INTO audit_log (event, created_at) VALUES (?, NOW())",
        ["user.login"]
    );
    ```
  </Accordion>

  <Accordion title="query — Stream results through a callback">
    Execute a query and hand the result set to a processor callback, `ResultSetExtractor`, `RowCallbackHandler`, or `RowMapper`.

    ```php theme={null}
    use dev\winterframework\pdbc\ResultSet;

    $names = $this->pdbc->query(
        "SELECT name FROM users WHERE active = ?",
        [1],
        function (ResultSet $rs) {
            $results = [];
            while ($rs->next()) {
                $results[] = $rs->getString('name');
            }
            return $results;
        }
    );
    ```
  </Accordion>

  <Accordion title="queryForList — Fetch all rows as arrays">
    Return all result rows as an array of associative arrays.

    **Returns:** `array` — `[['col' => 'val', ...], ...]`

    ```php theme={null}
    $rows = $this->pdbc->queryForList(
        "SELECT id, name, email FROM users WHERE status = ?",
        ["active"]
    );

    foreach ($rows as $row) {
        echo $row['name'] . ' — ' . $row['email'] . PHP_EOL;
    }
    ```
  </Accordion>

  <Accordion title="queryForMap — Fetch a single row as an array">
    Return a single row as an associative array. Throws an exception if the query returns zero or more than one row.

    ```php theme={null}
    $user = $this->pdbc->queryForMap(
        "SELECT * FROM users WHERE id = ?",
        [42]
    );
    echo $user['email'];
    ```
  </Accordion>

  <Accordion title="queryForScalar — Fetch a single value">
    Return the first column of the first row as a scalar value.

    ```php theme={null}
    $count = $this->pdbc->queryForScalar("SELECT COUNT(*) FROM users");
    echo "Total users: {$count}";
    ```
  </Accordion>

  <Accordion title="queryForObject — Map one row to an entity">
    Map a single result row to an object using a class name or a custom `RowMapper`.

    ```php theme={null}
    $user = $this->pdbc->queryForObject(
        "SELECT id, name, email FROM users WHERE id = ?",
        [1],
        User::class
    );
    echo $user->getName();
    ```
  </Accordion>

  <Accordion title="queryForObjects — Map multiple rows to entities">
    Return multiple rows as an array of PPA entity objects.

    ```php theme={null}
    $users = $this->pdbc->queryForObjects(
        "SELECT id, name, email FROM users WHERE status = ?",
        ["active"],
        User::class
    );

    foreach ($users as $user) {
        echo $user->getName();
    }
    ```
  </Accordion>

  <Accordion title="update — INSERT, UPDATE, or DELETE">
    Execute an `INSERT`, `UPDATE`, or `DELETE` statement. Supports OUT bind variables and retrieval of database-generated keys.

    ```php theme={null}
    $generatedKeys = [];
    $affected = $this->pdbc->update(
        "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
        ['name' => 'Alice', 'email' => 'alice@example.com', 'age' => 30],
        [],
        $generatedKeys
    );

    $newId = intval($generatedKeys['id']);
    ```
  </Accordion>

  <Accordion title="batchUpdate — Execute a statement for multiple rows">
    Execute the same parameterised SQL statement for multiple sets of bind variables in a single batch.

    ```php theme={null}
    $batchParams = [
        ["Alice", "alice@example.com"],
        ["Bob",   "bob@example.com"],
        ["Carol", "carol@example.com"],
    ];

    $results = $this->pdbc->batchUpdate(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        $batchParams
    );
    ```
  </Accordion>

  <Accordion title="updateObjects — Persist PPA entity objects">
    Persist one or more PPA entity objects. The framework generates the correct `INSERT` or `UPDATE` SQL automatically.

    ```php theme={null}
    $user = new User();
    $user->setName("Charlie");
    $user->setEmail("charlie@example.com");
    $user->setAge(25);

    $this->pdbc->updateObjects($user);
    ```
  </Accordion>

  <Accordion title="deleteObjects — Delete PPA entity objects">
    Delete one or more PPA entity objects from the database.

    ```php theme={null}
    $user = $this->pdbc->queryForObject(
        "SELECT * FROM users WHERE id = ?",
        [42],
        User::class
    );

    $this->pdbc->deleteObjects($user);
    ```
  </Accordion>
</AccordionGroup>

***

## BindVars — Typed Parameters

For queries where you need explicit type control, use the `BindVars` fluent builder instead of a plain array.

```php theme={null}
use dev\winterframework\pdbc\core\BindVars;
use dev\winterframework\pdbc\core\BindType;

$binds = (new BindVars())
    ->add('status',   'active', BindType::STRING)
    ->add('minAge',   18,       BindType::INTEGER)
    ->add('score',    9.5,      BindType::FLOAT)
    ->add('verified', true,     BindType::BOOL);

$users = $this->pdbc->queryForObjects(
    "SELECT * FROM users WHERE status = :status AND age >= :minAge AND score > :score AND verified = :verified",
    $binds,
    User::class
);
```

### BindType Constants

| Constant            | Value | PHP Type                         |
| ------------------- | ----- | -------------------------------- |
| `BindType::STRING`  | `3`   | `string` / `mixed`               |
| `BindType::INTEGER` | `1`   | `int`                            |
| `BindType::FLOAT`   | `2`   | `float`                          |
| `BindType::BOOL`    | `4`   | `bool`                           |
| `BindType::DATE`    | `5`   | `DateTime` / `DateTimeInterface` |
| `BindType::BLOB`    | `6`   | Binary large object              |
| `BindType::CLOB`    | `7`   | Character large object           |
| `BindType::NULL`    | `8`   | Explicit `NULL`                  |

***

## PPA — PHP Persistence API

PPA is Winter Boot's lightweight ORM layer. An entity is any class annotated with `#[Table]` that either implements the `PpaEntity` interface or uses the `PpaEntityTrait` convenience trait. The framework automatically generates `INSERT`, `UPDATE`, and `DELETE` SQL and maps query result columns back to PHP properties.

### Annotations

Use `#[Table]` at the class level to map the class to a database table, and `#[Column]` at the property level to map each property to a column.

<ParamField body="#[Table(name: string)]">
  Applied at class level. Maps the class to the named database table.
</ParamField>

**`#[Column]` options:**

<ParamField body="name" type="string" default="property name">
  The database column name.
</ParamField>

<ParamField body="id" type="bool" default="false">
  Marks this column as the primary key.
</ParamField>

<ParamField body="insertable" type="bool" default="true">
  Include this column in `INSERT` statements.
</ParamField>

<ParamField body="updatable" type="bool" default="true">
  Include this column in `UPDATE` statements.
</ParamField>

<ParamField body="nullable" type="bool" default="true">
  Allow `NULL` values for this column.
</ParamField>

<ParamField body="length" type="int" default="0">
  Column length hint.
</ParamField>

<ParamField body="precision" type="int" default="0">
  Decimal precision.
</ParamField>

<ParamField body="scale" type="int" default="0">
  Decimal scale.
</ParamField>

### Full Entity Example

```php User.php theme={null}
use dev\winterframework\ppa\PpaEntity;
use dev\winterframework\ppa\PpaEntityTrait;
use dev\winterframework\stereotype\ppa\Column;
use dev\winterframework\stereotype\ppa\Table;

#[Table("users")]
class User implements PpaEntity {
    use PpaEntityTrait;

    #[Column(name: "id", id: true, insertable: false, updatable: false)]
    private int $id;

    #[Column(name: "name", length: 100, nullable: false)]
    private string $name;

    #[Column(name: "email", length: 255, nullable: false)]
    private string $email;

    #[Column(name: "age")]
    private int $age;

    #[Column(name: "created_at", insertable: false, updatable: false)]
    private ?string $createdAt = null;

    public function getId(): int         { return $this->id; }
    public function setId(int $id): void { $this->id = $id; }

    public function getName(): string           { return $this->name; }
    public function setName(string $name): void { $this->name = $name; }

    public function getEmail(): string            { return $this->email; }
    public function setEmail(string $email): void { $this->email = $email; }

    public function getAge(): int        { return $this->age; }
    public function setAge(int $age): void { $this->age = $age; }

    public function getCreatedAt(): ?string { return $this->createdAt; }
}
```

<Note>
  Columns marked with `insertable: false, updatable: false` (like auto-generated `id` and `created_at` fields) are excluded from write operations but are still populated when reading rows back from the database.
</Note>

***

## Multi-Tenant Support

Winter Boot provides first-class multi-tenancy via `MultiTenantManager` and the `TenantDataSourceProvider` contract. Each tenant gets its own lazily-created, cached `PdbcTemplate` and `PlatformTransactionManager` backed by a separate data source resolved at runtime.

### Configure a Multi-Tenant Datasource

```yaml application.yml theme={null}
multitenant-datasource:
    -   name: tenantdb
        url: "mysql:host=localhost;port=3306"
        providerClass: "App\\Config\\MyTenantDataSourceProvider"
```

When the application starts, Winter Boot registers a `MultiTenantManager` bean under the name `tenantdb-manager`.

### Step 1 — Implement TenantDataSourceProvider

Implement `TenantDataSourceProvider` to tell Winter Boot how to look up each tenant's connection details and how to enumerate all active tenants.

```php MyTenantDataSourceProvider.php theme={null}
namespace App\Config;

use dev\winterframework\pdbc\datasource\DataSourceConfig;
use dev\winterframework\pdbc\multitenant\TenantDataSourceProvider;
use dev\winterframework\pdbc\PdbcTemplate;
use dev\winterframework\stereotype\Autowired;
use dev\winterframework\stereotype\Component;

#[Component]
class MyTenantDataSourceProvider implements TenantDataSourceProvider {

    #[Autowired("admindb-template")]
    private PdbcTemplate $adminPdbc;

    public function getTenantDataSourceConfig(string $tenantId): DataSourceConfig {
        $tenant = $this->adminPdbc->queryForObject(
            "SELECT * FROM tenants WHERE tenant_id = :tid",
            ['tid' => $tenantId],
            YourTenantEntity::class
        );

        $config = new DataSourceConfig();
        $config->setName($tenantId);
        $config->setUrl("mysql:host={$tenant->dbHost};port={$tenant->dbPort};dbname={$tenant->database}");
        $config->setUsername($tenant->username);
        $config->setPassword("tenant_pass");
        $config->setPersistent(true);
        $config->setAutoCommit(true);

        return $config;
    }

    public function getAllTenantIds(): array {
        $tenants = $this->adminPdbc->queryForObjects(
            "SELECT tenant_id FROM tenants WHERE status = 'Active'",
            [],
            YourTenantEntity::class
        );

        return array_map(fn($t) => $t->tenantId, $tenants);
    }
}
```

### Step 2 — Use MultiTenantManager in Business Classes

Inject the `MultiTenantManager` bean and call `getPdbcTemplate()` or `getTransactionManager()` with the tenant ID at runtime.

```php OrderService.php theme={null}
use dev\winterframework\pdbc\multitenant\MultiTenantManager;
use dev\winterframework\stereotype\Autowired;
use dev\winterframework\stereotype\Component;
use dev\winterframework\txn\support\DefaultTransactionDefinition;

#[Component]
class OrderService {

    #[Autowired]
    private MultiTenantManager $mt;  // resolves to "tenantdb-manager"

    public function createOrder(string $tenantId, array $orderData): void {
        $pdbc   = $this->mt->getPdbcTemplate($tenantId);
        $txnMgr = $this->mt->getTransactionManager($tenantId);

        $status = $txnMgr->getTransaction(new DefaultTransactionDefinition());
        try {
            $pdbc->update(
                "INSERT INTO orders (customer, amount) VALUES (:customer, :amount)",
                ['customer' => $orderData['customer'], 'amount' => $orderData['amount']]
            );
            $txnMgr->commit($status);
        } catch (\Throwable $e) {
            $txnMgr->rollback($status);
            throw $e;
        }
    }
}
```

### MultiTenantManager API

| Method                                    | Returns                      | Description                                                            |
| ----------------------------------------- | ---------------------------- | ---------------------------------------------------------------------- |
| `getPdbcTemplate(string $tenantId)`       | `PdbcTemplate`               | Returns (or lazily creates) a `PdbcTemplate` for the given tenant      |
| `getTransactionManager(string $tenantId)` | `PlatformTransactionManager` | Returns (or lazily creates) a transaction manager for the given tenant |
| `getTenantDataSourceProvider()`           | `TenantDataSourceProvider`   | Returns the configured provider instance                               |