LW14: Day 2

Learn more

Calendars in Postgres using Foreign Data Wrappers

20 Dec 2024

•

6 minute read

Today we're releasing Foreign Data Wrappers for Cal.com so that you can create event bookings directly from Postgres.

This is especially useful for signup forms where you create an event in your database and schedule an event simultaneously: now you can do all this in a single Postgres transaction.

What's Cal.com?

Cal.com is an open-source scheduling platform that allows individuals and businesses to book and manage appointments. It is designed to work with a variety of use cases, from personal calendars to enterprise-grade scheduling systems. They have a great developer toolkit.

Creating event bookings with Postgres

Cal.com offers various scheduling features. One of the most common scenarios for developers is creating a new event in a calendar (for example, after someone has purchased a flight).

Let's use your Supabase database to create an event in Cal.com, using Postgres Foreign Data Wrappers.

Set up a Cal.com account

create API key on Cal.com

Set up a Supabase account

enable wrappers extension

Create Wasm wrapper and a foreign server

Visit Supabase SQL Editor, use below SQL to create the Wasm foreign data wrapper:

create foreign data wrapper wasm_wrapper
handler wasm_fdw_handler
validator wasm_fdw_validator;

And then create a foreign server for Cal.com connection with your API Key:

create server cal_server
foreign data wrapper wasm_wrapper
options (
fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_cal_fdw_v0.1.0/cal_fdw.wasm',
fdw_package_name 'supabase:cal-fdw',
fdw_package_version '0.1.0',
fdw_package_checksum '4afe4fac8c51f2caa1de8483b3817d2cec3a14cd8a65a3942c8b4ff6c430f08a',
api_key '<your Cal.com API key>'
);

Set up foreign tables

Now let's setup the foreign tables. First of all, create a dedicate schema for the foreign tables:

create schema if not exists cal;

And then create a foreign table for Event Types:

create foreign table cal.event_types (
attrs jsonb
)
server cal_server
options (
object 'event-types'
);

And another foreign table for Bookings:

create foreign table cal.bookings (
attrs jsonb
)
server cal_server
options (
object 'bookings',
rowid_column 'attrs'
);

Note the rowid_column option which is required to insert data into cal.bookings table, we will see it later.

Query Event Types and Bookings from Cal.com

Great, now we are all set, it's time to query some juicy data from Cal.com! Let's start query from Event Types first:

-- extract event types
select
etg->'profile'->>'name' as profile,
et->>'id' as id,
et->>'title' as title
from cal.event_types t
cross join json_array_elements((attrs->'eventTypeGroups')::json) etg
cross join json_array_elements((etg->'eventTypes')::json) et;
query event types from Cal.com

Note all the scheduling information returned from Cal.com API are stored in the JSON column attrs , from which we can extract any fields of that object. For example, we can extract id, title, name and etc., from Bookings object:

-- extract bookings
select
bk->>'id' as id,
bk->>'title' as title,
bk->'responses'->>'name' as name,
bk->>'startTime' as start_time
from cal.bookings t
cross join json_array_elements((attrs->'bookings')::json) bk;
query bookings from Cal.com

Oops, it looks like we haven't booked any meetings with anybody yet. Now it's the fun part, let's make a booking on Cal.com from Supabase!

Make a bookings on Cal.com from Supabase

To make a booking directly from Postgres, all we need to do is to insert a record to cal.bookings foreign table, with the booking details in JSON format. For example,

-- make a 15 minutes meeting with Elon Musk
insert into cal.bookings(attrs)
values (
'{
"start": "2025-01-01T23:30:00.000Z",
"eventTypeId": 1398027,
"attendee": {
"name": "Elon Musk",
"email": "elon.musk@x.com",
"timeZone": "America/New_York"
}
}'::jsonb
);
make a bookings from postgres

Here you can see we made a 15 minutes meeting booking with Elon, just to give him a happy new year greeting 😄. Note the eventTypeId , “1398027”, is our 15 Min Meeting event type ID, you can find yours by querying the cal.event_types foreign table using above example SQL.

After inserting the booking record, we can verify it appears on our upcoming list in Cal.com.

verify bookings is on Cal.com

When we query cal.bookings again using the previous SQL, we can see our new booking record is in the results as well.

verify bookings on Supabase

That wraps up our tutorial! We've covered how to interact with Cal.com in Supabase using foreign wrapper and tables. For more information about available objects and fields, refer to the Cal.com API v2 reference and the Wrappers Cal.com Wasm Wrapper documentation.

Built with Wrappers

The Cal.com FDW is built with Wrappers, a framework for Postgres Foreign Data Wrappers (FDW). Our latest release supports Wasm (WebAssembly) to simplify development for API-based services.

Explore more

We've built a variety of wrappers available on fdw.dev, ranging from popular tools like Stripe and Notion to databases like ClickHouse and BigQuery. Check out the full catalog and get started with Supabase today:

database.new

Share this article

Build in a weekend, scale to millions

Start your projectRequest a demo