A PDO Wrapper

A Secure, fluent, lightweight, and efficient PDO wrapper.

Help to protect again SQL injections.

Install

Requirements

PHP >= 8.1
PDO ext
Optional development dependencies
- PHPUnit

Install with composer

composer require tivins/database

Quick example

use Tivins\Database\Database;
use Tivins\Database\Connectors\MySQLConnector;

require 'vendor/autoload.php';

$db = new Database(new MySQLConnector('dbname', 'user', 'password', 'localhost'));

$posts = $db->select('books', 'b')
    ->leftJoin('users', 'u', 'b.author_id = u.id')
    ->addFields('b')
    ->addField('u', 'name', 'author_name')
    ->condition('b.year', 2010)
    ->execute()
    ->fetchAll();

Summary

Usage
- Connectors
- Queries :
  - Select
  - Insert
  - Update
  - Create
  - Delete
  - Extended
    - SelectInsert
    - Merge
- Conditions and expressions
- Transversal
  - Order by
  - Range/Limit
- Transactions
- Error handling
Development
- Unit tests

Usage

Connectors

Creating a Database instance requires a valid Connector instance.

# MySQL
$connector = new MySQLConnector('dbname', 'user', 'password');
# SQLite
$connector = new SQLiteConnector('path/to/file');

$db = new Database (new MySQLConnector(
  dbname:   'my_database', 
  user:     'my_user', 
  password: 'my_encrypted_password',
  host:     'localhost',
  port:     3306,
));

Then create an instance of Database, with the created connector :

$database = new Database($connector);

A ConnectionException can be thrown when the new Database() attempt to connect the given Connector.

Using queries

Both usages below are valid :

// from database object
$query = $db->select('users', 'u');
// from new object
$query = new SelectQuery($db, 'users', 'u');

Select query

Basic

$data = $db->select('books', 'b')
    ->addFields('b')
    ->condition('b.reserved', 0)
    ->execute()
    ->fetchAll();

Join

use as well innerJoin, leftJoin.

$db->select('books', 'b')
    ->addFields('b', ['id', 'title'])
    ->leftJoin('users', 'u', 'u.id = b.owner')
    ->addField('u', 'name', 'owner_name')
    ->condition('b.reserved', 1)
    ->execute()
    ->fetchAll();

Expression

$db->select('books', 'b')
    ->addField('b', 'title')
    ->addExpression('concat(title, ?)', 'some_field', time())
    ->condition('b.reserved', 0)
    ->execute()
    ->fetchAll();

Group by

$tagsQuery = $db->select('tags', 't')
    ->innerJoin('book_tags', 'bt', 'bt.tag_id = t.id')
    ->addFields('t')
    ->addExpression('count(bt.book_id)', 'books_count')
    ->groupBy('t.id')
    ->orderBy('t.name', 'asc');

Condition Expression

$db->select('books', 'b')
    ->addFields('b')
    ->conditionExpression('concat(b.id, "-", ?) = b.reference', $someValue)
    ->execute();

Range/Limit

$query->limit(10);          # implicit start from 0.
$query->limitFrom(0, 10);   # explicit start from 0.
$query->limitFrom(100, 50); # will fetch 50 rows from 100th row.

Order by

orderBy() add a new order statement in the query. It can be called multiple times.

$query->orderBy('field', 'desc');

Multiple times. In the following example, the results will be sorted by post_type, and then, by date:

$query->orderBy('post_type', 'desc')
      ->orderBy('date', 'asc');

Insert query

$db->insert('book')
    ->fields([
        'title' => 'Book title',
        'author' => 'John Doe',
    ])
    ->execute();

Multiples inserts

$db->insert('book')
    ->multipleFields([
        ['title' => 'Book title', 'author' => 'John Doe'],
        ['title' => 'Another book title', 'author' => 'John Doe Jr'],
    ])
    ->execute();

or,

$db->insert('book')
    ->multipleFields([
          ['Book title', 'John Doe'],
          ['Another book title', 'John Doe Jr'],
        ], 
        ['title', 'author'])
    ->execute();

execute() will insert two rows in the table book.

See the build result

Query

insert into `book` (`title`,`author`) values (?,?), (?,?);

Parameters

["Book title","John Doe","Another book title","John Doe Jr"]

Insert expressions

Expressions can be used inside the array given to fields() function.

$db->insert('geom')
    ->fields([
        'name'     => $name,
        'position' => new InsertExpression('POINT(?,?)', $x, $y)
    ])
    ->execute();

Execute() will insert two rows in the table book.

See the build result

Query

insert into `geom` (`name`, `position`) values (?, POINT(?,?))

Parameters
```
[$name, $x, $y]
```

InsertExpression are also allowed with a MergeQuery.

Update query

$db->update('book')
    ->fields(['reserved' => 1])
    ->condition('id', 123)
    ->execute();

Merge query

$db->merge('book')
    ->keys(['ean' => '123456'])
    ->fields(['title' => 'Book title', 'author' => 'John Doe'])
    ->execute();

Delete query

Perform a delete query on the given table. All methods of Conditions can be used on a DeleteQuery object.

$db->delete('book')
    ->whereIn('id', [3, 4, 5])
    ->execute();

Create query

Perform a create table query on the current database.

$query = $db->create('sample')
    ->addAutoIncrement(name: 'id')
    ->addInteger('counter', 0, unsigned: true, nullable: false)
    ->addInteger('null_val', null, nullable: false)
    ->addJSON('json_field')
    ->execute();

Field types :

Integers

$query->addPointer('id_user'); // Shortcut to Not-null Unsigned Integer

UnitEnum or BackedEnum

Enum Fruits { case Apple; case Banana; }
$query->addEnum('fruits', Fruits::cases());

Standard Enum

$query->addStdEnum('fruits', ['apple','banana'], 'apple');

Select-Insert Query

Perform a select, then an insert if not found.

$qry = $db->selectInsert('users')->matching(['name' => 'test', 'state' => 1]);
$qry->fetch()->id; // 1
$qry->getProcessedOperation(); // MergeOperation::INSERT

$qry = $db->selectInsert('users')->matching(['name' => 'test', 'state' => 1]);
$qry->fetch()->id; // 1
$qry->getProcessedOperation(); // MergeOperation::SELECT

By default, array given in matching() are used to insert the new record.

You can define the fields for the insert query:

$matches = ['email' => 'user@example.com'];
$obj = $db->selectInsert('users')
    ->matching($matches)
    ->fields($matches + ['name' =>  'user', 'created' => time()])
    ->fetch();

Expressions

You can use SelectQuery::addExpression() to add an expression to the selected fields.

Signature : ->addExpression(string $expression, string $alias, array $args).

$query = $db->select('books', 'b')
    ->addExpression('concat(title, ?)', 'some_field', time())
    ->execute();

Predefined expressions

Count (addCount())

$total = $db->select('table','t')
    ->addCount('*')
    ->execute()
    ->fetchField();

Conditions

Some examples :

->condition('field', 2);      // eg: where field = 2
->condition('field', 2, '>'); // eg: where field > 2
->condition('field', 2, '<'); // eg: where field < 2
->whereIn('field', [2,6,8]);  // eg: where field in (2,6,8)
->like('field', '%search%');  // eg: where field like '%search%'
->isNull('field');            // eg: where field is null
->isNotNull('field');         // eg: where field is not null

Nested conditions

Conditions are available for SelectQuery, UpdateQuery and DeleteQuery.

$db->select('book', 'b')
    ->fields('b', ['id', 'title', 'author'])
    ->condition(
        $db->or()
        ->condition('id', 3, '>')
        ->like('title', '%php%')
    )
    ->execute();

And below is equivalent :

$db->select('book', 'b')
    ->fields('b', ['id', 'title', 'author'])
    ->condition(
        (new Conditions(Conditions::MODE_OR))
        ->condition('id', 3, '>')
        ->like('title', '%php%')
    )
    ->execute();

Having

$db->select('maps_polygons', 'p')
    // ->...
    ->having($db->and()->isNotNull('geom'))
    ->execute()
    //...
    ;

Transactions

use Tivins\Database{ Database, DatabaseException, MySQLConnector };
function makeSomething(Database $db)
{
    $db->transaction()
    try {
        // do some stuff
    }
    catch (DatabaseException $exception) {
        $db->rollback();
        // log exception...
    }
}

Full Example

See /tests/FullTest.php

Error handling

There are three main exception thrown by Database.

ConnectionException, raised by the Database constructor, if a connection cannot be established.
DatabaseException, thrown when a PDO exception is raised from the query execution.
ConditionException, raised when a given operator is not allowed.

All of these exceptions has an explicit message (from PDO, essentially).

Usage short example :

try {
    $this->db = new Database($connector);
}
catch (ConnectionException $exception) {
    $this->logErrorInternally($exception->getMessage());
    $this->displayError("Cannot join the database.");
}

try {
    $this->db->insert('users')
        ->fields([
            'name' => 'DuplicateName',
        ])
        ->execute();  
}
catch (DatabaseException $exception) {
    $this->logErrorInternally($exception->getMessage());
    $this->displayError("Cannot create the user.");
}

Unit tests

Create a test database, and a grant to a user on it. Add a phpunit.xml at the root of the repository.

/* NB: This is a quick-start example. */
create database test_db;
create user test_user@localhost identified by 'test_passwd';
grant all on test_db.* to test_user@localhost;
flush privileges;

<phpunit>
    <php>
        <env name="DB_NAME" value="test_db"/>
        <env name="DB_USER" value="test_user"/>
        <env name="DB_PASS" value="test_password"/>
        <env name="DB_HOST" value="localhost"/>
    </php>
</phpunit>

Then, run unit tests :

vendor/bin/phpunit tests/

To include a coverage test, use :

mkdir -p build/logs
vendor/bin/phpunit tests/ --coverage-clover cover.xml

License

Database is released under the MIT License. See the bundled LICENSE file for details.

In addition, if you are using the --dev mode, some parts of the project have their own licenses attached (either in the source files or in a LICENSE file next to them).

tivins/database