Database

Darya\Database

Darya's database package provides tools and abstractions for interacting with different relational databases in a consistent way, despite differences in SQL dialects.

• Connections
  • Queries
  • Results
• Storage
• Joins & Subqueries
  • Simple joins
  • Complex join
  • Select clause subquery
  • Where-condition subquery
  • Insert select

Connections

Create database connections using a factory.

Supported databases are currently 'mysql', 'sqlite' and 'mssql'/'sqlserver'.

use Darya\Database\Factory;

$factory = new Factory;

$connection = $factory->create('mysql', [
	'hostname' => 'localhost',
	'username' => 'darya',
	'password' => 'password',
	'database' => 'darya'
]);

Connections aren't initiated until you explicitly call either the connect() or query() method.

$connection->connect();

If you prefer, you can just instantiate a connection yourself.

use Darya\Database\Connection\MySql;
use Darya\Database\Connection\SqlServer;

$mySqlConnection = new MySql('hostname', 'username', 'password', 'database');

$sqlServerConnection = new SqlServer('hostname', 'username', 'password', 'database');

Queries

Perform simple queries and retrieve their result data.

$result = $connection->query('SELECT * FROM users');

// You can iterate straight over the result object
foreach ($result as $row) {
	// ...
}

// Or grab the array of result data directly
$data = $result->data;

// Optionally accepts parameters to bind
$result = $connection->query('SELECT * FROM users WHERE id = ?', [1]);

Or use a query object.

use Darya\Database\Query;

$query = new Query('SELECT * FROM users WHERE name LIKE ?', ['%darya%']);

$query->string;     // The SQL query string
$query->parameters; // The query parameters

$result = $connection->query($query);

Results

Access result metadata.

$result->count;
$result->fields;
$result->affected;
$result->insertId;

Access the query that produced the result.

$query = $result->query; // Darya\Database\Query

$sql = $result->query->string;

Results will expose an error property if an error occurred with the query.

$result->error; // Darya\Database\Error

if ($result->error) {
	$result->error->number;
	$result->error->message;
}

Storage

The database storage namespace provides a fluent interface for interacting with a connection.

use Darya\Database\Storage;

$storage = new Storage($connection);

Once you've created a storage object with a connection, you can start querying.

See the Darya\Storage namespace to learn more about using the query builder.

$result = $storage->query('users')->where('id >', 50)->read();

Results allow you to access the storage and database queries that produced it.

You can access metadata and error data in the same way as connection queries.

// Queries
$result->query;         // Darya\Database\Storage\Query
$result->databaseQuery; // Darya\Database\Query

// Metadata
$result->count;
$result->fields;
$result->affected;
$result->insertId;

if ($result->error) {
	$result->error->number;
	$result->error->message;
}

Joins & Subqueries

Database storage queries offer extra query builder functionality.

Simple joins

$result = $storage->query('users', 'users.*')
	->join('comments', 'comments.user_id = users.id')
	->where('comments.body like', '%darya%')
	->read();

$storage->query('users')->leftJoin('comments')->read();
$storage->query('users')->rightJoin('comments')->read();

Complex join

$result = $storage->query('users', [
		'users.id'   => 'user_id',
		'admin.id'   => 'admin_id',
		'users.name' => 'name'
	])
	->join('admin', function ($join) {
		$join->on('admin.user_id = users.id'); // Identifier-only condition
		$join->where('admin.active >', 0);     // Value condition
	})
	->where('users.name like', '%darya%')
	->read();

Select clause subquery

$subquery = $storage->query('sections', ['title'])
	->where('sections.page_id = pages.id')
	->where('sections.title like', '%awesome%');

$query = $storage->query('pages', ['id', $subquery]);

Where-condition subquery

$result = $storage->query('users')->where('id not in', $storage->query(
	'users_archive', ['id']
))->read();

Insert select

Insert into a table using the result of another query.

$storage->query('users_archive')->insertFrom(
	$storage->query('users')->where('created <=', strtotime('-1 year'))
)->run();

This works when providing columns too.

$storage->query('users_archive', ['id', 'name'])->insertFrom(
	$storage->query('users', ['id', 'name'])->where(
		'created <=', strtotime('-1 year')
	)
)->run();