README

🌐 English | Русский | Українська

Introduction

The main goal of Tereta/DBAL is to provide a convenient and powerful tool for working with database queries. Tereta/DBAL is a builder that generates SQL queries which can be executed on various database engines. Drivers supported out of the box:

• SQLite
• PostgreSQL
• MySQL

DQL (Data Query Language)

SELECT

$builder = Builder::singleton($driverCode)->select('users')
    ->where('id', 1)
    ->order('name')
    ->limit(10);

$stmt = $pdo->prepare($builder->getQuery());
$user = $stmt->execute($builder->getBindParams())->fetchAll(PDO::FETCH_ASSOC);

• ->select lets you specify the table to query, equivalent to ->select()->from('fixture_users')
• ->where lets you specify the conditions for the query.
• ->order lets you specify the sort order of the results.
• ->limit lets you limit the number of returned rows.
• ->getQuery() returns the generated SQL query as a string.
• ->getBindParams() returns the array of parameters to bind to the prepared statement.

$builder = Builder::singleton($driverCode)->select()
    ->columns(['fixture_users.id' => 'fixture_user_id'], "fixture_users.name")
    ->from('fixture_users')
    ->where(function (Where $whereBuilder, Builder $builder) {
        $whereBuilder->and(
            'fixture_users.id',
             $builder->select('fixture_users')->columns('id')
                 ->where('avaliable', false, 'IS NOT')
            );
        })
        ->leftJoin(
            'fixture_users_address',
            'fixture_users.id = fixture_users_address.user_id',
            [
                'fixture_users_address.user_id',
                ['fixture_users_address.user_id' => 'address_user_id']
            ]
        );

• ->columns lets you specify the columns to query, as well as aliases for those columns 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.
• ->from lets you specify the table to query.
• ->where lets you specify the query conditions. In this case a nested builder is used to express a condition that contains a subquery.
• ->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 (same interface), lets you specify a CROSS JOIN to combine tables.

DML (Data Manipulation Language)

INSERT

Tereta/DBAL supports the insert, update and delete methods for building queries that insert, update and delete data in tables. 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.

$builder = Builder::singleton($driverCode)->insert('fixture_users')
    ->value('name', 'John')->value('age', 20);

$builder->getQuery();
$builder->getBindParams();

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

$builder = Builder::singleton($driverCode)
    ->insert('fixture_users')
    ->value('age', 30)->value('email', 'tereta.alexander@gmail.com')
        ->value('group_id', 1)->value('created_at', '2026-06-05 21:10:00')
    ->value('age', 22)->value('email', 'support@tereta.dev')
        ->value('group_id', 1)->value('created_at', '2026-06-05 21:10:00');
        
$builder->getQuery();
$builder->getBindParams();

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 age, email, group_id and created_at belong to a single row; however, when the age key repeats, the builder determines that this is the start of a new row to insert.

$builder = Builder::singleton($driverCode)
    ->insert('fixture_users_select')
    ->values(['name' => 'Alex', 'age' => 34])
    ->values(['name' => 'Max', 'age' => 30])
    ->values(['name' => 'Ann', 'age' => 29]);

$builder->getQuery();
$builder->getBindParams();

UPSERT

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

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

• ->update lets you specify the actions to take on conflict, for example updating existing records.

  $updateBuilder->conflict('id')->set('name', 'name')
    ->set('age', new Expression('fixture_users.age + 1'));
  

The condition will generate query

INSERT INTO `fixture_users` (`id`, `name`) VALUES (:bnd1i1, :bnd1i2) AS new ON DUPLICATE KEY UPDATE `name` = new.`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.

• getQuery() returns the generated SQL query as a string.
• getBindParams() returns the array of parameters to bind to the prepared statement.

$query = Builder::singleton($driverCode)->update('fixture_users')
    ->set('name', 'Alex')->where('id', 1);

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

DELETE

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

• getQuery() returns the generated SQL query as a string.
• getBindParams() returns the array of parameters to bind to the prepared statement.

$query = Builder::singleton($driverCode)->delete('fixture_users')->where('id', 5)

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

DDL (Data Definition Language)

CREATE TABLE

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

$builder = Builder::singleton($driverCode)
    ->create()->table('fixture_users')
    ->schema(function (Schema $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');
    });

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

Because DDL queries can be complex and consist of several SQL statements, the getQueries method returns an array of SQL queries that must be executed to create the table.

DROP TABLE

$builder = Builder::singleton($driverCode)
    ->drop('fixture_users')
    ->getQuery();

ALTER TABLE

$builder = Builder::singleton($driverCode)
            ->alter()->table('fixture_users')
            ->column('newAge', function (Column $column): void {
                $column->add()->tinyint()->unsigned()->nullable(false)->index();
            })->column('uniqueLeft', function (Column $column): void {
                $column->add()->tinyint()->unsigned()->nullable(true);
            })->column('uniqueRight', function (Column $column): void {
                $column->add()->varchar(10)->nullable(false)->default('test');
            })->unique('uniqueLeft', 'uniqueRight');

$builder->getQueries();

• ->alter('table') or ->alter()->table('fixture_users') indicates that an ALTER TABLE query should be built.
• ->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.

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

Inside the column closure you can set

• ->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
• ->tinyint() - the column data type; available types are integer, varchar, text, datetime, timestamp, tinyint, bigint, decimal, float, json, boolean
• ->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(), indicates that a foreign key must be created for the 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).

Schema

Tereta\Dbal\Schema is a tool for describing a table schema in a single format across all drivers.

Extensibility

Each builder can be extended with a new driver via the addDriver(string $code, string $class): static method

Tereta\Dbal\Factories\Builders\Alter::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Create::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Delete::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Drop::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Field::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Index::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Insert::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Select::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Update::addDriver($driverIdentifier, $driverClassName);
Tereta\Dbal\Factories\Builders\Where::addDriver($driverIdentifier, $driverClassName);