Does this sound familiar?
1. Creating SQL databases? Not a problem:
CREATE TABLE table_name (
2. Writing SQL queries to interact with your data? Too easy:
3. Building a full-stack, multi-user application on a SQL database and deploying it to the cloud? Not so much.
This article is for you if you are,
In this how-to guide, we explain how to rapidly go from MySQL GUI to a web application, or how to develop and deploy web applications on a MySQL database. Let’s dive in!
Building a web application requires software developers to choose a reliable, scalable, and easy-to-use database.
MySQL, Microsoft SQL Server, PostgreSQL, or MariaDB are some of the usual database suspects to choose from. These databases all tick the three boxes of being reliable, scalable, and easy to use. But MySQL is the number 1 choice of developers and data scientists when it comes to building applications.
MySQL ranks first as the world’s most popular database in both StackOverflow’s Annual Developer Survey, as well as Kaggle’s Data Science & Machine Learning Survey 2021. This shows just how popular MySQL is as a database technology.
It’s worth highlighting that this is not just a one-year snapshot. MySQL has consistently taken the top spot as the world’s most popular database in StackOverflow annual developer survey. For six years straight, it ranked first in response to the question “Which database environments have you done extensive development work in over the past year, and which do you want to work in over the next year?”.
MySQL’s continued popularity makes it a rare exception in a world where new frameworks, libraries, or technologies pop up like mushrooms. Created in 1995, it is a tried-and-tested technology to store and retrieve data from and is used by some of the world’s largest websites such as Amazon, Shopify, Slack, and Netflix.
In case that makes you wonder if MySQL is also a good choice for smaller applications, the answer is a definite yes. MySQL is a suitable database for applications with small to big data.
Many websites and web applications use MySQL as the database of choice for back-end storage.
To understand why MySQL is so popular, it’s good to understand its history. But to keep it short, the popular MySQL-learners’ website W3 School sums up the advantages of MySQL in three bullet points:
Equally important, it’s easy to find help for MySQL questions.
On StackOverflow, the MySQL tag was created in 2009 and has amassed more than 380k watchers at the time of writing this article. These numbers dwarf even PostgreSQL’s popularity, an alternative relational database management system: at the time of writing, PostgreSQL has less than 100k followers on StackOverflow.
This makes MySQL a safe choice even for beginners who wish to embark on their first full-stack development project. It is very hard to get stuck (or rather, to not to get unstuck) on MySQL. Its support community is large and active, which makes it easy to get help.
Now let’s speak about MySQL tooling: MySQL GUIs or MySQL IDEs.
GUI = Graphical User Interface
IDE = Integrated Development Environment
These two terms are sometimes used interchangeably. But for the sake of consistency, we will stick with MySQL GUI in this article.
What is a MySQL GUI? A MySQL GUI is a visual interface designed to let developers create and manage a MySQL database. The standard functionality of any MySQL GUI includes creating new databases and tables; writing and executing SQL queries to inspect data; creating joins or inserting new records into a table; as well as visually modeling Entity-Relationship Diagrams (ERDs), for example.
Some developers recommend using the MySQL command line client, instead of a MySQL GUI.
When using the MySQL command line client, developers can execute queries directly using command-line syntax, such as
CREATE DATABASE dbname;
For a full list of the MySQL command-line syntax, check out the official MySQL documentation.
The MySQL Command Line Client is the “no-frills” way of administering a MySQL database: it is designed to run SQL commands from the CLI only. Some developers love the simplicity of it and find more advanced MySQL GUIs to be bloated.
A MySQL GUI replaces some of the commands run in the command-line client with a point-and-click or WYSIWYG interface. They are a more user-friendly way of administering a MySQL database than using the command line interface (CLI) as a MySQL client.
Oftentimes, developers ask: What is the best GUI for MySQL? There is no clear answer: different developers prefer different MySQL GUIs and there is a large selection of different GUIs available. We limit ourselves to two of the more popular choices for a MySQL GUI in this article: MySQL Workbench and phpMyAdmin.
MySQL Workbench and phpMyAdmin are free and open-source MySQL GUIs with robust and rich functionality. Visit their websites – free download of MySQL Workbench and free download of phpMyAdmin – to get started with these tools.
Both MySQL Workbench and phpMyAdmin are free and open-source database administration tools. And both offer a comprehensive suite of features to manage and create MySQL databases.
Both MySQL GUIs can be used to administer a MySQL database or to write and run queries, inspect data, and alter tables.
Both MySQL GUIs also offer visual database modeling capabilities, such as an Entity Relationship Diagram (ERD) modeler, and come packed with handy features, such as syntax highlighting. The visual ERD modeler is a useful tool for beginners and experts alike. It lets developers create and edit a database schema visually, and then export the diagram as MySQL code. This helps understand table relationships or discuss database structures with business analysts or data scientists.
There is an important difference between MySQL Workbench and phpMyAdmin, however. Only one of the two is an online MySQL GUI.
MySQL Workbench is not available as a web-based tool. It needs to be installed on your operating system of choice. phpMyAdmin, on the other hand, is a portable web application that can be accessed from any browser. This means your MySQL client is accessible from anywhere. The drawback is database security. When using phpMyAdmin, the advice is to secure your SQL client and the databases.
It’s also important to understand what these two MySQL GUIs are not: they are not interfaces that you would ever want your end users to ever see, touch, or have access to. They are just GUIs designed to replace the command-line client for MySQL. That’s why these tools alone are not enough to build a full-stack application on a MySQL database.
A MySQL GUI, such as MySQL Workbench or phpMyAdmin, is just one of the tools required to build an application on a MySQL database. The other tools & technologies required are:
To create a MySQL database application with a web front-end for end users to create, read, update, or delete (CRUD) the data stored inside the MySQL database, a few other tools & technologies are required.
In a traditional development process, the different tasks described above (creating a database, hosting the database, writing the code, creating and designing a front end) are usually managed by a team of back-end and front-end developers, as well as web designers. Some full-stack developers are capable of executing all steps, but this is a rare exception, especially for more complex applications.
An alternative way of creating a full-stack web application on a MySQL database is using Five, an IDE that covers all steps from data modeling to application deployment.
Five combines the functionality of the different tools described above. It gives developers an integrated development environment that includes (some) functionality of a traditional MySQL GUI, a Visual Studio-like code editor, as well as application theming options using CSS.
Five also simplifies the hosting of the application. Every application that is developed using Five comes with a pre-configured development, testing, and production environment hosted on the cloud. The production environment is supported by a fully provisioned, cloud-hosted MySQL database. Because Five handles deployment, no cloud expertise is required to set up a database server or connect Five to a MySQL database.
Let’s go through each step one by one to compare Five against the traditional development process:
Five lets software developers create and manage MySQL databases in its drag-and-drop database modeler. Similar to a MySQL GUI, Five lets developers
The database can be created visually using Five’s database modeler. Anything created inside the modeler gets translated into SQL, meaning that the ERD that is visible and configurable inside of Five is the actual application database.
Five also automatically creates a
PRIMARY KEY and
FOREIGN KEY (if there’s a table relationship) for tables. This makes it very easy to create relationships between tables. Lastly, Five also lets developers export their SQL database as an SQL dump.
Are there limits to using Five as a MySQL GUI? Not too many! Five is not a full-fledged database administration tool. However, it includes all commonly used functionality that developers typically require to build an application. Things such as
ON CASCADE DELETE, or
INSERT INTO are all supported by Five.
In step 1, we used Five in the same way we would use a MySQL GUI: we created our MySQL database. Now it’s time to build the application.
First, Five lets developers build applications using its pre-built features. Forms, charts, or user permissions and authentication can all be created without writing code. CRUD permissions, on the table or record level, can be assigned in point-and-click to different user groups.
Full code written inside of Five is stored and maintained in an IDE-like environment with syntax highlighting, code completion, and supported by AI using Open-AI’s debugger and code interpreter. For JS novices, there’s also a visual way to create logic using drag-and-drop.
To give a few examples of how full code can help extend applications built inside of Five:
Creating a user interface can be one of the biggest drags on time in any software development project. Going from Figma design to functional web application is often more time-consuming than anticipated, as complex back-end logic gets translated into design elements.
Five provides developers with a pre-built UI, using Material UI 5 (MUI), a React component library that implements Google’s Material Design. Five’s UI is responsive and allows for the customization of buttons, fonts, colors, navigation bars, and icons.
Especially back-end developers, who are less interested in spending time on the visual elements of the final product, like Five’s out-of-the-box UI design, as it enables them to ship a production-ready application faster.
Five is tightly integrated into AWS and automates deployment to the cloud. Deployment to any of three cloud environments (development, testing, or production) happens in a single click. If you love the convenience of Amazon’s one-click buy, you’ll love the simplicity of Five’s one-click deploy.
Developers can deploy applications built with Five to three application environments: development, testing, and production. This means that by the time an application reaches its end-users, developers can confidently deploy their software, as any application would have gone through development and testing before being pushed into production.
In addition, all applications built with Five are containerized, using Kubernetes for container orchestration.
The management of large codebases can get very complex. That’s why Five incorporates software development best practices inside its IDE.
For example, Five automatically keeps track of all changes that are being made to an application by its developers, or by end-users, through application logs. There is a full audit trail of who changed which part of the application and when. This audit trail is easily accessible from inside the development environment and allows for filtering by date and person. So if the new junior developer broke prod (again!), it’s something that you can easily figure out by checking out the application logs.
Five also has a built-in debugger, called the Five Inspector, that lets developers observe what code is executed when the application runs.
A MySQL GUI is used to create and manage a MySQL database. Popular MySQL GUI tools are MySQL Workbench and phpMyAdmin. In a full-stack application development project, these tools are used by database administrators or back-end engineers to manage and create a database or run SQL queries. To build & deploy a full-stack application on a MySQL database, however, other tools and technologies, such as a database server or a traditional IDE are required.
To try Five today, sign up for a free download on our website.