README

Introduction

The main goal of Tereta/DBAL is to provide a convenient and powerful tool for working with database queries. Tereta/DBAL is:

Tereta\Dbal\Builder - a query builder that generates SQL queries which can be executed on various database engines.
Tereta\Dbal\Schema - a unified schema of tables and the relationships between them

Drivers supported out of the box:

SQLite
PostgreSQL
MySQL

Getting started

The constructor accepts either a driver code (sqlite, pgsql, mysql) or a PDO instance (the driver is detected automatically):

use Tereta\Dbal\Builder;

$builder = new Builder($pdo);        // the driver is detected from the connection, the builder can run the query via execute()
$builder = new Builder('mysql');     // or an explicit driver code

In the examples below $driverCode is any of the supported driver codes; a PDO instance may be passed instead. If you pass a PDO instance, the Builder not only generates the SQL and its bind parameters, but also executes the query through the given PDO. For SELECT/INSERT/UPDATE/DELETE the execute() method returns a PDOStatement, while for DDL queries (CREATE/DROP/ALTER/INDEX) it returns nothing.

Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION on your connection. The builder relies on PDO to report failures, and with the default silent mode a failed statement is not reported at all:

$pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

Every example below is written against these two tables:

use Tereta\Dbal\Interfaces\Create\Schema as SchemaInterface;

$builder->create('fixture_users')->schema(function (SchemaInterface $schema): void {
    $schema->field('id')->integer()->primary()->autoIncrement();
    $schema->field('site_id')->integer();
    $schema->field('name')->varchar(64)->nullable(false);
    $schema->field('age')->tinyint()->unsigned();
    $schema->field('email')->varchar(320);
})->execute();

$builder->create('fixture_users_address')->schema(function (SchemaInterface $schema): void {
    $schema->field('id')->integer()->primary()->autoIncrement();
    $schema->field('user_id')->integer();
    $schema->field('city')->varchar(64);
})->execute();

DQL (Data Query Language)

SELECT

A builder created from a driver code only generates SQL. You run it yourself:

$query = (new Builder($driverCode))->select('fixture_users')
    ->where('id', 1)
    ->order('name')
    ->limit(10);

$statement = $pdo->prepare($query->getQuery());
$statement->execute($query->getBindParams());
$users = $statement->fetchAll(PDO::FETCH_ASSOC);

A builder created from a PDO instance can run the query itself:

$query = (new Builder($pdo))->select('fixture_users')
    ->where('id', 1)
    ->order('name')
    ->limit(10);

$users = $query->execute()->fetchAll(PDO::FETCH_ASSOC);

->select($table) lets you specify the table to query. An array ['fixture_users' => 'u'] sets a table alias.
->where(...$args) lets you specify the query conditions.
->order($column, $direction) lets you specify the sort order of the results. The direction is either ASC or DESC.
->limit($limit) lets you limit the number of returned rows.
->offset($offset) lets you skip the given number of rows.
->getQuery() returns the generated SQL query as a string.
->getBindParams() returns the array of parameters to bind to the prepared statement.
->execute() returns a PDOStatement if a PDO is set in the builder.
->reset($section) clears one section of the query (from, columns, where, groupBy, orderBy, limit, offset, join, having, union) or the whole query when called without arguments.

The generated SQL for the example above is:

SELECT * FROM `fixture_users` WHERE `id` = :bnd1i1 ORDER BY `name` ASC LIMIT 10

Columns, joins and subqueries

use Tereta\Dbal\Builder;
use Tereta\Dbal\Interfaces\Where as WhereInterface;

$query = (new Builder($pdo))->select('fixture_users')
    ->columns(['fixture_users.id' => 'fixture_user_id'], 'fixture_users.name')
    ->where(function (WhereInterface $where, Builder $builder): void {
        $where->and(
            'fixture_users.id',
            $builder->select('fixture_users')->columns('id')->where('age', null, 'IS NOT')
        );
    })
    ->leftJoin(
        'fixture_users_address',
        'fixture_users.id = fixture_users_address.user_id',
        [
            'fixture_users_address.city',
            ['fixture_users_address.user_id' => 'address_user_id'],
        ]
    );

