README

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

Helps protect against SQL injections.

Install

Requirements

• PHP >= 8.1
• PDO extension
• Optional development dependencies:
  • PHPUnit

See composer.json.

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

Table of Contents

• Usage
  • Connectors
  • Queries
    • Select
    • Insert
    • Update
    • Create
    • Delete
    • Extended Queries
      • SelectInsert
      • Merge
  • Conditions and Expressions
    • Nested conditions
    • Expressions
      • Predefined expressions
      • Insert expressions
    • Having
  • Common Operations
    • 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');
// Native (PDO object)
$connector = new NativeConnector($existingPDOHandler);

Or

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

Then create a Database instance with the created connector:

$database = new Database($connector);

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

Using Queries

Both approaches below are valid:

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

Select Query

Basic Usage

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

Joins

You can also use innerJoin and 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();

Expressions

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

$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() adds a new order statement to 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, then by date:

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

Insert Query

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

Multiple 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 into the book table.

Insert expressions

Expressions can be used inside the array passed to the fields() method.

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

execute() will insert one row into the geom table.

InsertExpression is 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

Performs a DELETE query on the given table. All methods from Conditions can be used on a DeleteQuery object.

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

Create Query

Performs 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

Performs 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, the array given in matching() is 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);      // e.g.: where field = 2
->condition('field', 2, '>'); // e.g.: where field > 2
->condition('field', 2, '<'); // e.g.: where field < 2
->whereIn('field', [2,6,8]);  // e.g.: where field in (2,6,8)
->like('field', '%search%');  // e.g.: where field like '%search%'
->isNull('field');            // e.g.: where field is null
->isNotNull('field');         // e.g.: 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();

The following 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 FullTest.php

Error handling

There are three main exceptions thrown by the Database class:

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

All of these exceptions have explicit messages (essentially from PDO).

Short usage example:

try {
    $this->db = new Database($connector);
} catch (ConnectionException $exception) {
    $this->logErrorInternally($exception->getMessage());
    $this->displayError("Cannot connect to 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 grant permissions to a user on it. Add a phpunit.xml file at the root of the repository.

-- 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 the unit tests:

vendor/bin/phpunit tests/

To include coverage testing, use:

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

License

This project 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).

Statistics