Database ​

Sugoi includes a lightweight ORM. Most ORMs provide Model driven patterns, Sugoi tries to avoid this entirely since requirements always end up becoming projection/DTO based, so here we do it from the start.

With this approach it becomes easier to do data mappings across several different persistence layers, fetching only the data you need. This leads to both simplicity and massively improved performance, developer ergonomics will always be tuned for the edge-cases first to keep the ORM feeling consistent.

Table Schemas ​

Schema definitions are not Models, these are plain schema definitions that define the columns and types of your table. These can then be used for the query DSL to write the actual queries as you would write plain SQL, except with type-safety!

php
#[TableSchema('public.albums')]
class AlbumTable extends IdTable
{
    #[Column(Type::STRING_UUID)]
    public static Column $id;

    #[Column(Type::STRING)]
    public static Column $name;

    #[Column(Type::STRING)]
    public static Column $description;

    #[Column(Type::STRING)]
    public static Column $genre;

    #[Column(Type::DATE_TIME, 'published_at')]
    public static Column $publishedAt;

    #[Column(Type::DATE_TIME, 'updated_at')]
    public static Column $updatedAt;

    #[Column(Type::DATE_TIME, 'created_at')]
    public static Column $createdAt;
}

DTO ​

DTOs can be defined like usual, but to get improved type-hinting in your IDE, it's recommended to define your transforms on the DTOs themselves.

php
#[Schema]
readonly class Album
{
    public function __construct(
        #[Property(type: Type::STRING_UUID, access: Access::READ)]
        public ?string            $id,
        #[Property(example: 'Late Night Coding')]
        public string             $name,
        public ?string            $description,
        #[Property(example: 'Eurobeat')]
        public ?string            $genre,
        #[PropertyDateTime]
        public ?DateTimeImmutable $publishedAt,
        #[PropertyDateTime(access: Access::READ)]
        public ?DateTimeImmutable $updatedAt,
        #[PropertyDateTime(access: Access::READ)]
        public ?DateTimeImmutable $createdAt,
    )
    {
    }

    /**
     * @return callable(Row): self
     */
    public static function fromDatabase(): callable
    {
        return static fn(Row $row) => new Album(
            id: $row->get(AlbumTable::$id),
            name: $row->get(AlbumTable::$name),
            description: $row->get(AlbumTable::$description),
            genre: $row->get(AlbumTable::$genre),
            publishedAt: $row->get(AlbumTable::$publishedAt),
            updatedAt: $row->get(AlbumTable::$updatedAt),
            createdAt: $row->get(AlbumTable::$createdAt),
        );
    }
}

Queries ​

Select ​

php
// Select *
$albums = AlbumTable::query()
            ->select()
            ->map(Album::fromDatabase());

// Select specific columns.
$albumNames = AlbumTable::query()
            ->select(AlbumTable::$name)
            ->map(fn(Row $row) => $row->get(AlbumTable::$name));

// Select one item.
$album = AlbumTable::query()
            ->select()
            ->where(AlbumTable::$id->eq('260061b0-8a27-4022-9b5b-2be2209c53fe'))
            ->let(Album::fromDatabase()) ?? throw NotFoundException();

Insert ​

php
return AlbumTable::query()
            ->insert()
            ->values(fn(Row $row) => $row
                ->set(AlbumTable::$name, 'New mix-tape 🔥')
                ->set(AlbumTable::$description, 'It\'s lit')
                ->setRaw(AlbumTable::$genre, 'Traditional')
                ->setNull(AlbumTable::$publishedAt)
                ->setNow(AlbumTable::$updatedAt)
                ->setNow(AlbumTable::$createdAt)
            )
            ->let(Album::fromDatabase());

Update ​

php
AlbumTable::query()
            ->update()
            ->values(fn(Row $row) => $row
                ->set(AlbumTable::$name, 'New mix-tape 🔥')
                ->set(AlbumTable::$description, 'It\'s lit')
                ->setRaw(AlbumTable::$genre, 'Eurobeat')
                ->setNull(AlbumTable::$publishedAt)
                ->setNow(AlbumTable::$updatedAt)
                ->setNow(AlbumTable::$createdAt)
            )
            ->where(AlbumTable::$id->eq('260061b0-8a27-4022-9b5b-2be2209c53fe'))
            ->exec() ?: throw new NotFoundException();

Delete ​

php
AlbumTable::query()
            ->delete()
            ->where(AlbumTable::$id->eq('260061b0-8a27-4022-9b5b-2be2209c53fe'))
            ->exec() ?: throw new NotFoundException();

Implementation example ​

php
class AlbumService
{
    /**
     * @return array<Album>
     */
    public function list(): array
    {
        return AlbumTable::query()
            ->select()
            ->map(Album::fromDatabase());
    }

    /**
     * @param string $id
     * @return Album
     * @throws NotFoundException
     */
    public function get(string $id): Album
    {
        return AlbumTable::query()
            ->select()
            ->where(AlbumTable::$id->eq($id))
            ->let(Album::fromDatabase()) ?? throw new NotFoundException();
    }

    /**
     * @param Album $form
     * @return Album
     */
    public function create(Album $form): Album
    {
        return AlbumTable::query()
            ->insert()
            ->values(fn(Row $row) => $row
                ->set(AlbumTable::$name, $form->name)
                ->set(AlbumTable::$description, $form->description)
                ->set(AlbumTable::$genre, $form->genre)
                ->set(AlbumTable::$publishedAt, $form->publishedAt)
                ->setNow(AlbumTable::$updatedAt)
                ->setNow(AlbumTable::$createdAt)
            )
            ->let(Album::fromDatabase());
    }

    /**
     * @param string $id
     * @param Album $form
     * @return void
     * @throws NotFoundException
     */
    public function update(string $id, Album $form): void
    {
        AlbumTable::query()
            ->update()
            ->values(fn(Row $row) => $row
                ->set(AlbumTable::$name, $form->name)
                ->set(AlbumTable::$description, $form->description)
                ->set(AlbumTable::$genre, $form->genre)
                ->set(AlbumTable::$publishedAt, $form->publishedAt)
                ->setNow(AlbumTable::$updatedAt)
            )
            ->where(AlbumTable::$id->eq($id))
            ->exec() ?: throw new NotFoundException();
    }

    /**
     * @param string $id
     * @return void
     * @throws NotFoundException
     */
    public function delete(string $id): void
    {
        AlbumTable::query()
            ->delete()
            ->where(AlbumTable::$id->eq($id))
            ->exec() ?: throw new NotFoundException();
    }
}