$query->getQuery();
$query->getBindParams();
$query->execute();

->columns lets you specify the columns to query, as well as aliases for them via the arrow array ['fixture_users.id' => 'fixture_user_id'], where the left-hand side is the column and the right-hand side is its alias.
->where lets you specify the query conditions. In this case a nested builder is used to express a condition that contains a subquery. The closure receives the condition builder and the parent Builder, so you can create a subquery inside it.
->leftJoin lets you specify a LEFT JOIN to combine tables. In this case the tables fixture_users and fixture_users_address are joined on the condition fixture_users.id = fixture_users_address.user_id. You can also specify columns to select from the joined table, as well as aliases for those columns via the arrow array ['fixture_users_address.user_id' => 'address_user_id'].
->innerJoin, analogous to ->leftJoin (same interface), lets you specify an INNER JOIN to combine tables.
->crossJoin, analogous to ->leftJoin but without a join condition, lets you specify a CROSS JOIN.

The generated SQL is:

SELECT `fixture_users`.`id` AS `fixture_user_id`, `fixture_users`.`name`, `fixture_users_address`.`city`, `fixture_users_address`.`user_id` AS `address_user_id`
FROM `fixture_users`
LEFT JOIN `fixture_users_address` ON `fixture_users`.`id` = `fixture_users_address`.`user_id`
WHERE `fixture_users`.`id` IN (SELECT `id` FROM `fixture_users` WHERE `age` IS NOT NULL)

Conditions

Every value is passed as a bind parameter, never inlined into the SQL. Identifiers, operators and sort directions are validated, so a value that is not a valid identifier is rejected with an InvalidArgumentException.

$query = (new Builder($pdo))->select('fixture_users')
    ->where('age', [20, 30, 40])          // IN (:bnd, :bnd, :bnd)
    ->where('name', 'A%', 'LIKE')         // LIKE :bnd
    ->where('site_id', null)              // IS NULL
    ->where('id', [1, 100], 'BETWEEN');   // BETWEEN :bnd AND :bnd

The supported operators are =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE, IN, NOT IN, IS, IS NOT, BETWEEN, NOT BETWEEN.
An array value turns = into IN and != into NOT IN automatically. An empty array is rendered as an always-false condition for IN and an always-true condition for NOT IN.
A null value turns = into IS NULL and != into IS NOT NULL.
IS and IS NOT accept only true, false and null.
A Select passed as a value becomes a subquery, and = turns into IN.

Repeated where() calls are joined with AND. To mix AND and OR, group the conditions in a closure:

use Tereta\Dbal\Interfaces\Where as WhereInterface;

$query = (new Builder($pdo))->select('fixture_users')
    ->where('site_id', 1)
    ->where(function (WhereInterface $where): void {
        $where->and('age', 30)->or('age', 40);
    });

SELECT * FROM `fixture_users` WHERE `site_id` = :bnd1i1 AND (`age` = :bnd2i1 OR `age` = :bnd2i2)

Aggregates and expressions

$builder->field() builds a column with an aggregate function and an optional alias:

$builder = new Builder($pdo);

$query = $builder->select('fixture_users')
    ->columns(
        'site_id',
        $builder->field('id')->count()->as('total'),
        $builder->field('age')->max()->as('max_age')
    )
    ->group('site_id')
    ->having('total', 0, '>')
    ->order('site_id', 'DESC');

SELECT `site_id`, COUNT(`id`) AS `total`, MAX(`age`) AS `max_age`
FROM `fixture_users` GROUP BY `site_id` HAVING `total` > :bnd1i1 ORDER BY `site_id` DESC

