Implementation of a Document Store using PostgreSQL's JSONB features.
It is based on doctrine/dbal for accessing the database internally.
composer require morebec/orkestra-postgresql-document-storeThe Document Store uses doctrine/dbal for accessing the database.
Therefore, it requires a DBAL Connection as a constructor dependency.
It also relies on a ClockInterface from the morebec/orkestra-date-timecomponent in order to access
the current date time.
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;
use Morebec\Orkestra\DateTime\SystemClock;
use Morebec\Orkestra\PostgreSqlDocumentStore\PostgreSqlDocumentStore;
use Morebec\Orkestra\PostgreSqlDocumentStore\PostgreSqlDocumentStoreConfiguration;
$connection = DriverManager::getConnection([
'url' => '...'
], new Configuration());
$config = new PostgreSqlDocumentStoreConfiguration();
$clock = new SystemClock();
$store = new PostgreSqlDocumentStore($connection, $config, $clock);The second parameter corresponds to the configuration of the DocumentStore. This configuration class can be used to alter the behaviour of the document store.
To insert a document in a collection:
/** @var Morebec\Orkestra\PostgreSqlDocumentStore\PostgreSqlDocumentStore $store */
$store->insertDocument('users', 'usr123456789', [
'id' => 'usr123456789',
'username' => 'jane.doe',
'fullname' => 'Jane Doe',
'emailAddress' => '[email protected]'
]);If the collection does not exist, it will be created automatically.
Finding elements can be performed using the findOneDocument and findManyDocuments methods of the
document store. These methods accept either a string representing a PostgreSQL json query or a Filter
which is a simple API for a query builder with the document store:
use Morebec\Orkestra\PostgreSqlDocumentStore\Filter\Filter;
use Morebec\Orkestra\PostgreSqlDocumentStore\Filter\FilterOperator;
$store->insertDocument('users', 'usr123456789', [
'id' => 'usr123456789',
'username' => 'jane.doe',
'fullname' => 'Jane Doe',
'emailAddress' => '[email protected]',
'preferredLanguage' => 'ENGLISH'
]);
// Finds a document by its ID.
$store->findOneDocument('users', Filter::findById('usr123456789'));
// Finds a document by a single field:
$store->findOneDocument('users', Filter::findByField('username', FilterOperator::EQUAL(), 'jane.doe'));
// Finds a document by a multiple criteria
$store->findOneDocument('users',
Filter::where('username', FilterOperator::EQUAL(), 'jane.doe')
->or('preferredLanguage', FilterOperator::IS_NOT(), null)
);
// You can also use strings to have greater control over the query:
$store->findOneDocument('users', 'data->>fullname = \'Jane Doe\'');If you are using the
Filterquery builder, the values are automatically escaped using prepared statements placeholders. However if you are using a string for a query, the values will not be escaped, and you must make sure that you are not introducing potential loopholes for SQL Injections.
Internally a column
datawith typeJSONBis added to every created collection table. This is why if you are doing a string query, you must specify thedatacolumn.
For even greater control, the document store exposes a
getConnectionmethod which returns theDBALconnection which you can use to make more complex queries using doctrine's Query Builder or raw connection.
To update a document, use the updateDocument method.
This method does not support partial documents, and therefore overwrites the document in the store
with the provided one:
use Morebec\Orkestra\PostgreSqlDocumentStore\PostgreSqlDocumentStore;
/** @var $store PostgreSqlDocumentStore **/
$store->updateDocument('users', 'usr123456789', [
'id' => 'usr123456789',
'username' => 'jane.doe',
'fullname' => 'Jane A. Doe',
'emailAddress' => '[email protected]',
'preferredLanguage' => 'FRENCH'
]);Removing a document can be done as follows:
use Morebec\Orkestra\PostgreSqlDocumentStore\PostgreSqlDocumentStore;
/** @var $store PostgreSqlDocumentStore **/
$store->removeDocument('users', 'usr123456789');In order to have better control over the collection tables it manages, the document store adds a prefix to any table that it creates.
This prefix can be configured in the document store configuration:
use Morebec\Orkestra\PostgreSqlDocumentStore\PostgreSqlDocumentStoreConfiguration;
$config = new PostgreSqlDocumentStoreConfiguration();
$config->collectionPrefix = 'you_prefix_';If you need to use transactions for your operations, you can do this by accessing the DBAL connection:
$connection = $store->getConnection();
$connection->transactional(static function() use ($store) {
$store->insertDocument('users', 'usr123456789', [
'id' => 'usr123456789',
'username' => 'jane.doe',
'fullname' => 'Jane Doe',
'emailAddress' => '[email protected]',
'preferredLanguage' => 'ENGLISH'
]);
$store->insertDocument('users', 'usrABCDEFGHI', [
'id' => 'usrABCDEFGHI',
'username' => 'john.doe',
'fullname' => 'John Doe',
'emailAddress' => '[email protected]',
'preferredLanguage' => 'SPANISH'
]);
});To run the tests execute the following command:
vendor/bin/phpunit tests/It is required to have an instance of postgresql running with a password-less role postgres and a database named postgres.
To easily get this setup and running a docker-compose configuration file is available at the root of this project.
To run it simply execute the following command:
docker-compose up -d
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent