Creating an Efficient Referral System Using Node.js and MySQL: A Step-by-Step Guide
Referral programs are a common feature in many services today. Whether you’re signing up for a new SaaS product or subscribing to a trendy email newsletter, you often receive a referral link that you can share with others to earn special rewards.
Creating a referral program can be a powerful strategy for establishing social proof for your product. When someone signs up and others see them doing so, it encourages more sign-ups from those observers. This phenomenon, known as “social proof,” describes how people tend to mimic the actions of others in a given situation.
Project Overview
For this project, I plan to build a server using Node.js and Express.js, paired with a MySQL database. You are free to choose any web server and database combination, as the core principles remain the same.
The Node.js server will perform the following tasks:
1. Render a web page with an email sign-up form. 2. Generate unique referral codes for each user who signs up. 3. Return the referral codes to the client so they can be displayed.
The database will store information about users who have signed up, including:
- Email address - Referral code (unique for each user) - Referrer’s code (the code they used for sign-up, if any) - Timestamp of when they signed up
MySQL Database Setup
To create a simple referral system, we need a database capable of storing these four pieces of information. You can set up the database with the following schema:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`email` VARCHAR(255) NOT NULL,
`referral_code` VARCHAR(255) NOT NULL,
`referrer` VARCHAR(255),
`time_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);Ensure you keep track of the database’s credentials, including:
- Database name - Database host - Database user - Database password
These credentials will be needed to access the database from the Node.js application.
Node.js + Express.js Setup
Start by creating a basic Express project named “referral-program” using the EJS view engine. You can use [express-generator](https://expressjs.com/en/starter/generator.html) to scaffold the project:
express - view=ejs referral-programFollow the on-screen instructions to complete the setup:
- Navigate to the project directory: `cd referral-program` - Install project dependencies: `npm install` - Start the application: `npm start`
Visit `localhost:3000` in your web browser to confirm that the app displays “Welcome to Express.”
User Interface
In the `index.ejs` file, include the following code to render email and referral code input fields and a submit button. Additionally, use JavaScript (jQuery in this example) to submit data to the server:
<!DOCTYPE html>
<html>
<head>
<title>My awesome app</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.5.1.min.js" integrity="sha256–9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
</head>
<body>
<h1>Welcome to my app!</h1>
<input type="email" id="email" placeholder="Enter your email…">
<input type="text" id="referralCode" placeholder="Referral code">
<button id="submit">Sign up</button>
<script>
$('#submit').on('click', function(e) {
e.preventDefault();
$.ajax({
url: '/',
type: "POST",
data: {
'email': $('#email').val(),
'referrer': $('#referralCode').val()
},
success: function(res) {
alert('Your referral code is: ' + res.referralCode);
},
error: function(jqXHR, textStatus, errorMessage) {
alert(errorMessage);
}
});
})
</script>
</body>
</html>The jQuery code within the script tag submits an AJAX POST request to the `/` route with data from the email and referral code input fields. Currently, you’ll get a 404 Not Found error because the POST handler for that route is not implemented yet. However, when implemented, it will display the user’s unique referral link upon successful sign-up.
Dependencies
Install the necessary backend dependencies using npm:
npm i mysql
npm i dotenv
npm i shortidapp.js
Make a small modification to the `app.js` file. Add the following line at the very top to load environment variables from a `.env` file:
require('dotenv').config();Creating Environment Variables
Create a `.env` file at the project’s root and provide your MySQL database credentials as follows:
DB_USER=your_database_username
DB_PASS=your_database_password
DB_NAME=your_database_name
DB_HOST=your_database_hostThe “POST /” Route
We specified that the form should submit all entries as POST requests to the `/` route. Now, let’s define the functionality for this route. This route should:
1. Read the user’s submitted data: email and referrer 2. Generate a unique referral code for the user 3. Store the submissions in the database 4. Return the referral code to the client
Here’s an example of how to implement this route in the `index.js` file:
var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var shortid = require('shortid');
// New POST route for form submissions
router.post('/', function(req, res, next) {
// Establish a MySQL connection
var connection = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASS,
database: process.env.DB_NAME
});
// User's email address
let email = req.body.email;
// Generate a unique referral code for the user
let referral_code = shortid.generate();
// Referral code a user submitted (might be null)
let referrer = req.body.referrer;
// Add user to the database with INSERT
let query = "INSERT INTO `users` (`email`, `referral_code`, `referrer`) VALUES (?, ?, ?)";
connection.query(query, [email, referral_code, referrer], (err, rows) => {
connection.end(function() {
if (err) return next();
return res.send({referralCode: referral_code});
});
});
});
// GET home page
router.get('/', function(req, res, next) {
res.render('index');
});
module.exports = router;With this implementation, you have successfully created a referral program in your application! To test it, restart your Node server (stop with `ctrl+c` and start with `npm start`) and revisit `localhost:3000`. Submit your email address, and you should receive a pop-up with your unique referral link. If you make another submission using that referral code, it will be recorded in the database. Congratulations! 🥳
BUY ME A COFFEE:
