README

Handle database read/write connections. Use native database engine query syntax.
( currently only postresql is supported )

Usage

Check integration tests for more details

Connect to the database

Connection parameters are provided in the form of the url, according to the mentioned schema:

// $dbUrl = 'pgsql://user:password@host:port/database_name
$masterDbUrl = 'pgsql://postgres:postgres@127.0.0.1:5432/postgres';
$slaveDbUrl = 'pgsql://postgres:postgres@127.0.0.2:5432/postgres';
$dbClient = new PgDatabaseClient($masterDbUrl, $slaveDbUrl);
$dbClient->init();

Connection is made inside the init() call.
Slave parameter can be skipped. In such case only one connection will be established with the database.
Using $dbClient->useWriteConnection() will keep the same connection. If both master and slave urls are provided, init() will connect to slave database only. Connection to master will be established on $dbClient->useWriteConnection() call, and will be kept as the active connection for all future queries.
If master and slave urls are the same it will be handled as two separate connections.

Running queries

Query comment is optional, it helps to identify the query in the database logs.
Example of postgresql log entry:

2022-03-30 17:44:26.321 UTC [2006] postgres@database_connector_test_20220330174425_889153_791607487 LOG:  statement: -- query native single row test
        SELECT 'some random string';

Simple query

Execute a query that does not require any parameter replacement.
Results are returned as \Generator objects.

$rowGenerator = $dbClient->queryNative("SELECT 'some random text';", 'read random text');
$row = $rowGenerator->current();

To get the result in the form of an associative array:

$allRows = $dbClient->queryNativeAll('SELECT name, age FROM customers;', 'read all customer names with age');
/**
 * $allRows = [
 *   ['name' => 'Konrad', 'age' => 21],
 *   ['name' => 'Thomas', 'age' => 32],
 * ];
 */

Query with parameter escaping

Parameters are escaped according to the used placeholder.

$queryBuilder = $dbClient->newQueryBuilder('SELECT name, age FROM customers WHERE name = %s;', ['Konrad'], [], 'read details about Konrad');
$rowGenerator = $dbClient->query($queryBuilder);

Placeholder	Description	Sample Value	Sample QUERY	Result
%%	% character	%	SELECT %%	SELECT %
%s	string value	'text', null	SELECT %s	SELECT 'text'<br/>SELECT NULL
%i	integer value	21, null	SELECT %i	SELECT 21<br/>SELECT NULL
%d	decimal value	43.23, null	SELECT %d	SELECT 43.23<br/>SELECT NULL
%b	boolean value	true, false, 'yes', 'no', 't', 'f', 1, 0, null	SELECT %b	SELECT TRUE<br/>SELECT FALSE<br/>SELECT NULL
%c	column name	'name'	SELECT FROM %c	SELECT FROM "name"
%t?	detect value type	'text', 21, 43.23, null, true	SELECT %t?	SELECT 'text'<br/>SELECT 21<br/>SELECT NULL
%ls	list of strings	['konrad', 'thomas', null]	SELECT IN (%s)	SELECT IN ('konrad', 'thomas', NULL)
%li	list of integers	[21, 33, null]	SELECT IN (%s)	SELECT IN (21, 33, NULL)
%ld	list of decimals	[3.5, 1.65, null]	SELECT IN (%s)	SELECT IN (3.5, 1.65, NULL)
%lb	list of booleans	[true, false, null]	SELECT IN (%s)	SELECT IN (TRUE, FALSE, NULL)
%l?	list of different types	[true, false, 21, 'konrad', null]	SELECT IN (%s)	SELECT IN (TRUE, FALSE, 21, 'konrad', NULL)
%lc	list of identifiers	['name', 'age']	SELECT %lc FROM	SELECT "name", "age" FROM
%lu	list of column=value	['name' => 'konrad', 'age' => 21]	UPDATE SET %lu	UPDATE SET "name" = 'konrad', "age" = 21

For longer queries you could prefer using named parameter replacement:

$queryBuilder = $dbClient->newQueryBuilder('SELECT name, age FROM customers WHERE country_code = %s_country_code AND age > %i_age;', ['country_code' => 'GB', 'age' => 18], [], 'find Great Britain grownups');
$rowGenerator = $dbClient->query($queryBuilder);

Positioned and named parameters cannot be mixed in a single query

Query with constant replacements

You can do string replacement

$queryBuilder = $dbClient->newQueryBuilder('SELECT count(*) as __total FROM customers', [], ['__total' => 'total_customers'], 'count customers');
// executed query: 'SELECT count(*) as total_customers FROM customers'
$rowGenerator = $dbClient->query($queryBuilder);

Query methods

queryNative(string $query, string $comment = ''): \Generator;
queryNativeAll(string $string, string $comment = ''): array

query(QueryBuilder $queryBuilder): \Generator
queryAll(QueryBuilder $queryBuilder): array

querySingleRow(QueryBuilder $queryBuilder): QueryResultValue
QueryResultValue:
  getValue(): mixed
  notFound(): bool

// below methods throw DatabaseValueNotFoundException if value does not exist
querySingleValue(QueryBuilder $queryBuilder): mixed
queryStringValue(QueryBuilder $queryBuilder): string|null
queryIntValue(QueryBuilder $queryBuilder): int|null
queryFloatValue(QueryBuilder $queryBuilder): float|null
queryBoolValue(QueryBuilder $queryBuilder): bool|null

Transactions

$dbClient->startTransaction();
try {
  $dbClient->queryNative("INSERT INTO catalog VALUES('12-43-54','teddy-bear')");
  $dbClient->queryNative("INSERT INTO category VALUES('toys','12-43-54')");
  $dbClient->commit();
} catch (DatabaseException $e) {
  $dbClient->rollback();
}

Nested transactions are implemented with a transaction counter. Transaction start increases the counter, transaction rollback decreases the counter. If a rollback transaction happens inside a nested execution, whole execution will be marked for rollback. If you try to commit a transaction that has been marked for rollback DatabaseTransactionException will be thrown. It is important to be aware or all possible failures and handle them properly.

// this is just an example, ignore the fact that it may not make sense:)
$dbClient->startTransaction();
  $dbClient->queryNative("INSERT INTO catalog VALUES('12-43-54','teddy-bear')");
  $dbClient->queryNative("INSERT INTO category VALUES('toys','12-43-54')");
  
  $dbClient->startTransaction();
    $dbClient->queryNative("INSERT INTO message_queue VALUES('12-43-54', 'teddy_bear')");
  $dbClient->commit();
$dbClient->commit();

Development

Vagrant setup is included in the repository.
Provisioning will install PHP, Xdebug and Postgresql.
Source code is mounted to /app.

Check composer.json for useful commands. The most important ones are:

./composer codecheck // run code analysis and tests
./composer drop_test_dbs // remove all databases created during intergration tests execution

For the purpose of running queries on a database, for every integration tests suite execution, database_connector_test_XXX database is created. I wanted a fully isolated environment for every test run, without the need of using transactions.

vijoni / database-connector

Maintainers

Details