From MySQL GUI to Application: 2 Ways to Build & Deploy Apps on a MySQL Database

From MySQL GUI to Application: 2 Ways to Build & Deploy Apps on a MySQL Database

Handling the database integration for a web application can be a daunting process that involves setting up database servers, working with front-end libraries, and managing database migrations and versioning. MySQL GUIs are designed to facilitate developers’ interactions with a database. But they are not sufficient to create web applications on a database.

Let’s delve deeper into the question of how to build and deploy a web app on a MySQL database in this blog post.

Who This Article Is For

Does this sound familiar?

1. Creating SQL databases? Not a problem:

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

3. Building a full-stack, multi-user application on an 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 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?”.

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:

• MySQL is a widely used relational database management system (RDBMS).
• MySQL is free and open-source.
• 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.

1. From MySQL GUI to Application: Traditional Development

To connect a database to a web application and create a MySQL database application with a web front-end for end users to create, read, update, or delete (CRUD), the following steps are required.

1. Choose a database management system (DBMS): Select a DBMS that suits your application’s requirements, such as MySQL, PostgreSQL, MongoDB, or SQLite. Consider factors like scalability, data structure, security, and compatibility with your development stack.
2. Set up the database: Install and configure the chosen DBMS on your server or use a cloud-based database service, such as AWS, Azure, or GCP.
3. 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. Create the necessary tables, define relationships between entities, and set up indexes or constraints as needed
4. Connect to the database: In your web application’s backend code, establish a connection to the database using appropriate credentials. The specific process may vary depending on the programming language and framework you’re using to build your application. Most frameworks provide libraries or modules to simplify database connectivity. This has to be done from inside your Integrated Development Environment, such Visual Studio Code.
   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.
5. Execute database operations: Use SQL (Structured Query Language) or an ORM (Object-Relational Mapping) tool to interact with the database. SQL allows you to perform operations like SELECT, INSERT, UPDATE, and DELETE to fetch, insert, modify, or delete data. ORMs provide an abstraction layer, allowing you to work with objects instead of writing raw SQL queries.
6. Handle database queries: Write queries or use ORM methods to retrieve data from the database based on specific criteria, such as filtering, sorting, or joining tables. Process the query results and format the data to suit your application’s needs.
7. Manage database transactions: When performing multiple database operations as part of a single logical unit, it’s important to handle transactions. Transactions ensure data consistency by allowing you to commit changes as a single atomic operation or roll back changes if an error occurs.
8. Implement data validation and security: Validate user inputs to prevent malicious data from entering the database. Sanitize input to avoid SQL injection attacks. Implement access controls, authentication, and authorization mechanisms to secure the database and control user permissions.
9. Optimize database performance: Design efficient database schemas, create appropriate indexes, and use caching mechanisms to optimize query performance. Regularly analyze query execution plans and optimize slow-performing queries to improve overall application performance.
10. Handle database migrations and versioning: As your web application evolves, you may need to modify the database schema or add new features. Use database migration tools or scripts to manage and track changes to the database schema across different application versions.

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.

2. 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

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.