Entities

An entity is an ordinary PHP class with two jobs: describing a table and receiving its rows. The schema comes from attributes on the properties, and query results are hydrated into objects of the same class. Below: how a property becomes a column, and every attribute with the SQL it produces on PostgreSQL and MySQL.

What an entity is and why

The problem. A table’s structure lives in two places at once: in the database, and in the developer’s head. Add a column and you have to remember the migration, the class that receives the row, and the query that selects it. A mismatch surfaces at runtime, usually in production.

The solution. The structure is described once — as attributes on the properties of an ordinary PHP class. From that description come both the table in the database and the object a row arrives in. There is a single source of truth, and it is the code.

<?php

namespace Main\Entities;

use Flytachi\Winter\Ppa\Mapping\Attributes\Entity\Table;
use Flytachi\Winter\Ppa\Mapping\Attributes\Hybrid\BigId;
use Flytachi\Winter\Ppa\Mapping\Attributes\Primal\{Decimal, SmallInteger, Timestamp, Varchar};
use Flytachi\Winter\Ppa\Mapping\Attributes\Idx\Unique;
use Flytachi\Winter\Ppa\Mapping\Attributes\Additive\DefaultVal;

#[Table]
class Order
{
  #[BigId]
  public ?int $id = null;

  #[Varchar(32)]
  #[Unique]
  public string $number = '';

  #[SmallInteger]
  public int $status = 0;

  #[Decimal(12, 2)]
  public string $total = '0';

  #[Timestamp]
  #[DefaultVal('NOW()')]
  public string $created_at = '';
}

This class becomes a table:

