Building a Personal Habit Tracker App with Custom DB Queries in Strapi

Strapi is a headless CMS built entirely with JavaScript. Whether your preference leans more toward REST or GraphQL, Strapi offers built-in support for both. As a result, you can use the frontend of your choice and simply connect to the Strapi API.

Strapi also offers some powerful database engines for you to create complex queries. In November of 2021, Strapi announced the launch of Strapi v4, which comes with the Query Engine API. With this new feature, developers are now able to create custom database queries, like joining two collections to filter and fetch data. This is particularly helpful for developers who have complex needs. For example, in the context of a blog, you could query all the categories used in 2022 and fetch only the relevant articles.

In this article, you’ll be building a personal habit tracker. To begin, you will set up your Strapi backend project and build a few collections for your habits and logs to track completion. Then you’ll set up your React project to create, display, and check off habits. Finally, you’ll discover how to create custom queries to fetch habits so it’s easier to track completion on the frontend.

At the end of this tutorial, you’ll have a habit tracker that will look like this:

Custom DB Queries in Strapi

As previously mentioned, Strapi v4 provides a Query Engine API that allows you to interact directly with the database by writing custom queries. Strapi’s REST API is extensive and comes with a lot of filtering options that will suit a wide range of developers. This includes options to filter whether a field is equal, greater, or smaller than a specified value. This flexibility lets developers query specific data without writing custom code. However, for projects with complex needs, it’s easier, and sometimes necessary, to give developers more direct access to their database.

For example, in your habit tracker project, you could do two API calls: one for the habits and another for the logs to check if a habit was completed that day. Or you could create a custom API endpoint that would return your list of habits. Each of these would include a completed flag that would be set to true or false depending on your logs. This level of customization is one of the major advantages of the Query Engine API.

Another advantage is bulk operations. Whether it’s inserting, updating, deleting, or aggregating, developers can easily and quickly write database operations with the Query Engine API.

Build a Habit Tracker App with Strapi

In this tutorial, you will build a habit tracker application. This project includes a Strapi backend to store your data and a React client to interact with it.

Set Up a Strapi Project

In a new terminal window, start by creating your Strapi backend:

    npx create-strapi-app@latest my-project --quickstart

Once completed, your Strapi server will launch automatically and prompt you to register your first administrator:

After you’re done filling out your information, select Let’s start, and you’re ready to start creating some types.

Note: If you’re unfamiliar with the concept of types in Strapi, don’t hesitate to check out Strapi’s documentation to discover the different kinds of types.

Under Plugins, click on Content-Type Builder. Then under Collection Types, click on Create new collection type. This will open a window where you’ll be able to create your first type. In the input labeled Display name, enter “Habit”:

After clicking on Continue, you’ll be able to create the first field of your collection. For your habits, you’ll need two kinds of data. The first one is its name. The second is its type (to know whether the habit is part of the morning, afternoon, or evening routine).

To accomplish this, select the Text option. In the input labeled Name, put “name” and keep the Short text option selected. Then click on Add another field:

For the type, you need a field of type Enumeration. In the input labeled Name, put “type”, and for the values, add the following:

    morning
    afternoon
    evening

This will guarantee that your type can only be one of those three approved values. Here’s what this step looks like:

Finally, select Finish.

You should now see your content type with your two fields. Click Save to persist your changes:

Next, continue by creating a new collection type called Habit Log. As the name suggests, the habit log type will keep a list of which habits were completed and when. As a result, your habit log type will have two fields:

1. habit: ID of the habit completed
2. completionDate: date when the habit was checked off

To create this type, click on Create a collection type again, but this time, input “Habit Log” in the Display name input. Then click on Continue.

For the first field, you’ll use Relation to create a relationship with the Habit collection. In the Field name, put “habit” and select the first relationship type. By choosing this type, you specify that the habit log has only one habit, but multiple logs can have the same habit (ie with different dates). Click on Add another field:

For the second field, choose the Date field type and enter “completionDate” as its name. For the Type, select date (ex: 01/01/2022):

Click on Finish and then Save to persist your new collection type.

Set Up a React Frontend

Now that your Strapi backend is set up, it’s time to get your frontend up and running.

When you’re done setting up the frontend, it will look like this:

