A concept for an Entity class that hydrates from a PDO result row

test.php

<?php

declare(strict_types=1);

/**
 * An example of a value object
 */
class EmailAddress
{
    private function __construct(
        private string $value,
    ) {}

    public static function fromString(string $value)
    {
        return new self($value);
    }

    public static function fromPdo(mixed $pdoColumn, string $value)
    {
        return new self($value);
    }

    public function __toString()
    {
        return $this->value;
    }
}

/**
 * An example of a more complex type.
 */
class Fancy
{
    private function __construct(
        private string $name,
        private object $details,
    ) {}

    public static function create(string $name, array $details): self
    {
        return new static($name, (object)$details);
    }

    public static function fromPdo(mixed $pdoColumn, object $value): self
    {
        if (is_null($value)) {
            return null;
        }
        return new self($value->name, $value->details);
    }

    public function __toString()
    {
        return json_encode([ 'name' => $this->name, 'details' => $this->details ]);
    }
}

/**
 * The base Entity class.
 */
class Entity
{
    protected static ?string $tableName = null;

    /**
     * We don't allow new Entity() from just anyone!
     */
    protected function __construct()
    {}

    public static function create(...$args): static
    {
        $entity = new static();

        foreach ($args as $name => $arg) {
            $entity->$name = $arg;
        }

        /**
         * Make sure all public properties are initialized, except those that
         * are nullable, have default values from the SQL level, or are the
         * primary key.
         */
        $properties =
            (new \ReflectionClass(static::class))
                ->getProperties(\ReflectionProperty::IS_PUBLIC);

        foreach ($properties as $property) {
            if (!$property->isInitialized($entity) &&
                !$property->getType()->allowsNull() &&
                !$property->getAttributes('SqlDefault') &&
                !$property->getAttributes('PrimaryKey')
            ) {
                $propertyName = $property->getName();
                throw new \ValueError("Property '{$propertyName}' not initialized");
            }
        }

        return $entity;
    }

    private static function getColumnsMeta(\PDOStatement $res): array
    {
        $columns = $res->columnCount();
        $columnsMeta = [];
        for ($i = 0; $i < $columns; $i++) {
            $columnsMeta[] = $res->getColumnMeta($i);
        }
        return $columnsMeta;
    }

    private static function decodeTypeAsValue(string $metaType, mixed $value): mixed
    {
        if (is_null($value))
        {
            return null;
        }

        return match ($metaType) {
            'jsonArray' =>
                json_decode($value, flags: \JSON_THROW_ON_ERROR|\JSON_OBJECT_AS_ARRAY),
            'json' => json_decode($value, flags: \JSON_THROW_ON_ERROR),
            default => $value,
        };
    }

    /* based on https://gist.github.com/carousel/1aacbea013d230768b3dec1a14ce5751 */
    private static function snakeCaseToCamelCase(string $snake_case)
    {
        return \lcfirst(\str_replace('_', '', \ucwords($snake_case, '_')));
    }

    /* This is really camelCase or PascalCase to snake_case */
    private static function camelCaseToSnakeCase(string $camelCase)
    {
        return \strtolower(\preg_replace('/(?<!^)[A-Z]/', '_$0', $camelCase));
    }

    public static function fromPdoRow(PDOStatement $res, array $columns): self
    {
        $entity = new static();

        $columnsMeta = self::getColumnsMeta($res);

        foreach ($columnsMeta as $column => $meta) {
            $propertyName = self::snakeCaseToCamelCase($meta['name']);
            $metaType = $meta['sqlite:decl_type'];
            $propertyType = (new \ReflectionProperty(static::class, $propertyName))->getType();

            $value = $columns[$column];

            $entity->$propertyName = match ($propertyType->getName()) {
                'DateTime', '?DateTime' => $value ? new DateTime($value) : null,
                'array', '?array' => static::decodeTypeAsValue('jsonArray', $value),
                default =>
                    ($propertyType->isBuiltin() ? $value :
                        (is_null($value) ? $value :
                            ($propertyType->getName())::
                                fromPdo($meta, static::decodeTypeAsValue($metaType, $value)))),
            };

        }

        /* Make sure all public properties are initialized. */
        $properties =
            (new \ReflectionClass(static::class))
                ->getProperties(\ReflectionProperty::IS_PUBLIC);

        foreach ($properties as $property) {
            if (!$property->isInitialized($entity)) {
                $propertyName = $property->getName();
                throw new \ValueError("Property '{$propertyName}' not initialized");
            }
        }

        return $entity;
    }