CREATE TABLE public.orders (
id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
number VARCHAR(32) NOT NULL DEFAULT '',
status SMALLINT NOT NULL DEFAULT 0,
total NUMERIC(12, 2) NOT NULL DEFAULT '0',
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX orders_number_udx ON public.orders USING BTREE (number);

And the same class receives rows back: findById() returns an Order object with its fields filled in.

Three rules worth knowing up front

A column’s name is the property’s name. There is no separate attribute for it: a property called created_at produces a column called created_at. So properties are named the way their columns should be named.

A property’s default value becomes the column’s default. The property public int $status = 0 produces DEFAULT 0 in the table. That is not always obvious and not always wanted — if the database should have no default, declare the property without a value.

A question mark in the property type is what allows an empty value. public string $number produces NOT NULL, while public ?string $comment = null produces a column that accepts NULL. No separate attribute is needed: PHP types already say everything required, and #[NullableIs] exists only for the cases where the database’s decision has to disagree with the property’s type.

Reference

#[Table]

Marks a class as a table description.

Without it the class will not reach a migration: call db migrate will not see it and the table will not be created. The attribute does not set the table’s name — that comes from the $table property of the repository pointing at this entity.

Syntax

#[Table]

Parameters

None.

Example

#[Table]
class Order
{
  // …
}

Keys

A primary key is declared with a single attribute that sets the column type, the auto-generation and the PRIMARY KEY membership all at once. There is no need to also mark the column with a type.

#[Id], #[BigId], #[SmallId]

An integer primary key with auto-increment.

The three attributes differ only in the width of the type. The choice is about how many rows the table will live through: SmallId runs out at 32 thousand, Id at two billion, BigId effectively never.

Syntax

#[Id(bool $always = false)]
#[BigId(bool $always = false)]
#[SmallId(bool $always = false)]

Parameters

$always — forbids inserting the key value by hand. false by default: your own value can be supplied, and the generator is used only when none was given. With true the database rejects any attempt to set the key explicitly.

What you get

Example

#[BigId]
public ?int $id = null;

Result

id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
…
PRIMARY KEY (id)

#[UuidPk]

A primary key as a UUID, generated by the database.

Used where the identifier must not be predictable, or must exist before the database is reached: distributed systems, public addresses, merging data from several sources.

Syntax

#[UuidPk]

Parameters

None.

Example

#[UuidPk]
public ?string $id = null;

Result

id UUID NOT NULL DEFAULT gen_random_uuid(),
…
PRIMARY KEY (id)

#[Migratable]
#[Extension('pgcrypto')]
class MainDbConfig extends PgDbConfig { … }

#[Primary]

Marks a column as part of the primary key without setting a type or enabling auto-generation.

Needed in two cases: a composite primary key made of several columns, and a key whose value arrives from outside rather than being issued by the database.

Syntax

#[Primary]

Parameters

None.

Example

// Composite key: one row per user-and-role pair
#[BigInteger] #[Primary]
public int $user_id = 0;

#[BigInteger] #[Primary]
public int $role_id = 0;

#[AutoIncrement]

Adds auto-increment to a column whose type is declared separately.

Rarely needed: #[Id] and its relatives already include auto-increment. This attribute remains for the case where the type is set by hand — through #[Type], for instance.

Syntax

#[AutoIncrement(bool $always = false)]

Parameters

$always — the same as on #[Id]: forbids supplying the value by hand.

Column types

One attribute per property — it sets the column’s type. The concrete SQL type is chosen for the database the repository is bound to: the dialect comes from its configuration, not from where the call is made.

#[Varchar]

A string of limited length — the most common type for text fields.

Syntax

#[Varchar(int $length = 255)]

Parameters

$length — the maximum length in characters. 255 by default.

Example

#[Varchar(32)]
public string $number = '';

Result

number VARCHAR(32) NOT NULL DEFAULT ''

Note the DEFAULT '': it came from the property’s value. To have no default in the database, declare the property without one — public string $number;.

#[Char]

A string of fixed length. Anything shorter than declared is padded with spaces by the database.

Used for values whose length really is constant: a country code, a currency code, a check character. For everything else there is #[Varchar].

Syntax

#[Char(int $length)]

Parameters

$length — the length in characters. The argument is required: the type makes no sense without it.

Example

#[Char(2)]
public string $lang = 'en';

Result

lang CHAR(2) NOT NULL DEFAULT 'en'

#[Decimal]

An exact decimal number. The only type fit for money.

Floating-point numbers (#[FloatType], #[Double]) store an approximation: in them 0.1 + 0.2 is not 0.3. For amounts that means cents drifting apart as thousands of rows are summed, so money is stored here.

Syntax

#[Decimal(int $precision = 12, int $scale = 2)]

Parameters

$precision — the total number of significant digits, the fractional part included. 12 by default.

$scale — how many of them come after the decimal point. 2 by default.

Decimal(12, 2) holds values up to 9999999999.99.

Example

#[Decimal(12, 2)]
public string $total = '0';

Result

total NUMERIC(12, 2) NOT NULL DEFAULT '0'      -- PostgreSQL
total DECIMAL(12, 2) NOT NULL DEFAULT '0'      -- MySQL

#[Timestamp]

A moment in time — a date together with a time.

Syntax

#[Timestamp(bool $withTimeZone = true)]

Parameters

$withTimeZone — whether to store the time zone. true by default.

The zone is worth storing whenever the moment relates to real time: an order was placed, a letter was sent, a session expires. Without a zone, 12:00 is noon somewhere unknown, and after a server move or a daylight-saving change the meaning cannot be recovered.

Example

#[Timestamp]
#[DefaultVal('NOW()')]
public string $created_at = '';

Result

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()   -- PostgreSQL
created_at TIMESTAMP NOT NULL DEFAULT NOW()                  -- MySQL

#[Uuid]

A UUID column — an ordinary one, not a primary key.

For a primary key there is #[UuidPk]: it additionally turns on generation and PRIMARY KEY.

Syntax

#[Uuid(bool $asBinary = false)]

Parameters

$asBinary — whether to store the value in binary form. false by default, meaning the UUID is stored as it is.

Binary storage takes 16 bytes instead of 36 and saves noticeably on a large table, but the value stops being readable in a database console.

Example

#[Uuid]
public ?string $author_id = null;

#[Binary]

Binary data of fixed length: a hash, a signature, a key.

Syntax

#[Binary(int $length = 255)]

Parameters

$length — the length in bytes. 255 by default.

#[Blob]

Binary data of arbitrary size: a file, an image, an archive.

Syntax

#[Blob(string $size = 'default')]

Parameters

$size — the size variant of the type: 'tiny', 'default', 'medium', 'long'. It affects the upper bound and the type the database picks.

#[Type]

Sets the SQL type literally — as written.

An escape hatch for types that have no attribute of their own: database-specific inet, tsvector, geometry, user-defined enumerations, extensions.

Syntax

#[Type(string $definition)]

Parameters

$definition — the type definition exactly as it should appear in CREATE TABLE.

Example

#[Type('inet')]
public ?string $ip = null;

Result

ip inet DEFAULT NULL

Types without arguments

The remaining type attributes have nothing to configure — they simply name a type.

Indexes

An index is an auxiliary structure the database uses to find rows without scanning the whole table. Without one, WHERE number = 'A-1042' reads every row in turn; with one it goes straight to the row it needs. The price is disk space and a slight slowdown on inserts: every index is updated along with the table.

Both attributes go on a property, and that property’s column automatically becomes the first in the index.

#[Index]

An ordinary index — it speeds up lookups without restricting values.

Syntax

#[Index(
  array $columns = [],
  ?string $name = null,
  IndexMethod $method = IndexMethod::BTREE,
  ?string $where = null,
  ?string $opClass = null,
)]

