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) it returns nothing.

DQL (Data Query Language)

SELECT

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

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

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

$pdoStatement = $builder->execute();

->select($table) lets you specify the table to query.
->where(...$args) lets you specify the query conditions.
->order($column, $direction) lets you specify the sort order of the results.
->limit($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.
->execute() returns a PDOStatement if a PDO is set in the builder

$builder = new Builder($pdo)->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']
            ]
        );

$builder->getQuery();
$builder->getBindParams();
$builder->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.
->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.
->execute() returns a PDOStatement if a PDO is set in the builder

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 = new Builder($pdo)->insert('fixture_users')
    ->value('name', 'John')->value('age', 20);

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

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

$builder = new Builder($pdo)
    ->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();
$builder->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 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 = new Builder($pdo)
    ->insert('fixture_users_select')
    ->values(['name' => 'Alex', 'age' => 34])
    ->values(['name' => 'Max', 'age' => 30])
    ->values(['name' => 'Ann', 'age' => 29]);

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

UPSERT

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

$builder = new Builder($pdo)
    ->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'));
    });

$builder->execute();

->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 = new Builder($pdo)->update('fixture_users')
    ->set('name', 'Alex')->where('id', 1);

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

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 = new Builder($pdo)->delete('fixture_users')->where('id', 5)

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

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.

$builder = new Builder($pdo)
    ->create('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 = new Builder($pdo)->drop('fixture_users');

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

ALTER TABLE

$builder = new Builder($pdo)
            ->alter('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();
$builder->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.

})->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 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.

$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.

Columns

foreach ($table->getColumns() as $column) {
    $column->getName();          // string
    $column->getType();          // normalized type, e.g. INT, VARCHAR
    $column->isUnsigned();       // bool
    $column->isNullable();       // bool
    $column->getDefault();       // int|string|null
    $column->primary();          // 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

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

$table->hasIndex('idx_email'); // bool
$table->getIndex('idx_email'); // Tereta\Dbal\Data\Schema\Index (throws if missing)

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

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

tereta / dbal

Maintainers

Package info

Statistics

Security