Best free SQL editors for MySQL developers and administrators
In this chapter, we will discuss about some of the Graphical User Interface(GUI) tools that will helps us in connecting and interacting with MySQL databases very easily. Some of the most popular free version tools are listed below:
- MySQL Workbench
As per official documentation, MySQL Workbench is one of the most popular multi-platform unified visual tool for database architects, developers, and DBAs. It provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. It offers both the free version as well as the paid version. Since it is developed and maintained by Oracle itself, we can expect it to be compatible with all the recent feature releases with MySQL server.
To download MySQL Workbench, go to the following url:
https://dev.mysql.com/downloads/workbench/
Download the installer as per your operating system and then install it. Below is the image of first screen that you will be seeing, when starting a MySQL Workbench.
To add a new database connection details, click on the plus(+) icon as shown by red arrow in the above image. Enter the database connection details. Here, we will be using writer user credentials, which we created in our earlier chapter. Click on Test Connection button to check if the connection details are ok or not. If checks are passed as shown below, then click on the Ok button.
Following is the dashboard image of MySQL Workbench, from where we can run various database administration and management tasks.
We can execute various SQL statements to interact with the database as shown below:
MySQL Workbench offers many other features and they are very much helpful when dealing with MySQL servers.
- phpMyAdmin
phpMyAdmin is a free and open source web based tool to handle all the administration and management tasks of MySQL server. It's written in PHP. Since it's been used to manage MySQL server databases for nearly as long as MySQL have existed, it supports almost all of the MySQL features. To download it, go to the following link:
https://www.phpmyadmin.net/downloads/
As of writing this article, the latest version of phpMyAdmin is 5.2.0
Installing phpMyAdmin with Docker
The easiest way to install phpMyAdmin is using Docker. If you do not have already installed docker in your system, go to the following link:
https://www.nodexplained.com/introduction-to-docker-and-dockerizing-nodejs-application/
Now that the docker is installed, we can download phpmyadmin docker image in our local machine. Issue the following command:
docker pull phpmyadmin
We can now run the docker container with phpmyadmin image using following syntax:
docker run --name CONTAINER_NAME -d -e PMA_HOST=MYSQL_SERVER_HOST -e PMA_PORT=MYSQL_SERVER_PORT -p HOST_PORT:80 phpmyadmin
Here,
- -d specify to run the docker container in daemon mode
- -e is for specifying environment variables, and phpmyadmin docker image exposes many environment variables. Some of them are:
PMA_HOST - define address/host name of the MySQL server
PMA_PORT - define port of the MySQL server
PMA_USER - define username to use for config authentication method
PMA_PASSWORD - define password to use for config authentication method - CONTAINER_NAME is the name of the container.
- -p is used to publish or expose ports to the host. HOST_PORT is mapped to port 80 of the container.
For our scenario,
- CONTAINER_NAME=phpmyadmin
(It can be any meaningful name) - PMA_HOST=host.docker.internal
(It can be any MySQL server host. Since the MySQL server is installed in the local machine directly, we need to access it from inside of a docker container and to do that, we need to specify host as host.docker.internal) - PMA_PORT=3306
(port on which MySQL server is running) - HOST_PORT=8080
(It can be any port. If port 80 is not used by other applications, you can also use port 80 as well)
docker run --name phpmyadmin -d -e PMA_HOST=host.docker.internal -e PMA_PORT=3306 -p 8080:80 phpmyadmin
Once phpmyadmin docker container is up and running, we can browse http://localhost:8080 to access phpMyAdmin application. Enter the MySQL username and password to start interacting with the databases. We will use writer user credentials.
After successful login, we can see the dashboard page containing all the accessible databases as well as various other database administration and management features as shown below:
From the dashboard, we can execute any SQL statements. Following image shows the SQL statement being executed for users table.
phpMyAdmin is extremely helpful, when dealing with database servers hosted in the cloud instances. We can easily access the cloud database using phpMyAdmin in the web browser itself. (For secure access, we can deploy phpMyAdmin in a private cloud server instance to which database server connection access is given. Then we expose phpMyAdmin to the internet behind VPN connections.)
- DBeaver
DBeaver is a free multi-platform database tool for interacting with various databases. It supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc. It provides both the free and open-source based community edition with limited features as well as the paid edition with advanced features.
To download it, go to the following link:
https://dbeaver.io/download/
As of writing this article, the latest version of community edition is 22.1.4
Download the installer as per the Operating System installed in your machine. Once the installer is downloaded, double click on it to install it. For macOS users, we can either use installer packages in .dmg file or use brew. Using brew is much easier and generally a preferred way of installing packages. In the future, we can easily upgrade to latest available versions as well.
Installing DBeaver in macOS
Issue the following command to install DBeaver in macOS machine:
brew update && brew upgrade
brew install --cask dbeaver-community
Once it is installed, you can see following icon in your machine. Click on that icon to start DBeaver. Click on Yes to create a sample database(optional).
Click on the icon as pointed by red arrow to start a database connection. It shows a prompt from which we need to select a database server. Select MySQL and click Next button.
Enter the database connection details:
Since we are using DBeaver for the first time, after entering db connection details, let's click on Test Connection button. If we are connecting to the MySQL server for the first time, it shows us the screen to download the necessary drivers, needed to connect to the database server. Download the drivers.
On successful test connection, it will give us the following screen. Click on Ok button to continue.
Click on Finish button to connect to the travel_app database.
Installing DBeaver in Ubuntu
To install DBeaver in ubuntu, first download the .deb package.
wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb
Issue following command to run the installer:
sudo dpkg -i dbeaver-ce_latest_amd64.deb
In the next chapter, we will connect our Node.js travel application with MySQL server and then perform CRUD operations.