Parameters

$columns — additional columns for a composite index. The property’s own column is added first automatically and does not need listing. By default the index covers one column.

$name — the middle part of the index name. The full name is assembled as <table>_<name>_idx; if not set, the column names joined by underscores take the place of name.

$method — the index structure: IndexMethod::BTREE (the default), HASH, GIN, GIST. BTREE fits almost everything — it handles equality, ranges and ordering. GIN is for searching inside composite values: JSON keys, array elements, words in text.

$where — the condition of a partial index: only rows satisfying it are indexed. An index conditioned on shipped_at IS NOT NULL, on a table where a tenth of the orders have shipped, takes ten times less space and serves the same queries.

$opClass — a PostgreSQL operator class for the first column. It is appended to the column name in CREATE INDEX and determines which operators the index applies to: gin_trgm_ops, for example, enables substring search.

The attribute is repeatable — a property may carry several #[Index] if the column takes part in different indexes.

Example

#[BigInteger]
#[Index]
public int $customer_id = 0;

#[Json]
#[Index(method: IndexMethod::GIN)]
public array $meta = [];

#[Timestamp]
#[Index(where: 'shipped_at IS NOT NULL')]
public ?string $shipped_at = null;

Result

CREATE INDEX orders_customer_id_idx ON orders USING BTREE (customer_id);
CREATE INDEX orders_meta_idx ON orders USING GIN (meta);
CREATE INDEX orders_shipped_at_idx ON orders USING BTREE (shipped_at) WHERE shipped_at IS NOT NULL;

A composite index

// An index on the pair (author, year) — the property comes first
#[Varchar(64)]
#[Index(['year'])]
public string $author = '';

CREATE INDEX books_author_year_idx ON books USING BTREE (author, year);

#[Unique]

A unique index — it speeds up lookups and forbids duplicates at the same time.

It differs from #[Index] in one thing: the database rejects a row whose value is already in the table. This is the only reliable way to guarantee uniqueness — a SELECT-then-INSERT check in code loses the race to two simultaneous requests, while an index never does.

Syntax

#[Unique(
  array $columns = [],
  ?string $name = null,
  IndexMethod $method = IndexMethod::BTREE,
  ?string $where = null,
  ?string $opClass = null,
)]

Parameters

The same as on #[Index]. The name suffix is _udx instead of _idx.

The attribute is repeatable.

Example

#[Varchar(32)]
#[Unique]
public string $number = '';

Result

CREATE UNIQUE INDEX orders_number_udx ON orders USING BTREE (number);

Composite uniqueness

// The same book title may repeat, but not within one year
#[Varchar(64)]
#[Unique(['year'], name: 'title_year')]
public string $title = '';

CREATE UNIQUE INDEX books_title_year_udx ON books USING BTREE (title, year);

Constraints

A constraint is a rule the database checks itself on every write. The point is that it cannot be bypassed: neither a bug in the code, nor a manual UPDATE from a console, nor a second service writing to the same table can leave the data in a state the rule forbids.

#[Check]

A condition every row must satisfy.

The expression is written in SQL and may refer to any column of the table, not only the one carrying the attribute. A row for which it is false will not be written.

Syntax

#[Check(string $expression, ?string $name = null)]

Parameters

$expression — the SQL condition as it should appear in CHECK (…).

$name — the constraint name. If not set, one is generated from a hash of the expression.

The attribute is repeatable.

Example

#[Decimal(12, 2)]
#[Check('total >= 0')]
public string $total = '0';

Result

ALTER TABLE orders
ADD CONSTRAINT chk_orders_a0f6358602c3db66492a6ac4bcdb3862 CHECK (total >= 0);

#[CheckEnum]

Restricts a column to the values of a PHP enumeration.

It removes a duplication: the list of allowed values already exists in the enum, and copying it by hand into a #[Check] means a second source of truth that will one day disagree with the first. The attribute reads the cases from the enumeration class and builds the IN (…) condition itself.

Syntax

#[CheckEnum(string $enumClassName, ?string $name = null)]

Parameters

