Rowcast

Lightweight DataMapper over PDO for PHP 8.4+.

Rowcast maps database rows to DTOs and back using reflection, supports explicit/auto mapping, type conversion, and includes a fluent query builder with dialect-aware UPSERT.

Documentation: English | Русский | LLM/Context7

Requirements

• PHP >= 8.4
• ext-pdo

Installation

composer require ascetic-soft/rowcast

Quick Start

use AsceticSoft\Rowcast\Connection;
use AsceticSoft\Rowcast\DataMapper;

class UserDto
{
    public int $id;
    public string $email;
    public bool $isActive;
}

$connection = Connection::create('sqlite::memory:');
$mapper = new DataMapper($connection);

$user = new UserDto();
$user->email = 'alice@example.com';
$user->isActive = true;

$mapper->insert('users', $user);
$found = $mapper->findOne(UserDto::class, ['email' => $user->email]);

Core Concepts

Rowcast supports two mapping styles:

• Auto mapping — pass class-string for reads and table name for writes. Names are converted via NameConverterInterface (default: SnakeCaseToCamelCase).
• Explicit mapping — pass Mapping to control table name, column/property pairs, and ignored properties.

Auto Mapping

Table name is derived from DTO class name for reads:

Column/property conversion (default):

Explicit Mapping

use AsceticSoft\Rowcast\Mapping;

$mapping = Mapping::auto(UserDto::class, 'custom_users')
    ->column('usr_email', 'email')
    ->ignore('internalNote');

$user = $mapper->findOne($mapping, ['id' => 1]);

Use Mapping::explicit(...) when only declared columns must be used:

$mapping = Mapping::explicit(UserDto::class, 'custom_users')
    ->column('id', 'id')
    ->column('usr_email', 'email');

Notes:

• Mapping is currently mutable and configured in place.
• Prefer building it once during setup and then reusing it as configuration.
• Avoid mutating the same Mapping instance after it is already shared across multiple operations or services.

Connection

Connection wraps PDO and provides query helpers, transaction API, nested transaction support (savepoints), and query-builder factory.

Create Connection

use AsceticSoft\Rowcast\Connection;

// From DSN
$connection = Connection::create(
    dsn: 'mysql:host=localhost;dbname=app',
    username: 'root',
    password: 'secret',
    nestTransactions: true,
);

// From existing PDO
$pdo = new \PDO('sqlite::memory:');
$connection = new Connection($pdo, nestTransactions: true);

Raw Queries

$stmt = $connection->executeQuery('SELECT * FROM users WHERE id = ?', [1]);
$affected = $connection->executeStatement('UPDATE users SET email = ? WHERE id = ?', ['a@x.com', 1]);
$rows = $connection->fetchAllAssociative('SELECT * FROM users');
$row = $connection->fetchAssociative('SELECT * FROM users WHERE id = ?', [1]);
$count = $connection->fetchOne('SELECT COUNT(*) FROM users');

Query Events

Register lightweight listeners to observe SQL execution.

$connection->onBeforeQuery(function (string $sql, array $params): void {
    // log or inspect SQL before execution
});

$connection->onAfterQuery(function (string $sql, array $params, float $duration, ?\Throwable $exception): void {
    // $exception is null for successful queries
});

Transactions

$connection->transactional(function (Connection $conn) {
    $conn->executeStatement('INSERT INTO users (email) VALUES (?)', ['alice@example.com']);
});

Nested mode creates savepoints for inner transactions.

DataMapper API

Main methods:

