Category: Article

A small dive into the MySQL 8.0 X-DevAPI

A small dive into the MySQL 8.0 X-DevAPI

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:

DESCRIBE teams;

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.

Returns on a browser (data clipped):

Or on your terminal:

1__node_index_js
Extra padding on strings

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:

  1. VARCHAR fields 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.
  2. JSON does 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 possibly NULL, on the other hand if you execute the following you will have trouble:
ALTER TABLE teams MODIFY players JSON NOT NULL;
worldcup_json_problem
Error on NOT NULL for JSON columns

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:

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:

result_node
Result set for a query

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:

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 🙂

Ramblings on optimizations, anti patterns and N+1

A lot of people ask me to teach them how to do query analysis and performance. The truth is: there isn’t a script to follow. The following paragraphs are a brain dump on what usually goes on my mind when I am debugging and analyzing.

Please comment on what you think I should focus on to cover here.

TL; DR;

  • It’s just a messy post with database-y stuff
  • This post doesn’t have a conclusion, it is just me laying down my thoughts on performance and optimizations.

Thoughts

Query performance is a really difficult subject to talk about. Mostly because because SQL is a declarative language, leaving it up to the Optimizer to decide which way is the best to retrieve the information needed and that is based in so many variables.

The most common problem regarding optimization I see, comes not from the Database itself, but how we handle the requests on the application layer, the following for instance would cause N+1 problems:

Code example:

Although seemingly innocent at first, this code could easily slow down performance on the database due to the amount of requests that would be made.

You also need to know about the intricacies of indexes, which one is the best, if you have a composite index, which should go first, and what happens if I only use one of the fields of a two column indexes in my search? Does it still uses the index somehow? Another rule of thumb is that if an index is a BTREE, on a single column, you can use it either ASC​ or DESC.

Or better yet: why my transactions are taking so long to complete? Does it have too many indexes on the table? Is any other query locking table X?

Even a single ​INNER JOIN could be highly costly if joining two large tables.

Why are you saving that JSON in a TEXT​ field? Since we are on the subject, you really need the JSON in the relational database and not in a document store?

You don’t need to port all your data from PostgreSQL/MySQL to MongoDB if you want to have MongoDB on your stack. Everything has its place, relational data on relational databases and non-relational data on non relational databases. I even find unfair benchmarks between a SQL database and a NoSQL one. They were made to solve different problems, you can’t possibly have the same use case for both of them.

No, it’s not ok to have category_1, category_2, ..., category_n as columns on your products table.

Avoid as much as possible nullable fields.

Relationships should also explicitly live on the RDBMS, not only on your model, if you have a user_id​ on your addresses​ table, tell the database so, naming it user_id doesn’t automatically create the foreign key.

You need:

Or your migration should look something like this:

Line 24: adds to the table addresses​ a foreign key from users.

End

And you, what you think is missing in this blogpost? What do you want to get deeper on?

Congress, who is? – A Civic Tech project

Congress, who is? – A Civic Tech project

A while ago I had this idea for a project: To show how representatives voted, either for or against, on bills.

People elect representatives but often forget to follow what they are up to. I asked around: who is your representative? The most common response: I don’t know. If people don’t even know who their representatives are, when it comes to being listened to, how they are going to contact the House or Senate member?

That’s when Congress, who is? was born out of a 2 week project where I poured myself into and worked with the ProPublica Congress API, Twilio API and a bit of the Twitter API (those pictures must come from somewhere!).

People are able to search through their zipcode to find their representative or filter by State/Territory, Party, House or Name. Once into the member profile you can do a call directly from your browser to the member’s office.

Some images from https://www.congresswhois.com

The USA map is rendered showing a simple majority of the representation of the House. On click the listing of representatives is shown on the right.

It’s possible to also compare statistics from one politician to another. See how they vote with the party and in common between themselves.

Screenshot-2017-11-28 Congress, who is (3)

Features to come

  • Show beyond current Congress, at this moment the congress number is 115, and the API can show me members since 102-115 for House and 80-115 for Senate.
  • Show bills and votes
  • Add full text search
  • More to be defined

Code

Code will be released under MIT license. There is a few cleaning up to do, and I want to open source it with a few issues already opened and documented. As I said, the app was developed in two weeks, but it grew on me and I want to take a step further.

Stack

Backend:

  • Ruby on Rails
  • PostgreSQL

Frontend:

  • React
  • Redux
  • Semantic UI

Contributions

Right now the code is running in a “closed” beta, if you can’t wait and want to help, DM me on Twitter (no need to follow back, DMs are open on my end), or use this website contact form, or simply mail me at gabriela.io.

Thank you

I want to give a special thanks to Twilio. During this year PHPWorld they hosted a competition to showcase your project using Twilio. I showcased this project and they awarded the project with some awesome amount of credits for us to run for a while on it. So thank you for the support!

Disclaimers

Calls only works on Chrome, Firefox and Safari for Desktop. The client call doesn’t work on mobile, Internet Explorer or Opera. It’s more of a technical limitation on how each browser implement their JavaScript than application level development.

The data displayed may be incorrect. That is because it is synced daily with the ProPublica API, whatever they have on record, it is what I am showing.