#automation

Michał Uzdowski

Oct 30, 2024

12 min read

Keeping your Supabase free tier from taking an unexpected nap

Welcome back to Binary Brain, the tech blog where we sprinkle a little humor on top of your daily coding grind. Today, we’re diving into the mystical land of Supabase — specifically, how to prevent your free tier project from taking an impromptu siesta. Because let’s face it, nobody likes waking up to find their beloved database has gone snoozing.

You know the drill: you’re happily building your indie software project, sipping on your favorite caffeinated beverage, and then — bam! — your Supabase project decides to take a nap after a week of inactivity. It’s like setting up a date and then forgetting to show up. Awkward. But fear not! We’re here to ensure your Supabase project stays as alert as a cat on a laser pointer.

And keep in mind that the same solutions can be applied to any other database or service that requires regular activity to stay awake.

The Great Supabase Snooze Problem

Supabase is fantastic. It’s like the Swiss Army knife of databases for indie developers and hobbyists. With its generous free tier, blazing-fast performance, and a user interface so clean you could eat off it (though we don’t recommend that), it’s hard not to love it. However, there’s a catch: if your project doesn’t get any love (read: activity) for a week, Supabase sends you a couple of warning emails before putting your project into hibernation mode. And once it’s asleep, your app might start throwing a tantrum.

Why Supabase Does This

Think of Supabase as a responsible landlord. If your project isn’t paying rent (in this case, making requests), the landlord might think it’s time to close the door. To keep things cozy and active, you need to keep those requests coming, ensuring your project remains in good standing.

The Solution: GitHub Actions to the Rescue!

Enter GitHub Actions, the superhero of automation. By setting up a scheduled workflow, you can make sure your Supabase project never catches a cold from inactivity. Here’s how to keep your project wide awake using GitHub Actions and a dash of Next.js magic.

Step 1: Set Up Your Supabase Project

Before we get into the nitty-gritty, let’s make sure your Supabase project is ready for its new daily routine.

Create a Supabase Project:
- Log in to your Supabase account.
- Click on New Project.
- Fill in the necessary details like project name, password, and region. Think of it as naming your new pet — something you won’t regret later.
Obtain Supabase Credentials:
- Once your project is created, navigate to Settings > API.
- Note down your Supabase URL and API Key. These are like the keys to your kingdom — guard them well!
Create a Database Table:
- In your Supabase dashboard, go to Table Editor.
- Create a new table (let’s call it users for simplicity) and add some sample data. This table is what we’ll be poking to keep the project awake.

Step 2: Crafting Your Next.js API Route

Now, let’s build an API route in your Next.js application that will periodically fetch data from your Supabase database. Think of it as setting an alarm to make sure your project doesn’t oversleep.

Install the Supabase Client: Open your terminal and navigate to your Next.js project directory. Then run:
```
npm install @supabase/supabase-js
```
Or if you prefer Yarn:
```
yarn add @supabase/supabase-js
```
Create the API Route:
- Navigate to the app/api/ directory in your Next.js project. If these folders don’t exist, go ahead and create them.
- Create a new file named supabase-poke-scheduler/route.ts.

Add the Following Code to route.ts:

// app/api/supabase-poke-scheduler/route.ts

import { createClient } from "@supabase/supabase-js";

const supabaseUrl = "<YOUR_SUPABASE_URL>";
const supabaseKey = "<YOUR_SUPABASE_KEY>";
const supabase = createClient(supabaseUrl, supabaseKey);

export async function GET() {
  try {
    // Fetch data to prevent Supabase project from pausing
    const { data, error } = await supabase.from("users").select();
    if (error) throw new Error(error.message);
    return Response.json(data);
  } catch (error) {
    const message = (error as Error).message ?? "An error occurred.";
    return Response.json({ error: message }, { status: 400 });
  }
}

Pro Tip: Replace <YOUR_SUPABASE_URL> and <YOUR_SUPABASE_KEY> with your actual Supabase URL and API key. It’s like filling in the blanks of a secret recipe — essential for the magic to happen.

Test the API Route:
- Start your Next.js development server:
```
npm run dev
```
  Or with Yarn:
```
yarn dev
```
- Navigate to http://localhost:3000/api/supabase-poke-scheduler in your browser. If everything is set up correctly, you should see your users table data in JSON format. High five!

Step 3: Deploy Your Next.js Application

Your API route needs to be accessible over the internet so that GitHub Actions can give it a gentle nudge (or a firm shove) to stay awake.

Deploy to Vercel (Highly Recommended):
- Install the Vercel CLI if you haven’t already:
```
npm install -g vercel
```
- Deploy your application:
```
vercel
```
- Follow the prompts to complete the deployment process. Once done, note down your application’s domain (e.g., https://your-app.vercel.app). It’s like getting the address to your new coffee shop — critical for customers (or GitHub Actions) to find you.
Update the API Route URL:
- Ensure that your API route URL is accessible, for example: https://your-app.vercel.app/api/supabase-poke-scheduler.

Feel free to check our other article Building a Full-Stack Application with Next.js: A Modern Development Journey if you need more detailed instructions about deploying your Next.js application to Vercel.

Step 4: Set Up GitHub Actions to Schedule API Calls

Time to let GitHub Actions do the heavy lifting. We’ll set up a workflow that periodically pings your API route to keep Supabase from taking an unscheduled nap.

Navigate to Your GitHub Repository:
- Go to the GitHub repository where your Next.js project resides.
Create the Workflow File:
- In your repository, navigate to .github/workflows/.
- Create a new file named supabase-poke-scheduler.yml.

Add the Following Configuration to supabase-poke-scheduler.yml:

# .github/workflows/supabase-poke-scheduler.yml

name: Supabase Activity Scheduler

on:
  schedule:
    - cron: "0 0 * * 0,3" # Runs every Sunday and Wednesday at 00:00 UTC
  workflow_dispatch:

jobs:
  send-http-request:
    runs-on: ubuntu-latest

    steps:
      - name: Send HTTP Request
        run: curl https://<YOUR_DOMAIN_NAME>/api/supabase-poke-scheduler

Important:

Replace <YOUR_DOMAIN_NAME> with your deployed Next.js application’s domain (e.g., https://your-app.vercel.app).
The cron schedule "0 0 * * 0,3" means the workflow runs every Sunday and Wednesday at midnight UTC. Adjust as needed to suit your sleep schedule.

Commit and Push the Workflow: Save the supabase-poke-scheduler.yml file, then commit and push the changes to your GitHub repository:

git add .github/workflows/supabase-poke-scheduler.yml
git commit -m "Add Supabase Poke Scheduler workflow"
git push origin main

Verify the Workflow:
- Go to the Actions tab in your GitHub repository.
- You should see the newly added workflow. It will run according to the schedule you’ve set (or you can trigger it manually using workflow_dispatch).

Step 5: Understanding GitHub Actions Usage

Let’s do some quick math to ensure your free tier is safe from GitHub Actions’ heavy-handedness.

Runtime: Each workflow run takes about 2 seconds.
Billing: On the GitHub Free plan, you get 2,000 minutes per month.
Usage Calculation:
- Running the workflow twice a week results in approximately 17,5 seconds of usage per month (2 seconds per run * 2 runs per week * ~4.35 weeks per month).
- This is just 0.015% of the total free minutes, leaving you plenty of breathing room.

So, by setting this up, you’re ensuring your Supabase project stays awake without blowing through your GitHub Actions quota. It’s like having a personal assistant who only works a few minutes a month but keeps your entire operation running smoothly.

Using Vercel’s Cron Instead of GitHub Actions

Feeling like GitHub Actions is the cool kid on the block but want to explore other avenues to keep your Supabase project awake? Enter Vercel’s Cron — the suave, efficient alternative that’s just as capable (and maybe a tad more stylish). Let’s dive into how you can use Vercel’s Cron to achieve the same goal without breaking a sweat.

Why Vercel’s Cron?

Maybe you’re already hosting your Next.js app on Vercel, or perhaps you just want to keep all your automation magic in one place. Vercel’s Cron Jobs offer a seamless way to schedule tasks directly within your deployment environment, making it a perfect fit for our sleepy Supabase project dilemma.

Step 1: Accessing Vercel’s Cron Feature

First things first, ensure you’re set up with Vercel and have your Next.js project deployed.

Log in to Vercel:
- Head over to Vercel and log in to your account. If you haven’t signed up yet, it’s as easy as pie — mmm, pie.
Navigate to Your Project:
- Click on your project to access its dashboard. It’s like walking into your personal development lair.
Access Cron Jobs:
- In the project dashboard, find the Settings tab.
- Scroll down to the Cron Jobs section. If you don’t see it immediately, don’t panic — Vercel’s layout is as sleek as their performance.

Step 2: Creating a New Cron Job

Now, let’s set up the cron job that will keep your Supabase project from dozing off.

Add a New Cron Job:
- Click on Add Cron Job. It’s like adding a new member to your automation squad.
Configure the Cron Schedule:
- Frequency: Choose how often you want the cron job to run. For our purposes, running twice a week should suffice.
- Cron Expression: Use a cron expression similar to what we used for GitHub Actions. For example:
```
0 0 * * 0,3
```
  This runs the job every Sunday and Wednesday at midnight UTC. Adjust it if you’re a night owl or an early bird.
Set the URL to Ping:
- URL: Enter your Next.js API route URL, e.g.,
```
https://your-app.vercel.app/api/supabase-poke-scheduler
```
  This is the magical endpoint that keeps Supabase from taking a snooze.
Save the Cron Job:
- Hit Save or Create to finalize your cron job. Congratulations, you’ve just automated a task that keeps your database lively!

Step 3: Verify Your Cron Job

Just like checking if your coffee machine is brewing the perfect cup, you need to ensure your cron job is set up correctly.

Check the Cron Job Status:
- In the Cron Jobs section of your Vercel project dashboard, you should see your newly created job listed.
- Ensure it’s active and scheduled to run at your specified times.
Manual Trigger (Optional):
- Some cron job interfaces allow you to trigger the job manually for testing purposes. If Vercel offers this, go ahead and give it a whirl to see your API endpoint in action.

Harnessing the Power of Supabase Edge Functions

So, you’ve danced with GitHub Actions and Vercel’s Cron Jobs to keep your Supabase project awake. But why stop there? Enter Supabase Edge Functions — the suave, all-in-one solution that keeps your database buzzing without you lifting a finger (except, you know, writing some code).

Why Supabase Edge Functions?

Imagine having a Swiss Army knife that not only slices and dices but also sends you a cup of coffee every morning. That’s Supabase Edge Functions for you — versatile, powerful, and always ready to save the day. Instead of relying on external schedulers, you can manage everything within Supabase’s own ecosystem. It’s like having your cake and eating it too, without worrying about the calories!

Prerequisites: Getting Ready for the Edge

Before we embark on this magical journey, ensure you have the following:

Supabase Project: Already set up from the previous steps.
Enabled Extensions: Specifically, pg_cron and pg_net. These extensions allow you to schedule tasks and make network requests directly from your database.
- Enable Extensions:
  - Navigate to your Supabase project dashboard.
  - Go to Settings > Database > Extensions.
  - Search for pg_cron and pg_net and click Enable for both.
Think of extensions as superpowers for your database. Without them, our Edge Functions would be like superheroes without their trusty gadgets.

Step 1: Create an Edge Function

Let’s craft an Edge Function that will periodically fetch data from your users table, keeping your Supabase project lively.

Install the Supabase CLI (if you haven’t already):
```
npm install -g supabase
```
Initialize Supabase in Your Project:
```
supabase init
```
Create a New Edge Function:
```
supabase functions new keep-awake
```
This command creates a new Edge Function named keep-awake in the supabase/functions directory.

Step 2: Write the Edge Function Code

Navigate to the newly created keep-awake function directory and open the index.ts file. Let’s write a function that queries the users table.

// supabase/functions/keep-awake/index.ts

import { serve } from "https://deno.land/std@0.140.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const supabaseUrl = Deno.env.get("SUPABASE_URL")!;
const supabaseKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
const supabase = createClient(supabaseUrl, supabaseKey);

serve(async (req) => {
  try {
    const { data, error } = await supabase.from("users").select();
    if (error) throw error;
    return new Response(JSON.stringify(data), { status: 200 });
  } catch (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: 400,
    });
  }
});

Pro Tip: Replace "users" with the name of your actual table if it’s different. This function simply fetches data from the users table, ensuring there’s activity to keep Supabase awake.

Step 3: Deploy the Edge Function

Deploying your Edge Function makes it accessible over the internet, allowing it to be triggered as scheduled.

Build and Deploy:
```
supabase functions deploy keep-awake
```
Once deployed, Supabase provides a unique URL for your Edge Function, something like https://<your-project>.functions.supabase.co/keep-awake.

Step 4: Schedule the Edge Function with `pg_cron`

Now, let’s schedule our Edge Function to run periodically using the pg_cron extension.

Connect to Your Supabase Database:

You can use any PostgreSQL client. Here’s an example using psql:
```
psql "postgresql://<username>:<password>@<host>:<port>/<database>"
```
Create a Cron Job:
```
SELECT cron.schedule('keep_awake_job', '0 0 * * 0,3', $$
  PERFORM http_get('https://<your-project>.functions.supabase.co/keep-awake');
$$);
```
Explanation:
- 'keep_awake_job': Name of your cron job.
- '0 0 * * 0,3': Cron expression to run the job every Sunday and Wednesday at midnight UTC.
- http_get('https://<your-project>.functions.supabase.co/keep-awake'): Makes an HTTP GET request to your Edge Function, triggering it to fetch data.
Humorous Insight: Think of pg_cron as the internal alarm clock for your database, ensuring it never oversleeps. No more surprise naps!

Step 5: Verify Everything is Running Smoothly

Check Cron Jobs:
```
SELECT * FROM cron.job;
```
Ensure your keep_awake_job is listed and scheduled correctly.
Monitor Edge Function Logs:

Navigate to your Supabase dashboard, go to Functions, and check the logs for the keep-awake function to ensure it’s running without hiccups.

Conclusion: Sleep Tight, Supabase

Congratulations! By following this guide, you’ve successfully set up scheduled tasks using GitHub Actions, Vercel’s Cron Jobs, and Supabase Edge Functions to keep your Supabase free tier project from snoozing away. No more surprise pauses, no more frantic emails from Supabase, just smooth, uninterrupted development bliss.

Remember, automation is your friend. Let GitHub Actions, Vercel’s Cron, or Supabase Edge Functions handle the repetitive tasks while you focus on building the next big thing. And if you ever need to tweak the schedule or explore more advanced integrations, the Supabase, GitHub Actions, and Vercel documentation are your trusty sidekicks.

So go ahead, give your Supabase project the caffeine boost it needs to stay awake and keep your apps running like a well-oiled machine. And as always, here at Binary Brain, we’re here to make your coding journey a little brighter, one humorous tip at a time.

Happy coding, and may your databases never sleep! 💤🚀