• insert(string|Mapping $target, object $dto): void
• batchInsert(string|Mapping $target, array $dtos, ?int $maxBindParameters = null): void
• update(string|Mapping $target, object $dto, array $where): int
• batchUpdate(string|Mapping $target, array $dtos, array $identityProperties, ?int $maxBindParameters = null): void
• delete(string|Mapping $target, array $where): int
• findAll(string|Mapping $target, array $where = [], array $orderBy = [], ?int $limit = null, ?int $offset = null): array
• iterateAll(string|Mapping $target, array $where = [], array $orderBy = [], ?int $limit = null, ?int $offset = null): iterable
• findOne(string|Mapping $target, array $where = []): ?object
• save(string|Mapping $target, object $dto, string ...$identityProperties): void
• upsert(string|Mapping $target, object $dto, string ...$conflictProperties): int
• batchUpsert(string|Mapping $target, array $dtos, array $conflictProperties, ?int $maxBindParameters = null): void
• hydrate(...), hydrateAll(...), extract(...)

CRUD Example

$dto = new UserDto();
$dto->email = 'alice@example.com';
$dto->isActive = true;

$mapper->insert('users', $dto);
$one = $mapper->findOne(UserDto::class, ['email' => $dto->email]);

$one->isActive = false;
$mapper->update('users', $one, ['id' => $one->id]);
$mapper->delete('users', ['id' => $one->id]);

save(...) Example

save(...) checks row existence by identity columns, then performs insert or update.

Notes:

• save(...) is a convenience API and does a read-before-write flow.
• It does not require a database conflict constraint to exist.
• It may use two SQL statements for one logical save operation.
• Prefer upsert(...) when your database and schema support conflict handling and write-path efficiency matters.

$mapper->save('users', $dto, 'id');

upsert(...) Example

$affected = $mapper->upsert('users', $dto, 'email');

Batch operations

$mapper->batchInsert('users', [$dto1, $dto2, $dto3]);
$mapper->batchUpsert('users', [$dto1, $dto2, $dto3], ['id']);
$mapper->batchUpdate('users', [$dto1, $dto2, $dto3], ['id']);

// Optional override for chunk sizing by bind parameter limit
$mapper->batchInsert('users', [$dto1, $dto2, $dto3], 500);
$mapper->batchUpsert('users', [$dto1, $dto2, $dto3], ['id'], 500);

batchInsert and batchUpsert automatically split large input into chunks based on DB parameter limits (for example, SQLite: 999 bind params), while executing all chunks inside one transaction.

Advanced where in DataMapper

DataMapper passes where arrays to the same QueryBuilder condition engine, so advanced operators are available there as well:

$users = $mapper->findAll(UserDto::class, where: [
    'deleted_at' => null,
    '$or' => [
        ['status' => ['active', 'pending']],
        ['role' => 'admin'],
    ],
    'age >=' => 18,
]);

Type Conversion

Rowcast converts DB values to declared PHP property types on hydrate, and PHP values to DB-safe values on extract/write.

Built-in converters:

• ScalarConverter (int, float, string)
• BoolConverter (bool <-> 0/1)
• DateTimeConverter (DateTimeInterface <-> formatted UTC string)
• JsonConverter (array <-> JSON)
• EnumConverter (BackedEnum <-> backing value)

Custom Type Converter

Implement TypeConverterInterface and pass a custom registry to DataMapper:

use AsceticSoft\Rowcast\DataMapper;
use AsceticSoft\Rowcast\TypeConverter\TypeConverterInterface;
use AsceticSoft\Rowcast\TypeConverter\TypeConverterRegistry;

final class UuidConverter implements TypeConverterInterface
{
    public function supports(string $phpType): bool
    {
        return $phpType === Uuid::class;
    }

    public function toPhp(mixed $value, string $phpType): mixed
    {
        return new Uuid((string) $value);
    }

    public function toDb(mixed $value): mixed
    {
        return (string) $value;
    }
}

$converters = TypeConverterRegistry::defaults()->add(new UuidConverter());
$mapper = new DataMapper($connection, typeConverter: $converters);

Custom Name Converter

Implement NameConverterInterface and pass it to DataMapper:

use AsceticSoft\Rowcast\DataMapper;
use AsceticSoft\Rowcast\NameConverter\NameConverterInterface;

final class PrefixedConverter implements NameConverterInterface
{
    public function toPropertyName(string $columnName): string
    {
        return lcfirst(str_replace('usr_', '', $columnName));
    }

    public function toColumnName(string $propertyName): string
    {
        return 'usr_' . $propertyName;
    }
}

$mapper = new DataMapper($connection, nameConverter: new PrefixedConverter());

Query Builder

Connection::createQueryBuilder() provides a fluent SQL builder.

SELECT

$rows = $connection->createQueryBuilder()
    ->select('u.id', 'u.email')
    ->from('users', 'u')
    ->where('u.is_active = :active')
    ->orderBy('u.id', 'DESC')
    ->setOffset(20)
    ->setLimit(10)
    ->setParameter('active', 1)
    ->fetchAllAssociative();

For pagination, use:

• setOffset(int $offset) — start row
• setLimit(int $limit) — max rows

You can also pass associative arrays to where(), andWhere(), and orWhere():

$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->where(['email' => 'alice@example.com', 'is_active' => 1])
    ->fetchAllAssociative();
// SQL: SELECT * FROM users WHERE email = :w_email AND is_active = :w_is_active

array predicates are converted to field = :param expressions joined by AND:

• where(['a' => 1, 'b' => 2]) -> a = :w_a AND b = :w_b
• andWhere(['a' => 1]) appends another AND block
• orWhere(['a' => 1]) wraps previous predicate: (prev OR a = :w_a)

Parameter names are generated automatically and made unique (:w_id, :w_id_1, ...).

Supported array operators:

// IS NULL / IS NOT NULL
->where(['deleted_at' => null])        // deleted_at IS NULL
->where(['deleted_at !=' => null])     // deleted_at IS NOT NULL

// IN / NOT IN
->where(['status' => ['active', 'pending']])     // status IN (...)
->where(['status !=' => ['banned']])             // status NOT IN (...)
->where(['status IN' => ['active']])             // explicit IN
->where(['status NOT IN' => ['banned']])         // explicit NOT IN

// BackedEnum in WHERE (direct QueryBuilder usage)
->where(['status' => UserStatus::Active])                               // status = :w_status, parameter value: 'active'
->where(['status' => [UserStatus::Active, UserStatus::Inactive]])       // status IN (...), parameters: 'active', 'inactive'

// Comparison operators
->where(['age >' => 18, 'age <=' => 65, 'score !=' => 0])

// LIKE / ILIKE / NOT LIKE / NOT ILIKE
->where(['name LIKE' => '%alice%'])
->where(['name ILIKE' => '%alice%'])             // useful for PostgreSQL
->where(['name NOT LIKE' => '%bot%'])
->where(['name NOT ILIKE' => '%bot%'])           // PostgreSQL only

// BETWEEN
->where(['age BETWEEN' => [18, 65]])

Operator reference:

Notes:

• Empty IN array compiles to 1 = 0 (always false).
• Empty NOT IN array compiles to 1 = 1 (always true).
• ILIKE and NOT ILIKE are PostgreSQL-specific.
• BackedEnum values are normalized to backing scalar values in WHERE parameters (including IN / NOT IN arrays).

Dialect-specific operator support:

Base set for all dialects: >, >=, <, <=, LIKE, NOT LIKE.

OR Conditions

You can compose OR logic in two ways.

Method-based OR groups:

// (status = 'active' AND age > 18) OR (role = 'admin')
$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->whereOr(
        ['status' => 'active', 'age >' => 18],
        ['role' => 'admin'],
    )
    ->fetchAllAssociative();

Combine with existing filters:

// deleted_at IS NULL AND ((status = 'active') OR (role = 'admin'))
$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->where(['deleted_at' => null])
    ->andWhereOr(['status' => 'active'], ['role' => 'admin'])
    ->fetchAllAssociative();

Nested-key style in one array:

$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->where([
        'age >' => 18,
        '$or' => [
            ['status' => 'active'],
            ['$and' => [
                ['role' => 'admin'],
                ['verified' => true],
            ]],
        ],
    ])
    ->fetchAllAssociative();

OR composition reference:

INSERT / UPDATE / DELETE

$connection->createQueryBuilder()
    ->insert('users')
    ->values(['email' => ':email', 'is_active' => ':is_active'])
    ->setParameter('email', 'alice@example.com')
    ->setParameter('is_active', 1)
    ->executeStatement();

$connection->createQueryBuilder()
    ->update('users')
    ->values(['is_active' => ':is_active'])
    ->where('id = :id')
    ->setParameter('is_active', 0)
    ->setParameter('id', 1)
    ->executeStatement();

$connection->createQueryBuilder()
    ->delete('users')
    ->where('id = :id')
    ->setParameter('id', 1)
    ->executeStatement();

UPSERT

$sql = $connection->createQueryBuilder()
    ->upsert('users')
    ->values(['email' => ':email', 'name' => ':name'])
    ->onConflict('email')
    ->doUpdateSet(['name'])
    ->getSQL();

Upsert is compiled via SQL dialects:

• MysqlDialect
• PostgresDialect
• SqliteDialect
• GenericDialect (throws for unsupported UPSERT)

WHERE array operator support is also dialect-aware (for example, ILIKE/NOT ILIKE only for PostgreSQL).

Expressions in SET / VALUES

Use Expression::raw() to embed arbitrary SQL expressions into UPDATE SET or INSERT VALUES:

use AsceticSoft\Rowcast\QueryBuilder\Expression\Expression;

// Decrement balance: balance = balance - :amount
$qb = $connection->createQueryBuilder()
    ->update('credit_accounts')
    ->set('balance', Expression::raw('balance - :amount'))
    ->where(['id' => 1])
    ->setParameter('amount', 100)
    ->executeStatement();

// Using NOW() in INSERT
$connection->createQueryBuilder()
    ->insert('logs')
    ->values([
        'message'    => ':msg',
        'created_at' => Expression::raw('NOW()'),
    ])
    ->setParameter('msg', 'user login')
    ->executeStatement();

// Column references in expressions
$connection->createQueryBuilder()
    ->update('items')
    ->set('quantity', Expression::raw('quantity + :add_qty'))
    ->where(['id' => 5])
    ->setParameter('add_qty', 3)
    ->executeStatement();

Expression SQL is written through verbatim — no parameter binding or quoting happens on the expression itself. You pass placeholders (:param) as part of the raw expression and bind them separately via setParameter().

Architecture

AsceticSoft\Rowcast\
├── ConnectionInterface
├── Connection
├── DataMapper
├── Hydrator
├── Extractor
├── Mapping
├── TargetResolver
├── QueryHelper
├── NameConverter\
│   ├── NameConverterInterface
│   └── SnakeCaseToCamelCase
├── TypeConverter\
│   ├── TypeConverterInterface
│   ├── TypeConverterRegistry
│   ├── ScalarConverter
│   ├── BoolConverter
│   ├── DateTimeConverter
│   ├── JsonConverter
│   └── EnumConverter
└── QueryBuilder\
    ├── QueryBuilder
    ├── QueryType
    ├── Dialect\
    │   ├── DialectInterface
    │   ├── DialectFactory
    │   ├── AbstractStandardDialect
    │   ├── AbstractOnConflictDialect
    │   ├── MysqlDialect
    │   ├── PostgresDialect
    │   ├── SqliteDialect
    │   └── GenericDialect
    └── Compiler\
        ├── SqlCompilerInterface
        ├── SqlFragments
        ├── SelectCompiler
        ├── InsertCompiler
        ├── UpsertCompiler
        ├── UpdateCompiler
        └── DeleteCompiler

Development

composer install
make install-hooks
vendor/bin/phpunit
vendor/bin/phpstan analyse

License

MIT