In the part 1 of this tutorial, How to Build a Login/Register App with the MERN Stack (Part 1): Getting Started, we got things ready. In this part we will start the real work. We will write the code needed to create and connect to database.
Setting Up the Project Folder
So, first things first, let’s create a package.json file.
Run npm init from your terminal and answer the questions to create the package.json file. The way I like to do this is just keep hitting Enter 😎.
Anyway, now we have to install the packages we listed in the previous part.
Run npm install express mysql2 express-session express-mysql-session bcrypt body-parser cors dotenv to install needed dependencies.
We also need to install nodemon as a dev dependency, which means it will not be installed in production environment. For that, we have to run npm install nodemon --save-dev.
And to use nodemon‘s special power of restarting the server on any change in the files, we need to add "dev": "nodemon <YOUR_APP_ENTRY_POINT>" to the scripts section in package.json.
Creating the Database
For this project we will use a database from Free MySQL Hosting, but you have to know that this database is not suitable for production.
Head over to their website and create an account. Then, you will receive an email containing your database credentials. Go ahead and login to phpMyAdmin, where you will be able to manage your database.
Now, we need to create a table for users. I will just create a table that contains user’s id, name, email, and password using the following piece of SQL code.
NOTE: You can run SQL commands on a database in phpmyadmin by clicking on the database name from the sidebar and switching to SQL tab.
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(25) NOT NULL,
email varchar(40) NOT NULL,
password varchar(255) NOT NULL,
PRIMARY KEY (id)
);
You can further customize the table to meet your app’s needs. We also need to change the database Character Set and Collation to utf8 and utf8_unicode_ci respectively. We can do so by executing the following piece of SQL code.
ALTER DATABASE <YOUR_DATABASE_NAME> CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Connecting to the Database
Let’s create a folder for our database related stuff, like code to connect and models. I’ll name mine db. Because we will use it in different places, let’s create a file, called connectionConfig.js for example, that exports an object of our database connection options.
What we are doing here, on line 2, is basically loading environment variables using dotenv if not in PRODUCTION, because in production these variables will be provided by the deployment platform. For development environment we need to create a .env file that looks like this, and put values corresponding to each of these keys right next to the equal sign.
DB_HOST= DB_NAME= DB_USER= DB_PASSWORD= DB_PORT=
NOTE: Do not forget to add .env to your .gitignore file to avoid pushing the .env file to your repository.
The way mysql2 works is, first create a connection, then execute queries, and at the end you typically want to end the connection. And for that, we need to make a file, called execQuery.js for example, that exports a function that connects to the database, executes a query, ends the connection and returns result of the query.
The function takes in the SQL query to run and values to pass to this query. Let’s expand on this a little bit. The connection.execute() function prepares the query, which gives us better performance and protection against SQL Injection, which typically happens because of concatenating or injecting user input directly into the SQL query.
Well! How can we put parameters in SQL queries if we can not actually put them in there?
This is fairly simple, we use the symbol ? as a placeholder in the query, and then provide an array of corresponding values for each placeholder.
User Helper Class
Next, we need to make a helper class that defines some operations for us, like register and login, to make it easier to use them all over our app. Let’s create a file, called User.js for example, that looks like this.
Now let’s add a function that registers a user.
The function returns an HTTP status code.
At first, we assume the status code is 409, that is CONFLICT. I use this to indicate the existence of a user with the same username. Users matching the provided username, are selected on line 7.
BTW, the CONVERT(? USING utf8) COLLATE utf8_bin part is just there to make the SELECT statement case sensitive.
If there are no users with the same username, the password is hashed on line 10 and the user is inserted into database on line 14, and the status code is set to 201, that is CREATED.
If any error happens along the process, we console log it on line 19 and change status code to be 500, that is INTERNAL SERVER ERROR.
Let’s add another function to login a user.
First, we get users with the same username as the passed one. If there is any matching user, the passed password is compared to the stored password hash for that user, and if they match, then the status code is set to 204. And at the end, we return an object containing the status code and authenticated user.
Conclusion
We created a database through phpMyAdmin. Then we put our code to connect to that database in organized files. We also secured our database credentials using environment variables. And finally, we made a helper class that will make it easier for us to do specific operations on the database anywhere in our app. It is very important to organize your code and files and make it clean, so that it makes it easier to maintain your code. Next, we will create our API endpoints using Express.
NOTE: You can find all parts of this tutorial here and code here.
