A secure, fluent, lightweight, and efficient PDO wrapper.
Helps protect against SQL injections.
Travis CI Github CI Coverage Status
- PHP >= 8.1
- PDO extension
- Optional development dependencies:
See composer.json.
composer require tivins/database
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();
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.
Both approaches below are valid:
// From database object $query = $db->select('users', 'u'); // From new object $query = new SelectQuery($db, 'users', 'u');
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();
$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
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');
$db->insert('book') ->fields([ 'title' => 'Book title', 'author' => 'John Doe', ]) ->execute();
$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.
See the build result
- Query
insert into `book` (`title`,`author`) values (?,?), (?,?);
- Parameters
["Book title","John Doe","Another book title","John Doe Jr"]
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.
See the build result
- Query
insert into `geom` (`name`, `position`) values (?, POINT(?,?))
- Parameters
[$name, $x, $y]
InsertExpression is also allowed with a MergeQuery.
$db->update('book') ->fields(['reserved' => 1]) ->condition('id', 123) ->execute();
$db->merge('book') ->keys(['ean' => '123456']) ->fields(['title' => 'Book title', 'author' => 'John Doe']) ->execute();
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();
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');
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();
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();
Count (addCount())
$total = $db->select('table', 't') ->addCount('*') ->execute() ->fetchField();
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
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();
$db->select('maps_polygons', 'p') // ... ->having($db->and()->isNotNull('geom')) ->execute(); // ...
use Tivins\Database{ Database, DatabaseException, MySQLConnector }; function makeSomething(Database $db) { $db->transaction(); try { // do some stuff } catch (DatabaseException $exception) { $db->rollback(); // log exception... } }
See FullTest.php
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."); }
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
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).