When completed, your Habit Picker application will have the following functionalities:

1. A form with the component CreateHabitForm that lets you or your users create new habits.
2. A calendar with the component Calendar to select the day as well as view your habits and progress for that day.
3. A Habits component that regroups your three sections (morning, afternoon, and evening).
4. A list of habits of a particular type with the component HabitsList. This component will contain dummy data until you connect it to the backend.

For this tutorial, you’ll use the MUI library (previously called Material-UI) to get access to a built-in datepicker. Thankfully, MUI comes with a starter project with Create React App.

To get started, in a new terminal window, run the following commands:

    curl https://codeload.github.com/mui/material-ui/tar.gz/master | tar -xz --strip=2 material-ui-master/examples/create-react-app
    cd create-react-app
    npm install

Then install a few necessary libraries. In this project, you’ll be using one of the four date-libraries supported called date-fns. You also need axios for making HTTP requests and qs for parsing/creating queries. You can get all the libraries needed by running one command:

    npm install @mui/x-date-pickers @date-io/date-fns date-fns axios qs

Inside your project and your src folder, create a new file called Calendar.js.

Because the date selected by the calendar will be shared not only with the calendar but also with the list of habits, you need to set this variable at the App.js level and treat it as if you will receive calendarDate and setCalendarDate as props from the parent.

You should also set the maxDate to today, as this prevents users from selecting future dates and checking off habits in advance:

    import * as React from 'react';

    import Grid from '@mui/material/Grid';
    import TextField from '@mui/material/TextField';

    import { StaticDatePicker } from '@mui/x-date-pickers/StaticDatePicker';
    import { AdapterDateFns } from '@mui/x-date-pickers/AdapterDateFns';
    import { LocalizationProvider } from '@mui/x-date-pickers/LocalizationProvider';

    export default function Calendar({ calendarDate, setCalendarDate }) {
      return (
        <Grid item xs={6}>
          <LocalizationProvider dateAdapter={AdapterDateFns}>
            <StaticDatePicker
              displayStaticWrapperAs="desktop"
              openTo="day"
              value={calendarDate}
              maxDate={new Date()}
              onChange={(newValue) => {
                setCalendarDate(newValue);
              }}
              renderInput={(params) => <TextField {...params} />}
            />
          </LocalizationProvider>
        </Grid>
      )
    }

In the same src folder, create a new file called CreateHabitForm.js. This component is a very straightforward form, and you need to create three variables: name, type, and alert.

MUI comes with all the pre-built components you would need, including the following:

• TextField for inputs
• InputLabel for labels
• Select and Menu Item to display a drop-down
• FormControl for styling your form
• Button to render a button
• Alert to display a banner for messages (whether successful or not)

With the mentioned components, you can quickly put a form together and link your name and type variables along with their setter functions. Here is the result:

    import * as React from 'react';

    import Grid from '@mui/material/Grid';
    import Alert from '@mui/material/Alert';

    import FormControl from '@mui/material/FormControl';
    import TextField from '@mui/material/TextField';
    import Select from '@mui/material/Select';
    import MenuItem from '@mui/material/MenuItem';
    import InputLabel from '@mui/material/InputLabel';
    import Button from '@mui/material/Button';

    export default function CreateHabitForm() {
      const [name, setName] = React.useState("");
      const [type, setType] = React.useState("morning");
      const [alert, setAlert] = React.useState({message: null, type: null});

      return(
        <Grid container spacing={2} direction="column">
          {alert.type && <Alert severity={alert.type}>{alert.message}</Alert>}
          <Grid item>
            <FormControl fullWidth>
              <TextField
                id="habit-name"
                label="Name"
                variant="outlined"
                value={name}
                onChange={(event) => setName(event.target.value)}/>
            </FormControl>
          </Grid>
          <Grid item>
            <FormControl fullWidth>
              <InputLabel id="habit-project-select-type-label">Type</InputLabel>
              <Select
                labelId="habit-project-select-type-label"
                id="habit-project-select-type"
                value={type}
                label="Type"
                autoWidth
                onChange={(event) => setType(event.target.value)}
              >
                <MenuItem value="morning">Morning</MenuItem>
                <MenuItem value="afternoon">Afternoon</MenuItem>
                <MenuItem value="evening">Evening</MenuItem>
              </Select>
            </FormControl>
          </Grid>
          <Grid item>
            <Button variant="contained">Add</Button>
          </Grid>
        </Grid>
      )
    }

