A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL.

Related tags

Database tables
Overview

grifart/tables

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

  1. 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
  2. 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
    );
  3. Create a definition file for scaffolder. Tables expose a helper that creates all necessary class definitions for you:

    
    
    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 the article table;
    • ArticleRow, a simple DTO that wraps a single row from the article table;
    • ArticleChangeSet, a mutable wrapper over data to be persisted in the article table,
    • ArticlePrimaryKey, a representation of the article table's primary key.
  4. 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())),
]);

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
   
     $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, new TextType()),
		);
	}
}

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

sub->toSql(), ); } public function getType(): Type { return new IntType(); } }">
use Grifart\Tables\Expression;
use Grifart\Tables\Types\IntType;
use Grifart\Tables\Type;

/**
 * @implements Expression
   
 */
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 new IntType();
    }
}

Alternatively, you can extend the ExpressionWithShorthands base class:

/**
 * @extends ExpressionWithShorthands
   
 */
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:

toSql()); $rows = $table->findBy( $year($table->createdAt())->is(equalTo(2021)), );">
$year = fn(Expression $expr) => expr(new IntType(), "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 save:

$changeSet->modifyText('Post text');
$table->save($changeSet);

Update

To update a record in the table, first 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);

Then you can add modifications to the change set and finally save it:

$changeSet->modifyDeletedAt(\Brick\DateTime\Instant::now());
$table->save($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 to string.
  • Integer types (smallint, int, bigint) all map to int.
  • Boolean type maps to bool.
  • Binary type (bytea) maps to a binary string.
  • Json types (json, jsonb) map to a json_decode()'d PHP value.

Additional basic types are only mapped provided that certain packages are installed:

  • Numeric type (numeric/decimal) maps to a BigDecimal from brick/math.
  • Date-time types (date, time, timestamp) map to LocalDate, LocalType, and Instant, 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:
        byName:
            typeName: App\Tables\MyType
        byLocation:
            schema.table.column: App\Tables\MyType

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;
  • getDatabaseTypes(): string[] returns a list of database type names – these are used when the type is registered using the TypeResolver::addType($type) method;
  • toDatabase(mixed $value): mixed maps a PHP value of given type to its database representation;
  • fromDatabase(mixed $value): mixed maps a database representation to its respective PHP 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 DecimalType(), new CurrencyType());
    }

    public function getPhpType(): PhpType
    {
        return resolve(Money::class);
    }

    public function getDatabaseTypes(): array
    {
        return [];
    }

    public function toDatabase(mixed $value): mixed
    {
        return $this->tupleToDatabase([
            $value->getAmount(),
            $value->getCurrency(),
        ]);
    }

    public function fromDatabase(mixed $value): mixed
    {
        [$amount, $currency] = $this->tupleFromDatabase($value);
        return Money::of($amount, $currency);
    }
};
You might also like...
SQL database access through PDO.

Aura.Sql Provides an extension to the native PDO along with a profiler and connection locator. Because ExtendedPdo is an extension of the native PDO,

API abstracting communication with SQL providers (eg: MySQL) on top of PDO inspired by Java JDBC

SQL Data Access API Table of contents: About Configuration Execution Installation Unit Tests Examples Reference Guide About This API is a ultra light

SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.

SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate. NoSQL API

Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.

Please give it a Star if you like the project 🎉 ❤️ SleekDB - A NoSQL Database made using PHP Full documentation: https://sleekdb.github.io/ SleekDB i

A simple library for managing database connections, results pagination and building queries in PHP

PHP lions-software-database-manager This is a simple library for managing database connections, results pagination and building queries in PHP. Esta é

ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).
ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).

ATK Data - Data Model Abstraction for Agile Toolkit Agile Toolkit is a Low Code framework written in PHP. Agile UI implement server side rendering eng

The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

A drop-in library for certain database functionality in Laravel, that allows for extra features that may never make it into the main project.

Eloquence Eloquence is a package to extend Laravel's base Eloquent models and functionality. It provides a number of utilities and classes to work wit

Just another PDO database library

PDO Just another PDO database library Installation Use Composer $ composer require faapz/pdo Usage Examples selecting, inserting, updating and deletin

Owner
GRIFART
GRIFART spol. s r.o. public repositories
GRIFART
Orm is a simple database abstraction layer that supports postgresql.

Orm What is it Orm is a simple database abstraction layer that supports postgresql. Welcome to join us or star us for encouragement. Requires php 8.1

null 2 Sep 28, 2022
Laravel Thermite is an extended PostgreSQL Laravel database driver to connect to a CockroachDB cluster.

Laravel Thermite Laravel Thermite is an extended PostgreSQL Laravel database driver to connect to a CockroachDB cluster. ?? Supporting If you are usin

Renoki Co. 9 Nov 15, 2022
Spot v2.x DataMapper built on top of Doctrine's Database Abstraction Layer

Spot DataMapper ORM v2.0 Spot v2.x is built on the Doctrine DBAL, and targets PHP 5.4+. The aim of Spot is to be a lightweight DataMapper alternative

Spot ORM 602 Dec 27, 2022
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )

Mysql Optimizer mysql optimizer also known as MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query

null 3 Feb 14, 2022
phpMyFAQ - Open Source FAQ web application for PHP and MySQL, PostgreSQL and other databases

phpMyFAQ 3.1 What is phpMyFAQ? phpMyFAQ is a multilingual, completely database-driven FAQ-system. It supports various databases to store all data, PHP

Thorsten Rinne 547 Dec 27, 2022
Independent query builders for MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.

Aura.SqlQuery Provides query builders for MySQL, Postgres, SQLite, and Microsoft SQL Server. These builders are independent of any particular database

Aura for PHP 424 Dec 12, 2022
PHP Object Model Manager for Postgresql

POMM: The PHP Object Model Manager for Postgresql Note This is the 1,x version of Pomm. This package is not maintained anymore, the stable Pomm 2.0 is

Grégoire HUBERT 161 Oct 17, 2022
Support for many missing PostgreSQL specific features

Laravel supports many different databases and therefore has to limit itself to the lowest common denominator of all databases. PostgreSQL, however, of

Tobias Petry 359 Jan 3, 2023
PostgreSQL enhancements for Doctrine

PostgreSQL enhancements for Doctrine. Provides support for advanced data types (json, jssnb, arrays), text search, array operators and jsonb specific functions.

Martin Georgiev 258 Dec 31, 2022
Async Redis client implementation, built on top of ReactPHP.

clue/reactphp-redis Async Redis client implementation, built on top of ReactPHP. Redis is an open source, advanced, in-memory key-value database. It o

Christian Lück 240 Dec 20, 2022