Features

Shell Commands

Note that you can use the sql, js, and py shell commands to switch the mode on the fly. This makes working with SQL and NoSQL data much easier because you don’t have to exit the application to switch modes. Furthermore, you can use these shell commands even if you used the startup option to set the mode.

Finally, note the way you exit the shell (q or quit). If you type quit as you used to in the old client, the shell will respond differently depending on the mode you’re in. The following presents an example of what happens in each mode.

You may see similar oddities if you are used to the old MySQL client and accidentally use an old client command, but it only takes a bit of regular use to remind you of the correct commands to use. Now, let’s look at the startup options for the shell.

Options

Note that there are aliases for some of the options that have the same purpose as the original client. This makes switching to the shell a bit easier if you have scripts for launching the client to perform operations. Note that there is also a set of options for using a secure socket layer (SSL) connection.

Most of these are self-explanatory and we’ve seen several of these previously. Let’s now look at the sessions and connections available and how to use them.

For a complete list of options, execute the shell with the --help option as shown in the following.

Sessions and Modes

As with the original client and indeed most MySQL client applications, you will need to connect to a MySQL server so that you can run commands. The MySQL Shell supports several ways to connect to a MySQL server and a variety of options for interacting with the server (called a session). Within a session, you can change the way the shell accepts commands (called modes) to include SQL, JavaScript, or Python commands.

Given all the different and new concepts of working with servers, those new to using the shell may find the difference subtle and even at times confusing. Indeed, the online MySQL Shell reference manual and various blogs and other reports sometimes use mode and session interchangeably, but as you will see, they are different (however subtle). The following sections clarify each of the major concepts including sessions, modes, and connections so that you can get accustomed to the new methods faster. I introduce the concepts first with some simple examples then discuss making connections in detail with examples. Let’s begin by looking at the session objects available.

Connections

Making connections in the shell is one area that may take some getting used to doing differently than the original MySQL client.¹ You can use a specially formatted URI string or connect to a server using individual options by name (like the old client). SSL connections are also supported. Connections can be made via startup options, shell commands, and in scripts. However, all connections are expected to use a password. Thus, unless you state otherwise, the shell will prompt for a password if one is not given.

Rather than discuss all the available ways to connect and all the options to do so, the following presents one example of each method of making a connection in the following sections.

Install the MySQL Shell

Installing the MySQL Shell follows the same pattern as installing the MySQL server. That is, you can simply download the installer for your platform and install it by clicking through the dialog panels. There is one exception, however. At the time of this writing, the latest release of the MySQL Shell is not part of the MySQL Windows Installer.

You can find the installation package on http://dev.mysql.com/downloads/shell/ . Just select the latest version and package for your platform (in this case, macOS) and install the shell.

When you launch the installer (.pkg or .dmg), you will be presented with a welcome dialog that contains the name and version of the product you are going to install. Figure 4-2 shows the welcome panel for the MySQL Shell installer.

Note that in Figure 4-2 I am installing a release candidate version of the MySQL Shell, namely, version 8.0.11. You should install the latest version of the shell available for your platform to ensure you have the latest features.

Once you are ready, click Continue. You will then be presented with the end-user license agreement as shown in Figure 4-3.

Once you have read the license,³ click Continue. You will be asked to accept the license as shown in Figure 4-4. Click Agree to continue.

Once you’ve accepted the license, and are okay with installing in the default location (for macOS that’s always a good idea), click Continue. You will be asked to approve the installation as shown in Figure 4-5. Click Install when ready to begin the installation.

This will begin the copy of files to the destination along with settings on your system to ensure you can launch the application correctly. Depending on the speed of your system, it should only take about 2 to 3 minutes at the most to complete.

Once the installation is complete, you will be presented with a completion dialog as shown in Figure 4-6. When ready, click Close to complete the installation. If you choose to launch the shell, you will see a new command window open and the shell will start.

Recall you can launch the shell without specifying a server and the shell will run but it is not connected to any MySQL server. You must use the connect shell command to connect to a server if you do not specify a server connection (URI or individual options) on the command line.

Now that the MySQL Shell is installed, we need to configure the X Plugin.

Setup the X Plugin