Available aggregates: count(), max(), min(), sum(), avg(), distinct(), now().
->group($column, ...) lets you specify GROUP BY columns.
->having($column, $value, $operator) lets you filter grouped rows. The value is bound as a parameter.

When you need a piece of SQL the builder cannot express, use Expression. Its content is inserted as is, so never build it from user input:

use Tereta\Dbal\Data\Expression;

$builder->update('fixture_users')
    ->set('age', new Expression('age + 1'))
    ->where('id', 1)
    ->execute();

Tereta\Dbal\Data\Now is a ready-made expression for the current timestamp of the active driver. It is meant for column defaults, see the CREATE TABLE section.

UNION

$query = (new Builder($pdo))->select('fixture_users')->columns('id');
$query->union(
    (new Builder($pdo))->select('fixture_users_address')->columns('user_id')
);

SELECT `id` FROM `fixture_users` UNION SELECT `user_id` FROM `fixture_users_address`

Known limitation: order(), limit() and offset() are rendered before the UNION keyword, which is not valid SQL. Do not combine them with union() for now.

DML (Data Manipulation Language)

INSERT

INSERT operations are performed with the insert method, which lets you specify the table and the values to insert.

getQuery() returns the generated SQL query as a string.
getBindParams() returns the array of parameters to bind to the prepared statement.
execute() returns a PDOStatement if a PDO is set in the builder.

$query = (new Builder($pdo))->insert('fixture_users')
    ->value('name', 'John')->value('age', 20);

$query->getQuery();
$query->getBindParams();
$query->execute();

You can use the values and value methods to provide multiple rows to insert.

$query = (new Builder($pdo))
    ->insert('fixture_users')
    ->value('name', 'Alex')->value('age', 30)->value('email', 'tereta.alexander@gmail.com')
    ->value('name', 'Max')->value('age', 22)->value('email', 'support@tereta.dev');

$query->execute();

In this case the builder determines the boundaries between different rows to insert when keys repeat. The example above specifies two sets of values to insert. The builder automatically determines that the values for name, age and email belong to a single row; however, when the name key repeats, the builder determines that this is the start of a new row to insert.

The same result with the values method, which takes a whole row at once:

$query = (new Builder($pdo))
    ->insert('fixture_users')
    ->values(['name' => 'Alex', 'age' => 34])
    ->values(['name' => 'Max', 'age' => 30])
    ->values(['name' => 'Ann', 'age' => 29]);

$query->execute();

Rows can also be taken from another table. The select method returns a nested SELECT builder that you configure as usual:

$query = (new Builder($pdo))->insert('fixture_users_archive');
$query->select('fixture_users')->columns('name', 'age')->where('age', 60, '>');

$query->execute();

INSERT INTO `fixture_users_archive` (`name`, `age`) SELECT `name`, `age` FROM `fixture_users` WHERE `age` > :bnd1i1

UPSERT

Tereta/DBAL supports UPSERT operations, which let you insert data into a table or update existing records on conflict.

use Tereta\Dbal\Data\Expression;
use Tereta\Dbal\Interfaces\Insert\Update as UpdateInterface;

$query = (new Builder($pdo))
    ->insert('fixture_users')
    ->value('id', 2)
    ->value('name', 'New')
    ->update(function (UpdateInterface $update): void {
        $update
            ->conflict('id')
            ->set('name', 'name')
            ->set('age', new Expression('fixture_users.age + 1'));
    });

$query->execute();

->update lets you specify the actions to take on conflict, for example updating existing records.
->conflict($column, ...) lets you specify the columns that trigger the conflict.
->set($column, $value) lets you specify the update to apply. A string value is the name of the column taken from the row that was being inserted; an Expression is inserted as is.
->setValues($values) does the same for several columns at once.

The example generates the following query in MySQL:

INSERT INTO `fixture_users` (`id`, `name`) VALUES (:bnd1i1, :bnd1i2) AS new ON DUPLICATE KEY UPDATE `name` = new.`name`, `age` = fixture_users.age + 1

