Entities and repositories support via attributes and repository generator
MIT License
Simple & safe implementation of handling SQL entities and repositories as well as multi-table SQL queries while staying lightweight and easy to understand and use. Offers rapid application development by generating repositories (which should not be added to VCS) for entities, and squirrelphp/entities-bundle offers automatic integration of these repositories into Symfony.
This library builds upon squirrelphp/queries and works in a similar way: the interfaces, method names and the query builder look and feel almost the same and are just at a higher abstraction level with entities and typed field properties.
composer require squirrelphp/entities
If you have used an ORM like Doctrine this will feel similar at first, although the functionality is different. Below is an example how an entity can be defined with attributes:
namespace Application\Entity;
use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;
#[Entity("users")]
class User
{
#[Field("user_id", autoincrement: true)]
private int $userId;
#[Field("active")]
private bool $active;
#[Field("street_name")]
private ?string $streetName;
#[Field("street_number")]
private ?string $streetNumber;
#[Field("city")]
private string $city;
#[Field("balance")]
private float $balance;
#[Field("picture_file", blob: true)]
private ?string $picture;
#[Field("visits")]
private int $visitsNumber;
}
The class is defined as an entity with the table name, and each class property is defined as a table field with the column name in the database, where the type is taken from the PHP property type (string, int, float, bool). If the property type is nullable, the column type is assumed to be nullable too. You can also define whether it is an autoincrement column (called SERIAL in Postgres) and whether it is a blob column (binary large object, called "blob" in most databases or "bytea" in Postgres).
Whether the class properties are private, protected or public does not matter (this library does not use the constructor to create entities), you can choose whatever names you want, and you can design the rest of the class however you want. You can even make the classes or properties read-only - see Read-only entity objects for more details on why you would want to do that.
This is not currently recommended, but if you are not using squirrelphp/entities-bundle and want to manually configure/create entities, you can create RepositoryConfig objects - with entities-bundle these are created automatically for you (using reflection). The attributes in the User
example in the last section would be equivalent to this RepositoryConfig definition:
$repositoryConfig = new \Squirrel\Entities\RepositoryConfig(
'', // connectionName, none defined
'users', // tableName
[ // tableToObjectFields, mapping table column names to object property names
'user_id' => 'userId',
'active' => 'active',
'street_name' => 'streetName',
'street_number' => 'streetNumber',
'city' => 'city',
'balance' => 'balance',
'picture_file' => 'picture',
'visits' => 'visitsNumber',
],
[ // objectToTableFields, mapping object property names to table column names
'userId' => 'user_id',
'active' => 'active',
'streetName' => 'street_name',
'streetNumber' => 'street_number',
'city' => 'city',
'balance' => 'balance',
'picture' => 'picture_file',
'visitsNumber' => 'visits',
],
\Application\Entity\User::class, // object class
[ // objectTypes, which class properties should have which database type
'userId' => 'int',
'active' => 'bool',
'streetName' => 'string',
'streetNumber' => 'string',
'city' => 'string',
'balance' => 'float',
'picture' => 'blob',
'visitsNumber' => 'int',
],
[ // objectTypesNullable, which fields can be NULL
'userId' => false,
'active' => false,
'streetName' => true,
'streetNumber' => true,
'city' => false,
'balance' => false,
'picture' => true,
'visitsNumber' => false,
],
'user_id' // Table field name of the autoincrement column - if there is none this is an empty string
);
You need to define repositories for each entity in order to use them, and create RepositoryConfig classes from the entity classes (squirrelphp/entities-bundle does this for you, so you don't need to care too much about these steps).
The repositories only need a DBInterface
service (from squirrelphp/queries) and the RepositoryConfig object. There are read-only repositories and writeable repositories so you can more easily restrict where and how your data gets changed. These are the base repository classes:
They offer almost the same functionality as DBInterface
in squirrelphp/queries
, but they do additional steps to avoid mistakes:
This makes it hard to write invalid queries which are not identified as such before executing them, and removes the need to do any tedious type conversions.
Although you could use the base repositories directly, the builder repositories are easier to use and make your queries more readable - these are very similar to the query builder in squirrelphp/queries. This library assumes you use the builder repositories.
Builder repositories need to be generated for all entities, in order to have proper type hints (for easier coding and static analyzers) and to have individual classes for all entities to use in dependency injection.
You can use the squirrel_repositories_generate command in this library to generate the repositories and .gitignore files automatically - run it like this:
vendor/bin/squirrel_repositories_generate --source-dir=src
You can define multiple source-dirs:
vendor/bin/squirrel_repositories_generate --source-dir=src/Entity --source-dir=src/Domain/Entity
Whenever an entity with the library attributes is found, the following files are created in the same directory of the entity class:
RepositoryReadOnly
to the entity class nameRepositoryWriteable
to the entity class nameThis means you do not need to ever edit these generated repositories and should never commit them to git. They are within the responsibility of this library, not of your application.
Our entity example User
would generate the following classes in the same directory as the entity:
As a stark difference to "normal" ORMs, the entity class is only used when getting results from the database, not to write any changes to the database, which is why it does not matter how you use or design the entity classes except for the necessary attributes.
All examples use the generated builder repositories, not the base repositories. Your IDE will give you appropriate type hints and suggestions on what methods can be used, or you can look at the generated builder repositories.
$users = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->select()
->where([
'active' => true,
'userId' => [5, 77, 186],
])
->orderBy([
'balance' => 'DESC',
])
->getAllEntries();
foreach ($users as $user) {
// Each $user entry is an instance of Application\Entity\User
}
If you only need certain fields in the table, you can define those you want explicitely:
$users = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->select()
->fields([
'userId',
'active',
'city',
])
->where([
'active' => true,
'userId' => [5, 77, 186],
])
->orderBy([
'balance' => 'DESC',
])
->getAllEntries();
foreach ($users as $user) {
// Only 'userId', 'active' and 'city' have been populated in the entity instances
}
Or if you only want a list of user IDs, you can get only those with getFlattenedFields
:
$userIds = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->select()
->fields([
'userId',
])
->where([
'active' => true,
'userId' => [5, 77, 186],
])
->orderBy([
'balance' => 'DESC',
])
->getFlattenedFields();
foreach ($userIds as $userId) {
// Each $userId is an integer with the user ID
}
You can enforce a type on the flattened fields by using getFlattenedIntegerFields
, getFlattenedFloatFields
, getFlattenedStringFields
or getFlattenedBooleanFields
. This is recommended in order to be more type safe and make it easier for static analyzers/IDEs to understand your code. This library will then attempt to convert all values to the requested type and throw a DBInvalidOptionException
if there is any ambiguity.
If you want to get one entry after another, you can use the select builder as an iterator:
$userBuilder = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->select()
->where([
'active' => true,
'userId' => [5, 77, 186],
])
->orderBy([
'balance' => 'DESC',
]);
foreach ($userBuilder as $user) {
// The query is executed when the foreach loop starts,
// and one entry after another is retrieved until no more results exist
}
Or if you just need exactly one entry, you can use `getOneEntry':
$user = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->select()
->where([
'userId' => 13,
])
->getOneEntry();
// $user is now either null, if the entry was not found,
// or an instance of Application\Entity\User
If the SELECT query is done within a transaction, you might want to block the retrieved entries so they aren't changed by another query before the transaction finishes - you can use blocking()
for that:
$user = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->select()
->where([
'userId' => 13,
])
->blocking()
->getOneEntry();
The SELECT query is done with ... FOR UPDATE
at the end in the above query.
Often you just want to know how many entries there are, which is where count
comes in:
$activeUsersNumber = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
->count()
->where([
'active' => true,
])
->getNumber();
// $activeUsersNumber is an integer
if ($activeUsersNumber === 0) {
throw new \Exception('No users found!');
}
You can block changes to the counted entries by using blocking()
and putting the count query within a transaction, although this can easily lock many entries in a table and lead to deadlocks - use it cautiously.
$newUserId = $userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->insert()
->set([
'active' => true,
'city' => 'London',
'balance' => 500,
])
->writeAndReturnNewId();
writeAndReturnNewId
only works if you have specified an autoincrement column, otherwise it will throw a DBInvalidOptionException
- use write
if there is no autoincrement column (or you do not need its value).
$foundRows = $userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->update()
->set([
'active' => false,
'city' => 'Paris',
])
->where([
'userId' => 5,
])
->writeAndReturnAffectedNumber();
The number of affected rows is just the rows which match the WHERE clause in the database. You can use write
instead if you are not interested in this number.
You can do an UPDATE without a WHERE clause, updating all entries in the table, but you need to explicitely tell the builder because we want to avoid accidental "UPDATE all" queries:
$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->update()
->set([
'active' => false,
])
->confirmNoWhereRestrictions()
->write();
Insert an entry if it does not exist yet, or otherwise update the existing entry. This functionality exists because it can be executed as one atomic query in the database, making it faster and more efficient than doing your own separate queries in a transaction. It is commonly known as UPSERT (update-or-insert), for MySQL with the syntax INSERT ... ON DUPLICATE KEY UPDATE ...
and for Postgres/SQLite with INSERT ... ON CONFLICT (index_columns) DO UPDATE SET ...
.
$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->insertOrUpdate()
->set([
'userId' => 5,
'active' => true,
'city' => 'Paris',
'balance' => 500,
])
->index('userId')
->write();
You need to provide the columns which form a unique index with the index
method. If the row does not exist yet it is inserted, and if it does exist, the values in set
are updated. You can change the UPDATE part though:
$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->insertOrUpdate()
->set([
'userId' => 5,
'active' => true,
'city' => 'Paris',
'balance' => 500,
])
->index('userId')
->setOnUpdate([
'balance' => 500,
])
->write();
This would insert the row with all the provided values, but if it already exists only balance
is changed, not city
and active
. A common use case for a custom UPDATE part is to change an existing number:
$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->insertOrUpdate()
->set([
'userId' => 5,
'active' => true,
'visitsNumber' => 1,
])
->index('userId')
->setOnUpdate([
':visitsNumber: = :visitsNumber: + 1',
])
->write();
This would create the user entry with one visit, or if it exists just increase visitsNumber
by one.
$deletedNumber = $userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->delete()
->where([
'active' => true,
])
->writeAndReturnAffectedNumber();
This would delete all active users and return the number of rows which were deleted as an integer. If you are not interested in the number of deleted entries you can call the write
method instead.
You can delete all entries in a table, but you have to make it explicit to avoid accidentally forgetting WHERE restrictions and removing all data (similar to the update method where you need to confirm no where restrictions too):
$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
->delete()
->confirmNoWhereRestrictions()
->write();
Sometimes you might want to do queries where multiple entities are involved (or the same entity multiple times), which is where the MultiRepository classes come in. Like with regular repositories there are base repositories and builder repositories, but unlike the regular repositories they have no configuration of their own - they take all the necessary data from the involved repositories.
All the examples are for the builder repositories, as they are easier to explain and use. We use the User entity again, and an additional entity called Visit
with the following definition:
namespace Application\Entity;
use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;
#[Entity("users_visits")]
class Visit
{
#[Field("visit_id", autoincrement: true)]
private int $visitId = 0;
#[Field("user_id")]
private int $userId = 0;
#[Field("created_timestamp")]
private int $timestamp = 0;
}
$multiBuilder = new \Squirrel\Entities\MultiRepositoryBuilderReadOnly();
$entries = $multiBuilder
->select()
->fields([
'user.userId',
'user.active',
'visit.timestamp',
])
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->where([
':user.userId: = :visit.userId:',
'user.userId' => 5,
])
->orderBy([
'visit.timestamp' => 'DESC',
])
->limitTo(10)
->getAllEntries();
foreach ($entries as $entry) {
// Each $entry has the following data in it:
// - $entry['user.userId'] as an integer
// - $entry['user.active'] as a boolean
// - $entry['visit.timestamp'] as an integer
}
You can rename the returned fields:
$entries = $multiBuilder
->select()
->fields([
'userId' => 'user.userId',
'isActive' => 'user.active',
'visitTimestamp' => 'visit.timestamp',
])
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->where([
':user.userId: = :visit.userId:',
'user.userId' => 5,
])
->getAllEntries();
foreach ($entries as $entry) {
// Each $entry has the following data in it:
// - $entry['userId'] as an integer
// - $entry['isActive'] as a boolean
// - $entry['visitTimestamp'] as an integer
}
You can define your own way of joining the entity tables, group the entries and make the SELECT blocking. This example uses all these possibilities:
$multiBuilder = new \Squirrel\Entities\MultiRepositoryBuilderReadOnly();
$entries = $multiBuilder
->select()
->fields([
'visit.userId',
'visit.timestamp',
'userIdWhenActive' => 'user.userId',
])
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->joinTables([
':visit: LEFT JOIN :user: ON (:user.userId: = :visit.userId: AND :user.active: = ?)' => true,
])
->where([
':visit.timestamp: > ?' => time() - 86400, // Visit timestamp within the last 24 hours
])
->groupBy([
'visit.userId',
])
->orderBy([
'visit.timestamp' => 'DESC',
])
->limitTo(5)
->startAt(10)
->blocking()
->getAllEntries();
foreach ($entries as $entry) {
// Each $entry has the following data in it:
// - $entry['visit.userId'] as an integer
// - $entry['visit.timestamp'] as an integer
// - $entry['userIdWhenActive'] as an integer if the LEFT JOIN was successful, otherwise NULL
}
Just like with the select builder of singular repositories you can retrieve results via getAllEntries
, getOneEntry
, getFlattenedFields
(or any of its variants getFlattenedIntegerFields
, getFlattenedFloatFields
, getFlattenedStringFields
, getFlattenedBooleanFields
) or by iterating over the builder:
$selectBuilder = $multiBuilder
->select()
->fields([
'userId' => 'user.userId',
'isActive' => 'user.active',
'visitTimestamp' => 'visit.timestamp',
])
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->where([
':user.userId: = :visit.userId:',
'user.userId' => 5,
]);
foreach ($selectBuilder as $entry) {
// Each $entry has the following data in it:
// - $entry['userId'] as an integer
// - $entry['isActive'] as a boolean
// - $entry['visitTimestamp'] as an integer
}
$entriesNumber = $multiBuilder
->count()
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->where([
':user.userId: = :visit.userId:',
'user.userId' => 5,
])
->getNumber();
// $entriesNumber now contains the number of visits of userId = 5
Sometimes you might want to create a more complex SELECT query, for example with subqueries or other functionality that is not directly supported by the multi repository select builder. Freeform queries give you that freedom, although it is recommended to use them sparingly, as they cannot be checked as rigorously as regular queries and they are more likely to only work for a specific database system (as there are often syntax/behavior differences between vendors). Using vendor-specific functionality might be a good use case for freeform queries, as long as you keep in mind that you are writing non-portable SQL.
$entries = $multiBuilder
->selectFreeform()
->fields([
'userId' => 'user.userId',
'isActive' => 'user.active',
])
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->queryAfterFROM(':user: WHERE :user.userId: = ? AND NOT EXISTS ( SELECT * FROM :visit: WHERE :user.userId: = :visit.userId: )')
->withParameters([5])
->confirmFreeformQueriesAreNotRecommended('OK')
->getAllEntries();
foreach ($entries as $entry) {
// Each $entry has the following data in it:
// - $entry['userId'] as an integer
// - $entry['isActive'] as a boolean
}
Getting and casting the fields is done in the same way as with a fully structured select query, but everything after SELECT ... FROM
can be freely defined - how the tables are joined, what is checked, etc. You need to call confirmFreeformQueriesAreNotRecommended
with 'OK' in the query builder to make it clear that you have made a conscious decision to use freeform queries.
Freeform update queries are not recommended either, but sometimes you might have no other way of executing a query, and having full freedom can enable queries which are much more efficient than doing multiple other queries / multiple UPDATEs. The general way it works is by defining query
and withParameters
:
$multiBuilder
->updateFreeform()
->inRepositories([
'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
])
->query('UPDATE :user:, :visit: SET :visit.timestamp: = ? WHERE :user.userId: = :visit.userId: AND :user.userId: = ?')
->withParameters([time(), 5])
->confirmFreeformQueriesAreNotRecommended('OK')
->write();
The above query also shows the main drawback of multi table UPDATE queries - they are almost never portable to other database systems (because they are not part of the SQL standard), as the above query would work for MySQL, but would fail for Postgres or SQLite, as they have a different syntax / different restrictions. In many cases this might be fine if you get a real benefit from having such a custom query.
You can use writeAndReturnAffectedNumber
(instead of using write
) to find out how many entries were found for the UPDATE. You need to call confirmFreeformQueriesAreNotRecommended
with 'OK' in the query builder to make it clear that you have made a conscious decision to use freeform queries.
It is usually important for multiple queries to be executed within a transaction, especially when something is changed, to make sure all changes are done atomically. Using repositories this is easy by using the Transaction
class:
use Squirrel\Entities\Transaction;
// Transaction class checks that all involved repositories use
// the same database connection so a transaction is actually possible
$transactionHandler = Transaction::withRepositories([
$userRepositoryWriteable, // \Application\Entity\UserRepositoryWriteable instance
$visitRepositoryReadOnly, // \Application\Entity\VisitRepositoryReadOnly instance
]);
// Passing additional arguments via `use` is recommended - you could also pass them as function arguments
$transactionHandler->run(function () use ($userId, $userRepositoryWriteable, $visitRepositoryReadOnly) {
$visitsNumber = $visitRepositoryReadOnly
->count()
->where([
'userId' => $userId,
])
->blocking()
->getNumber();
$userRepositoryWriteable
->update()
->set([
'visitsNumber' => $visitsNumber,
])
->where([
'userId' => $userId,
])
->write();
});
The advantage of the static withRepositories
function is that you cannot do anything wrong without it throwing a DBInvalidOptionException
- no invalid repositories, no different connections, etc. Internally the Transaction
class uses class reflection to check the data and expects either RepositoryBuilderReadOnly
instances or RepositoryReadOnly
instances (or the Writeable
instead of ReadyOnly
versions).
You can easily create Transaction objects yourself by just passing in an object implementing DBInterface
(from squirrelphp/queries). When using the class in that way you will need to make sure yourself that all involved repositories/queries use the same connection.
Only string
, int
, bool
, float
and blob
are supported as column types, yet databases support many specialized column types - like dates, times, geographic positions, IP addresses, enumerated values, JSON, and possibly many more (depending on SQL platform and version).
You should have no problems supporting such special types, but because this library is kept simple, it only supports the basic PHP types and it will be your responsibility to use or convert them to any other types, according to the needs of your application.
Below is a modification of our existing example to show how you could handle non-trivial column types:
namespace Application\Entity;
use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;
#[Entity("users")]
class User
{
#[Field("user_id", autoincrement: true)]
private int $userId = 0;
#[Field("active")]
private bool $active = false;
/** @var string JSON type in the database */
#[Field("note_data")]
private string $notes = '';
/** @var string datetime type in the database */
#[Field("created")]
private string $createDate = '';
public function getUserId(): int
{
return $this->userId;
}
public function isActive(): bool
{
return $this->active;
}
public function getNotes(): array
{
return \json_decode($this->notes, true);
}
public function getCreateDate(): \DateTimeImmutable
{
return new \DateTimeImmutable($this->createDate, new \DateTimeZone('Europe/London'));
}
}
The entity class converts between the database and the application to give the application something it can work with in a format it understands. If squirrelphp would handle these conversions it could quickly go wrong - even something seemingly simple like a date is not self-explanatory, it always needs a time zone it is relative too.
You should use the freedom of choosing how to convert your database values to application values by using value objects where it makes sense - we used DateTimeImmutable
as a value object, but it can be custom:
namespace Application\Value;
class GeoPoint
{
public function __construct(
private float $lat = 0,
private float $lng = 0,
) {
}
public function getLatitude(): float
{
return $this->lat;
}
public function getLongitude(): float
{
return $this->lng;
}
}
namespace Application\Entity;
use Application\Value\GeoPoint;
use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;
#[Entity("users_locations")]
class UserLocation
{
#[Field("user_id")]
private int $userId = 0;
/** @var string "point" type in Postgres */
#[Field("location")]
private string $locationPoint = '';
public function getUserId(): int
{
return $this->userId;
}
/**
* Convert the point syntax from the database into a value object
*/
public function getLocation(): GeoPoint
{
$point = \explode(',', \trim($this->locationPoint, '()'));
return new GeoPoint($point[0], $point[1]);
}
}
Here a point
data type from Postgres is used as an example, which is then converted into the GeoPoint
value object so the application can easily pass it around and use it. Custom data types are usually received as strings by the application and can then be processed however you want.
Beware that using database-specific column types will make it harder to change database systems / make your entities and SQL code be vendor-specific. It might still be worth it to use these column types, but you should be aware of it.
Because this library separates reads and writes and only uses objects for reads, the entity objects do not need to be mutable - they can be immutable and read-only. If you have used other ORMs this might seem counterintuitive at first (because most ORMs are built around mutable entity objects), but it does offer new possibilities:
Having the writing queries so clearly separated from the objects also offers advantages:
EntityRepositoryWriteable
classes are used, and only use the Writeable classes where you actually write to the repository and EntityRepositoryReadOnly
everywhere elsepublic readonly
with PHP 8.1+Transaction::withRepositories
for transactions