$enumClassName — the enumeration class name, usually OrderStatus::class.

$name — the constraint name; generated from a hash by default.

Errors

InvalidArgumentException — if the enumeration class does not exist or is not typed. Only a backed enum will do (enum X: string or enum X: int): a plain enumeration has no values that could be written into a column.

Example

enum OrderStatus: string
{
  case NEW     = 'new';
  case PAID    = 'paid';
  case SHIPPED = 'shipped';
}

#[Varchar(16)]
#[CheckEnum(OrderStatus::class)]
public string $status = 'new';

Result

ALTER TABLE orders
ADD CONSTRAINT chk_orders_51f931f425fae7ce2821bcbe50a8660c
CHECK (status IN ('new', 'paid', 'shipped'));

#[ForeignKey]

A foreign key — a link to a column of another table.

The database starts enforcing that the value in the column exists in the table it points at: inserting an order with a non-existent customer_id becomes impossible. It also decides what happens when the row on the other side is deleted or changes its key.

Syntax

#[ForeignKey(
  string $referencedTable,
  string $referencedColumn,
  FKAction $onUpdate = FKAction::RESTRICT,
  FKAction $onDelete = FKAction::RESTRICT,
  ?string $name = null,
)]

Parameters

$referencedTable — the name of the table the key points at.

$referencedColumn — the column in it; almost always the primary key.

$onUpdate — what to do when the value in the parent column changes.

$onDelete — what to do when the parent row is deleted.

$name — the constraint name. fk_<table>_<column> by default.

FKAction actions

The attribute is repeatable.

Example

#[BigInteger]
#[ForeignKey('customers', 'id', onDelete: FKAction::CASCADE)]
public int $customer_id = 0;

Result

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY (customer_id)
REFERENCES customers(id) ON DELETE CASCADE ON UPDATE RESTRICT;

#[ForeignRepo]

The same foreign key, but the target table and column are taken from a repository.

The difference from #[ForeignKey] is where the names come from. Here you name a repository class, and it reports the table name and the primary key name itself. Renaming a table in the repository then reaches the foreign key automatically, and a typo in the name becomes impossible: the class either exists or the code does not start.

Syntax

#[ForeignRepo(
  string $referencedRepoClass,
  FKAction $onUpdate = FKAction::RESTRICT,
  FKAction $onDelete = FKAction::RESTRICT,
  ?string $name = null,
)]

Parameters

$referencedRepoClass — the repository class of the target table, CustomerRepository::class.

$onUpdate, $onDelete, $name — the same as on #[ForeignKey].

Errors

InvalidArgumentException — if the named class is not a PPA repository.

The attribute is repeatable.

Example

#[BigInteger]
#[ForeignRepo(CustomerRepository::class, onDelete: FKAction::CASCADE)]
public int $customer_id = 0;

The result is the same ALTER TABLE as for #[ForeignKey]; the table name customers and the column id came from the repository.

Adjustments

Two attributes that describe nothing themselves and instead correct what was inferred from the property’s type and value.

#[NullableIs]

States explicitly whether the column accepts NULL.

Needed where the database’s decision has to disagree with the property’s type. In the ordinary case it is not required: ?string already produces a column with NULL, and string produces NOT NULL.

Syntax

#[NullableIs(bool $isNullable = true)]

Parameters

$isNullable — whether NULL is allowed. true by default.

Example

// The property must be a string in PHP,
// but older rows of the table hold NULL in that place.
#[Text]
#[NullableIs]
public string $comment = '';

Result

comment TEXT DEFAULT ''

There is no NOT NULL — the column would accept NULL even though the property’s type does not. The DEFAULT '' is left over from the property’s value.

#[DefaultVal]

Sets the column’s default value as an SQL expression.

It differs from a property’s value in that the database computes it, not PHP: NOW() gives the moment the row was inserted, not the moment the object was created in code. The same attribute is used when the value simply does not exist in PHP — a database function, an extension call, a reference to another column.

Syntax

#[DefaultVal(string $definition)]

Parameters

$definition — the SQL expression substituted into DEFAULT as it is. A string literal has to be quoted by hand: #[DefaultVal("'new'")].

Example

#[Timestamp]
#[DefaultVal('NOW()')]
public string $created_at = '';

Result

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()

How it all comes together

Names in the schema

None of the names are set by hand — all of them are derived, and the rules are worth knowing in advance, because these are exactly the names you will see in the database’s error messages.

A full example

An order with everything that turns up in practice: a key, a unique number, a link to a customer, a restricted list of statuses, money, a free-form document and two moments in time.