Inside your src folder, create a file called HabitsList.js. This component will receive a type (morning, afternoon, or evening) and will display a list of habits belonging to this type. Each habit comes with a checkbox that the user can tick off as the habit is completed.

To accomplish this, MUI has a few interesting components:

• List: is the entire list of habits
• ListItem: has one for each habit
• ListItemButton: makes the ListItem clickable
• ListItemIcon: renders the checkbox in-line and in front of your text in the ListItem
• ListItemText: is pretty explicit but, essentially, the text of your list

When combined, you should have something like this:

    import * as React from 'react';

    import Grid from '@mui/material/Grid';
    import Typography from '@mui/material/Typography';

    import List from '@mui/material/List';
    import ListItem from '@mui/material/ListItem';
    import ListItemButton from '@mui/material/ListItemButton';
    import ListItemIcon from '@mui/material/ListItemIcon';
    import ListItemText from '@mui/material/ListItemText';

    import Checkbox from '@mui/material/Checkbox';

    export default function Habits({type}) {

      const handleToggle = (value) => () => {};

      return(
        <Grid item>
          {/*  Title of the section aka Morning routine */}
          <Typography variant="h6" gutterBottom component="div">
            {type.replace(/^\w/, (c) => c.toUpperCase())} routine
          </Typography>
          {/* Whole list. You are using dummy data [0, 1, 2, 3] just for display */}
          <List sx={{ width: '100%' }}>
          {[0, 1, 2, 3].map((value) => {
            const labelId = `checkbox-list-label-${value}`;
            return (
              <ListItem
                key={value}
                disablePadding
              >
                <ListItemButton role={undefined} onClick={handleToggle(value)} dense>
                  <ListItemIcon>
                    <Checkbox
                      edge="start"
                      checked={false}
                      tabIndex={-1}
                      disableRipple
                      inputProps={{ 'aria-labelledby': labelId }}
                    />
                  </ListItemIcon>
                  <ListItemText id={labelId} primary={`Line item ${value + 1}`} />
                </ListItemButton>
              </ListItem>
            );
          })}
        </List>
        </Grid>
      )
    }

Now, you need to create your section with your three types of habits. Inside the src folder, create a file called Habits.js. Inside this, you will receive calendarDate as props from the parent and display it. You also need to import your newly created HabitsList and create three lists: one for the morning, one for the afternoon, and one for the evening.

Following is the result:

    import * as React from 'react';
    import { format } from 'date-fns';

    import Grid from '@mui/material/Grid';
    import Typography from '@mui/material/Typography';

    import HabitsList from './HabitsList';

    export default function Habits({calendarDate}) {
      return(
      <>
        <Typography variant="h5" gutterBottom component="div">
          {format(calendarDate, "eeee, d LLLL yyyy")}
        </Typography>
        <Grid container spacing={2} direction="column">
          <HabitsList type="morning" />
          <HabitsList type="afternoon" />
          <HabitsList type="evening" />
        </Grid>
      </>
      )
    }

With your App.js file created, you can begin to piece together your frontend. All you need now is to import your new components—Habits, CreateHabitForm, and Calendar—and set up your UI:

    import * as React from 'react';

    import Container from '@mui/material/Container';
    import Grid from '@mui/material/Grid';
    import Typography from '@mui/material/Typography';

    import TextField from '@mui/material/TextField';

    import Habits from './Habits';
    import CreateHabitForm from './CreateHabitForm';
    import Calendar from './Calendar';

    export default function App() {
      const [calendarDate, setCalendarDate] = React.useState(new Date());

      return (
        <Container maxWidth="md" style={{ paddingTop: '20px'}}>
          {/* Main Container */}
          <Grid container spacing={2} direction="column">
            {/* Form + Calendar*/}
            <Grid container spacing={2}>
              <Grid item xs={6}>
                <Typography variant="h4" component="div" gutterBottom>
                  Habit Picker project
                </Typography>
                <Typography variant="body1" gutterBottom>
                  Form
                </Typography>
                { /* Form to add habits */ }
                <CreateHabitForm />
              </Grid>
              { /* Calendar */ }
              <Calendar calendarDate={calendarDate} setCalendarDate={setCalendarDate} />
            </Grid>
            {/* Habits Table */}
            <Habits calendarDate={calendarDate} />
          </Grid>
        </Container>
      );
    }