and the following one in SQLite and PostgreSQL:

INSERT INTO "fixture_users" ("id", "name") VALUES (:bnd1i1, :bnd1i2) ON CONFLICT ("id") DO UPDATE SET "name" = excluded."name", "age" = fixture_users.age + 1

UPDATE

UPDATE operations are constructed with the update method, which lets you specify the table, the new values and the conditions for the update.

$query = (new Builder($pdo))->update('fixture_users')
    ->set('name', 'Alex')
    ->where('id', 1);

$query->getQuery();
$query->getBindParams();
$query->execute();

->set($column, $value) lets you specify a single new value.
->setValues(['name' => 'Alex', 'age' => 34]) lets you specify several new values at once.
->table($table) lets you change the table after the builder was created.
->where($column, $value, $operator) lets you specify the conditions of the update.

DELETE

DELETE operations are constructed with the delete method, which lets you specify the table and the conditions for deleting data.

$query = (new Builder($pdo))->delete('fixture_users')->where('id', 5);

$query->getQuery();
$query->getBindParams();
$query->execute();

Transactions

A transaction is taken from the builder and reused, so nested calls share one connection state:

$transaction = $builder->transaction();

$transaction->begin();
$builder->insert('fixture_users')->value('name', 'John')->execute();
$transaction->commit();

->begin() starts a transaction. If a transaction is already running, a SAVEPOINT is created instead, so blocks can be nested.
->commit() commits the transaction, or releases the innermost savepoint.
->rollBack() rolls the transaction back, or rolls back to the innermost savepoint.
->inTransaction() tells whether a transaction is currently running.

The run method wraps a closure: it commits when the closure returns and rolls back when the closure throws. The exception is rethrown, and the return value of the closure is returned to the caller:

use Tereta\Dbal\Interfaces\Transaction as TransactionInterface;

$userId = $builder->transaction()->run(function (TransactionInterface $transaction) use ($builder): int {
    $builder->insert('fixture_users')->value('name', 'John')->execute();

    return (int) $builder->select('fixture_users')->columns('id')
        ->where('name', 'John')->execute()->fetchColumn();
});

DDL (Data Definition Language)

Note (MySQL): In MySQL you cannot run several DDL operations atomically: each DDL statement (CREATE/ALTER/DROP) triggers an implicit commit of the current transaction, so rolling back a series of DDL statements as a single unit is not possible. This is a limitation of the MySQL server, not of the InnoDB engine. On SQLite and PostgreSQL DDL is transactional and such operations can be rolled back.

CREATE TABLE

The Tereta/DBAL builder includes tools for constructing tables, indexes and foreign keys.

use Tereta\Dbal\Data\Now;
use Tereta\Dbal\Interfaces\Create\Schema as SchemaInterface;

$query = (new Builder($pdo))
    ->create('fixture_users')
    ->ifNotExists()
    ->schema(function (SchemaInterface $schema): void {
        $schema->field('id')->integer()->primary()->autoIncrement();
        $schema->field('site_id')->integer();
        $schema->field('name')->varchar(64)->nullable(false);
        $schema->field('age')->tinyint()->unsigned();
        $schema->field('balance')->tinyint()->unsigned(false);
        $schema->field('email')->varchar(320);
        $schema->field('description')->string();
        $schema->field('created_at')->datetime()->default(new Now())->index();
        $schema->field('created_at_timestamp')->timestamp()->default(new Now())->index();
        $schema->unique('site_id', 'email');
    });

$query->execute();

->ifNotExists() adds an IF NOT EXISTS guard so creating an existing table is not an error. The indexes declared in the schema do not get that guard, so running the same CREATE again on an existing table fails on the index creation.
->schema(\Closure) describes the table. The closure receives the schema object.
$schema->field($name) adds a column, see the list of column methods in the ALTER TABLE section.
$schema->unique($column, ...) adds a unique index over the listed columns.
$schema->index($column, ...) adds a regular index over the listed columns.

