MDB GO Getting Started - Node.js with Express.js and MySQL API

MDB GO Getting Started - Node.js with Express.js and MySQL API

After reading this tutorial you will know how to create Node.js API with Express.js and MySQL database using MDB GO.


Introduction

Many people think that when you use Node.js you have to use MongoDB for storing data. But that's not true. You can use any kind of storage - it simply depends on your use case. In this tutorial we will show you how to create a Node.js API with MySQL database.

Technology recap

Here's a quick recap what are the main technologies used in this tutorial:

  • MySQL is an open-source relational database management system. If you want to learn more about it check out a separate tutorial.
  • Express.js is a web framework built for Node.js that allows you to create web servers in a very convenient way. Learn more about it here.
  • Node.js is a multi-platform JavaScript runtime built on V8 engine. It's used as a backend side of web apps. Read more about it here.

We won't dive deep into details about how to get started with these technologies using MDB GO. Go ahead and check the linked tutorials and once you learn a few things get back here.

If you have enough knowledge to get started, let's do this.

Building the API

Our example app will be a TODO list. It's a very popular use-case so let's simply get things done one at a time. We will go through the following steps:

  • Create a MySQL database named todo_app
  • Create Node.js API with Express and Sequelize

Let's go!

Creating MySQL database

In order to create a new database you need to run the following command:


            $ mdb database init -db mysql8
          

Now you need to provide your user data and then the database details. Please provide the values of your choice. For example:

? Enter username thor
? Enter password Mjolnir_1
? Repeat password Mjolnir_1
? Enter database name todo_app
? Enter description Database for the TODO app

Note: the password must contain at least one uppercase letter, one lowercase letter, one number, one special symbol and have minimum length of 8.

Hit Enter and it's done.

Please notice that in the command output the username and database name slightly differs from what you provided earlier. Don't worry - it's OK. A few random characters have been added to these values to randomize them and improve security of your database credentials.

Important: Do not close your terminal window until you save your credentials somewhere. This is the only time we will show you your database password. If you won't save it you'll loose it.

That's pretty much it. You can now see how to connect with this database from the Node.js API.

Creating Node.js API with Express and Sequelize

In case you don't know, Sequelize is an object-relational mapper (ORM) built for Node.js. It helps managing MySQL access and translates the database results into easy-to-use objects. You can initialize a MDB GO starter that already has configured Sequelize. Simply run the following command:


            $ mdb backend init
          

and choose Express.js + MySQL API starter from the list that shows up.

After initialization just go to the newly created project directory and open the .env file. After that edit the DB_CONNECTION_STRING value. You should paste the connection string that's been shown to you in the previous step. In my case the updated value looks like this:

DB_CONNECTION_STRING=mysql://thor83ad51bf:Mjolnir_1@mysql.db.mdbgo.com:3306/todo_app48a0ca47

Save the file and close it.

The starter project is ready to use. You don't have to change anything to run the example app for the sake of this tutorial. Simply publish it using the following command:


            $ mdb publish -p node12
          

After your files get uploaded you can access your app under the address shown in the output.

Note: You may want to check the tutorial where we explain in detail what can go wrong here and how to deal with it.

Note: Since we need to install dependencies and run your app, it may take a few moments until it will be available under the provided URL.

Alright, let's see how to "talk" with our fresh API.

Playing with Node.js API - CRUD requests

Before we jump into "talking" stuff, firstly let's find out what endpoints do we have.

In the ./app/routes/tasks.js file there are a few endpoints defined to show you how to get started.

By default on the / route there is the index.html file served (located in ./public/ folder). There are also four additional endpoints defined:

  • GET /tasks - reads the tasks array and sends it as a response
  • POST /tasks - creates a new task and appends it to the tasks array
  • PUT /tasks/:taskId - updates a specific task
  • DELETE /tasks/:taskId - deletes a specific task

And these are the ones we are going to talk to. In order to do that I recommend you install some REST client - like Postman for example - because it'll make your life way easier. Alternatively, you can utilize curl command available in Linux/MacOS terminals. However, in this tutorial we will go with Postman.

Go ahead and install it using official docs.

Talking to our API

Once you download and install Postman you can start making requests. We will cover all of the above requests.

GET

In order to make a GET request you need to create a new request in Postman and do two things:

  • First, select the request method - GET
  • Then provide the request URL. In our case it's going to be: https://express-sequelize-starter.mdbgo.io/tasks

Once you hit Send you will see the response - an empty array. Let's add an item there.

POST

In order to create a task we need to make a POST request. In order to do that you need to:

  • Select the request method - POST
  • Provide request URL: https://express-sequelize-starter.mdbgo.io/tasks
  • Click the Body tab and select raw | JSON (application/json) and provide the following content:
    {
    "name": "Do laundry",
    "desc": "Boring but necessary..."
    }

Once you hit Send you will see the response - a new task with id 1. Go ahead and run the GET request again to see it. Now let's try to update it.

PUT

Updating the task requires the following steps:

  • Select the request method - PUT
  • Provide the request URL: https://express-sequelize-starter.mdbgo.io/tasks/1
  • Click the Body tab and select raw | JSON (application/json) and provide the following content:
    {
    "name": "Do ironing",
    "desc": "Boring but necessary... and also quite dangerous!"
    }

Once you hit Send you will see the OK response. Go ahead and run the GET request again to see it. Alright, let's delete it.

DELETE

In order to delete a task you need to do the following:

  • Select the request method - DELETE
  • Provide the request URL: https://express-sequelize-starter.mdbgo.io/tasks/1
  • Do not provide any body. Select none in the Body tab.

Once you hit Send you will see the OK response - it means the task has been deleted. If you run the GET request again you will see that the task is gone.