Introduction
What is the X-DevApi? From insidemysql.com there is a definition of the X-DevAPI and its features in the following paragraphs:
The X DevAPI is the common client-side API used by all connectors to abstract the details of the X Protocol. It specifies the common set of CRUD-style functions/methods used by all the official connectors to work with both document store collections and relational tables, a common expression language to establish query properties such as criteria, projections, aliases, and a standard set of additional database management features for handling things like transactions, indexes, etc.
The fact that most of these features share the same format and API between connectors, makes the X DevAPI a perfect fit for modern polyglot development environments such as microservices, and the fact that they are based on a well-documented format allows advanced users to extend client-side implementations and build new middleware components or extensions tailor-made for their use case.
With MySQL 8.0, the X-DevAPI can be used either with the MySQL Shell, or with the MySQL Connectors that supports the X-Protocol. For this blogpost I will be showing you code using the Node.js driver as interface with it.
As of the moment of writing of this post, the latest version for the driver (available on npm) is the 8.0.11.
Hands-on, the relational way
You can use the X-DevAPI to use it in a more “relational” way, consider a table teams from the worldcup schema:
[code lang=sql]
DESCRIBE teams;
[/code]
Description:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | NULL | ||
| players | json | YES | NULL |
Notice that the field players is a JSON and it can be NULL.
| const mysqlx = require('@mysql/xdevapi'); | |
| const options = require('./config') | |
| mysqlx.getSession(options) | |
| .then((session) => { | |
| const db = session.getSchema("worldcup"); | |
| const tableTeams = db.getTable("teams"); | |
| return tableTeams; | |
| }) | |
| .then((t) => { | |
| t.select(['id', 'name', 'players']) | |
| .where('name = :name') | |
| .bind('name', 'Brazil') | |
| .limit(1) | |
| .execute(console.log); | |
| }) | |
| .catch(err => { | |
| console.log(err.stack); | |
| process.exit(1); | |
| }); |
Returns on a browser (data clipped):
| [ | |
| 4, | |
| "Brazil ", | |
| { | |
| "players": [{ | |
| "id": 70, | |
| "dob": "05.03.1993", | |
| "club": "FC Shakhtar Donetsk (UKR)", | |
| "name": "FRED", | |
| "height": 169, | |
| "weight": 64, | |
| "position": "MF", | |
| "shirt_name": "FRED", | |
| "shirt_number": 18 | |
| }, | |
| { | |
| "id": 71, | |
| "dob": "13.01.1988", | |
| "club": "FC Shakhtar Donetsk (UKR)", | |
| "name": "TAISON", | |
| "height": 172, | |
| "weight": 64, | |
| "position": "FW", | |
| "shirt_name": "TAISON", | |
| "shirt_number": 21 | |
| }] | |
| } | |
| ] |
Or on your terminal:
This looks handy, specially if you are working as a document store (which I will talk in a bit), however there are some issues with JSON and VARCHAR fields for now:
VARCHARfields are getting padded. Take a look at the third line, where it is “Brazil” actually has trailing spaces. Notice on the terminal is how far off the closing quote is.JSONdoes work, but a bit in a limited way, in this case, it is only bringing the result set because the column is set to be possiblyNULL, on the other hand if you execute the following you will have trouble:
[code lang=sql]
ALTER TABLE teams MODIFY players JSON NOT NULL;
[/code]
See how the error is in a string parser that tries to concatenate whatever to it. On my tests usually is empty space, I had zeroes padded on the right too (yes zero on a string), problem 1 looks like is causing problem 2.
They are aware of it, and apparently it will be fixed on a future release.
Hands-on, the document store way
MySQL 8.0 has been marketed extensively as an alternative for NoSQL, although you can actually use JSON with it, there are some caveats to the feature.
I restructured data to be inside a collection instead of a table.
To query it now, you do the following:
| const mysqlx = require('@mysql/xdevapi'); | |
| const options = require('./config') | |
| mysqlx.getSession(options) | |
| .then((session) => { | |
| const db = session.getSchema("worldcup"); | |
| let teamsCollection = db.getCollection("teams_2018"); | |
| return teamsCollection.find("name = :country1 OR name = :country2") | |
| .bind("country1", "Brazil") | |
| .bind("country2", "England") | |
| .execute(console.log) | |
| }) | |
| .catch(err => { | |
| console.log(err.stack); | |
| process.exit(1); | |
| }); |
Something feels wrong to me with this approach of having to write strings instead of dealing with JavaScript objects inside the .find() method, using the “literal” word for OR and AND. I think there is room for improvement here. At this point I am sorry to say this looks more like as an hasty ORM put together than a real document store interface.
By the way, problem 1 and problem 2 don’t happen here:
Should I use it now?
I would recommend to wait a bit more. The current version is the first General Availability version, which means next one will have bug fixes and more stability added to it. This definitely is a nifty way for those using Node.js to be able to manipulate data inside MySQL more “natively”, with that in mind, I still feel that I am actually writing SQL to be able to use it the “document store” way.
Bonus
You can actually use the X-DevAPI on Google Cloud Functions.
You need to add the @mysql/xdevapi to package.json and put the code above inside a “wrapper” that looks like this:
| /** | |
| * Responds to any HTTP request that can provide a "message" field in the body. | |
| * | |
| * @param {!Object} req Cloud Function request context. | |
| * @param {!Object} res Cloud Function response context. | |
| */ | |
| const options = { | |
| host: "your ip address", | |
| port: 33060, | |
| password: "your password", | |
| user: "your username" | |
| }; | |
| const mysqlx = require('@mysql/xdevapi'); | |
| exports.getTeam = (req, res) => { | |
| console.log(req.query) | |
| if (req.query.name === undefined) { | |
| res.status(400).send('No name defined!'); | |
| } else { | |
| const name = req.query.name; | |
| /** your code goes here **/ | |
| mysqlx.getSession(options) | |
| .then((session) => { | |
| const db = session.getSchema("worldcup"); | |
| let teamsCollection = db.getCollection("teams_2018"); | |
| return teamsCollection.find("name = :country") | |
| .limit(1) | |
| .bind("country", req.query.name) | |
| .execute(row => { | |
| res.status(200).send(row); | |
| }) | |
| }) | |
| .catch(err => { | |
| console.log(err.stack); | |
| process.exit(1); | |
| }) | |
| } | |
| }; |
Keep in mind that .execute() doesn’t return a promise, but rather receives a callback function to do your data processing of each individual row. This could be a bit annoying for you.
Image header of the post belongs to Pexel. And yes, I know the code shown is not SQL or JavaScript 🙂
One thought on “A small dive into the MySQL 8.0 X-DevAPI”