DataAccessKit

Type-safe minimum-writing SQL repositories for PHP

Quick start

Start by creating an object.

use DataAccessKit\Attribute\Table;
use DataAccessKit\Attribute\Column;

#[Table]
class User
{
    public function __construct(
        #[Column]
        public int $id,
        #[Column]
        public string $firstName,
        #[Column]
        public string $lastName,
    )
    {
    }
}

Then create a repository interface.

use DataAccessKit\Repository\Attribute\Repository;

#[Repository(User::class)]
interface UserRepositoryInterface
{
    public function getById(int $id): User;
}

By an integration with your framework (e.g. Symfony), a repository implementation will be generated for you and you can use it in your services.

class UserService
{
    public function __construct(
        private UserRepositoryInterface $userRepository,
    )
    {
    }

    public function login(int $userId): void
    {
        $user = $this->userRepository->getById($userId);
        
        // ...
    }
}

Installation

composer require data-access-kit/data-access-kit@dev-main

Requirements

• PHP 8.3 or higher.

Supported databases

• MySQL
• MariaDB
• PostgreSQL
• SQLite

Object attributes

DataAccessKit maps plain old PHP objects to database using attributes.

use DataAccessKit\Attribute\Table;

#[Table(
    name: "users",
)]

Table attribute connects class to a database table.

• name specifies the table name. If not provided, the table name is derived from the class name by NameConverterInterface. Default name converter converts CamelCase to snake_case and pluralizes the name (i.e. User to users, UserCredential to user_credentials).

use DataAccessKit\Attribute\Column;

#[Column(
    name: "user_id",
    primary: true,
    generated: true,
)]

Column attribute is supposed to be added above class property.

• name argument, same as with Table, is optional and if omitted the column name is derived from the property name by NameConverterInterface. Default name converter converts CamelCase to snake_case (i.e. userId to user_id). The primary argument specifies whether the column is a primary key.
• primary means that the column is a part of the primary key. When UPDATE or DELETE is called, values from properties annotated with #[Column(primary: true)] are used in WHERE clause. When INSERT is called, values from properties annotated with #[Column(primary: true)] are not used in the query (they are not part of the INSERT statement), but if you INSERT only one row with a single primary property, it is populated from LAST_INSERT_ID() (or equivalent) after the query.
• generated specifies whether the column is generated by the database (e.g. auto increment, but also for generated columns). Generated columns are only read from the database (they figure in SELECTs), but not written to it (they are not used in INSERTs, UPDATEs).

Nested objects and arrays

You may annotate with Column also nested objects and arrays. When you do so, the Column-annotated properties of nested object or array are serialized to JSON and stored in a single column.

use DataAccessKit\Attribute\Table;
use DataAccessKit\Attribute\Column;

#[Table]
class User
{
    #[Column(primary: true, generated: true)]public int $id;
    #[Column] public string $name;
    #[Column] public Address $mainAddress;
    /** @var Address[] */
    #[Column] public array $alternativeAddresses;
    #[Column] public object $settings;
}

class Address
{
    #[Column] public string $street;
    #[Column] public string $city;
    #[Column] public string $zip;
}

Because Address isn't represented by a table, you don't annotate it with Table.

If you want to store arbitrary data in a column, you can use object type hint.

PHP offers only array type hint without ability to specify the type of the array items. You can provide item type by @var annotation. Alternatively, specify the type using #[Column(itemType: Address::class)]. If you don't specify, the item type, array is serialized as arbitrary data.

Persistence

Persistence layer is based on Doctrine\DBAL. It is a thin layer on top of it, providing type-safe object mapping from and to database based on object attributes. See PersistenceInterface for more details.

Repositories

Repositories are generated from interfaces. The interface needs to be annotated with Repository attribute.

use DataAccessKit\Repository\Attribute\Repository;

#[Repository(
    class: User::class,
)]

• class specifies the class of the entity the repository is for.

Interface methods are then implemented based on what attribute they are annotated with. If a method doesn't have any of the method attributes, the compiler tries to determine the method attribute based on the method name. Methods starting with find and get are considered as Find methods, methods starting with count are considered as Count methods. If a method attribute cannot be determined, the compiler throws an exception.

Find

Find methods are used to retrieve entities from the database.

They can return a single entity or a collection of entities. Supported return types for collections are iterable and array.

A single entity return type must be the class the repository is for. If the return type is non-nullable and no rows is returned from the database, the method throws an exception. Also, if multiple rows are returned from the database, an exception is thrown.

use DataAccessKit\Repository\Attribute\Find;

#[Find(
    select: "%columns(except password alias u)", // optional, default is all columns specified by Column attributes
    from: "users", // optional, default is the table the repository is for
    alias: "u", // optional, default is "t"
    where: "u.id = @id", // optional, default is AND of all equality conditions based on parameter names and arguments
                         // to get the column names, parameter is matched with the object property and Column attribute from the property is used
                         // e.g. for method `find(int $id, string $firstName)` the default where clause is `u.id = ? AND u.first_name = ?`
    orderBy: "u.first_name", // optional, default is empty
    limit: 1, // optional, default is no limit
    offset: 10, // optional, default is no offset
)]
public function find(int $id): User;