If you installed MySQL 8.0.11 or later on your system, you already have the X Plugin installed and enabled. However, some of the older installations do not setup or enable the X Plugin by default. Thus, you may need to enable the plugin to connect to your server with the shell. Although you can still use the shell to connect using a classic session object, you won’t be able to use the X Protocol session object until the X Plugin is enabled.

Furthermore, if you installed the server on Windows using the Windows Installer, you can enable the X Plugin during installation by checking the Enable X Protocol/MySQL as a Document Store checkbox. If you did not do that or are installing on a different platform, there are at least two other methods for enabling the X Plugin; you can use the new MySQL Shell or you can use the old client. The following demonstrates each option.

Installing the Sample Database

Oracle provides several sample databases for you to use in testing and developing your applications. Sample databases can be downloaded from http://dev.mysql.com/doc/index-other.html . The sample database we want to use is named world_x to indicate it contains JSON documents and is intended for testing with the X DevAPI, the shell, and so forth. Go ahead and navigate to that page and download the database.

The sample database contains several relational tables (country, city, and countrylanguage) as well as a collection (countryinfo). We will only use the relational tables in this chapter, but will see more examples working with collections in later chapters.

Once you’ve downloaded the file, uncompress it and note the location of the files. You will need that when we import it. Next, start the MySQL Shell and make a connection to your server. Use the sql shell command to switch to SQL mode then the source shell command to read the world_x.sql file and process all its statements.

Listing 4-4 shows an excerpt of the commands and the responses you should see. I highlight the commands and a row in the output to show that this world database does indeed permit storing of JSON documents in a table.

Note that the source shell command is a way to load a file and execute the commands in a batch. This is a very popular method of replaying frequently used command sequences and it does work for JavaScript and Python commands too.

You can also install the sample database using the --recreate-schema option on the command line as follows. Note that this will delete and recreate the database if it already exists. This is another example of running the SQL commands as a batch.

Of course, you could install the sample database with the old client by using the similar source command, but where’s the fun in that?

Now, let’s see our example task in SQL mode.

SQL

The task we want to do is to insert two rows into the city table adding a JSON document in each and then read data from the table only those rows that have the extra data. More specific, we are going to be adding a list of places of interest to the table so that we can ask questions later about which cities have places of interest. Think of it as a way to add your own comments about places you’ve visited in those cities that you found interesting and would recommend to others.

Because this exercise is an example, we will also see how to delete the data we added so that we return the database to its original state. It also helps to do this if you plan to follow along with these examples so that completing one doesn’t affect trying out the next.

Let’s begin with listing the databases on the server then listing the tables in the world_x database. Listing 4-5 shows a transcript of the familiar SQL commands to accomplish these steps. I omit some of the messages for brevity. Note that I started the shell in the SQL mode using the command option.

Next, let’s insert some data. We will insert two rows into the table; one for each city I’ve visited recently (Charlotte, North Carolina and Daytona, Florida). In this step, we will use the INSERT SQL command to insert data. Recall from earlier, we need to format our JSON document carefully so that we don’t encounter errors. In particular, we want to add structured data including the name, country code, and district but we also want to add a JSON document that contains the population and a list (array) of places of interest. Recall from Chapter 1, we can do this in the INSERT statement by creating the JSON document inline. The following shows each of the commands we would use to insert the rows.

Although that seems a bit messy (and it is), if you read the statements carefully, you will see the JSON document is encoded as a string. For example, a well-formatted version of the JSON document for the first insert is shown following. Clearly, that’s a lot easier to read. You could enter the statement using formatting like this, but the results will be shown without the extra formatting.

Note that we retain the population key per the other rows in the table (select some and see) and we also add an array named Places_of_interest to list those places we may want to visit.

Now, let’s see how the data looks if we use a SELECT SQL statement. In this case, we’ll just select the two rows by city name because they are unique in the table. The following is an excerpt of the results.

That’s interesting, but it doesn’t answer the question we want to ask. That is, which cities have places of interest? To do that, we need to use a number of special functions designed for the JSON data type. All of the functions begin with the name JSON_*. Let’s see each of these in turn starting with a way to search for rows that have a specific key in the JSON document. In this case, we select all of the data for rows that have places of interest.

