Build a Simple Referral System With Node.js and MySQL

Referral programs are everywhere. Just about any time you sign up for a new SaaS product or subscribe to a hot new email newsletter, you're given a referral link you can share with others for a special bonus.

Creating a referral program is an incredible way to get social proof for your product. Someone signs up, other people see them doing so, and in turn they're more likely to sign up as well.

“[Social proof] describes a psychological and social phenomenon wherein people copy the actions of others in an attempt to undertake behavior in a given situation.”

While working on my most recent product, Shelf (check it out!), I implemented a custom referral program, and wanted to share how I did it.

The Plan

For this project, I'm going to use a Node.js/Expressjs server and a MySQL database. You're free to use any web server + database combo, as the same principles will apply.

The Node server will need to do the following:

• Render a web page with an email sign-up
• Generate unique referral codes for each user who signs up
• Return the referral codes to the client so they can be displayed

…and the database needs to hold a list of everyone who has signed up, with the following info:

• Email address
• Referral code (their unique code)
• Referrer (the code they used to sign up with, if any)
• Time added

MySQL

For this simple referral system, we just need a database that can hold four things: a user's email address, their unique referral code, the code of the person who referred them, and the time they were added.

I created my table 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`)
);

Wherever or however you set up your database, make sure to keep track of its credentials so we can access it via the Node.js app later. You will need:

• Database name
• Database host
• Database user
• Database password

Node.js + Expressjs

I'll first create a simple Express project called referral-program using the EJS view engine with [express-generator](https://expressjs.com/en/starter/generator.html).

express --view=ejs referral-program**create : referral-program/
create : referral-program/public/
create : referral-program/public/javascripts/
create : referral-program/public/images/
create : referral-program/public/stylesheets/
create : referral-program/public/stylesheets/style.css
create : referral-program/routes/
create : referral-program/routes/index.js
create : referral-program/routes/users.js
create : referral-program/views/
create : referral-program/views/error.ejs
create : referral-program/views/index.ejs
create : referral-program/app.js
create : referral-program/package.json
create : referral-program/bin/
create : referral-program/bin/wwwchange directory:
$ cd referral-programinstall dependencies:
$ npm installrun the app:
$ DEBUG=referral-program:* npm start

As instructed, we can run this project with the following commands:

cd referral-program
npm install
npm start

Once you have that running, confirm everything is working by visiting localhost:3000. You should see a page that says “Welcome to Express.”

User Interface

In the index.ejs file, we need some code to render email & referral code input fields and a submit button, with a bit of Javascript (jQuery in this case) to submit the data to our server.

The jQuery within the script tag here will submit an AJAX POST request to the / route with the data from the email and referral code input boxes. You can test this by submitting your email now; you should get get a 404 Not Found error, since we haven't implemented a POST handler on that route yet. However, when that route is implemented, you can see that on success it will alert the user with their unique referral link.

If you're using code like mine, you should expect your home page to look like this:

Dependencies

Let's start working on the backend by installing a few dependencies from npm.

We will need:

• mysql for interacting with our MySQL database
• dotenv for accessing environment variables from a .env file
• shortid for generating short, URL-friendly referral codes

Use the following commands to install them:

npm i mysql
npm i dotenv
npm i shortid

app.js

We need to make one small tweak to the app.js file for our project. At the very top of the file, before anything else, add the following line:

require('dotenv').config();

This will allow us to use process.env.VARIABLE_NAME to access environment variables later.

Creating Environment Variables

Create a file called .env at the root of your project, and put your MySQL database credentials inside. Your file should look something like this:

DB_USER=your_database_username
DB_PASS=your_database_password
DB_NAME=your_database_name
DB_HOST=your_database_host

The “POST /” Route

We told our form to submit all entries as POST requests to the / route, so let's define what happens there now. This route needs to:

1. Read the user's submitted data: email and referrer
2. Generate a unique referral code the user can share
3. Put submissions in the database
4. Return the referral code to the client

Congratulations! 🥳

You've successfully implemented a referral program in your app!

To test it out, restart your Node server (ctrl+c to stop it, and npm start to start again) and revisit localhost:3000. Submit your email address, and you should get a pop-up with your unique referral link. Then, if you make another submission using that referral code, you'll see that recorded in the database.

If you're running into any trouble, you can check out the full code in my GitHub repo: https://github.com/adboio/nodejs-referral-program

Further Thoughts

The ideas presented here are the core of a simple referral system, but where you take it from here is up to you! For some inspiration, here's how I use the referral program in my latest product, Shelf:

• I present the referral codes as referral links, such as https://shelf.news/?ref=ea8d222. When a user visits the site using one of these links, the referral code is automatically sent along with the POST request to keep track. This way they never have to actually type out a referral code.
• Users who refer more people to the app are moved up higher on the waitlist. This works by calculating how many referrals each user has, and sorting them that way.