The classic MySQL command line tool is how most users – developers and administrators – interact with a MySQL server. From administrative tasks to trying out queries, it wouldn’t be far-fetched to call it the face of MySQL.

With a new programming interface built into the MySQL Server, we need to properly support it at the client side in connectors, tools and of course, the command line client.

The original client  has some issues, like its tight coupling to the server code base or the fact it wasn’t really designed to be extensible, much less supporting a new protocol or a new API. And thus, the new MySQL Shell was born, built on a new platform.

The MySQL Shell

As seen in a previous post, the X DevAPI provides an alternative CRUD API for using MySQL as a JSON Document Store, without going through the SQL language. The X DevAPI will be available in drivers for many popular languages, but MySQL Shell provides an interactive interpreter that lets you try out DevAPI code with similar convenience you get for SQL from the classic MySQL client.

Here’s a quick example, where we create a collection, add a couple of documents and then query one of them back.

The shell session above is in JavaScript. MySQL Shell embeds a full JavaScript engine, so you can write complex scripts, prototype code and do pretty much anything that you can with the core JavaScript language.

Python support is also built into the Shell. The MySQL Shell has a runtime that allows it to expose common functions to JavaScript and Python at the same time; so the same DevAPI functionality is also usable from both languages. And because of its rich standard module library, it may be more suitable if you want to write scripts that interact with the operating system (e.g.: reading files, parsing CSV data etc).

Note that the implementation of the X DevAPI in the shell is not the same as that from the full-fledged Connectors. So while the API is essentially the same, there might be some differences. For example, Connector/Node.js is also written for JavaScript, but because of the asynchronous nature of Node.js and its heavy use of Promises, methods that trigger a request to be sent to the MySQL server will look a little different.

We want the MySQL Shell to be the primary frontend for most future MySQL functionality, acting as a common gateway to MySQL development and administrative tasks. It is designed to be embedded in GUI frontends like MySQL Workbench and MySQL for VisualStudio plugin, so the same scripting and DevAPI functionality is also available for those who prefer GUIs.

X DevAPI in the Shell

Note, if you haven’t read Part 3 of this blog series yet, it is strongly recommend that you do so first, so you understand what this is about.

In the Shell, the X DevAPI is available in JavaScript and Python with minimal differences. The Shell adds a few features in addition to the plain X DevAPI, designed to make its interactive use more convenient. These additional features are not present in the full Connectors meant to be used for Application development.

• When you start mysqlsh and specify connection parameters, the shell will open a new connection and assign it to the session global variable in it.
• If you provide a default schema when starting mysqlsh, the db global variable is set to that schema.
• The db global variable has direct shortcuts for tables and collections it contains. For example:
• 1 2	mysql-js> db.shopping_list <Collection:shopping_list>

Whenever you input some code in the shell, it will be evaluated and if it results in a value or object, it will be automatically printed. Certain object have special behavior attached to them, meant to make things more convenient during interactive use:

• If your code snippet evaluates to a CRUD statement object (like those created by fluent API methods like collection.find(), or collection.add()), the execute() method is automatically called. So, while in an application or in a script you would need to type the full form of the statement, like db.shopping_list.find().execute();, it is enough to type db.shopping_list.find()<return> in the shell, to have it sent to and executed in the MySQL server.
• If the code evaluates to a Result object, which is returned by the execute() method, the full result set will be automatically fetched and printed. Normally, if you’re in a script, application code or just want to get a reference to the Result object, so that you can use its contents in your own code, you would have to “capture” it, so that it doesn’t fall-through to the shell interpreter. In JavaScript, that means you have to prefix the assignment with the var keyword:

• 1	mysql-js> var results = db.shopping_list.find().execute()

Here’s another example, where we populate a collection with a bunch of test documents in a loop:

Session Management

As explained in the DevAPI post, there are two types of sessions: stateless XSessions and NodeSessions, which allow raw SQL execution. By default, the Shell connects into a XSession. But if you want to use SQL, you may want to start the shell using the --node option; or use the shortcut --sql, which in addition to that, switches to SQL input mode.

You can, btw, switch between input languages using \sql, \js or \py.

The shell supports the URI style syntax for specifying MySQL connection parameters, which may be more convenient for copy/pasting around:
mysqlsh --uri root@192.168.1.45:3307

To make working with multiple MySQL servers easier, the Shell has a built-in registry of MySQL server connections. You can save connections and refer to them later by name:

Classic MySQL Compatibility

If you want to use the MySQL Shell with older versions of MySQL that do not include Document Store support, you can use the --classic command line option, which will connect to MySQL using the classic MySQL protocol (port 3306).

Differences in Classic MySQL sessions:

• The syntax for running queries in JavaScript or Python is a little different, since the session type you get is not XSession nor a NodeSession:
  mysql-js> session.runSql("select * from mytable");
  In SQL input mode, there shell will look very similar to the old command line client.
• There is no CRUD API support, only SQL.

Shell and X DevAPI Tutorial

If you want to try it out, the JavaScript MySQL Shell Tutorial and the Python MySQL Shell Tutorial will guide you through the steps to start using it, through a more in-depth tutorial.

You can also explore what you can do in the shell, by using the \help command and the –help command line option.