Database Management Tools and Compose for MySQL

Since the launching of Compose for MySQL, we’ve shown how to connect to your deployment using the command line and various connection drivers. However, there are times when you may want to dive deeper into your database without having to run a series of SQL statements from the command line. A database management tool will help you out.

Most database management tools are designed with rich graphic user interfaces, or GUIs. These tools are sometimes preferable to use than command line tools, since they can lessen the cognitive load and increase productivity for users who are new to MySQL.

There are many data management tools that are compatible with MySQL. However, not all tools are created equal. So, while this is not an exhaustive list, we’ve chosen three management tools to cover: MySQL Workbench, Sequel Pro, DBeaver. These tools have been chosen based on 1) the ability to connect to a Compose deployment using SSL/TLS with a self-signed certificate, 2) their popularity among MySQL users, and 3) their usability and features.

MySQL Workbench

MySQL Workbench is the official database management tool for MySQL. It comes with a variety of features that help you manage, develop and design your MySQL databases.

One of the notable features of MySQL Workbench is its visual database design tool. This feature alone is perhaps the most compelling reason to try out MySQL Workbench since it allows you to take a visual data model and transform it into a database. That means you can drag and drop tables and views onto an ER diagram view like

and define the columns and datatypes you want to use for each table.

The column names are user defined, while you have a list of data types to select from. Indices, foreign keys, and other options can be determined as well.

After your diagram is complete, MySQL Workbench will forward engineer your ER diagram and generate the SQL script like

All you need to do is press Continue and it will execute the SQL script on your database.

Viewing data within the tables can only be accomplished by writing an SQL query. Once you’ve written and executed a query, you’ll be presented with the results at the bottom of the query editor.

So, with this point and click solution, it's relatively easy for those without extensive experience working with SQL to create a professionally functioning database, query data, and make edits. MySQL Workbench handles most of the SQL scripts for you, so that even the seasoned database administrator doesn't have to spend a lot of time remembering the latest MySQL commands.

Overall, MySQL Workbench contains a lot of features that are continuously being improved. The number of features that the tool provides arguably the best available. But, the best part is that it’s available for most platforms for free.

Sequel Pro

Sequel Pro is another database management tool that helps to make querying your MySQL database rather painless. While it doesn’t have all the database design features of MySQL Workbench, the tool’s GUI is simple and user friendly.

Most of the tools to view your databases tables and content are located on the top toolbar. This makes it particularly nice to work with since you don't have to sift through various tabs and views to find your data.

Using the side menu to select a table, you're able to insert, delete or modify content, table fields, indices, and relationships.

Although Sequel Pro doesn't allow you to visualize your tables in an ER diagram, a simpler design and layout of the tool appears to eliminate some of the redundant features found in MySQL Workbench surrounding the creation of tables, views, procedures, and functions.

The process of querying the database is essentially the same as MySQL Workbench. You type in an SQL query in the editor then press Run previous to execute the query and view the results.

You can edit data directly from the result table by clicking on a value in the result table. MySQL will automatically validate your data and will throw an error if a value cannot be modified or if it's an illegal value.

An important aspect of Sequel Pro is that it can be tailored to the user. The developers of the tool have allowed the community to build bundles that essentially extend Sequel Pro's functionality. This allows for users to add only the features that are necessary for their use case, and it helps to cut down on unnecessary tools that are not used by the majority of users.

Sequel Pro is free to download and is currently available for MacOS X and macOS only.

DBeaver

DBeaver is also an open-source database management tool. Unlike Sequel Pro, however, DBeaver is a Java-based tool that supports a multitude of databases and comes with the similar database querying and management tools as Sequel Pro and MySQL Workbench.

Like the other tools we've reviewed, the menu and toolbar is where most of the database features live. Once you've accessed your deployment, you have all the databases and tables situated in the menu. The top center view is where you write your queries, and below that is where the results of the executed query reside.

Selecting a table from the menu will create a new tab in the center view and will show you the properties of the selected table.

Here is where all the information concerning your table columns, foreign keys, constraints references, indices, etc. are created, edited and deleted. Selecting Columns on the Properties side menu, for instance, will show you the selected table's columns, data types, and other options assigned to them.

By right clicking on the view, you'll be given a list of options that are available to modify the columns, which can be done on any of the menu items located in the Properties tab.

Clicking on the Data tab, you'll be given the results of the table. Like the other tools, the table values can be modified and the values will be verified by MySQL.

Perhaps one of the most interesting features of DBeaver is located in the Diagram tab.

Like MySQL Workbench, the Diagram view allows you to view the relationships between tables. While you can’t create new tables, views and define new relationships, you’re able to see tables and their metadata with the diagram.

This image only shows the relationships between the Cars table and others within the database. However, if you select compose from the side menu and then click on the Diagram tab, you'll get a full diagram of all the database tables and relationships.

ER diagrams can be saved, but only as images, which makes them useful for presentations or showing them in a report. Unfortunately, unlike MySQL Workbench, they cannot be created and then converted into SQL. Their presence is only to serve as a visual aid.

Creating queries in DBeaver is also a lot like Sequel Pro and MySQL Workbench in that you have to use SQL to query your database. While this might not seem to be a feature that should be highlighted, DBeaver is the only tool that includes an autocomplete feature in the editor.

The autocomplete tool is helpful when writing queries in the SQL editor. To generate hints it uses pre-loaded MySQL metadata and any information loaded from your database.

Autocomplete is also helpful if you use DBeaver’s SQL templates. Templates are adopted from Eclipse code templates that can be configured to hold SQL code and saved with a unique name.

After setting up a template called get_cars, all you have to do is enter the name and press the tab key and it will automatically insert your SQL query. It's a really nice tool to have if you're writing the same query multiple times.

Like MySQL Workbench and Sequel Pro, DBeaver depends on a community of developers to develop new features and improve the database management tool. Also, DBeaver uses plugins developed by the community to tailor the tool to your needs.

DBeaver is available on most platforms and can be downloaded for free.

Get Querying

This was a brief overview of some popular database management tools that you can use right now with your Compose for MySQL deployment. While we didn't cover all of the features of the tools, we wanted to go over the basic features and benefits of each. There are many more tools that are available for Windows, Linux and macOS environments that we didn't cover, but as you can see most of them all contain similar features and have similar capabilities. We suggest to try them all out to understand how they work and to perhaps choose one next time your working with Compose for MySQL.


If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

Image by Jesse Orrico