If you’re still wondering what MyCli actually is, then look no further. This brilliant piece of technology brings a boosted terminal for both MySQL and MariaDB activities. Still not sold? Well, it has Auto-completion and syntax highlighting!
Although not actually tested on Windows operating systems yet, I decided to take it for a spin. In this article, I will explain how to get set up and start to use its magic.
What should be noted first and foremost is that there currently is no Windows installer for this terminal. And therefore requires Python to run it, so there is a little of bit of leg work to get it installed.
Firstly the most obvious one, your local computer must already have MySQL or MariaDB installed. Hopefully with a database, some tables and data too.
Secondly, if you have Python installed on your computer and are experienced with using it, then all you gotta do is run a simple pip command like the following
pip install -U mycli. But if not, there much more to do before we can actually install the package!
If you’re not on Windows and still want to follow on, there are install instructions for Linux & Mac on the GitHub repository.
Let’s get started.
Installing MyCli on Windows
This part of the article will be divided into a few steps and sub steps. There will be one download involved and a few setting adjustments within your computers properties. See the following steps to setup python & pip (pythons package manager) and finally install mycli.
- Go to Python’s Windows download page
- In the list, find the executable installer that is suitable for your local machine e.g. x64, x86
- Wait for the download to complete and run the .exe installer.
- Install Python, but for easiness, modify the install location to something like
C:\Python37-32. This isn’t a must but personally, it makes accessing it easier.
- After the install is complete, it’s time to set up some environment variables for shortcut access to Python & pip via the command prompt.
- Go to Control Panel, then System, then Advanced System Properties.
- Click the Advanced tab and click the Environment Variable button.
- Add the following two values to your PATH variable (Or replace with the path you installed the Python files)
- Click the Ok button until all is closed, now both the python.exe and pip.exe executable will be accessible.
- Open the command prompt and simply type
python. See Figure 1 for an example of what should be present after the command. If it’s not similar to Figure 1, then use the following in-depth link to install python on windows again.
- Now we know Python is installed, we need to run the pip command to install the mycli package. Let’s move on to Step 2 below.
- Close down all command prompts and re-open a fresh one.
- Run the following command –
pip install -U mycli
- If you get the error of not pip is not a recognized command or similar, then please see Step 1, sub-step h.
- If you get an error of ‘Unknown or unsupported command install’, then it’s probably something to do with other pip instances on your machine (It’s actually included in an XAMPP build). We can get around this by directly referencing pip like the following sub-steps.
pip install -U mycli
- Now, pip should of installed the package, and you will find the mycli.exe file in the Python Scripts folder, for reference.
Using MyCli on Windows
Now hopefully you’ve got MyCli installed on your computer and are prepared for utilising it’s power. First of, we will need to connect to a MySQL or MariaDB database. See the next section on how to do so.
Connecting To A Database
- Open up the command prompt
- Enter the following command, replacing your credentials where required –
mycli -u your_user_name -h your_host -p your_password --database your_db_nameand finally hit enter.
- You should see some response in the terminal and some new information labels at the bottom of the prompt. See Figure 2 for an example.
The Auto Completion Feature
This is one of the most exciting parts of the terminal helper, it’s auto complete capability. As you type with mycli active, a drop-down appears below your cursor, allowing you to hit the down arrow and then enter to select a command.
What’s also highly notable here is that mycli even recognizes the tables that exist in your database. Therefore it also offers auto-complete on table names too. So just to clarify see the bullet points below
- Command auto completion
- Database table name auto completion
- Table field auto completion
I am going to demonstrate this in action in the next couple of animated images.
Insert Into Command
I don’t know about you, but I’m already sold with this functionality. The only other place I’ve seen this type of functionality is PHPMyAdmin which does has it’s downsides, and certainly not in any other type of terminal. Mycli is clearly blazing fast, especially with the super-speedy completion helper.
The Syntax-Highlighting Feature
If you’ve read the last section, you will of already seen some of the syntax highlighting in action. Commands, table names, insert values are all highlighted during writing a statement. Let’s see a full insert statement below.
Now, this may not look like much, but after using it for a while you will find it rather handy. If you, for example, miss an apostrophe around a certain string value your going to insert, the highlighting won’t work. Therefore, letting you know, it’s erroneous SQL. See the next image with bad syntax.
If you look at the value of
'dan, you will see it’s in white color rather than deep red, showing there is a problem present. Pretty cool eh?
Other Notable Features
So during taking mycli for a spin, I noticed some more great functionality that are worth documenting too. I must warn you, they are pretty awesome!
MyCli Statement Memory
This brilliant terminal helper remembers statements that you’ve used before, and you can imagine how handy this is. Let’s say you want to run a similar query you ran yesterday for a different identifier, then it can be done within seconds! And yes, it even remembers after you’ve shut it down and booted it up again. See the example below
When this happens, as informed at the bottom of the terminal, you need to hit the right arrow and the command will fill out the line.
Data Output Format
If you’ve ever used the mysql shell before, then you will be aware of the crappy format that is returned when you execute a select statement. Well, mycli removes this problem entirely, presenting data in a visually excellent way. Firstly, see the mysql shell format, then mycli’s.
MySQL Shell Select Output
As you can see, it’s pretty unreadable unless you really try with your eyes.
Let’s see how mycli performs here.
MyCli Shell Select Output
As you can see, it’s beautifully formatted and a dream to read.
This elegant engine really does blow the MySQL shell out of the water. As I said before, the auto-complete feature is a selling point alone, not to mention the other great features. Another beautiful thing that isn’t really obvious by this article is that it’s completely cross-platformed, meaning you can use it on Linux and Mac too.
MyCli is developed by a group of volunteers and has a warm welcome for individuals who want to contribute too. Via it’s GitHub repository, there is even documentation on how to get up and running with a development version.
I will ask this one time, to give this article a share due to the fact I think this terminal client deserves a lot of credit. And at the same time, I want every developer who uses MySQL to know about it! Thanks for reading.