How to create API using Express & MySQL
In this short we are building a basic API that connect to a MySQL database and fetch data from User table. This tutorial part of a series of tutorial on building authentication and other features expected of an API. This tutorial will require a strong understanding of Javascript and MySQL. Code are available on my github
[Required] Install Node.js on your local machine.
- Install Express generator:
npm install -g express-generator
Create a new express app using the command: npx express-generator api
It will generate our starter app with Express and some other library that are essential for our API to fully function. To test the start app, do “npm install” to install the dependencies and “npm start” to launch the application. If the download of the depencies was successful, you should be able to view the application at your localhost:3000. You should see something like this:
Note: The content being displayed in the browser comes from the index.js file located under api/routes/index.js. We will discuss how the views, jade and pug work in Express. Our next step in this tutorial is to gather our mysql database configuration and link it to our Express app.
2. Gather MySQL database configuration
In order to link our MySQL database to our API, we will need the configuration of the database. You have the option to use you local machine database or get a free database online. Come back when you have a database host, database name, username and password.
Create the folder config in the root folder of the API. In the folder config, create config.js that will hold the variables of our database. The config.js should be exported as a module in the following screenshot.
Use this link to import the SQL data in your mysql database
3. Install mysql library on our API: npm install mysql
The library “mysql” will be used by our application to connect to our database. (More information can be found here)
4. Create connection to the database
Create the folder called db in the root folder of the API. In folder db, create connection.js that is using the mysql library and the configuration we gathered in step 2. The connection.js should look like the following screenshot:
5. Update index.js to fetch data from your mysql database
We are updating the index.js to add connection to the database. The connection will be used to execute our SQL query. For now we are only selecting all the users from our Users table.
6. Test the API
Install the dependencies using “npm install” and start the API using “npm start”. If you go to your localhost:3000 again, you should see a json response display on your web browser.
There you have it. We just made an Express ap and connect it to a MySQL database. The current API only fetch all users from a database, but we will implement a login and signup feature in our next tutorial. The codes are available on my github.
Thanks for reading and happy coding 🚀.
