2 Ways to Build & Deploy Apps on a MySQL Database

Does this sound familiar?

1. Creating SQL databases? Not a problem:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
   ....);

1. Writing SQL queries to interact with your data? Too easy:

SELECT *
FROM table_name
WHERE condition;

1. 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,

• a database administrator creating a database for use by an application,
• a backend developer who dislikes creating a CRUD front end,
• a data scientist or data enthusiast with a knack for SQL and relational databases,
• a self-taught coder with a strong interest in relational databases, or
• a full-stack engineer wishing to go from database to web app faster.

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!

Reasons to Choose MySQL as a Database

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: The Most Popular Database Chosen by Software Developers and Data Scientists

MySQL ranks first as the world's most popular database in both Stack Overflow'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.

Why MySQL is the World's Most Popular Database Technology

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:

1. MySQL is a widely used relational database management system (RDBMS).
2. MySQL is free and open-source.
3. MySQL is ideal for both small and large applications.

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.

What is a MySQL GUI?

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.

Using the MySQL Command Line Client

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;
USE dbname;
DROP 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.

Using a MySQL GUI

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.

Similarities and Differences between MySQL Workbench and phpMyAdmin

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.

Two Ways of Building and Deploying an 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:

1. A database server.
2. A front-end, created in an IDE.
3. Optionally, a mock-up using a web design tool such as Figma.

From MySQL GUI to Application: Traditional Development

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.

• A database server: your database needs to be stored somewhere online. That's where a database server comes in.
  A database server is a networked computer that is dedicated to database storage and data retrieval from the MySQL database. It holds the database management system (DBMS) and the databases.
  All big cloud providers, such as AWS, GCP, or Microsoft Azure offer hosting of MySQL databases as a standard service. There are also some free options, such as Free Remote MySQL but these usually come with size or usage limitations, such as keeping databases under 100 MB.
  To set up a new database connection from inside your MySQL GUI, provide the hostname (endpoint), port, and username. This information is provided by the database instance that you are connecting to. Beyond that, the cloud providers also give you configuration options to manage your database, such as back-ups, availability or picking volume types.

• An application front-end: The application front end is where your end-users will access your application. Front-end frameworks can help with the design of a user interface (UI). They provide pre-written code or component libraries that simplify the job of creating a consistent UI that will delight end-users.
  Popular front-end frameworks are React, Vue.JS and Angular. To work with any of these frameworks, an Integrated Development Environment (IDE), such as Visual Studio Code is required.

• Even before writing any code for your front end, developers typically want to create and agree on a mock-up of their web application's front end. This is where tools such as Figma or Adobe XD come in handy. They are design tools that make it easier to build for the web and are part of any web designer's toolbox.

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.

From MySQL GUI to Application: Using Five's Low-Code IDE

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:

1. Using Five as a MySQL GUI,
2. Using Five to write code,
3. Using Five to create the user interface, and
4. Using Five to deploy the application to the cloud

Step 1: Using Five as a MySQL GUI

Five lets software developers create and manage MySQL databases in its drag-and-drop database modeler. Similar to a MySQL GUI, Five lets developers

• create tables,
• assign common data and display types, such as strings, floats, booleans, binary or integers to database fields,
• write and run SQL queries, or
• import data from CSV files.

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 PRIMARY KEY, FOREIGN KEY, ON CASCADE DELETE, or INSERT INTO are all supported by Five.

Step 2: Using Five to Write Code & Build Applications

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.

Moreover, Five also lets developers write SQL, JavaScript, or TypeScript to extend their applications.  For example, developers can write SQL to create reports or JavaScript functions that are fired when a certain event occurs.

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:

• You want to create a report that shows all bookings that were made by end-users in the last week. Inside Five's SQL Editor, you can write the SQL query that returns last week's bookings. You can also build this query visually, using Five's Query Builder.
• You want to create a button that sends a text message to end-users using the Twilio API. Inside Five, you can simply write a JS function that calls the Twilio API and associate this function with an "On-Click" event.
• You want to check your end-users' IP addresses to automatically pre-fill the country they are visiting your website from inside a form. Inside Five, you can write a JavaScript function that performs an IP lookup and returns the country that is associated with your end-user IP address.
• You want to check whether an end-user has entered a valid email address inside a form field. Inside Five, you can run a data validation using Regular Expressions. If an invalid email address is entered, Five will return an error message to your end users.

Step 3: Using Five to Create the User Interface

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.

Step 4: Using Five to Deploy the Application to the Cloud

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.

The development environment is designed for developers to preview and test their applications and is completely isolated from the other environments. Development is primarily used for sandboxing.
The testing environment, which is completely isolated from both development and production, is where end-users can test new features and provide feedback to developers.
The production environment, which is completely isolated from both development and testing, is where all live services are deployed. It is where your end users access the final application. Production is also supported by a dedicated, fully-provisioned MySQL database.

In addition, all applications built with Five are containerized, using Kubernetes for container orchestration.

Other Useful Application Development Features in Five

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.

Conclusion

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.

Five is a web-based development environment for creating applications on a MySQL database. It offers functionality similar to that of a MySQL GUI by letting developers create and manage a MySQL database. Over and beyond its MySQL GUI features, Five gives developers everything they need to build & deploy a full-stack web application: prebuilt features, such as forms, charts, and authentication; a full code editor for SQL, JavaScript, and TypeScript; a prebuilt user interface (UI), as well as single-click deployment to the cloud.

To try Five today, sign up for a free download on our website.