To determine if a JSON document has a specific key, we use the JSON_CONTAINS_PATH() function. Recall a path is simply a resolution of the keys in the document. In this case, we want to know if the JSON document contains a path for Places_of_interest. Because the function returns a 0 for no match and 1 for at least one match, we check to see if it is equal to 1. You can omit the equality, but it is best to be pedantic when experimenting with new features and commands. We also use the ‘all’ option to tell the function to return all of the matches (values) as opposed to ‘one’, which returns only the first occurrence. You can also use the slightly more correct IS NOT NULL comparison.

Now, let’s say we only want to see those places of interest and not the entire JSON document. In this case, we need to use the JSON_EXTRACT() function to extract the values from the document. In particular, we want to search the info column for all values in the array Places_of_interest. Although that seems complicated, it isn’t too bad as you can see in the following.

Now, what if we wanted to only retrieve the values for the Places_of_interest array? In this case, we can use a special format of the JSON access to get these values from the array. The following demonstrates the technique. Note the portion highlighted in bold.

Okay, now that’s a lot easier to read, isn’t it? It’s also a bit messy SQL command. And if all of that seemed a bit painful, you’re right, it was. Working with JSON data in SQL works with the help of the JSON functions, but it is an extra step and can be a bit confusing in syntax. See the online MySQL reference manual for full explanations of each of the JSON_* functions.

If you’ve used the old MySQL client much to query data with wide rows, chances are you’ve used the G option to display the results in a vertical format, which makes reading the data easier. With the shell, we don’t have that option but we can display data using the --json option. Although the option is easier to read, it tends to be a bit verbose. We will see this in action in the Python section.

Finally, we can remove the rows with the DELETE SQL command as shown in the following.

Now, let’s see the same operations performed using JavaScript.

JavaScript

To execute the example task in JavaScript, we’re going to start the shell with the X Protocol session object and pass in the world_x schema to demonstrate how you can save a step. We will then use the getTables() method of the global db object (sometimes called a variable) to get the list of tables the world_x database. Listing 4-6 demonstrates these commands.

Now, let’s insert the data. Note that in Listing 4-6, the result of the db.getTables() method shows three tables. We can use the table name to reference a table object by name. For example, to access the city table, we use db.city. To insert data, we will use the db.city.insert() method as shown in the following.

Now that we have the data, let’s select the rows with the following code. Here we use the db.city.select() method along with the where() method for the TableSelect object (the object returned from db.city.select). Note that we specify the list of columns quoted and listed inside square brackets. Within that list, we can specify data in a JSON document using the column name and the special -> operator to extract a key. In this case, we want the Places_of_interest key (path) in the document stored in the Info column.

Note the column type in the result. It’s a JSON function! That means we can use a JSON function in our code to narrow the resulting column data to only the values for the Places_of_interest array as we did in the SQL example as shown in the following. How cool is that?

Now, let’s remove the rows we added to restore the data.

Okay, that wasn’t so bad. If you’re thinking it seems more programmatic than SQL and perhaps even a bit more intuitive, they you’re on the right track. Don’t worry if it seems a bit strange. The more you use scripting in the shell, the easier and more natural it will become. It is also good practice because the future of working with MySQL is the MySQL Shell and scripting languages!

Now, let’s see the same script executed as Python.

Python

Because we’ve already seen the task demonstrated twice, I skip the details of the execution of each step and show you the transcript of my Python session.

One thing you will notice right away is that once we get the tables from the db object, the code is the same as the JavaScript example except the names of the functions are spelled a bit different. This is by design. Because the db object is actually a special variable in the shell, it has the same syntax in both languages. You only see differences when you start using the X DevAPI objects, which we will see in more detail in Chapter 5.

Listing 4-7 shows the complete transcript of running the task using Python. Note that the only difference is the call for getting tables. In this case, we use db.get_tables() method in Python. It’s the same method with the same functionality only it is named differently in accordance with typical Python naming conventions.

Note how the code is similar to the JavaScript version. This makes learning the X DevAPI easier because you can use your favorite language and even when you must use another language, everything is familiar. Cool.