Introducing the MySQL X DevAPI PHP Extension for MySQL 8.0

MySQL 8.0 is now finally GA, bringing into play the powerful Document Store set of feature along with Connectors for many of the most popular languages! Also PHP is coming with it’s own extension designed to support all of the new exciting feature coming with this latest MySQL milestone.

The complete web documentation for the MySQL X DevAPI Extension for PHP is available here.

About Document Store.

The X DevAPI for PHP is an extension which allows the user to access MySQL with installed the X Plugin as a document store via the X DevAPI and the related underlying protocol.

A document store differs substantially from a traditional relational database organization where a schema needs to be provided in order to push data into the database, a document store permit to insert information in a non-uniform manner, thus without the requirement of defining and maintaining a specific set of schema’s –and their links– needed to properly store the object being recorded.

This database model became very popular with NoSQL and other similar products, the MySQL document store X Plugin has the purpose of allowing the MySQL users to retain their current  MySQL configurations and being able to benefit from the new schema-less data organization.

Different language connectors are provided to access MySQL as Document Store, and in this post I’m going to focus on the powerful and widely used PHP language and it’s xdevapi extension which is the key use this MySQL functionality.

What does that mean in short? It means that now you can store non homogeneous data in your database without the need to define and specify meticulously the content of the tables, just open a xdevapi session and push your data into the database!

Installing the PHP extension

The easier way to install the extension is by using pecl tool or is possible to download the tarball file directly from this link. There are some dependencies to fulfill in order to use the extension, the most relevant is certainly boost and the protobufs libraries.

PHP Extension for MySQL 8.0 and Document Store.

First of all we need to create a connection to the database, in order to do so you need to access the mysql_xdevapi namespace and call the getSession function. getSession accepts as parameter the URI string with the credentials and address of the target server, in my example the URI is going to be: “mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled“, probably during your configuration of MySQL you’ve chosen a different password so please use yours instead of XXX!

The URI strings starts with the required “mysqlx” followed by your credentials and the address of the server, the port 33060 is the default one where MySQL is listening for X DevAPI connections. Also, by default the connection with the server is going to be over SSL, is possible to change this default behavior by providing the proper ssl-mode, like in the example below:

$uri = "mysqlx://root:XXX@localhost:33060?ssl-mode=disabled";
$nodeSession = mysql_xdevapi\getSession( $uri );

$nodeSession is the object which handle the session for the current connection. Let’s see how to create a schema, a collection for documents and how to add a simple document:

$schema = $nodeSession->createSchema( "testx" );
$coll = $schema->createCollection( "store" );

$result = $coll->add( '{ "product" : "iPhone X", "price":1000, "stock" : 2 }'
                      '{ "product" : "Nokia Y", "price":900, "stock" : 3,
                         "description": "A good mobile phone" }' )->execute();

In this code example the first two lines are for the purpose of creating a schema and a collection within the schema, in all the following samples I’ll use the variable $coll as a reference to the Collection obtained by createCollection

With the add you can trigger the insertion of a new document into the collection, each add has to be followed by the execute command, in the example code I’m submitting two documents with one add operation, you can add as many documents as you want with a single operation, each document have to be separated by a comma.

The same execute command is required by most of the DevAPI functions, before the execution of the request additional operations could be performed like adding more documents, manipulating the fields &c. The documents I’m inserting in the code sample are easy to understand JSON, if you are not familiar with JSON please have a look here.

We can verify the content of the database as well:

mysql> select * from;
| doc                                                                                           | _id                              |
| {"_id": "F5A1F292E55FBDEDFE8394BAF9B00B88", "price": 1000, "stock": 2, "product": "iPhone X"} | F5A1F292E55FBDEDFE8394BAF9B00B88 |
| {"_id": "F5A1F292E55FBDEDFE8394BAF9B00B89", "price": 900, "stock": 3, "product": "Nokia Y", "description": "A good mobile phone"} | F5A1F292E55FBDEDFE8394BAF9B00B89 |
2 row in set (0.00 sec) mysql>

So, from this last shell output is clear what a collection is and how a document looks like! A collection is just a table with two columns, one representing the document itself which is a JSON and the other is the unique identifier of the document –which is a varchar-! Those ID’s are generate automatically for each inserted document, you can provide your own ID’s if you want.

The $result object returned by the add operation can be used to verify what changes has been applied to the collection or to obtain a list of the ID’s generated by the server while adding the documents.

