I’m not a big fan of ORMs. I feel very confortable working with raw SQLs and because of that I normally use DBAL (or PDO in old projects). I’ve got one small library to handle my dayly operations with databases and today I’ve written this library

First of all imagine one DBAL connection. I’m using a sqlite in-memomy database in this example but we can use any database supported by DBAL (aka “almost all”):

[sourcecode language=”php”]
use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection([
‘driver’ => ‘pdo_sqlite’,
‘memory’ => true
]);
[/sourcecode]

We can also create one DBAL connection from a PDO connection. It’s usefull to use DBAL within legacy applications instead of creating a new connection (remember that DBAL works over PDO)

[sourcecode language=”php”]
use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection([‘pdo’ => new PDO(‘sqlite::memory:’)]);
[/sourcecode]

Now we set up the database for the example
[sourcecode language=”php”]
$conn->exec("CREATE TABLE users (
userid VARCHAR PRIMARY KEY NOT NULL ,
password VARCHAR NOT NULL ,
name VARCHAR,
surname VARCHAR
);");
$conn->exec("INSERT INTO users VALUES(‘user’,’pass’,’Name’,’Surname’);");
$conn->exec("INSERT INTO users VALUES(‘user2′,’pass2′,’Name2′,’Surname2’);");
[/sourcecode]

Our table “users” has two records. Now we can start to use our library.

First we create a new instance of our library:
[sourcecode language=”php”]
use G\Db;

$db = new Db($conn);
[/sourcecode]

Now a simple query from a string:
[sourcecode language=”php”]
$data = $db->select("select * from users");
[/sourcecode]

Sometimes I’m lazy and I don’t want to write the whole SQL string and I want to perform a select * from table:
[sourcecode language=”php”]
use G\Sql;
$data = $db->select(SQL::createFromTable("users"));
[/sourcecode]

Probably we need to filter our Select statement with a WHERE clause:
[sourcecode language=”php”]
$data = $db->select(SQL::createFromTable("users", [‘userid’ => ‘user2’]));
[/sourcecode]

And now something very intersting (at least for me). I want to iterate over the recordset and maybe change it. Of course I can use “foreach” over $data and do whatever I need, but I preffer to use the following sintax:
[sourcecode language=”php”]
$data = $db->select(SQL::createFromTable("users"), function (&$row) {
$row[‘name’] = strtoupper($row[‘name’]);
});
[/sourcecode]

For me it’s more readable. I iterate over the recordset and change the row ‘name’ to uppercase. Here you can see what is doing my “select” function:

[sourcecode language=”php”]
/**
* @param Sql|string $sql
* @param \Closure $callback
* @return array
*/
public function select($sql, \Closure $callback = null)
{
if ($sql instanceof Sql) {
$sqlString = $sql->getString();
$parameters = $sql->getParameters();
$types = $sql->getTypes();
} else {
$sqlString = $sql;
$parameters = [];
$types = [];
}

$statement = $this->conn->executeQuery($sqlString, $parameters, $types);
$data = $statement->fetchAll();
if (!is_null($callback) && count($data) > 0) {
$out = [];
foreach ($data as $row) {
if (call_user_func_array($callback, [&$row]) !== false) {
$out[] = $row;
}
}
$data = $out;
}

return $data;
}
[/sourcecode]

And finally transactions (I normally never use autocommit and I like to handle transactions by my own)

[sourcecode language=”php”]
$db->transactional(function (Db $db) {
$userId = ‘temporal’;

$db->insert(‘users’, [
‘USERID’ => $userId,
‘PASSWORD’ => uniqid(),
‘NAME’ => ‘name3’,
‘SURNAME’ => ‘name3’
]);

$db->update(‘users’, [‘NAME’ => ‘updatedName’], [‘USERID’ => $userId]);
$db->delete(‘users’, [‘USERID’ => $userId]);
});
[/sourcecode]

The “transactional” function it’s very simmilar than DBAL’s transactional function

[sourcecode language=”php”]
public function transactional(\Closure $callback)
{
$out = null;
$this->conn->beginTransaction();
try {
$out = $callback($this);
$this->conn->commit();
} catch (\Exception $e) {
$this->conn->rollback();
throw $e;
}

return $out;
}
[/sourcecode]

I change a little bit because I like to return a value within the closure and allow to do things like that:
[sourcecode language=”php”]
$status = $db->transactional(function (Db $db) {
$userId = ‘temporal’;

$db->insert(‘users’, [
‘USERID’ => $userId,
‘PASSWORD’ => uniqid(),
‘NAME’ => ‘name3’,
‘SURNAME’ => ‘name3’
]);

$db->update(‘users’, [‘NAME’ => ‘updatedName’], [‘USERID’ => $userId]);
$db->delete(‘users’, [‘USERID’ => $userId]);

return "OK"
});
[/sourcecode]

The other functions (insert, update, delete) only bypass the calls to DBAL’s funcitons:
[sourcecode language=”php”]
private $conn;

public function __construct(Doctrine\DBAL\Connection $conn)
{
$this->conn = $conn;
}

public function insert($tableName, array $values = [], array $types = [])
{
$this->conn->insert($tableName, $values, $types);
}

public function delete($tableName, array $where = [], array $types = [])
{
$this->conn->delete($tableName, $where, $types);
}

public function update($tableName, array $data, array $where = [], array $types = [])
{
$this->conn->update($tableName, $data, $where, $types);
}
[/sourcecode]

And that’s all. You can use the library with composer and download at github.

BTW I’ve test the new Sensiolabs product (SensioLabs Insight) to analyze the code and verify good practices and I’ve got the Platinum medal #yeah!