grifart / tables
Statically typed table gate with support for composite fields, arrays and more from PostgreSQL.
Installs: 5 568
Dependents: 0
Suggesters: 0
Security: 0
Stars: 6
Watchers: 4
Forks: 0
Open Issues: 0
Requires
- php: ^8.1
- dibi/dibi: ^4.0.2||^5.0
- grifart/scaffolder: ^0.6.3
- lstrojny/functional-php: ^1.17
- nette/utils: ^3.0.1||^4.0
Requires (Dev)
- brick/date-time: ^0.4.1
- brick/math: ^0.11.0
- grifart/phpstan-oneline: ^0.4.0
- nette/bootstrap: ^3.1
- nette/di: ^3.0
- nette/tester: ^2.3
- nikic/php-parser: ^4.13
- phpstan/phpstan: ^1.0
- ramsey/uuid: ^4.2
Suggests
- brick/date-time: if you want to use date-time types
- brick/math: if you want to use DecimalType
- nette/di: if you want to integrate with DI container
- ramsey/uuid: if you want to use UuidType
README
A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL.
This library is developed at gitlab.grifart.cz/grifart/tables and distributed using github.com/grifart/tables. GitLab repository is automatically mirrored to GitHub for all protected branches and tags. Development branches can be found only at GitLab.
Installation
composer require grifart/tables
Quick start
-
Register the tables DI extension. Tables expect that an instance of Dibi is also configured and registered in the container.
extensions: tables: Grifart\Tables\DI\TablesExtension
-
Create a database table. You can use your favourite database migration tool.
CREATE TABLE "article" ( "id" uuid NOT NULL PRIMARY KEY, "title" varchar NOT NULL, "text" text NOT NULL, "createdAt" timestamp without time zone NOT NULL, "deletedAt" timestamp without time zone DEFAULT NULL, "published" boolean NOT NULL );
-
Create a definition file for scaffolder. Tables expose a helper that creates all necessary class definitions for you:
<?php use Grifart\Tables\Scaffolding\TablesDefinitions; // create a DI container, the same way as you do in your application's bootstrap.php, e.g. $container = App\Bootstrap::boot(); // grab the definitions factory from the container $tablesDefinitions = $container->getByType(TablesDefinitions::class); return $tablesDefinitions->for( 'public', // table schema 'article', // table name ArticleRow::class, ArticleChangeSet::class, ArticlesTable::class, ArticlePrimaryKey::class, );
Once you run scaffolder, it will inspect the database schema and generate a set of four classes:
ArticlesTable
, a service that provides API for accessing and manipulating data in thearticle
table;ArticleRow
, a simple DTO that wraps a single row from thearticle
table;ArticleChangeSet
, a mutable wrapper over data to be persisted in thearticle
table,ArticlePrimaryKey
, a representation of thearticle
table's primary key.
-
Register the
ArticlesTable
in your DI container.
services: - ArticlesTable
Usage
Use dependency injection to retrieve an instance of the ArticlesTable
service in your model layer. The table class exposes the following methods:
Read
You can list all records in the table by calling the getAll()
method. The method optionally accepts sorting criteria and a paginator (more on both below).
$rows = $table->getAll($orderBy, $paginator);
To fetch a specific record from the table, use either the find()
or get()
method with the desired record's primary key. The difference is that find()
returns null
if the query yields empty result, whereas get()
throws an exception in such case:
$row = $table->find(ArticlePrimaryKey::of($articleId)); // or $row = $table->get(ArticlePrimaryKey::of($articleId));
To retrieve a list of records that match given criteria, you can use the findBy()
method and pass a set of conditions to it (more on that below):
$rows = $table->findBy($conditions, $orderBy, $paginator);
There is also a helper method to retrieve a single record that matches given criteria. It throws an exception when the query doesn't yield exactly one result:
$row = $table->getBy($conditions);
Conditions
When it comes to search criteria, the table expects a Condition
(or a list thereof). This is how a simple search for published articles might look like:
$rows = $table->findBy( Composite::and( $table->published()->is(equalTo(true)), $table->createdAt()->is(lesserThanOrEqualTo(Instant::now())), ), );
The code above could be simplified to a list of conditions – if a list is passed, the and
relationship is assumed implicitly:
$rows = $table->findBy([ $table->published()->is(equalTo(true)), $table->createdAt()->is(lesserThanOrEqualTo(Instant::now())), ]);
Also, the is()
method defaults to equality check, so you can omit the equalTo()
and pass the value directly:
$rows = $table->findBy([ $table->published()->is(true), $table->createdAt()->is(lesserThanOrEqualTo(Instant::now())), ]);
This package provides a Composite
condition that lets you compose the most complex trees of boolean logic together, and a set of most common conditions such as equality, comparison, and null-checks. For a complete list, look into the Conditions/functions.php
file.
In addition to these, you can also write your own conditions by implementing the Condition
interface. It defines the sole method toSql()
which is expected to return an array compatible with Dibi.
Take a look at how a LIKE
condition could be implemented. It maps to a LIKE
database operation with two operands, a sub-expression (more on that below), and a pattern mapped to a database text:
use Grifart\Tables\Expression; use Grifart\Tables\Types\TextType; use function Grifart\Tables\Types\mapToDatabase; final class IsLike implements Condition { /** * @param Expression<string> $expression */ public function __construct( private Expression $expression, private string $pattern, ) {} public function toSql(): \Dibi\Expression { return new \Dibi\Expression( '? LIKE ?', $this->expression->toSql(), mapToDatabase($this->pattern, TextType::varchar()), ); } }
You can then use the condition like this:
$rows = $table->findBy([ new IsLike($table->title(), 'Top 10%'), ]);
Or create a factory function:
function like(string $pattern) { return static fn(Expression $expression) => new IsLike($expression, $pattern); }
And then use it like this:
$rows = $table->findBy([ $table->title()->is(like('Top 10%')), ]);
Expressions
Expressions are an abstraction over database expressions. All table columns are expressions and as you've seen, the generated ArticlesTable
exposes each of them via an aptly named method.
You can also create custom expressions that map to various database functions and operations. You just need to implement the Expression
interface which requires you to specify the SQL representation of the expression, and also its type (used for formatting values in conditions):
use Grifart\Tables\Expression; use Grifart\Tables\Types\IntType; use Grifart\Tables\Type; /** * @implements Expression<int> */ final class Year implements Expression { /** * @param Expression<\Brick\DateTime\Instant>|Expression<\Brick\DateTime\LocalDate> $sub */ public function __construct( private Expression $sub, ) {} public function toSql(): \Dibi\Expression { return new \Dibi\Expression( "EXTRACT ('year' FROM ?)", $this->sub->toSql(), ); } public function getType(): Type { return IntType::integer(); } }
Alternatively, you can extend the ExpressionWithShorthands
base class:
/** * @extends ExpressionWithShorthands<int> */ final class Year extends ExpressionWithShorthands { // ... }
That way, the convenient is()
shorthand will be available on the expression instance:
$rows = $table->findBy( (new Year($table->createdAt()))->is(equalTo(2021)), );
You can also use the expr()
function to create such expression:
$year = fn(Expression $expr) => expr(IntType::integer(), "EXTRACT ('year' FROM ?)", $expr->toSql()); $rows = $table->findBy( $year($table->createdAt())->is(equalTo(2021)), );
Ordering
To specify the desired order of records, you can provide a list of sorting criteria. This uses the same expression mechanism as filtering. You can use the Expression
's shorthand methods ascending()
and descending()
:
$rows = $table->getAll(orderBy: [ $table->createdAt()->descending(), $table->title(), // ->ascending() is the default ]);
Pagination
The getAll
and findBy
methods also optionally accept an instance of Nette\Utils\Paginator
. If you provide it, the table will not only set the correct limit and offset, but also query the database for the total number of items, and update the paginator with that value.
$paginator = new \Nette\Utils\Paginator(); $paginator->setItemsPerPage(20); $paginator->setPage(2); $rows = $table->getAll($orderBy, $paginator);
Insert
To insert a new record into the database table, use the $table->new()
method. You have to provide all required values (for columns without a default value) to the method:
$changeSet = $table->new( id: \Ramsey\Uuid\Uuid::uuid4(), title: 'Title of the post', text: 'Postt text', createdAt: \Brick\DateTime\Instant::now(), published: true, );
The method returns a change set which you can further modify, and eventually insert:
$changeSet->modifyText('Post text'); $table->insert($changeSet);
Update
To update a record in the table, you need to get an instance of change set for the specific record. You can get one for any given primary key or row:
$changeSet = $table->edit(ArticlePrimaryKey::from($articleId)); // or $changeSet = $table->edit($articleRow);
You can use named parameters to provide the values to update right within the method call:
$changeSet = $table->edit( $articleRow, deletedAt: \Brick\DateTime\Instant::now(), );
As before, you can also add modifications to the change set afterward, and finally save it:
$changeSet->modifyDeletedAt(\Brick\DateTime\Instant::now()); $table->update($changeSet);
Delete
To delete a record, you simply need its primary key or row:
$table->delete(ArticlePrimaryKey::from($articleId)); // or $table->delete($articleRow);
Type mapping
Basic types
As you might have noticed, Tables provide default mapping for most PostgreSQL's basic types:
- Textual types (
character
,character varying
,text
) all map tostring
. - Integer types (
smallint
,int
,bigint
) all map toint
. - Floating-point types (
real
,double precision
) all map tofloat
. - Boolean type maps to
bool
. - Binary type (
bytea
) maps to a binarystring
. - Json types (
json
,jsonb
) map to ajson_decode()
'd PHP value.
Additional basic types are only mapped provided that certain packages are installed:
- Numeric type (
numeric
/decimal
) maps to aBigDecimal
from brick/math. - Date-time types (
date
,time
,timestamp
) map toLocalDate
,LocalTime
, andInstant
, respectively, from brick/date-time. - Uuid type maps to a
Uuid
from ramsey/uuid.
Advanced types
In addition to mapping PostgreSQL's basic types by default, Tables let you make the most of the database's complex type system. You can describe and provide mapping for even the wildest combinations of PostgreSQL types.
Type resolver
At the core of the type system in Tables is the TypeResolver
. It decides which type to use for each column based on its database type, or even its scoped name.
You can register your own types in the config file:
tables: types: - App\Tables\MyType - App\Tables\MyType::decimal(10, 5) # named constructor with parameters schema.table.column: App\Tables\MyType
You can explicitly map the type to a specific column by using the fully qualified identifier in the item's key (as seen in the second item above.) If you omit the item's key (as seen in the first item above), the type will be registered based on its getDatabaseType()
and will be used for all columns of that type that do not have an explicit mapping.
Alternatively, you can register implementations of the TypeResolverConfigurator
interface in the DI container. Tables will automatically pick them up and pass the TypeResolver
to the configurators's configure()
method.
Custom types
All types implement the Type
interface and its four methods:
getPhpType(): PhpType
returns the scaffolder-compatible type of the represented PHP value;getDatabaseType(): DatabaseType
returns the database type name – this is used when the type is registered using theTypeResolver::addResolutionByTypeName($type)
method;toDatabase(mixed $value): Dibi\Expression
maps a PHP value of given type to its database representation;fromDatabase(mixed $value): mixed
maps a database representation to its respective PHP value.
This is an example of a custom currency type that maps instances of some Currency
onto currency codes in the database's char(3)
:
/** * @implements Type<Currency> */ final class CurrencyType implements Type { public function getPhpType(): PhpType { return resolve(Currency::class); } public function getDatabaseType(): DatabaseType { return BuiltInType::char(); } public function toDatabase(mixed $value): Expression { return $value->getCode(); } public function fromDatabase(mixed $value): mixed { return Currency::of($value); } }
There are also a few helpers for creating the most common advanced types:
Array types
You can map values to an array via the ArrayType
. This formats the items using the declared subtype, and serializes them into a PostgreSQL array. Example of an array of dates:
$dateArrayType = ArrayType::of(new DateType());
Enum types
You can map native PHP enumerations to PostgreSQL's enums using the EnumType
. This requires that the provided enum is a \BackedEnum
, and serializes it to its backing value:
enum Status: string { case DRAFT = 'draft'; case PUBLISHED = 'published'; } $statusType = EnumType::of(Status::class);
Composite types
There is also a base class for describing composite types:
$moneyType = new class extends CompositeType { public function __construct() { parent::__construct( new Database\NamedType(new Database\Identifier('public', 'money')), DecimalType::decimal(), new CurrencyType(), // custom type from above ); } public function getPhpType(): PhpType { return resolve(Money::class); } public function toDatabase(mixed $value): Dibi\Expression { return $this->tupleToDatabase([ $value->getAmount(), $value->getCurrency(), ]); } public function fromDatabase(mixed $value): Money { [$amount, $currency] = $this->tupleFromDatabase($value); return Money::of($amount, $currency); } };