Download the PHP package squirrelphp/entities without Composer

Squirrel Entities Component

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.

Installation

composer require squirrelphp/entities

Table of contents

• Creating entities
• Creating repositories
• Using repositories
• Multi repository queries
• Transactions
• More complex column types
• Read-only entity objects
• Recommendations on how to use this library

Creating entities

Defining an entity with attributes

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:

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.

Defining an entity directly

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:

Creating repositories

Base repositories

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:

• Squirrel\Entities\RepositoryReadOnly
• Squirrel\Entities\RepositoryWriteable

They offer almost the same functionality as DBInterface in squirrelphp/queries, but they do additional steps to avoid mistakes:

• You use the object property names in your queries, not the column names in the table, the library then converts them for the query
• All provided values are converted to the appropriate type (string, int, bool, float, blob)
• If any unknown column names are used an exception is thrown
• Whenever you do a SELECT query to retrieve entries, you get entity objects back instead of arrays, and all values are correctly converted before putting them in the entity objects

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.

Builder repositories

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.

Generating 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 builder class, by adding RepositoryReadOnly to the entity class name
• RepositoryWriteable builder class, by adding RepositoryWriteable to the entity class name
• A .gitignore file which ignores both the .gitignore file itself and all generated builder classes

This 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:

• Application\Entity\UserRepositoryReadOnly with the filename UserRepositoryReadOnly.php
• Application\Entity\UserRepositoryWriteable with the filename UserRepositoryWriteable.php
• .gitignore file listing .gitignore, UserRepositoryReadOnly.php and UserRepositoryWriteable.php

Using repositories

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.

Retrieving database entries as objects

If you only need certain fields in the table, you can define those you want explicitely:

Or if you only want a list of user IDs, you can get only those with getFlattenedFields:

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:

Or if you just need exactly one entry, you can use `getOneEntry':

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:

The SELECT query is done with ... FOR UPDATE at the end in the above query.

Counting the number of entries

Often you just want to know how many entries there are, which is where count comes in:

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.

Adding a new entry (INSERT)

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).

Updating an existing entry (UPDATE)

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:

Insert entry if it does not exist, update it otherwise (UPSERT - insertOrUpdate)

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 ....

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:

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:

This would create the user entry with one visit, or if it exists just increase visitsNumber by one.

Delete an existing entry

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):

Multi repository queries

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:

Select queries

You can rename the returned fields:

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:

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:

Count queries

Freeform select queries

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.

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

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:

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.

Transactions

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:

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.

More complex column types

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:

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:

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.

Read-only entity objects

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:

• Immutable entity objects can be passed to templates or any services without the risk of them being changed or having any side effects on other parts of the application
• Caching is easy: use whatever technique you like to cache entity objects if this becomes necessary, because they are just data / plain objects
• Entity classes can be more about the logic of the entity (how to use it), offering additional functionality and leaving out all the database/infrastructure functionality
• You can create interfaces for your entity objects to separate infrastructure and entity logic, defining the methods on an entity you want to provide independent of what data is in the database (and you can create aggregates/root entities with multiple read-only entities, the possibilities for more abstractions are plentiful)
• The library does not need to keep track of your entities, lazy-loading parts of them, and doing complicated things you don't really understand: everything that happens is straightforward and within your control
• You still have the advantages of using objects to access the data instead of using unstructured data, so you can clearly define the types you are using, or implement methods returning value objects, and the code can be checked by static analyzers

Having the writing queries so clearly separated from the objects also offers advantages:

• It is easy to spot where something is being changed, just look for where EntityRepositoryWriteable classes are used, and only use the Writeable classes where you actually write to the repository and EntityRepositoryReadOnly everywhere else
• Queries are not completely abstracted away from you, leaving it up to you to make simple or complicated queries according to your needs, instead of having to hope that the library creates efficient queries for you and forgetting how the data is stored and manipulated
• You can change many rows at once, or do multi repository queries with a straightforward syntax and without learning a new query language
• Using a command bus, or doing CQRS (Command Query Responsibility Segregation) in your application becomes an easy pattern to follow, because you always define if you are writing to or reading from a repository

Recommendations on how to use this library

• If you use Symfony, be sure to install and use squirrelphp/entities-bundle, which builds upon this library and autoconfigures almost everything for you
• Make your entity objects read-only / immutable, by defining all properties as private with only getters, or using public readonly with PHP 8.1+
• Only use the generated builder repositories to access and change your entities, and use Transaction::withRepositories for transactions
• Only use multi repository queries when it is a conscious decision and there are no good alternatives
• Separate read and write operations in your application to make it clear where data is changed and where it is only retrieved