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.2 of clerk_fdw extension 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';