Clerk_fdw
This is a simple open-source data wrapper that bridges the gap between your Postgres database and Clerk a leading user management solution. For more info, check out the blog post!
Prerequisites
- have the v0.3.3 of
clerk_fdwextension enabled in your instance
Create the foreign data wrapper:
create foreign data wrapper clerk_wrapper
handler clerk_fdw_handler
validator clerk_fdw_validator;
Connect to clerk using your credentials:
create server my_clerk_server
foreign data wrapper clerk_wrapper
options (
api_key '<clerk secret Key>')
Create Foreign Table:
User table
This table will store information about the users.
create foreign table clerk_users (
user_id text,
first_name text,
last_name text,
email text,
gender text,
created_at bigint,
updated_at bigint,
last_sign_in_at bigint,
phone_numbers bigint,
username text,
attrs jsonb
)
server my_clerk_server
options (
object 'users'
);
Organization Table
This table will store information about the organizations.
create foreign table clerk_organizations (
organization_id text,
name text,
slug text,
created_at bigint,
updated_at bigint,
created_by text,
attrs jsonb
)
server my_clerk_server
options (
object 'organizations'
);
Junction Table
This table connects the clerk_users and clerk_orgs. It lists out all users and their roles in each organization.
create foreign table clerk_organization_memberships (
user_id text,
organization_id text,
role text
)
server my_clerk_server
options (
object 'organization_memberships'
);
NOTE: This query requires us to loop through all organizations. In case if we get rate-limited, the fdw implements exponential backoff with a max_interval of 15 minutes. The response might take a while, and it is recommended that you store the information in a local table for quick access.
Query from the Foreign Table:
select * from clerk_users
To get all members of an organization:
select * from organization_memberships where organization_id='org_id';