 where you are in the world. This is a presentation on extending the MySQL shell with JavaScript. This is a presentation for OpenJSworld 2021. My name is Dave Stokes. I have the honor of being a MySQL community manager for Oracle Corporation. I've been on the MySQL community team for just over a decade. Before that one of my jobs was the head of the certification department and I'm going to show you how to extend the new shell that we have with JavaScript. But first, since I work for Oracle, I have to post a safe harbor statement. Everything I'm talking about today is in the free open source version of MySQL. We're talking about the MySQL server, the MySQL shell, the connectors, router, all other pieces of software. There isn't an enterprise version of all those products that you have to buy a subscription to use and to get the license for. And I am speaking about the free version, the community version, because that's my job. Now during Q&A or some other time I might go off in a tangent on stuff that has not been released yet, new products. And unfortunately, I don't have perfect knowledge of those products. So take anything I say or may say about future products with a grain of salt. I might be thinking of the color blue and you're thinking royal blue and it is a big blue cheese. Anyway, about me, live in Texas. I work for Oracle Corporation. I have a blog at elephantdolphinit.blogspot.com. Email david.stokes.oracle.com and on Twitter I'm at Stoker. The slides for this event will be up at slideshare.net slash Dave Stokes. Looking for open JS 2021 MySQL shell and that should bring it down for you. Let me turn off the webcam to save some screen space. MySQL SH, which is our new shell. If you've used our old shell in the past, it was very much an old style command line interface. The new shell is a brand new product that came out three years ago with the launch of MySQL 8 and has a lot of very interesting features. The main use for it is to process code written in either JavaScript, Python, and SQL. And you'll see that does all those very well. Now, any of the executed code is processed in any of these languages. So you can write something in Python, have it run in an SQL or JavaScript and run in Python or however. It's all basically based on the current language that you're currently active with. Well, how do you tell that? Well, here's where I fired up MySQL SH and you'll see the prompt there in yellow, the JS for JavaScript. That's why I'm here at OpenJavaJS 2021. Now, if you want to work directly with Python, you type in slash py. And if you're an old database person like myself and want to use structured query language slash SQL. By the way, something to point out, rather than our monochromatic shell where you now have colors and the command line prompt is all customizable. In this case here, I typed in a backslash s to get the status and you'll see that I'm in SQL mode. And if you look a little bit deeper, you'll notice that we are using SSL. We're using TLS AES 256 encryption by default. When you set up your MySQL server, that is enabled for you. We wanted to have MySQL 80B secure out of the box. Now, if you want to turn that off because your environment where everything is air-gapped from the outside world or you have some other way of securing stuff, you can turn it off by default. We turn it on. By the way, if you notice at the bottom of the screen, second line up, we can compress your data as it goes back and forth between the server and your client here in the MySQL shell. Also, I like to point out with MySQL 8, the default character set for the server and the client is UTF-8M before. A big part of that is because of the JSON support that we have, JSON by default is all UTF-M before. Also, it lets you use emojis wherever you want. So let's carry on. Now, with the new shell, there are two types of sessions that you can have. We have the classic session, which is what you probably are used to from MySQL 5.7 all back to the 3.2.1 days, which listens on Port 3306. Also, four years ago or three years ago when MySQL 8 came out, we had the X protocol, which we call the XDEV API, which speaks both structured query language and no SQL. By default, it listens on Port 33060. And this is a nice little diagram that shows kind of our worldview. We're at the top level. You have applications talking through connectors. Or you can use scripts running through the MySQL shell. And they can talk to standard protocol or the X protocol. And on the server, you can have relational tables and you can have no SQL JSON document stores. And you can use them interchangeably. So in the no SQL side, I can actually reach relational tables. And the relational table side, I can actually go out and get information from JSON document collections. Now, you can use the functions that are available in JavaScript and Python mode to create multiple sessions with multiple objects during all sorts of different stuff. These session objects let you run concurrent sessions. You can have them interact with each other. Or you have different things going at different times. So you have one running report in one and some quick and dirty stuff running another. All from one instance of the MySQL shell. Now default when you log in, you fire up MySQL SH. And the underlined here in red, we have backslash C and then the account and the hostname. And this example here, the password has been cached and saved. And it creates a session to root at localhost. And in this case, it's going to run on the new X protocol. And it's ready to go. And you see down here that we're on the bottom line here, that MySQL is running on the localhost. This thing on port 33060, SSL is enabled and we're in JavaScript mode. Well, if you want to run multiple sessions, what you can do is what you see here. You fire up MySQL shell. You set up a variable. Variable session one equals MySQL dot get classic session. That's the port 3306. Give the parameters to log in. And suddenly we have session one. If I want to set up multiple sessions, I can have bar session two, bar session X, and address them by the session dot one object. By the way, if you want encryption, as I said earlier, it's turned on by default. Let's say you're in an environment where you don't have it turned on by default. You can still enable it. All you have to do is when you create your session, specify the MySQL X protocol, and give all the parameters host root password, and then the pass to the various files for encryption. Compression is very much the same way. You can say that it's required, what algorithms you want to use, and the level of compression. By the way, as mentioned earlier, the first time you log in using the MySQL shell, it will ask you for that account if you want to save the password. It gives you the options of yes, no, never, and the default is no. This is stored in a hash, not human readable, and it becomes very handy for those of you who don't like typing passwords. Also used in MySQL shell from the command line. So if you have a JavaScript program that you want to feed into MySQL shell that has all the authentication parameters set in there, all you have to do is type in MySQL shell and use the less than sign from your JavaScript file, and away it goes. Now, by default, your files that you're going to write for the server, if you want to share them with other folks, are going to be stored under user, share, MySQL SH, modules, and you can have JavaScript and Python modules. In this example here, a quick little one called Dave.js has two little quick functions. You can also change your path so if you have something off your root directory, you can specify that there too. By the way, if you want to use the XDEV API for using MySQL, it's a NoSQL JSON document store. This is an example written in JavaScript. We create a variable called MySession, which says we're going to connect with the X protocol with the username, password, and machine name. And then from there, we're going to go out and get the schema. Now in the MySQL world, we use schema and database interchangeably. That's just the way we're doing it and that's how we're going to do it. So we're saying for our session, we're going to go out and get the world underscore X database or schema. And in that database, there's a document collection of JSON documents called country info. We're going to go out there and find everything in that collection. Then we're going to execute that. And from there, we do a fetch one to read all the records. Now, if you notice this is going out and pulling back data, not using SQL. Very JavaScript-like, very modern design-like. You're not finding SQL syntax. So if you love databases but hate SQL syntax, this is another option for you. By the way, when you run the script and you get the proper information, it goes out. I believe it returns 237 different documents with the various information. Now the Rolex schema and the World schema are two schemas that we have out on the MySQL.com website that we use for documentation and illustrative purposes, such as documentation examples. And that's free to download if you want to play with it. Also, if you're in JavaScript mode and you decide that you just have to run an SQL query, but I want to change modes, I have to do is say, okay, for this session, the current session I'm on, I want to run an SQL commune, very cleverly called run SQL function, and then just plug in your structured query language. And in this example, we're going out to the MySQL schema and getting the user table and getting a list of users and the machines they're allowed from. Very, very handy. So you don't like this normal output. You can get, as an option, from the horizontal output, either JSON or vertical. I have to do the shell.options.set result format JSON. And then when you run the same command, this time it's limited to, you get the same information in a JSON format. Now, if you want to make all your output forever and ever JSON, you change the set to a set capital P persist. And that would save that out there for you. By the way, as I mentioned, you don't have to use structured query language. This is an example where, say, we don't know what our data is going to look like, we want to use a database, but we don't have time to set up the structures, the relationships, all the tables, the indexes and all that. What we can do is create a schema. In this case, it's called demo. And then within that schema, create a document collection. In this case, we're going to cleverly call it OpenJS. I'm going to plug the show you're talking about. And then we say DB. And DB is an object that points to the current schema. OpenJS, the name of the collection we just created, add. And then we put in a valid JSON document. Yes, it has to be valid JSON or the server will reject it. There's some checking there to make sure you're doing the right thing. And once the data is there, we can do DB, OpenJS.Find. This tells us all the records. I want to skip over the underscore ID for this talk. It has some important information there. But it's primarily to be a primary key in the database. As you know, our primary storage engine is something called NODB, which would love for you to have primary keys on everything. It sorts everything into a B tree. And this is the way we do it. But you can ignore it for now. By the way, if you want to specify your own value for underscore ID, you can do that too. Now, I think I showed this a little bit earlier. You can have your own script. So if you have a library that you like or a script that you've written that does certain things for you with your data, very easy to include it. And in this case, we're logged in and create a variable called dave that requires the dave.js file. And if I just type dave period and then hit tab, it'll show me my options are hello and my SQL, as you can see, hello and my SQL. And if I type dave.hello, it prints out hello there, like you'd expect. And the same thing for dave.mysql. So it's very easy that if you have libraries for statistics, analytics, machine learning or whatever, very easy to include them with your MySQL shell. Now, the neat thing is the shell is extensible. You can define what you want to do besides the base function under the shell and write it yourself in either JavaScript or Python. And if you want to show these other people, you can. You don't have to. And this is a very precise way of tuning the shell to exactly what you need. Now, you can persist these reports and objects in what we call a plugin that are loaded automatically when your shell starts. Now, to register a report, it's very simple. You write your code in either JavaScript or Python. And the reporting facility handles built-in reports very easily that's used in the same API for both languages. There's color a little differently. I'll put that a little bit later. Now, reports can specify a list of report specific options. So like you want a minus V for verbose or something similar that you can define all those you want and have that work. And you can also have reports that don't need any qualifiers on the command line. And you can also put in a help statement in there. The new Moscow shell, one of these great features besides command completion is the help facility. It really lets you drill down. And if you forget syntax on something, you can hit tab and it will prompt you for the closest available options. Now registering shell reports is very easy. You'll notice the difference is that when you register a report for JavaScript, it's register uppercase R report. Now in Python, it's all lowercase with a underscore between them. So if you write something in Python that you want to call from JavaScript, you'll probably follow the same rules. So here's an example in Python. We're defining a function called session. And this session, we go out to the SIS schema, which is our schema that has metadata about your server. And we're going out there and from a table, instead of a JSON document collection, we're going out and getting these columns and doing some processing with them. And then we return a report. Now we have this function here, shell register report, we say, okay, we're going to have a report called sessions. It's going to output a list. And this is the pointer to the function up here. And this is to help information out here with the brief and the details. And when you run that, it's rather exciting. You can use backslash watch sessions, which is the name of our function here. In this case, we're running it every two seconds. So if you want to watch something run, some command that you have, it's very handy to do that. Now there are some built in functions already within my SQL shell. There's watch query. This is an interval of a half section, second, where we're going to show global status like COM wildcard. Other thing you do is show threads, which will go out there and check what's going on in your system. And this is the example slightly different. This is for particular ID. This is in the Python mode, not any difference right there. Now, the MySQL shell comes with a lot of utilities built in. And believe it or not, you can actually extend these if you want to. The first one you'll probably run into, if you're running MySQL 5.7, is usual check for server upgrade. This will go out there and take a look at your 5.7 instance and see if there's anything that you need to change before you upgrade to version 8. Very, very handy, highly recommended. If you're doing things like forcing the use of the old temporary storage engine, we have a new temporary engine that's much, much faster. If you're using something that wasn't a re-reserved word but is now reserved word, it's there. I'll warn you about problems with character sets and collations because in MySQL 8 by default, everything is UTF-8 and before you might be using something else, there's always other little checks out there that will run for you. Very, very handy. Another nice utility we have out there allows you to import JSON documents, is import JSON. And in this case, you tell it where the file is and then the schema you want to put it in and what document collection you want to drop in that information and it will pull in all that data for you. By the way, it knows how to handle BISONs if you're coming from a Mongo background, you can either ignore or inculcate that into your data. Also, there's a very nice import table function called import table, creatively enough. And once again, you tell it where the file is, the schema you want to put that stuff in there, the table that you're putting in there. Now, this is not important in JSON, it's actually importing a table. The dialect, there are actually several dialects of comma-separated variables. DOS is one. You can skip over the first row if that's all headers and actually draw you a progress chart, so progress. So if you want to run something and keep an eye on it, it's a load, it's a huge file, you can actually use that little bar that draws across your screen. We also have some other interesting utilities. First I want to mention is the parallel table import utility. This works in parallel, I believe up to eight threads. On this example, once again, we give the name of the file, the schema or database we're putting in there, the name of the table, the dialect and show progress. And what's fun to use that with is the export table utility. So this will export in parallel and then you can read it in in parallel. Things like MySQL dump are single threaded, so if you've been doing backups with those, you're going to love this. Now, even better than those, and these are less than six months old, I believe, are the next couple of utilities I'm going to show you here. Dump instance, which takes the entire server that you have there and will write it out there. Now, in this example, we're just doing a dry run for time, as you can see how it works. And if you're on the Oracle cloud, there's some IDs, information, stuff that it will do for you that's very handy. Now, here's an example where we're going to use the dump tables utility. And in this case, we're going out to the schema called HR. And in that, we're going to take the tables employees and salary, and we're going to throw that in an object called AMP. Now, if you don't want if you want to do better than tables, there's dump schemas, where you can actually name the various schemas that you're going to put out there. So you have instance for everything schemas for specific databases. In this example, we're going to have one called world. And if you're using the MySQL cloud again, there's buckets that you can set up and compatibility modes that you put out there. And also, once you do a dump from either dump instance or done schema, you can use load dump. And what you do is you tell it where the data has been dumped to loads it back in there very, very fast. If you've been doing your backups with MySQL dump, you might want to take a look at this. So where do you go if you want to learn more about extending the MySQL shell? Well, the first thing I recommend is the MySQL shell manual itself. This is grown from a fairly simple booklet to a commanding tome all of its own. It has a lot of great examples and explains things in great detail, very easily, well written, easy to read. Now, if you're looking for a quick how to who I write a plugin, the second thing from develop paper.com, I thought was very well. Now, if you're looking for example plugins, my counterpart from Mia Le Fred has a whole bunch of wonderful examples out there. They're mostly in Python, but they'll give you the idea of what's going on there. And if you go out to slideshow.net, this is his presentation. And from there, he'll have a link to his GitHub account. And from there, you can actually download, I think it's like a dozen or dozen and a half, wonderful examples of how to do various things of various complexity from fairly simple to very, very, very complex with the MySQL shell. And Fred believes that the best tool for DVA to admin a server is the MySQL shell. I haven't gone into some of the tools on that for setting up clusters and cloning servers and all that, but it has a lot of other uses. And with that, let me earn my pay by saying if you want to test drive the MySQL database service, which we call MDS for free, you can get $300 in credits. And if you stay on the lower end platforms, that will last you quite a long time. This is the full enterprise version of MySQL, by the way. Very, very good deal there. I encourage you to try that. Also, please follow us on social media. We're at MySQL.com. On Twitter, we're MySQL, Facebook, we're MySQL. LinkedIn.com, we have over 20,000 people as part of our MySQL group. And of course, MySQL community.slack, you're now part of the MySQL community. Please join us there. And by the way, if you are in a startup, Oracle would like to help you out. They offer, or we offer great discounts, global exposure, marketing help, promotions, and access to Oracle's over 400,000 customers. If you are a startup, I highly recommend going to Oracle.com. And signing up. I am one of the startup ambassadors. You have more questions about that, please contact me. By the way, if you're interested in the JSON data type and MySQL, I've written a book now in second editions called MySQL and JSON, a Practical Programming Guide. The manual is wonderful for the JSON functions and JSON data type. I just wanted to write something more cohesive and easier to follow. Reference manuals are for reference, they're not for learning. And this is a book designed to teach you how to exploit the JSON data type and the surrounding facilities that MySQL gives you with the MySQL server to your advantage as quickly as possible. And with that, it's on to Q&A. Hopefully during this presentation, which is pre-recorded, I've been answering your questions. But if you have been waiting to ask me a question, this is your time. And once again, go out to slideshow.net slash Dave Stokes. You will find the link to these slides, look for OpenJS 2021 MySQL Shell, and you should be allowed to write to it. And please ask me any questions you have. Now if you're shy, there's my email. And if you're semi-shy, there's my Twitter handle. And with that, I would like to thank you for joining me today. And thank you for participating in OpenJS 2021.