Save your files and start your frontend server. When launched, you should see the following at http://localhost:3000/:

Post and Fetch Data from Strapi

Now that your backend and frontend are set up, it’s time to post and fetch data to and from your Strapi application.

The first things you need to do is head to your Strapi backend and navigate to Settings. Then under Users & Permissions Plugin, click on Roles > Public. Under Permissions, you will see your content type habit and habit-log. For both of them, click on the arrow to expand; then Select all > Save. This allows you to make HTTP requests from your frontend.

Note: If you get a 403 Forbidden error when making calls, you may have missed a step.

Here’s how this step looks:

Now, go back to your frontend application and head to CreateHabitForm.js.

Inside, import axios to help create requests. Then add a handleSubmit on your button, and in your function, make a POST request to create a new habit. If the request is successful, clean the form and display a success message. If not, display an error message.

The result looks like this:

    import * as React from 'react';
    import axios from "axios";

    ...

    export default function CreateHabitForm() {
      ...

      const handleSubmit = () => {
        setAlert({ message: null, type: null})
        axios
          .post('http://localhost:1337/api/habits', {
            data: {
              name,
              type
            }
          })
          .then((response) => {
            setName("")
            setType("")
            setAlert({ message: 'Habit created', type: 'success'})
          })
          .catch((error) => {
            console.log(error);
            setAlert({ message: 'An error occurred', type: 'error'})
          });
      }
      return(
        <Grid container spacing={2} direction="column">
          {alert.type && <Alert severity={alert.type}>{alert.message}</Alert>}
          ...
          <Grid item>
            <Button variant="contained" onClick={handleSubmit}>Add</Button>
          </Grid>
        </Grid>
      )
    }

In your Habits.js component, pass the calendarDate to your HabitsList component. This is useful for knowing when a habit is completed. Here’s the code to use:

    import * as React from 'react';
    import { format } from 'date-fns';

    import Grid from '@mui/material/Grid';
    import Typography from '@mui/material/Typography';

    import HabitsList from './HabitsList';

    export default function Habits({calendarDate}) {
      return(
      <>
        <Typography variant="h5" gutterBottom component="div">
          {format(calendarDate, "eeee, d LLLL yyyy")}
        </Typography>
        <Grid container spacing={2} direction="column">
          <HabitsList type="morning" calendarDate={calendarDate}/>
          <HabitsList type="afternoon" calendarDate={calendarDate}/>
          <HabitsList type="evening" calendarDate={calendarDate}/>
        </Grid>
      </>
      )
    }

Finally, in your HabitsList.js component, create a habits variable that includes your list of habits, which will be used instead of dummy data. To fetch your habits, use useEffect so that upon rendering and calendarDate changes, the component fetches the list from Strapi. The calendar date will be helpful in the future when you need the logs.

Strapi offers lots of cool filters, and you can do a comparison (equal, greater than, smaller than, etc.) on any field in your collection. In order to filter and only get habits of a particular type, you need to use qs to create the query parameters to add to your URL:

    const query = qs.stringify({
        filters: {
          type: {
            $eq: type,
         },
       },
     }, {
          encodeValuesOnly: true,
    });

Finally, create a completeHabit function. This is added to the checkbox and creates a log for a particular habit on the date specified by the calendar. This means that if you forget to mark it as completed yesterday, you can select yesterday’s date on the calendar and check it off. You can use this code:

    import * as React from 'react';
    import axios from "axios";
    import * as qs from 'qs'

    import Grid from '@mui/material/Grid';
    import Typography from '@mui/material/Typography';

    import List from '@mui/material/List';
    import ListItem from '@mui/material/ListItem';
    import ListItemButton from '@mui/material/ListItemButton';
    import ListItemIcon from '@mui/material/ListItemIcon';
    import ListItemText from '@mui/material/ListItemText';

    import Checkbox from '@mui/material/Checkbox';

    export default function Habits({type, calendarDate}) {
      const [habits, setHabits] = React.useState([]);

      React.useEffect(() => {
        const query = qs.stringify({
          filters: {
            type: {
              $eq: type,
            },
          },
        }, {
          encodeValuesOnly: true,
        });

        axios.get(`http://localhost:1337/api/habits?${query}`)
        .then((response) =>{
          setHabits(response.data.data)
        })
        .catch((error) => console.log(error))
      }, [calendarDate])

      const completeHabit = (habitId) => () => {
        axios
          .post('http://localhost:1337/api/habit-logs', {
            data: {
              habit: habitId,
              completionDate: calendarDate
            }
          })
          .then((response) => {
            console.log(response)
          })
          .catch((error) => {
            console.log(error);
          });
      };

      return(
        <Grid item>
          <Typography variant="h6" gutterBottom component="div">
            {type.replace(/^\w/, (c) => c.toUpperCase())} routine
          </Typography>
          <List sx={{ width: '100%' }}>
          {habits.map((habit) => {
            const { id, attributes } = habit
            const { name } = attributes
            const labelId = `checkbox-list-label-${id}`;
            return (
              <ListItem
                key={id}
                disablePadding
              >
                <ListItemButton role={undefined} onClick={completeHabit(id)} dense>
                  <ListItemIcon>
                    <Checkbox
                      edge="start"
                      checked={false}
                      tabIndex={-1}
                      disableRipple
                      inputProps={{ 'aria-labelledby': labelId }}
                    />
                  </ListItemIcon>
                  <ListItemText id={labelId} primary={name} />
                </ListItemButton>
              </ListItem>
            );
          })}
        </List>
        </Grid>
      )
    }

At this point, you should be able to create new habits. When refreshing the page, the habits will appear in their corresponding list (ie your morning habits will be displayed under the Morning heading). If you click on a checkbox, a log will be created for that habit and on that date in your Strapi database, too. But how can you show that this habit has been completed?

This is the point where you could make a REST API endpoint to /habit-log, retrieve the logs for a specific day, and see if the habit of the log matches the habit listed. However, this would be a lot of unnecessary logic. It’s easier if the habits are retrieved for a specific type and day, so Strapi sends this instead:

    {
      id: 1,
      name: "Drink water",
      type: "morning",
      completed: true,
      ...
    }

This cannot be accomplished with REST, but you can use the Query Engine API.

Implement Custom Queries in Strapi

Instead of interacting with the Admin UI, http://localhost:1337/admin/, of Strapi, in this tutorial, you’ll be getting your hands dirty in the code to implement a custom query.

To start, head to src/api/habit/routes and create a new file custom-habits.js. Inside this file, define a new custom endpoint called /get-habits-with-logs and tell it to use the function getHabitWithLogs in the controller custom-habit:

    'use strict';

    module.exports = {
      "routes": [
        {
          "method": "GET",
          "path": "/get-habits-with-logs",
          "handler": "custom-habit.getHabitWithLogs",
          "config": {
            "policies": []
          }
        },
      ]
    };

Then in src/api/habit/controllers, create a new file called custom-habit.js. Inside, you’ll add the getHabitWithLogs function.

The first thing you need are the calendarDate and type values, which come as query parameters from your request. You can get them from ctx.request.query.

Then create a query for all the habits of that particular type. Once you have all the habits, you need to make a second query for each habit to the habit-log type. Here, you fetch all the logs where the ID of the habit on the log matches the habits you just retrieved. The completionDate will also match the calendarDate requested. If the ID and the date match, that means that the habit was marked as completed for that date. Then you can add a new key called completed on your habit object and pass it as true or false depending on whether the list of logs was empty or not.

Note: You need to make sure that your query for the logs is async and inside a map(). This means that habits_with_completed will return a list of promises. To execute them, you need to use await Promise.all(habits_with_completed);.