<?php

namespace Main\Entities;

use Flytachi\Winter\Ppa\Mapping\Attributes\Entity\Table;
use Flytachi\Winter\Ppa\Mapping\Attributes\Hybrid\BigId;
use Flytachi\Winter\Ppa\Mapping\Attributes\Primal\{BigInteger, Char, Decimal, Json, Text, Timestamp, Varchar};
use Flytachi\Winter\Ppa\Mapping\Attributes\Idx\{Index, Unique};
use Flytachi\Winter\Ppa\Mapping\Attributes\Constraint\{Check, CheckEnum, ForeignKey};
use Flytachi\Winter\Ppa\Mapping\Attributes\Additive\DefaultVal;
use Flytachi\Winter\Ppa\Mapping\Constants\{FKAction, IndexMethod};

#[Table]
class Order
{
  #[BigId]
  public ?int $id = null;

  #[Varchar(32)]
  #[Unique]
  public string $number = '';

  #[BigInteger]
  #[ForeignKey('customers', 'id', onDelete: FKAction::CASCADE)]
  #[Index]
  public int $customer_id = 0;

  #[Varchar(16)]
  #[CheckEnum(OrderStatus::class)]
  public string $status = 'new';

  #[Decimal(12, 2)]
  #[Check('total >= 0')]
  public string $total = '0';

  #[Char(3)]
  public string $currency = 'USD';

  #[Json]
  #[Index(method: IndexMethod::GIN)]
  public array $meta = [];

  #[Text]
  public ?string $comment = null;

  #[Timestamp]
  #[DefaultVal('NOW()')]
  public string $created_at = '';

  #[Timestamp]
  #[Index(where: 'shipped_at IS NOT NULL')]
  public ?string $shipped_at = null;
}

What comes out on PostgreSQL

CREATE TABLE orders (
id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
number VARCHAR(32) NOT NULL DEFAULT '',
customer_id BIGINT NOT NULL DEFAULT 0,
status VARCHAR(16) NOT NULL DEFAULT 'new',
total NUMERIC(12, 2) NOT NULL DEFAULT '0',
currency CHAR(3) NOT NULL DEFAULT 'USD',
meta JSONB NOT NULL DEFAULT '[]'::jsonb,
comment TEXT DEFAULT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
shipped_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX orders_number_udx ON orders USING BTREE (number);
CREATE INDEX orders_customer_id_idx ON orders USING BTREE (customer_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY (customer_id)
REFERENCES customers(id) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE orders ADD CONSTRAINT chk_orders_51f931f425fae7ce2821bcbe50a8660c
CHECK (status IN ('new', 'paid', 'shipped'));
ALTER TABLE orders ADD CONSTRAINT chk_orders_a0f6358602c3db66492a6ac4bcdb3862
CHECK (total >= 0);
CREATE INDEX orders_meta_idx ON orders USING GIN (meta);
CREATE INDEX orders_shipped_at_idx ON orders USING BTREE (shipped_at)
WHERE shipped_at IS NOT NULL;

What comes out on MySQL

The same class, a different database — the differences show up line by line:

CREATE TABLE orders (
id BIGINT AUTO_INCREMENT NOT NULL,
number VARCHAR(32) NOT NULL DEFAULT '',
customer_id BIGINT NOT NULL DEFAULT 0,
status VARCHAR(16) NOT NULL DEFAULT 'new',
total DECIMAL(12, 2) NOT NULL DEFAULT '0',
currency CHAR(3) NOT NULL DEFAULT 'USD',
meta JSON NOT NULL DEFAULT ('[]'),
comment TEXT DEFAULT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
shipped_at TIMESTAMP DEFAULT NULL,
PRIMARY KEY (id)
);

The auto-increment is spelled its own way, NUMERIC became DECIMAL, JSONB became JSON, and the time zone on TIMESTAMP is gone: MySQL has no such type with a zone. Indexes and constraints match, except in the two places the #[Index] section warns about: MySQL will not execute USING GIN, and the WHERE of a partial index is dropped.

Applying it to the database

An entity does not create a table by itself. The schema is brought into line by a command:

php call db migrate

It walks the classes marked with #[Table] and creates what is not in the database yet — tables, indexes, constraints. What exactly will run can be inspected in advance, without touching the database:

php call db sql

Details are on the Migrations page.

Next

• Repositories — how to read and write these objects
• Migrations — how the description reaches the database
• Database configuration — connection, schema, extensions
• Pagination — page-by-page listing