Because a CREATE TABLE can consist of several SQL statements (the table itself plus its indexes), you can also take those statements instead of running them:

foreach ($query->getQueries() as $sql) {
    $pdo->exec($sql);
}

DROP TABLE

$query = (new Builder($pdo))->drop('fixture_users')->ifExists();

$query->getQuery();
$query->execute();

->ifExists() adds an IF EXISTS guard so dropping a missing table is not an error.

ALTER TABLE

use Tereta\Dbal\Interfaces\Alter\Column as ColumnInterface;

$query = (new Builder($pdo))
    ->alter('fixture_users')
    ->column('newAge', function (ColumnInterface $column): void {
        $column->add()->tinyint()->unsigned()->nullable(false)->default(0)->index();
    })->column('uniqueLeft', function (ColumnInterface $column): void {
        $column->add()->tinyint()->unsigned()->nullable(true);
    })->column('uniqueRight', function (ColumnInterface $column): void {
        $column->add()->varchar(10)->nullable(false)->default('test');
    })->unique('uniqueLeft', 'uniqueRight');

$query->getQueries();
$query->execute();

->alter('fixture_users') indicates that an ALTER TABLE query should be built for the given table.
->column('name', \Closure) lets you specify changes for a column. The column configuration happens inside the closure.
->unique('uniqueLeft', 'uniqueRight') indicates that the columns uniqueLeft and uniqueRight must be unique in combination with each other.
->getQueries() returns every statement the change requires. On SQLite an ALTER TABLE is emulated by rebuilding the table, so a single change can produce several statements. ->execute() runs them inside one transaction.

Inside the column closure you first choose the operation:

->column('uniqueRight', function (ColumnInterface $column): void {
    $column->add()->varchar(10)->nullable(false)->default('test');
})

->add() - indicates that the column is being added
->remove() - indicates that the column is being removed
->modify() - indicates that the column is being modified
->rename('newName') - renames the column

The add and modify operations return the column definition, which accepts:

->integer(), ->bigint(), ->tinyint(), ->decimal($precision, $scale), ->float() - numeric types
->varchar($length), ->string(), ->text(), ->blob(), ->json() - string and binary types
->datetime(), ->timestamp() - date and time types
->boolean() - a boolean type
->type('CUSTOM TYPE') - any type written by hand
->primary() - indicates that the column is a primary key
->autoIncrement() - indicates that the column is auto-increment
->unsigned() - indicates that the column is unsigned
->nullable(false) - indicates that the column cannot be NULL
->index() - indicates that an index must be created for the column
->default('test') - indicates that the column default value must be set to 'test'
->foreign($table, $column, $on) - creates a foreign key to the given table and column

A unique index is set not at the column level but at the builder level: ->unique('uniqueLeft', 'uniqueRight') (ALTER) or $schema->unique('site_id', 'email') (CREATE).

Indexes

Indexes can also be managed on their own, without CREATE or ALTER:

$builder->index('idx_users_name')
    ->on('fixture_users')
    ->columns('name')
    ->create()
    ->execute();

$builder->index('idx_users_name')
    ->on('fixture_users')
    ->ifExists(true)
    ->remove()
    ->execute();

->index($name) starts an index query with the given name.
->on($table) sets the table the index belongs to.
->columns($column, ...) sets the indexed columns.
->unique() marks the index as unique.
->create() and ->remove() choose the operation.
->ifNotExists(true) and ->ifExists(true) add the matching guards.

Schema

Tereta\Dbal\Schema introspects an existing table and returns its structure in a single, driver-independent format. It is a plain, dependency-injection-friendly object, create it with new and reuse it; there is no singleton or global state.

use Tereta\Dbal\Schema;

$schema = new Schema($pdo);
$table = $schema->table('fixture_users');

