PicoRM

The smallest and simplest ORM for your MVP

Requires

• PHP version 7.2 or higher
• PDO
• MySQL

Can

• simplify MySQL database interactions via PDO
• apply common prefixes to table names
• SELECT rows and return them as simple entity objects using built-in methods
• UPDATE, INSERT, and DELETE entities
• map any custom query results to any custom entity class
• JOIN results as nested mapped entity objects

Can’t

• work with other databases
• work with multiple connections simultaneously
• build relations for tables with arbitrary structures
• DELETE entities using queries with JOINs (this should be handled using FOREIGN KEYS)
• work correctly with tables having complex PRIMARY KEYs or having no PRIMARY KEYs

Rules for correct operation

• All models have to extend PicoRM\PicoRM\Model.
• The models understand table structure using their constructor’s arguments.
• It’s strongly recommended to typify these arguments.
• Arguments representing nested objects have be typified as classes.
• Arguments representing lists of nested objects have be typified as arrays.
• Referencing columns should be named like _ for correct relation mapping.
• Property defined as PRIMARY KEY have to be nullable if PRIMARY KEY is MySQL-generated.

Start

1) Create model

namespace App\Models;

class User extends \PicoRM\PicoRM\Model
{
    public $id;
    public $login;
    public $created_at;
    public $updated_at;

    public function __construct(
        ?int $id,
        string $login,
        int $created_at,
        int $updated_at
    )
    {
        $this->id = $id;
        $this->login = $login;
        $this->created_at = $created_at;
        $this->updated_at = $updated_at;
    }

    public static function getTableName(): string
    {
        return 'user';
    }

    public static function getPkColumn(): string
    {
        return 'id';
    }
}

2) Create table

CREATE TABLE `prefix_user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `login` VARCHAR(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `created_at` INT NOT NULL,
    `updated_at` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3) Setup connection

use PicoRM\PicoRM\ORM;

$pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

ORM::config($pdo, 'prefix_');

4) Insert some rows

use PicoRM\PicoRM\ORM;

ORM::insert([
    new User(null, 'First',  time(), time()),
    new User(null, 'Second', time(), time()),
    new User(null, 'Third',  time(), time()),
]);

5) Select some rows

use App\Models\User;
use PicoRM\PicoRM\ORM;

$users = User::findAll();

print_r($users);
/*
Array
(
    [1] => App\Model\User Object
        (
            [id] => 1
            [login] => First
            [created_at] => 1726861822
            [updated_at] => 1726861822
        )
    .....
)
*/

6) Modify some rows

use App\Models\User;
use PicoRM\PicoRM\ORM;

$user1 = User::find(1);
$user2 = User::find(2);
$user1->updated_at = time();
$user2->updated_at = time();

ORM::update([
    $user1,
    $user2,
]);

6) Remove some rows

use App\Models\User;
use PicoRM\PicoRM\ORM;

$user2 = User::find(2);
$user3 = User::find(3);

ORM::delete([
    $user2,
    $user3,
]);

7) Create related model

namespace App\Models;

class Message extends \PicoRM\PicoRM\Model
{
    public $id;
    public $user_id;
    public $text;
    public $created_at;
    public $updated_at;
    public $user;

    public function __construct(
        ?int $id,
        int $user_id,
        string $text,
        int $created_at,
        int $updated_at,
        ?\App\Models\User $user = null
    )
    {
        $this->id = $id;
        $this->user_id = $user_id;
        $this->text = $text;
        $this->created_at = $created_at;
        $this->updated_at = $updated_at;
        $this->user = $user;
    }

    public static function getTableName(): string
    {
        return 'message';
    }

    public static function getPkColumn(): string
    {
        return 'id';
    }
}

CREATE TABLE `prefix_message` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `text` TEXT,
    `created_at` INT NOT NULL,
    `updated_at` INT NOT NULL,
    CONSTRAINT `fk_1` FOREIGN KEY (`user_id`) REFERENCES `prefix_user` (`id`) ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

namespace App\Models;

class User extends \PicoRM\PicoRM\Model
{
    public $id;
    public $login;
    public $created_at;
    public $updated_at;
+   public $messages;

    public function __construct(
        ?int $id,
        string $login,
        int $created_at,
        int $updated_at,
+       array $messages
    )
    {
        $this->id = $id;
        $this->login = $login;
        $this->created_at = $created_at;
        $this->updated_at = $updated_at;
+       $this->messages = $messages;
    }

    public static function getTableName(): string
    {
        return 'user';
    }

    public static function getPkColumn(): string
    {
        return 'id';
    }
}

10) Insert new relations

use PicoRM\PicoRM\ORM;

//attach messages to user #1
ORM::insert([
    new Message(null, 1, 'Congratulations, You are registered here!',  time(), time()),
    new Message(null, 1, 'Now you can get our exclusive features!', time(), time()),
]);

11) Select rows with their relations

use PicoRM\PicoRM\ORM;
use App\Models\User;
use App\Models\Message;

//SELECT * FROM user LEFT JOIN message WHERE user.id=1
$user = User::find(1, ['messages' => Message::class]);

print_r($user);

//SELECT * FROM message LEFT JOIN user WHERE message.id=1
$message = Message::find(1, ['user' => User::class]);

print_r($message);

12) Alternative way to insert relations

use PicoRM\PicoRM\ORM;

$user = User::find(1);

//attach messages to user #1
ORM::insert([ //set user_id as 0, it's value does not matter
    new Message(null, 0, 'Congratulations, You...',  time(), time(), $user),
    new Message(null, 0, 'Now you can get...', time(), time(), $user),
]);

13) Let’s bind data of custom query to custom model

namespace App\Models;

class ExtendedUser extends \PicoRM\PicoRM\Model
{
    public $id;
    public $login;
    public $messagesNumber = 0;
    public $created_at;
    public $updated_at;

    public function __construct(
        ?int $id,
        string $login,
        ?int $messagesNumber,
        int $created_at,
        int $updated_at
    )
    {
        $this->id = $id;
        $this->login = $login;
        $this->messagesNumber = $messagesNumber;
        $this->created_at = $created_at;
        $this->updated_at = $updated_at;
    }

    public static function getTableName(): string
    {
        return 'user';
    }

    public static function getPkColumn(): string
    {
        return 'id';
    }
}

use PicoRM\PicoRM\ORM;
use App\Models\ExtendedUser;

$statement = ORM::query("SELECT 
    a.id, a.login, COUNT(b.id) as 'messagesNumber', a.`created_at`, a.`updated_at` 
    FROM `prefix_user` a 
    LEFT JOIN `prefix_messages` b ON a.`id` = b.`user_id` 
    GROUP BY a.`id`");
$results = $statement->fetchAll();
$users   = ExtendedUser::map($results);
print_r($users);
/*
Array
(
    [1] => App\Models\ExtendedUser Object
        (
            [id] => 1
            [login] => First
            [messagesNumber] => 2
            [created_at] => 1726861822
            [updated_at] => 1726861822
        )
    .....
)
*/

// You can also delete, insert and update these models but check MySQL table's defaults