Automating database cleanup with scheduled pipelines

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 demo app 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:

• 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 clone the demo application, run:

git clone -b starter https://github.com/CIRCLECI-GWP/db-cleanup.git

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 the 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

Or, to copy the content from the starter project .env.sample file, run:

cp .env.sample .env

Note: You will need to replace the YOUR_MONGODB_URL placeholder with the connection string you used for your remote MongoDB URI.

Creating a MongoDB Atlas account and database

This tutorial uses MongoDB Atlas database and you can easily set one up.

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.

Run the demo application

When the database has been created and configured, open a terminal and run the demo application:

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": "2023-12-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 you extracted from the MongoDB Atlas dashboard earlier. Go to the Settings page, scroll down, and click the Reveal Config Vars button.

Specify the key and value as shown in the following screenshot. Click Add once you are done.

Now 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

The CircleCI pipeline configuration 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:
  node: circleci/node@5.1.0

jobs:
  build-and-deploy:
    docker:
      - image: "cimg/base:stable"
    steps:
      - checkout
      - node/install
      - run:
          command: npm install
          name: Install dependencies
      - run:
          name: Deploy app to Heroku
          command: |
            git push https://heroku:$HEROKU_API_KEY@git.heroku.com/$HEROKU_APP_NAME.git main
workflows:
  deploy:
    jobs:
      - build-and-deploy

This configuration uses Git to deploy your latest code changes to your Heroku account using your account credentials.

Now, 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 db-clean-up project.

You will be prompted with a couple of options for the configuration file. Select the use the .circleci/config.yml in my repo option. Enter the name of the branch your code is on, 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, open the newly deployed app in your browser. The URL for your application should be in this format: https://<HEROKU_APP_NAME>-<RANDOM_NUMBER>.herokuapp.com/. You can find the generated domain name for your app on the Settings page.

Creating and implementing the scheduled pipeline

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 this tutorial, use the API. You will need the CircleCI API token, the name of the version control system where your repository is housed, your organization name, and the current project id on CircleCI. To get the token, go to your CircleCI dashboard and click your avatar.

You will be redirected to the User Settings page. From there, create a new token, give your token a name, and save it somewhere you can easily find it later.

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

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

Replace the placeholders with the correct values:

• VCS_TYPE: Your version control system, such as github.
• ORG_NAME: Your GitHub username or organization name.
• REPO_NAME: Your project id on CircleCI. For this tutorial it is db-clean-up.
• CIRCLECI_TOKEN: Your CircleCI Token.
• MONGODB_URI: Your MongoDB URI string as extracted from MongoDB Atlas dashboard.

Create a new file named schedule_pipeline.js within the root of your project. Enter this content:

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.REPO_NAME;
const token = process.env.CIRCLECI_TOKEN;
const postScheduleEndpoint = `${API_BASE_URL}/${vcs}/${org}/${project}/schedule`;

async function checkAndChangePermissionAccess() {
  try {
    let res = await axios.post(
      postScheduleEndpoint,
      {
        name: "Check and Change permission",
        description: "Check and revoke permissions assigned to users.",
        "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"],
        },
      },
      {
        headers: { "circle-token": token },
      }
    );
    console.log(res.data);
  } catch (error) {
    console.log(error.response);
  }
}

checkAndChangePermissionAccess();

This code creates a function named *checkAndChangePermissionAccess()* to post pipeline schedule details to CircleCI API. The payload specified are:

• name is the schedule name. It must be unique.
• description: is an optional field used to describe the schedule.
• The payload also includes an attribution-actor, which can be either system for a neutral actor or current. current takes your current user’s permissions (as per the token you use)
• parametersis the branch to trigger was specified within this object and an additional value to check when to run the pipeline.
• timetable: defines when and how frequently to run the 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. In this case, the schedule is set to run 30 times within an hour. That amounts to approximately every 2 minutes.

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

Running the scheduled pipeline

The schedule configuration file is ready! From the root of your project, create the scheduled pipeline by running:

node schedule_pipeline

or npm script command as specified within package.json file:

npm run schedule

You will see output similar to this:

> db-cleanup-starter@1.0.0 schedule
> node schedule_pipeline

{
  description: 'Check and revoke permissions assigned to users.',
  'updated-at': '2023-10-21T10:52:23.159Z',
  name: 'Check and Change permission',
  id: '0e32e3a9-8ac4-4ac2-806a-cb5b1d39c53a',
  'project-slug': 'gh/CIRCLECI-GWP/db-clean-up',
  'created-at': '2023-10-21T10:52:23.159Z',
  parameters: { branch: 'main', 'run-schedule': true },
  actor: {
    login: 'yemiwebby',
    name: 'Oluyemi',
    id: 'ACTOR_ID'
  },
  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' ],
    'days-of-month': [],
    months: []
  }
}

Updating the configuration file

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

version: "2.1"

orbs:
  node: circleci/node@5.1.0

jobs:
  build-and-deploy:
    docker:
      - image: cimg/base:stable
    steps:
      - checkout
      - node/install
      - run:
          command: npm install
          name: Install dependencies
      - run:
          name: Deploy app to Heroku
          command: |
            git push https://heroku:$HEROKU_API_KEY@git.heroku.com/$HEROKU_APP_NAME.git main
  permission:
    docker:
      - image: cimg/base:stable
    steps:
      - run:
          name: Check access and update permissions for users
          command: "curl https://<HEROKU_APP_NAME>-<RANDOM_NUMBER>.herokuapp.com/check-access"
parameters:
  run-schedule:
    type: boolean
    default: false
workflows:
  deploy:
    when:
      not: << pipeline.parameters.run-schedule >>
    jobs:
      - build-and-deploy
  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. Review 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. You may want to run this database cleanup workflow every night.

Conclusion

Scheduled pipelines can trigger your builds at a specific interval. When you let automation handle continuous integration, deployment, and automated actions you gain time 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.