Automating database cleanup with scheduled pipelines

This tutorial covers:

1. Cloning a sample application
2. Creating and configuring a scheduled pipeline
3. Running the scheduled pipeline

RESTful API projects often require that developers grant temporary access to a particular resource. Sometimes this happens within a specific interval, such as a few days or months. Revoking permissions when they expire could mean including extra logic during the authentication process or writing a middleware function to attach to the secured endpoint. Or, this logic could be abstracted to a separate part and configured to check and manage permissions at a regular interval.

In this tutorial, I will show you how to automate your database cleanup and revoke temporary access granted to users of your API using scheduled pipelines. For simplicity, I have created a demo application for you to clone and deploy to Heroku. This application uses MongoDB to persist data.

This tutorial continues in Schedule database backups for MongoDB in a Node.js app.

Prerequisites

Here is what you need to follow this tutorial properly:

• Node.js installed on your computer
• A CircleCI account
• A GitHub account
• A Heroku account
• A free MongoDB Atlas account or its equivalent
• An API testing tool, such as Postman
• Basic knowledge of JavaScript

Cloning the demo application

To get started, run the following command to clone the demo application:

git clone https://github.com/yemiwebby/db-cleanup-starter.git db-clean-up

Next, move into the newly cloned app and install all its dependencies:

cd db-clean-up
npm install

This application contains the following endpoints:

• /create-permission is an endpoint to create users with a specific date for access expiration.
• /check-access will be used to make sure that the temporary access end date for users is less than the current date. If yes, the access for such user will be revoked.
• The /secured endpoint takes the email address of a particular user as a route parameter and checks whether or not the user can access the secured resources.
• /all-access shows the list of users.

When the installation process has finished, create a .env file and populate it with this:

MONGODB_URI=YOUR_MONGODB_URL

If you would rather, you can simply run the following command to copy the content from .env.sample file within the starter project:

cp .env.sample .env

Of course, you need to replace the YOUR_MONGODB_URL placeholder with the connection string you used for your remote MongoDB URI. This tutorial uses MongoDB Atlas database and you can easily set one up. I will explain how do that next.

Creating a MongoDB Atlas account and database

Create a free Atlas account here and follow the instructions to deploy a free tier cluster. Once you have a cluster and database user set up, open and edit the .env file.

Replace the YOUR_MONGODB_URL placeholder with the extracted connection string from your MongoDB Atlas dashboard:

MONGODB_URI=mongodb+srv://<username>:<password>@<clustername>.mongodb.net/<dbname>?retryWrites=true&w=majority

Replace the <username>, <password>, <clustername> and <dbname> with the values for your cluster.

Running the demo application

When the database is properly created and configured, open a terminal and run the demo application with:

npm run start

You will get this output:

> db-cleanup-starter@1.0.0 start
> node server.js

Server is running at port 3000
Connected successfully

Create users with permissions

In Postman, make a POST request to the http://localhost:3000/create-permission endpoint using this JSON data:

{
  "username": "sample",
  "email": "sample@mail.com",
  "endDate": "2021-07-30",
  "hasAccess": true
}

Create more users:

{
    "username": "demo",
    "email": "demo@mail.com" ,
    "endDate": "2021-05-22",
    "hasAccess": true
}


{
    "username": "webby",
    "email": "webby@mail.com" ,
    "endDate": "2022-07-30",
    "hasAccess": true
}

You have just created users with endDate values for temporary access and a hasAccess flag to indicate their ability to access the database. This is set to false by default as defined by the UserSchema in /models.js:

const mongoose = require("mongoose");

const UserSchema = new mongoose.Schema({
  username: {
    type: String,
    required: true,
  },
  email: {
    type: String,
    required: true,
  },
  endDate: {
    type: Date,
    default: Date.now(),
  },
  hasAccess: {
    type: Boolean,
    default: false,
  },
});
const User = mongoose.model("User", UserSchema);

module.exports = User;

Retrieving secured resources

At the moment, all the created users can view and retrieve the secured resources from the /secured endpoint by sending a GET request with an email address as a route parameter.

Testing the check-access endpoint locally