$item_count = $result->getAffectedItemsCount();
print($item_count." documents has been added to the collection, printing ID's");
$ids = $result->getGeneratedIds();
for( $i = 0 ; $i < $item_count ; $i++ ) {
    print("The document ID number ".$i." is ".$ids[$i].PHP_EOL);

Manipulating the documents

Let’s see how easily those Collections can be manipulated, for example removing documents can be done by using the straightforward removeOne API, which is a function that expect one single argument, the ID of the document to remove:

//Add some documents, note that I'm providing the IDs myself
$res = $coll->add(
	["_id" => "1", "name" => "Carlotta", "age" => 34, "job" => "Dentista"],
	["_id" => "2", "name" => "Antonello", "age" => 45, "job" => "Tassinaro"],
	["_id" => "3", "name" => "Mariangela", "age" => 32, "job" => "Attrice"],
	["_id" => "4", "name" => "Antonio", "age" => 42, "job" => "Urologo"]
//Remove the document with ID 4

Is possible to look for documents using the `find` operation, in it’s most basic implementation the `find` function will require an expression that can be used to match the document to extract from the collection:

//Find all the entries for which the 'age' field is greater than 30
$res = $coll->find("age > 30")->execute();
//Fetch the entries
$data = $res->fetchAll();
//Print the results
for( $i = 0 ; $i < count( $data ) ; $i++ ) {
    print($data[$i]["name"]." have more than 30 years!");

As last example here’s how is possible to modify the content of a document using the powerful `modify` operation. The only argument to `modify` is an expression that can be used to identify the documents that have to be modified, it’s then followed by one or more operation that define the modification:

//Fill the collection with some documents
$coll->add('{"name": "Sakila", "age": 15, "job": "Programmer"}',
           '{"name": "Sakila", "age": 17, "job": "Singer"}',
           '{"name": "Sakila", "age": 18, "job": "Student"}',
           '{"name": "Arnold", "age": 24, "job": "Plumber"}',
           '{"name": "Robert", "age": 39, "job": "Manager"}')->execute();

//This modify operation will change the 'job' to 'Unemployed' for all
//the three Sakila in the collection
$coll->modify("name like 'Sakila'")->set("job", "Unemployed")->execute();
//Add a second job to Arnold, the field 'job' will now on be an array
//of two elements: 'Plumber' and 'Nursey'
$coll->modify("name like 'Arnold'")->arrayAppend('job','Nursey')->execute();


There’s a strong feeling of excitement around MySQL 8.0 and his features, in particular Document Store is going to be a game changes in the industry by providing a powerful and flexible tool into the most popular and recognized DB.

MySQL X DevAPI 8.0.3 for PHP is here!

Dear MySQL users,

I’m glad to announce that MySQL X DevAPI extension for PHP 8.0.3 has been recently released!

If for some reason you don’t know what this is all about, then in short the MySQL X DevAPI for PHP add support for the new X DevAPI in PHP, and it’s released as an extension to the language. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models –MySQL as a Document Store– using a modern NoSQL-like syntax that does not assume previous experience writing traditional SQL.

For general documentation about how to get started using MySQL as a document store, see “Using MySQL as a Document Store”.

Download the extension.

Depending on your operating system and working environment, you can download the extensio from:

  • Direct link to the mysql_devapi extension pecl web page, here.
  • On Fedor-ish Linux system, use the Remi’s repo.
  • Clone the source code repo, here.

Further instruction on how to install the extension with samples are included in the readme script and documentation.

This new release contains many bug fixes, refactoring’s and improvements that are a step forward in our work to improve the quality of the code base and thus of the extension itself, beside the minor tasks we were able to deliver some big features:

Support for Array or Object “contains” operator:

New CONTAINS and NOT_CONTAINS operators introduced in any expression valid in CRUD operations. They are different from the IN operator, in that IN works with SQL and requires a set of literals at the right side. CONTAINS should accept JSON values at the left and right sides of the operator,including expressions that generate a JSON value.
Example (Where $coll is a Column object):

$coll->find('"Spavatore" IN jobs[*].title AND "Spavare" IN hobby')->execute();
$coll->find('("Spavatore" IN jobs[*].title OR "Mangiatore" IN jobs[*].title ) AND 12000 IN jobs[*].Salary')->execute();
$coll->find('true IN [(1>5), !(false), (true || false), (false && true)] AND _id > 5')->execute();
$coll->find('true IN [1-5/2*2 < 3-2/1*2] AND $.code > $._id')->execute();

New API’s: getOne, removeOne, replaceOne, addOrReplaceOne:

This extension is specific to Collections. These are commands that operate at a single document level, unlike the other CRUD commands that operate on all documents that match a filter the following collection commands are introduced as a set of direct-execution complementary operations that reference documented by id, as opposed to a free-form expression.

bool Collection.replaceOne(string id, Document doc)
Updates (or replaces) the document identified by id with the provided one, if it exists.

bool Collection.addOrReplaceOne(string id, Document doc)
Add the given document. If the id or any other field that has a unique index on it already exists in the collection, it will update the matching document instead.

Document Collection.getOne(string id)
Return the document with the given id. This is a shortcut for:
    Collection.find("_id = :id").bind("id", id).execute().fetchOne()

Result Collection.removeOne(string id)
Removes the document with the given id. This is a shortcut for:
    Collection.remove("_id = :id").bind("id", id).execute()

When working with small documents (majority of the time), the simplest and easiest way to change a certain document is to use a load/modify/save pattern:

doc = collection.getOne(id);
doc["address"] = "123 Sesame Street";
collection.replaceOne(id, doc);

New row locking mechanism for Crud.Find / Table.Select

The MySQL SELECT statement supports now locking matching rows, for reads and for writes (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE).

The find() CRUD method is extended to support this feature, allowing safe, transactional document updates on collections. Transaction support is an important MySQL differentiator compared to other NoSQL databases. Example:

Client A execute:

doc = collection.find("_id = 123").lockExclusive().execute().fetchOne();
doc["xxx"] = "foo";
doc["counter"] = doc["counter"] + 1;
collection.modify("_id = 123").replace(doc).execute();

Client B execute:

doc = collection.find("_id = 123").lockExclusive().execute().fetchOne();

# The document with _id = 123 is already locked by Client A, so Client B will block now.

Then Client A commits:


# The lock on _id = 123 is released, so Client B can now continue

And finally Client B can continue:

doc["xxx"] = "bla";
doc["yyy"] = "bar";
doc["counter"] = doc["counter"] + 1;
collection.modify("_id = 123").replace(doc).execute();

Support for the new MySQL Server 8.x, including fixes for phpize build.

Large refactorings and fixes where made by the team to allow this PHP extension to fully support he new MySQL server 8.x, also the problems with the phpize build are now solved and we’re happy to deliver a rock solid release with plenty of support for the new MySQL’s.

Thanks for reading,

On behalf of the MySQL/Oracle X DevAPI for PHP Team, Filip Janiszewski