    public static function getCreateTableStatement(\PDO $driver)
    {
        /* Note: this using ANSI SQL quoting style. */
        $tableName = static::$tableName ?? static::camelCaseToSnakeCase(static::class);

        /* We will create columns for all public properties. */
        $properties =
            (new \ReflectionClass(static::class))
                ->getProperties(\ReflectionProperty::IS_PUBLIC);

        $columns = [];
        $indexes = [];
        foreach ($properties as $property) {
            $columnName = static::camelCaseToSnakeCase($property->getName());
            $propertyType = $property->getType();
            $columnType = match ($propertyType->getName()) {
                'int' => 'integer',
                'string' => 'string',
                'DateTime' => 'datetime',
                'array' => 'json',
                default => $property->getName(),
            };
            $columnConstraint = $propertyType->allowsNull() ? '' : 'NOT NULL';

            $attributes = $property->getAttributes();
            foreach ($attributes as $attribute) {
                switch ($attribute->getName()) {
                case 'SqlType':
                    $columnType = $attribute->getArguments()[0];
                    break;
                case 'SqlDefault':
                    $default = $attribute->getArguments()[0];
                    if ($default instanceof DefaultConstant) {
                        $default = $default->value;
                    } else {
                        $default = $driver->quote($default);
                    }
                    $columnConstraint .= " DEFAULT ({$default})";
                    break;
                case 'PrimaryKey':
                    $columnConstraint .= " PRIMARY KEY";
                    break;
                case 'AutoIncrement':
                    $columnConstraint .= " AUTOINCREMENT";
                    break;
                case 'Unique':
                    $columnConstraint .= " UNIQUE";
                    break;
                case 'Indexed':
                    $indexes[] = <<<SQL
                        CREATE INDEX "idx_{$columnName}" ON "{$tableName}"("{$columnName}");
                    SQL;
                    break;
                }
            }

            $columns[] = "\"$columnName\" $columnType $columnConstraint";
        }
        $allColumns = join(', ', $columns);

        var_dump([
            "CREATE TABLE \"{$tableName}\" ($allColumns)",
            ...$indexes
        ]);
        return [
            "CREATE TABLE \"{$tableName}\" ($allColumns)",
            ...$indexes
        ];
    }

    public function getInsertStatementAndValues()
    {
        /* Note: this using ANSI SQL quoting style. */
        $tableName = static::$tableName ?? static::camelCaseToSnakeCase(static::class);

        $properties =
            (new \ReflectionClass($this))
                ->getProperties(\ReflectionProperty::IS_PUBLIC);

        $columns = [];
        $values = [];
        foreach ($properties as $property) {
            $columnName = static::camelCaseToSnakeCase($property->getName());
            if ($property->isInitialized($this)) {
                $columns[] = $columnName;
                $value = $this->{$property->getName()};
                if ($value instanceof Stringable) {
                    $value = (string)$value;
                } elseif ($value instanceof DateTime) {
                    $value = $value->format('c');
                } elseif (is_array($value)) {
                    $value = json_encode($value);
                }
                $values[] = $value;
            }
        }
        $allColumns = '"' . join('","', $columns) . '"';
        $placeholders = join(',', array_pad([], count($values), "?"));
        return [
            "INSERT INTO \"{$tableName}\" ({$allColumns}) VALUES ({$placeholders})",
            $values,
        ];
    }