• select - the columns to select. Macros and variables available.
• from - the table to select from.
• alias - the table alias.
• where - the WHERE clause. Macros and variables available.
• orderBy - the ORDER BY clause. Macros and variables available.
• limit - the LIMIT clause.
• offset - the OFFSET clause.
• for - locking reads, see e.g. MySQL's documentation

Count

Count methods return number of rows in the database. They must return int.

use DataAccessKit\Repository\Attribute\Count;

#[Count(
    from: "users", // optional, default is the table the repository is for
    alias: "u", // optional, default is "t"
    where: "u.id = @id", // optional, default is AND of all equality conditions based on parameter names and arguments
                         // to get the column names, parameter is matched with the object property and Column attribute from the property is used
                         // e.g. for method `count(int $id, string $firstName)` the default where clause is `u.id = ? AND u.first_name = ?`
)]
public function count(int $id): int;

• from - the table to count from.
• alias - the table alias.
• where - the WHERE clause. Macros and variables available.

SQL

SQL methods execute arbitrary SQL queries. They can return a single entity, a collection of entities, a scalar value, or nothing.

use DataAccessKit\Repository\Attribute\SQL;

#[SQL(
    sql: "SELECT * FROM users u WHERE u.first_name = @firstName", // required
    itemType: User::class, // optional
)]
public function sql(string $firstName): iterable;

• sql - the SQL query.
• itemType - the type of the item if the query returns an iterable or array and the item type is different from the class the repository is for. The use case is e.g. when you want to retrieve custom aggregation of the data and map it to objects.

Macros and variables

To reference a method argument in the SQL query, you can use @ followed by the parameter name (e.g. @id). This is then replaced by a placeholder in the actual SQL query and bound to the argument in the statement.

Array parameters are expanded to a list of placeholders. For example, if you have a method with an array parameter ids, you can use @ids in the SQL query, and it will be expanded to ?, ?, ?, ... and bound to the values from array.

There are also several macros that expand to SQL fragments.

• %columns - expands to all columns specified by Column attributes.
  • %columns(alias u) - expands to all columns specified by Column attributes prefixed by the alias.
  • %columns(except password) - expands to all columns specified by Column attributes except the specified columns.
  • %columns(except password, long_description alias u) - combination of the previous two.
• %table - expands to the table name.

SQLFile

The same as SQL attribute, but the SQL query is loaded from a file.

use DataAccessKit\Repository\Attribute\SQLFile;

#[SQLFile(
    file: "sql/find_by_first_name.sql", // required
    itemType: User::class, // optional
)]
public function sqlFile(string $firstName): iterable;

Insert, Upsert, Update, Delete

To manipulate data in the database, you can use Insert, Upsert, Update, and Delete methods.

use DataAccessKit\Repository\Attribute\Insert;
use DataAccessKit\Repository\Attribute\Upsert;
use DataAccessKit\Repository\Attribute\Update;
use DataAccessKit\Repository\Attribute\Delete;

#[Insert]
public function insert(User $user): void;

#[Insert]
public function insertAll(array $users): void;

#[Upsert(
    columns: ["first_name", "last_name"], // optional, if omitted/null all columns specified by Column attributes are updated
)]
public function upsert(User $user): void;

#[Upsert(
    columns: ...,
)]
public function upsertAll(array $users): void;

#[Update(
    columns: ..., // optional, if omitted/null all columns specified by Column attributes are updated
)]
public function update(User $user): void;

#[Delete]
public function delete(User $user): void;

#[Delete]
public function deleteAll(array $users): void;

Methods support both single entity and array of entities signatures, except for update, which works only on a single object. Array methods issue a single SQL query with all the data.

Upsert and update methods can be limited to update only specific columns in the columns argument of the attribute.

Delegate

If a repository method is more complex than what can be expressed by a single SQL query, you will probably want to implement it yourself.

use DataAccessKit\Repository\Attribute\Delegate;

#[Delegate(
    class: UserRepositoryDelegate::class, // required
    method: "delegateMethodName", // optional, default is the same name as the annotated method
)]
public function delegate(string $delegatedParameter): array; 

• class - the class that implements the method. It can be a class, an interface, or a trait.
  • Classes and interfaces are then added as a constructor parameter in the generated repository class.
  • Traits are used by an anonymous class instantiated in the generated repository's constructor. If the trait has a constructor, its parameters are added as constructor parameters in the generated repository class.
• method - target method in the class. If not provided, the interface method name is used.

Contributing

This repository is automatically split from the main repository. Please open issues and pull requests there.

License

Licensed under MIT license. See LICENSE.