The /check-access endpoint, as defined in the ./routes.js file, will check if the endDate for each user is less than the current date and then revoke its access if yes.

app.get("/check-access", async (request, response) => {
  const result = await userModel.updateMany(
    { endDate: { $lt: Date.now() } },
    { hasAccess: false }
  );

  try {
    response.send(result);
  } catch (error) {
    response.status(500).send(error);
  }
});

Switch to Postman to try it out.

Finally, retrieve the list of all users.

At this point, users whose endDate is less than the current date have been denied access, as indicated with the hasAccess flag. The process of sending GET HTTP requests to the /check-access endpoint is what we want to automate.

Creating an application on Heroku

Next, create a new application on Heroku to host and run the Node.js project. Go to the Heroku dashboard to begin. Click New and then New App. Fill in the form with a name for your application and your region.

Note: Application names on Heroku are unique. Pick one that is available and make a note of it.

Click the Create app button. You will be redirected to the Deploy view of your newly created application.

Next, create a configuration variable to reference the MongoDB URI that was extracted from the MongoDB Atlas dashboard earlier. To do that, navigate to the Settings page, scroll down, and click the Reveal Config Vars button.

Specify the key and value as shown here, and click Add once you are done.

Lastly, you need to retrieve the API key for your Heroku account. This key will be used to connect your CircleCI pipeline to Heroku. To get your API key, open the Account Settings page.

Scroll to the API keys section.

Click the Reveal button and copy the API key. Save it somewhere you can easily find it later.

Adding the pipeline configuration script

Next, we need to add the pipeline configuration for CircleCI. The pipeline will consist of steps to install the project’s dependencies and compile the application for production.

At the root of your project, create a folder named .circleci. In that folder, create a file named config.yml. In the newly created file, add this configuration:

version: 2.1
orbs:
  heroku: circleci/heroku@1.2.6
jobs:
  build:
    executor: heroku/default
    steps:
      - checkout
      - heroku/install
      - heroku/deploy-via-git:
          force: true
workflows:
  deploy:
    jobs:
      - build

This configuration pulls in the Heroku orb circleci/heroku, which automatically provides access to a robust set of Heroku jobs and commands. One of those jobs is heroku/deploy-via-git, which deploys your application straight from your GitHub repo to your Heroku account.

Next, set up a repository on GitHub and link the project to CircleCI. Review Pushing a project to GitHub for step-by-step instructions.

Log in to your CircleCI account. If you signed up with your GitHub account, all your repositories will be available on your project’s dashboard.

Click Set Up Project for your database-clean-up project.

You will be prompted with a couple of options regarding the configuration file. Select the use the .circleci/config.yml in my repo option. Enter the name of the branch where your code is housed on GitHub, then click the Set Up Project button.

Your first workflow will start running, but it will fail. This is because you have not provided your Heroku API key. You can fix that now.

Click the Project Settings button, then click Environment Variables. Add these two new variables:

• HEROKU_APP_NAME is the app name in Heroku (database-clean-up)
• HEROKU_API_KEY is the Heroku API key that you retrieved from the account settings page

Select Rerun Workflow from Failed to rerun the Heroku deployment. This time, your workflow will run successfully.

To confirm that your workflow was successful, you can open your newly deployed app in your browser. The URL for your application should be in this format https://<HEROKU_APP_NAME>.herokuapp.com/. For this tutorial project, I used: ‘https://database-clean-up.herokuapp.com‘

Creating and implementing the scheduled pipeline

As stated in the CircleCI official documentation, there are two different options for setting up a scheduled pipeline:

1. Using the API
2. Using project settings

For the sake of this tutorial and its objectives, we will use the API. To facilitate that, we will need the CircleCI API token, the name of the version control system where your repository is housed, your organisation name, and the current project id on CircleCI. To obtain the token, navigate to your CircleCI dashboard and click on your avatar:

You will be redirected to your User Settings page. From there, navigate to Personal API Tokens, create a new token, give your token a name and save it somewhere safe.

Now, open the .env file from the root of your project and add the following:

VCS_TYPE=VERSION_CONTROL_SYSTEM
ORG_NAME=ORGANISATION_NAME
PROJECT_ID=PROJECT_ID
CIRCLECI_TOKEN=YOUR_CIRCLECI_TOKEN
MONGODB_URI=YOUR_MONGODB_URL

Replace the placeholders with the appropriate values:

• VCS_TYPE: Your version control system, such as github.
• ORG_NAME: Your GitHub username or organisation name
• PROJECT_ID: Your project Id on CircleCI. db-clean-up in our case
• CIRCLECI_TOKEN: Your CircleCI Token
• MONGODB_URI: Your MongoDB URI string as extracted from MongoDB Atlas dashboard.

The next thing to do is create a new file named schedule.js within the root of your project and use the following content for it:

const axios = require("axios").default;
require("dotenv").config();
const API_BASE_URL = "https://circleci.com/api/v2/project";

const vcs = process.env.VCS_TYPE;
const org = process.env.ORG_NAME;
const project = process.env.PROJECT_ID;
const token = process.env.CIRCLECI_TOKEN;
const postScheduleEndpoint = `${API_BASE_URL}/${vcs}/${org}/${project}/schedule`;

async function scheduleDatabaseBackup() {
  try {
    let res = await axios.post(
      postScheduleEndpoint,
      {
        name: "Database backup",
        description: "Schedule database backup for your app in production",
        "attribution-actor": "current",
        parameters: {
          branch: "main",
          "run-schedule": true,
        },
        timetable: {
          "per-hour": 30,
          "hours-of-day": [
            0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
            19, 20, 21, 22, 23,
          ],
          "days-of-week": ["MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN"],
        },
      },
      {
        headers: { "circle-token": token },
      }
    );
    console.log(res.data);
  } catch (error) {
    console.log(error.response);
  }
}
scheduleDatabaseBackup();

Here, we created a function named **scheduleDatabaseBackup()** to post pipeline schedule details to CircleCI API. The payload specified are:

• name: This is the schedule name. It needs to be unique.
• description: This is an optional field and is used to describe the schedule.
• The payload also includes an attribution-actor, which can be either system for a neutral actor or current, which takes your current user’s permissions (as per the token you use)
• parameters: The branch to trigger was specified within this object and an additional value to check when to run the pipeline.
• timetable: This is where we define when and how frequently to run our scheduled pipelines. The fields to use here are per-hour, hours-of-day, and days-of-week. Note that this does not take a cron expression, making it more easily parsable by humans reasoning with the API. Here we set the schedule to run 30 times within an hour. That amounts to approximately every 2 minutes.

And lastly, the CircleCI token was passed within the header.

Updating the configuration file

Before running the scheduled pipeline, we need to update the CircleCI pipeline configuration script. Open .circleci/config.yml file and replace its content with this:

version: 2.1
orbs:
  heroku: circleci/heroku@1.2.6
jobs:
  build:
    executor: heroku/default
    steps:
      - checkout
      - heroku/install
      - heroku/deploy-via-git:
          force: true
  schedule_backup:
    working_directory: ~/project
    docker:
      - image: cimg/node:17.4.0
    steps:
      - checkout
      - run:
          name: Install MongoDB Tools.
          command: |
            npm install
            sudo apt-get update
            sudo apt-get install -y mongodb
      - run:
          name: Run database back up
          command: npm run backup
parameters:
  run-schedule:
    type: boolean
    default: false
workflows:
  deploy:
    when:
      not: << pipeline.parameters.run-schedule >>
    jobs:
      - build
  backup:
    when: << pipeline.parameters.run-schedule >>
    jobs:
      - schedule_backup

Here, we have included a new job named schedule_backup. It uses the docker image to install Node.js and MongoDB tools. Also, we included parameters and used the pipeline variable called run-schedule to check when to run the workflows. For all workflows, add when expressions that indicate to run them when run-schedule is true and not to run other workflows unless run-schedule is false.

Creating more environment variables on CircleCI

Just before you add and push all updates to GitHub, you will add the MongoDB connection string, Azure Account Name, and Key as environment variables on your CircleCI project. From the current project pipelines page, click on the Project Settings button. Next, select Environment Variables from the side menu and add the following variables:

• ACCOUNT_KEY: This is your Microsoft Azure storage account key
• ACCOUNT_NAME: This is the Microsoft Azure storage account name, which is dbblobs for this tutorial
• MONGODB_URI: Your MongoDB connection string.

At the moment, your environment variables page should look like this:

Now, update git and push your code back to GitHub.

Running the scheduled pipeline for database cleanup

With the schedule configuration file ready you can run the pipeline. From the root of your project, run this command:

node schedule_pipeline

Or you could use the npm script command as specified within package.json file:

npm run schedule

Your output should be similar to this:

{
  description: 'Check and revoke permissions assigned to users.',
  'updated-at': '2022-02-06T13:54:01.993Z',
  name: 'Check and Change permission',
  id: '821f4190-7261-4433-ae08-d5d41d700879',
  'project-slug': 'gh/CIRCLECI-GWP/database-clean-up',
  'created-at': '2022-02-06T13:54:01.993Z',
  parameters: { branch: 'main', 'run-schedule': true },
  actor: {
    login: 'daumie',
    name: 'Dominic Motuka',
    id: '335b50ce-fd34-4a74-bc0b-b6455aa90325'
  },
  timetable: {
    'per-hour': 30,
    'hours-of-day': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23],
    'days-of-week': [ 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN' ]
  }
}

Updating the configuration file

Navigate to the .circleci/config.yml file and update its content as shown here:

version: 2.1
orbs:
  heroku: circleci/heroku@1.2.6
jobs:
  build:
    executor: heroku/default
    steps:
      - checkout
      - heroku/install
      - heroku/deploy-via-git:
          force: true
  permission:
    docker:
      - image: cimg/node:17.4.0
    steps:
      - run:
          name: Check access and update permissions for users
          command: "curl https://<YOUR_HEROKU_APP_NAME>.herokuapp.com/check-access"
parameters:
  run-schedule:
    type: boolean
    default: false

workflows:
  deploy:
    when:
      not: << pipeline.parameters.run-schedule >>
    jobs:
      - build
  check_permissions:
    when: << pipeline.parameters.run-schedule >>
    jobs:
      - permission

This configuration includes a new job named permission. It uses the Docker image to install Node.js and runs a curl command that calls the check-access endpoint for the deployed application. The configuration also includes parameters and uses the pipeline variable called run-schedule to check when to run the workflows. For all workflows, add when expressions that indicate to run them when run-schedule is true and not to run other workflows unless run-schedule is false.

Now, update git and push your code back to GitHub. View your pipeline on CircleCI.

The pipeline will run every 2 minutes. This interval is very short, but it is just for the demo.

In production, you would not want to trigger a pipeline at such a very short interval. You can modify the content of the ./schedule_pipeline.js file and make adjustments to fit your workflow. Running this database cleanup workflow every night might be a good idea.

Conclusion

Scheduled pipelines can trigger your builds at a specific interval. Let automation handle continuous integration, deployment, and automated actions for your application. Use the time you save to focus on implementing more features, writing more tests, and fixing bugs faster. Your team will love it!

For additional information about scheduled pipelines on CircleCI, refer to the official documentation.

I do hope that you found this helpful. The complete source code can be found on GitHub.

---

Oluyemi is a tech enthusiast with a background in Telecommunication Engineering. With a keen interest in solving day-to-day problems encountered by users, he ventured into programming and has since directed his problem-solving skills at building software for both web and mobile. A full-stack software engineer with a passion for sharing knowledge, Oluyemi has published a good number of technical articles and blog posts on several blogs around the world. Being tech-savvy, his hobbies include trying out new programming languages and frameworks.

---

Oluyemi is a tech enthusiast with a background in Telecommunication Engineering. With a keen interest in solving day-to-day problems encountered by users, he ventured into programming and has since directed his problem solving skills at building software for both web and mobile. A full stack software engineer with a passion for sharing knowledge, Oluyemi has published a good number of technical articles and blog posts on several blogs around the world. Being tech savvy, his hobbies include trying out new programming languages and frameworks.

Read more posts by Olususi Oluyemi