    /**
     * This is very blunt. The next level of sophistication would be doing
     * dirty-property tracking so we only update what has changed.
     */
    public function getUpdateStatementAndValues()
    {
        /* Note: this using ANSI SQL quoting style. */
        $tableName = static::$tableName ?? static::camelCaseToSnakeCase(static::class);

        $properties =
            (new \ReflectionClass($this))
                ->getProperties(\ReflectionProperty::IS_PUBLIC);

        $primaryKeyColumn = $primaryKeyValue = null;
        $columns = [];
        $values = [];
        foreach ($properties as $property) {
            $columnName = static::camelCaseToSnakeCase($property->getName());
            $value = $this->{$property->getName()};
            $onUpdate = $property->getAttributes('SqlOnUpdate');

            if ($property->getAttributes('PrimaryKey')) {
                $primaryKeyColumn = $columnName;
                $primaryKeyValue = $value;
            } elseif ($onUpdate) {
                $updateValue = $onUpdate[0]->getArguments()[0];
                if ($updateValue instanceof DefaultConstant) {
                    $columns[] = [ $columnName, $updateValue->value ];
                } else {
                    $columns[] = $columnName;
                    $values[] = $updateValue;
                }
            } else {
                if ($value instanceof Stringable) {
                    $value = (string)$value;
                } elseif ($value instanceof DateTime) {
                    $value = $value->format('c');
                } elseif (is_array($value)) {
                    $value = json_encode($value);
                }
                $columns[] = $columnName;
                $values[] = $value;
            }
        }
        $allColumns = array_map(
            function ($column) {
                if (is_array($column)) {
                    return "\"{$column[0]}\" = {$column[1]}";
                } else {
                    return "\"{$column}\" = ?";
                }
            },
            $columns
        );
        $allColumns = join (',', $allColumns);
        $values[] = $primaryKeyValue;
        return [
            "UPDATE \"{$tableName}\" SET {$allColumns} WHERE \"{$primaryKeyColumn}\" = ?",
            $values,
        ];
    }
}

enum DefaultConstant: string
{
    case CurrentTimestamp = "datetime('now')";
}

class SampleEntity extends Entity
{
    #[PrimaryKey, AutoIncrement]
    public int $id;

    #[SqlType('string'), Unique]
    public EmailAddress $emailAddress;

    #[Indexed]
    public string $name;

    #[SqlDefault('whatever')]
    public string $defaulted;

    public ?array $arrayValue;

    #[SqlType('json')]
    public ?Fancy $fancyValue;

    #[SqlDefault(DefaultConstant::CurrentTimestamp)]
    public DateTime $createdAt;

    #[SqlOnUpdate(DefaultConstant::CurrentTimestamp)]
    public ?DateTime $updatedAt;
}

$pdo = new PDO('sqlite::memory:');

$createStatements = SampleEntity::getCreateTableStatement($pdo);

foreach ($createStatements as $statement) {
    $pdo->query($statement);
}

$entities = [
    SampleEntity::create(
        emailAddress: EmailAddress::fromString('abc@example.com'),
        name: 'Bob',
        createdAt: new DateTime('2025-02-01 10:00:00'),
    ),
    SampleEntity::create(
        emailAddress: EmailAddress::fromString('def@example.com'),
        name: 'Sally',
        arrayValue: [1, 2, 3],
    ),
    SampleEntity::create(
        emailAddress: EmailAddress::fromString('ghi@example.com'),
        name: 'Fred',
        arrayValue: ['a' => 'b'],
    ),
    SampleEntity::create(
        emailAddress: EmailAddress::fromString('jkl@example.com'),
        name: 'Doug',
        fancyValue: Fancy::create('foo', [ 'foo' => 'bar', 'baz' => 'bop' ]),
    ),
];


/**
 * This would be the guts of some sort of Repository class, probably.
 */

foreach ($entities as $entity)
{
    [$statement, $values] = $entity->getInsertStatementAndValues();
    $stmt = $pdo->prepare($statement);
    $stmt->execute($values);
}

$res = $pdo->query("SELECT * FROM sample_entity");

$entities = $res->fetchAll(
    PDO::FETCH_FUNC,
    fn (...$cols) => SampleEntity::fromPdoRow($res, $cols)
);

#var_dump($entities);

$res = $pdo->query("SELECT * FROM sample_entity ORDER BY id ASC LIMIT 1");

$entity = $res->fetchAll(
    PDO::FETCH_FUNC,
    fn (...$cols) => SampleEntity::fromPdoRow($res, $cols)
)[0];

$entity->emailAddress = EmailAddress::fromString('mno@example.com');

[$statement, $values] = $entity->getUpdateStatementAndValues();
var_dump([
    'statement' => $statement,
    'values' => $values,
]);
$stmt = $pdo->prepare($statement);
$stmt->execute($values);

$res = $pdo->query("SELECT * FROM sample_entity ORDER BY id ASC");

$entities = $res->fetchAll(
    PDO::FETCH_FUNC,
    fn (...$cols) => SampleEntity::fromPdoRow($res, $cols)
);

var_dump($entities);