new Schema(PDO $pdo) creates the introspector around a connection (the driver is detected from the PDO instance). An optional Tereta\Dbal\Factories\Schema may be injected as the second argument to override how driver strategies are resolved.
->table(string $table) reads the table from the connection and returns a Tereta\Dbal\Data\Schema describing its columns, indexes and foreign keys.
->exists(string $table) tells whether the table exists.
->tables() returns the names of all tables in the current database.

Columns

foreach ($table->getColumns() as $column) {
    $column->getName();          // string
    $column->getType();          // normalized type, e.g. INT, VARCHAR
    $column->isUnsigned();       // bool
    $column->isNullable();       // bool
    $column->hasDefault();       // bool
    $column->getDefault();       // Expression|int|string|null
    $column->isPrimary();        // bool, true if part of the primary key
    $column->isAutoincrement();  // bool
    $column->isGenerated();      // bool
}

$table->hasColumn('email');      // bool
$table->getColumn('email');      // Tereta\Dbal\Data\Schema\Column (throws if missing)

Indexes

The hasIndex and getIndex methods work by index name; getIndex throws if the index is missing.

foreach ($table->getIndexes() as $index) {
    $index->getName();     // string
    $index->isUnique();    // bool
    $index->getColumns();  // string[]
}

if ($table->hasIndex('idx_users_name')) {
    $table->getIndex('idx_users_name'); // Tereta\Dbal\Data\Schema\Index
}

Foreign keys

foreach ($table->getForeignKeys() as $foreignKey) {
    $foreignKey->getColumns();        // string[], local columns
    $foreignKey->getForeignTable();   // string
    $foreignKey->getForeignColumns(); // string[]
    $foreignKey->getOnUpdate();       // string, referential action
    $foreignKey->getOnDelete();       // string, referential action
}

Extensibility

Each builder can be extended with a new driver via the addDriver(string $code, string $class): static method. The method is called on a factory instance, and the registered driver stays available for every builder created afterwards:

use Tereta\Dbal\Factories\Builders\Select as SelectFactory;

(new SelectFactory())->addDriver($driverIdentifier, $driverClassName);

The class you pass must implement the interface of the matching builder, otherwise an InvalidArgumentException is thrown. The same applies to every factory:

Tereta\Dbal\Factories\Builders\Alter
Tereta\Dbal\Factories\Builders\Create
Tereta\Dbal\Factories\Builders\Delete
Tereta\Dbal\Factories\Builders\Drop
Tereta\Dbal\Factories\Builders\Field
Tereta\Dbal\Factories\Builders\Index
Tereta\Dbal\Factories\Builders\Insert
Tereta\Dbal\Factories\Builders\Select
Tereta\Dbal\Factories\Builders\Transaction
Tereta\Dbal\Factories\Builders\Update
Tereta\Dbal\Factories\Builders\Where

A prepared factory can also be passed to the Builder constructor, which is the way to replace a driver for one builder only instead of registering it globally.

License and author

 www.████████╗███████╗██████╗ ███████╗████████╗ █████╗
     ╚══██╔══╝██╔════╝██╔══██╗██╔════╝╚══██╔══╝██╔══██╗
        ██║   █████╗  ██████╔╝█████╗     ██║   ███████║
        ██║   ██╔══╝  ██╔══██╗██╔══╝     ██║   ██╔══██║
        ██║   ███████╗██║  ██║███████╗   ██║   ██║  ██║
        ╚═╝   ╚══════╝╚═╝  ╚═╝╚══════╝   ╚═╝   ╚═╝  ╚═╝
                                                    .dev

tereta / dbal

Package info

Security

Statistics

README

Introduction

Getting started

DQL (Data Query Language)

SELECT

Columns, joins and subqueries

Conditions

Aggregates and expressions

UNION

DML (Data Manipulation Language)

INSERT

UPSERT

UPDATE

DELETE

Transactions

DDL (Data Definition Language)

CREATE TABLE

DROP TABLE

ALTER TABLE

Indexes

Schema

Columns

Indexes

Foreign keys

Extensibility

License and author