Here is the result:

    'use strict';

    const { createCoreController } = require('@strapi/strapi').factories;
    const habitModel = "api::habit.habit";
    const habitLogModel = 'api::habit-log.habit-log';

    module.exports = createCoreController(habitModel, ({ strapi }) => ({
      async getHabitWithLogs(ctx, next) {
        const { calendarDate, type } = ctx.request.query
        try {
          // Query all the habits of a particular type (say morning)
          const habits = await strapi.db.query(habitModel).findMany({
            where: {
              type: {
                $eq: type,
              }
            },
          });

          const habits_with_completed = habits.map(async (habit, i) => {
            // For each habit, query the logs
            // to see if there were completed that day
            let entries_logs = await strapi.db.query(habitLogModel).findMany({
              where: {
                $and: [
                  {
                    habit: habit.id,
                  },
                  {
                    completionDate: { $eq: calendarDate },
                  },
                ],
              },
            });
            // Add a completed key based on the logs found for that day
            return {
              completed: entries_logs.length > 0,
              ...habit
            }
          });
          ctx.body = await Promise.all(habits_with_completed);
        } catch (err) {
          ctx.body = err;
        }
      }
    }));

Query Your New Custom Endpoint

Once your file is saved, your Strapi server will restart with your new endpoint. Don’t forget to enable permissions for the endpoint by going back to Settings > Roles > Public and selecting all for custom-habit, too. Otherwise, you’ll get the 403 Forbidden error.

Here’s how to enable the new endpoint:

Now, call your new endpoint in HabitsList.js. In your query parameters, pass the formatted date and the type with calendarDate=${format(calendarDate, "yyyy-MM-dd")}&type=${type}. Then retrieve id, name, and completed; and use the latter to set the checkbox correctly.

You should also move the fetching of the data into its own function called fetchData() so when you complete a habit, you can refetch the data so the list is up-to-date:

    import * as React from 'react';
    import axios from "axios";
    import * as qs from 'qs'
    import { format } from 'date-fns';

    import Grid from '@mui/material/Grid';
    import Typography from '@mui/material/Typography';

    import List from '@mui/material/List';
    import ListItem from '@mui/material/ListItem';
    import ListItemButton from '@mui/material/ListItemButton';
    import ListItemIcon from '@mui/material/ListItemIcon';
    import ListItemText from '@mui/material/ListItemText';

    import Checkbox from '@mui/material/Checkbox';

    export default function Habits({type, calendarDate}) {
      const [habits, setHabits] = React.useState([]);

      React.useEffect(() => {
        fetchData()
      }, [calendarDate])

      const fetchData = () => {
        axios.get(`http://localhost:1337/api/get-habits-with-logs?calendarDate=${format(calendarDate, "yyyy-MM-dd")}&type=${type}`)
        .then((response) =>{
          setHabits(response.data)
        })
        .catch((error) => console.log(error))
      }

      const completeHabit = (habitId) => () => {
        axios
          .post('http://localhost:1337/api/habit-logs', {
            data: {
              habit: habitId,
              completionDate: calendarDate
            }
          })
          .then((response) => {
            fetchData()
          })
          .catch((error) => {
            console.log(error);
          });
      };

      return(
        <Grid item>
          <Typography variant="h6" gutterBottom component="div">
            {type.replace(/^\w/, (c) => c.toUpperCase())} routine
          </Typography>
          <List sx={{ width: '100%' }}>
          {habits.length > 0 && habits.map((habit) => {
            const { id, name, completed } = habit
            const labelId = `checkbox-list-label-${id}`;
            return (
              <ListItem
                key={id}
                disablePadding
              >
                <ListItemButton role={undefined} onClick={completeHabit(id)} dense>
                  <ListItemIcon>
                    <Checkbox
                      edge="start"
                      checked={completed}
                      tabIndex={-1}
                      disableRipple
                      inputProps={{ 'aria-labelledby': labelId }}
                    />
                  </ListItemIcon>
                  <ListItemText id={labelId} primary={name} />
                </ListItemButton>
              </ListItem>
            );
          })}
        </List>
        </Grid>
      )
    }

Following is a GIF of your habit tracker in action:

If you want to clone the project and follow along in your own editor, use this GitHub repo.

Conclusion

In this tutorial, you learned how to create a habit tracker by setting up a Strapi backend and creating a frontend in React using the MUI library. Then you connected your frontend to your Strapi backend by making POST requests.

You also learned that the REST API would only take you so far and that the Query Engine API can create a custom route, create a custom controller, and write queries to retrieve habits with their logs. With this, the backend retrieved the habits and returned them in the format that you wanted, making it easier to implement checking off your habits.