Download the PHP package morebec/orkestra-postgresql-document-store without Composer

PostgreSQLDocumentStore

Implementation of a Document Store using PostgreSQL's JSONB features. It is based on doctrine/dbal for accessing the database internally.

Installation

Usage

The 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.

The second parameter corresponds to the configuration of the DocumentStore. This configuration class can be used to alter the behaviour of the document store.

Inserting Documents

To insert a document in a collection:

If the collection does not exist, it will be created automatically.

Finding Documents

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:

If you are using the Filter query 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 data with type JSONB is added to every created collection table. This is why if you are doing a string query, you must specify the data column.

For even greater control, the document store exposes a getConnection method which returns the DBAL connection which you can use to make more complex queries using doctrine's Query Builder or raw connection.

Updating Documents

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:

Removing Documents

Removing a document can be done as follows:

Changing table names prefix.

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:

Transaction Management

If you need to use transactions for your operations, you can do this by accessing the DBAL connection:

Testing

To run the tests execute the following command:

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: