Building a Database for a Web Application

Part I: Preparing the database

So I want to learn how to build a database for a web application. I started with a prompt in good old ChatGPT.

Here is what it taught me…

I don’t know how to determine a DBMS for particular needs yet, so I opted to choose a database that is popular among software engineers because this may hint to one that is versatile. MySQL seems like a good choice to begin learning. It offers an open source edition (https://www.mysql.com/products/community/). MySQL is owned and managed by Oracle so this seems like a good place to start since Oracle is reputable and has been in the database products industry for as long as I’ve been studying computer science.

Steps to take:

1. Download and run MySQL Installer for Windows

2. Developer Default option

3. Install requirements

4. Needed to install Visual Studio 2019 (https://learn.microsoft.com/en-us/visualstudio/releases/2019/release-notes) and add MySQL for Visual Studio (https://dev.mysql.com/downloads/windows/visualstudio/)

5. Had a unresolved issue so followed instructions here (https://dba.stackexchange.com/questions/278659/mysql-visual-studio-must-be-installed-but-it-is)

6. Download and install steps

7. MySQL should be installed at this point

MySQL installed what seems like a few applications, but the GUI is MySQL Workbench. After opening the local instance, I think we can start making our database. But before that, it can be helpful to read the documentation because it contains lots of explanations on the tools and features included so you can determine the benefits you want to achieve. Doing this, I learned that MySQL Workbench comes with an example model which can be used as a reference when you need to develop databases.This saves us time and we can begin building an API or server layer to call this database.

Part II: Building a backend service to retrieve data from the database

This is where things can get a little interesting. Now that we have a database. How do we use the database to create data, read data, update data, or delete data from it? Well this brings us to the second layer, the service layer or it might be better known as the back-end service. It’s called this because it sort of runs in the background, for users. Lets now ask ChatGPT for a popular backend framework that we can use to interact with our database.

Seems like a reasonable response. Lets use Node.js, to see if we can interact with the database. Before we jump into Node.js, it's good to point out here that a lot of open source projects are wrapped in something we call “frameworks.” This is a fancy word for making this a bit easier to get started. Frameworks are exactly that, they are scaffolding or templates to make it easy to get started with certain technologies. There is likely a framework for all technologies at each implementation layer. Since frameworks are built “on top of” base implementations you can still use the technology that's “underneath” along with the framework in most cases. Lets ask ChatGPT what are some popular frameworks for Node.js..

I haven’t used Express.js in a while but it seems like it can be a good fit for the general purpose of learning to build a system back-to-front. Frameworks usually come with templates or generators that you can implement because of the nature of frameworks are templates you can customize. So look out for useful tools when developing.

Some steps:

1. Open a terminal > Created a New Folder > cd to New Folder

2. Running the command from this link (https://expressjs.com/en/starter/generator.html)

3. $ npx express-generator

This command generates a template of a runnable express app. Follow the rest of the commands and you will end up running the application.

Pro Tip: Use nodemon(https://www.npmjs.com/package/nodemon) to refresh your app that is running locally when you save your work in your editor.

Run the installation steps Nodemone(https://www.npmjs.com/package/nodemon)

In the application, you can add nodemon as a runtime command by adding it to the script object in package.json
"nodemon": "nodemon ./bin/www"

Then run the application again with “npm run nodemon”

From here you can scan the existing code to get an idea of how the application works.

Part III: Pulling data from the MySQL database to the Express application

First let’s check the database to see if there is data populated inside. We can do this by selecting the schema tab at the bottom of the “Navigator” view. Here you can see a list of schemas, or rather a collection of information about the tables, views, stored procedures, .etc, regarding a database. Now let's query our given example database schema. Select the top left SQL+ icon to open a .sql file and run a basic query, then execute.

Looks like there is data in the database. And we can attempt to make a router call using Express so we can pull data into the back-end of our application. This is a process id called database integration. Express contains a guide (https://expressjs.com/en/guide/database-integration.html#mysql) to integrate. Lets follow the guide and see what results we can get.

After installing MySQL npm package you can add a folder in the root directory of your application, create a .js file and add the connection script into the .js file. Then in app.js, import the connection script like so…

// MySQL Database Connection

var mysql = require('./db/mysql')

app.use(express.static('db'))

I ran into an issue authenticating into mysql workbench from the Express application.

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgra`ding MySQL client

I posted this error in ChatGPT and it thoroughly explained the problem and provided a solution script that was also posted in Stackoverflow.

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Opened a SQL file in workbench and executed this query with the relative information regarding my setup and it worked in Express. Then we can query the same sql query above in the connection .js file like so…

connection.query('SELECT * FROM customer_list', (err, rows, fields) => {

    if (err) throw err

    console.log('The customer list is: ', rows[0])

})

Resulting console.log

Pretty cool. Now we have access to the data in the database in our Express back-end. Now instead of the console log of the data, we want to be able to make a http service call because applications communicate over the web via an API. So we can modify the connection file to export the connection object. Then we can import this file and assign it to a variable in a route file. Then make a service call.

Running the application and navigating to http://localhost:3000/users/mysql/users. We can see the SQL response in the web browser.