Easy-peasy serverless workflow management. Part 2: Schedule updates to Supabase with Pipedream
In part I, we looked at how to automate updates to a serverless database like FaunaDB using a serverless ‘middle-man platform’ like Pipedream. We examined a practical scenario where we managed a Car Listing website where users could post their adverts in the hope of selling their beloved though no longer-needed, car. Our users pay to have their advert live for 30 days, after which their ad expires, and we need to bring it down.
Continuing our journey into serverless workflows, in this part, we will focus on accomplishing the same task through Pipedream though this time using Supabase — another serverless db — to update our car listings when they expire.
Setting up a Pipedream workflow trigger
Just like in part I, we need to set up a schedule in Pipedream to check daily if the expiry date on any of the car listings in our database (in this case, Supabase) matches today’s date. Our workflow looks like this:
To schedule something in the Pipedream workflow, we need first to create the trigger:
- First, search for a “Schedule” app and
- then select a “Daily Schedule” from the options
From here, we can easily set up the time we want our workflow to run each day and then move on to the next step, connecting to Supabase and writing some logic to update things as needed daily automatically.
Connect to Supabase in a Pipedream workflow
In the workflow builder, let’s add another step after the scheduled trigger and search for Supabase. Pipedream provides a set of out-of-the-box, Pipedream-ready, Supabase steps like “Select Row”, “Upsert Row”, etc. While this can be helpful, I’ve found them overly prescribed and had trouble writing more dynamic filtering logic.
I recommend using instead the “Use any Supabase API in Node.js” option, which will give us a lot more flexibility. This option will allow us the Supabase JS client, which is very intuitive and comes with a tone of easy-to-reason-with methods and utilities for performing operations on Supabase tables.
We’ve created another step in our flow that will allow us to interface with Supabase via a Node.js runtime. Our step has a few parts, the first being the “Configure” part. Here, we need to add our credentials to connect to our Supabase instance securely.
To do this we first need our Supabase subdomain which we can get from the Supabase UI by going to “Settings” → “API” → “Project URL”. If our Project URL is something like https://mysubdomain.supabase.co the subdomain we need to use will be “mysubdomain”.
In the same page, right below the Project URL we can find the Project API keys. From these, we will take the service_key of the service_role and add it to the second input for “service_key” in Pipedream. We are now ready to connect to our Supabase instance and proceed to writing some code.
Workflow logic
//1. import the Supabase JS client
import { createClient } from '@supabase/supabase-js';
export default defineComponent({
props: {
supabase: {
type: "app",
app: "supabase",
}
},
async run({steps, $}) {
// 2. Initialize Supabase client
const supabase = createClient( `https://${this.supabase.$auth.subdomain}.supabase.co`, `${this.supabase.$auth.service_key}`);
}
})In the “Code” part of our workflow step, we first need to import the JS client at the top of the file (1) and then instantiate one within the step’s run function with the credentials we added to Pipedream (2). When we added the subdomain and service_key in the previous step, Pipedream securely saved these as environment variables we can reference anywhere in our code without exposing sensitive their sensitive values. When we pass the subdomain and service_key as arguments to createClient, this creates a secure connection between Pipedream’s Node.js environment and our database.
//1. import the Supabase JS client
import { createClient } from '@supabase/supabase-js';
export default defineComponent({
props: {
supabase: {
type: "app",
app: "supabase",
}
},
async run({steps, $}) {
// 2. Initialize Supabase clientconst supabase = createClient( `https://${this.supabase.$auth.subdomain}.supabase.co`, `${this.supabase.$auth.service_key}`);
// 3. Initialise today's date
const today = new Date().toISOString().split('T')[0];
}
})Next, we need to initialise today’s date (3). This will allow us to compare it with every car listing’s expiry date and identify the expired listings. Remember that the schedule we added as the workflow trigger will run the code we write here daily. Hence, we need a way for our function to dynamically decide what day it is every time it runs (read more about the ‘stateless’ nature of lambda functions and why we need to do this in part I).
All we need now is to use the Supabase JS client’s methods to update the status field of all live ads that have an expiry date of today to “expired” (4). Doing so only takes a few lines of JS.
//1. import the Supabase JS client
import { createClient } from '@supabase/supabase-js';
export default defineComponent({
props: {
supabase: {
type: "app",
app: "supabase",
}
},
async run({steps, $}) {
// 2. Initialize Supabase client
const supabase = createClient( `https://${this.supabase.$auth.subdomain}.supabase.co`, `${this.supabase.$auth.service_key}`);
// 3. Initialise today's date
const today = new Date().toISOString().split('T')[0];
// 4. Update the status field to expired for of rows that are published and expire today
try{
const {data, error } = await supabase
.from("jobs")
.update({ ["status"]: "Expired" })
.eq('status', 'Published')
.eq("expiry_date", today)
if (error) {
throw new Error(error.message);
}
return;
}catch (error) {
console.error(error.message);
}
}
})Our Pipedream workflow is now ready to be deployed, and it will run daily and automatically update any expired posts in our database. All you have to do now is have a nice cup of coffee and learn what else you could use serverless workflow management for.
Stay tuned for part III, where we will explore how to add additional steps and logic to send a reminder email to users notifying them in advance that their ad is about to expire and prompt them to purchase extra air time.
