Migrating User Authentication from Supabase to Clerk.dev: A Step-by-Step Guide

This step-by-step guide simplifies the process of migrating user authentication from Supabase to Clerk.dev. With just a few steps, you will learn to prepare your Clerk.dev environment, extract user data from Supabase, and programmatically create users in Clerk.dev

Felix Vemmer

June 7, 2023

411

Migrating User Authentication from Supabase to Clerk.dev: A Step-by-Step Guide

Do you find the idea of migrating auth from Supabase to Clerk.dev intimidating? Let me assure you, it's less complex than it seems. In this guide, we'll simplify the process and walk you through every step of the migration. With just a handful of slight code tweaks, you'll be on the fast track to integrating Clerk.dev. So, let's dive right in and demystify this process together with a comprehensive, easy-to-follow guide.

More interested on why I switched? Check out the post below:

Supabase vs. Clerk.dev: Comparative Analysis of Auth Tools

Dive into the Supabase vs Clerk.dev debate. Uncover insights about their performance, custom tokens, user impersonation, cost dynamics, and more. Which tool will reign supreme for you?

June 14, 2023

Step 1: Set Up Your Clerk.dev Account

Navigate to the User & Authentication settings and select Email, Phone, Username. This is the stage where you'll want to ensure that the settings mirror those of your existing Supabase account.

If you use a username make sure that you first toggle the setting off in clerk, as the import without setting a username will throw an error.

Step 2: Establishing Required Secrets as Environment Variables

In order to facilitate the creation of new users, we need to communicate with the Create new User Endpoint. This can be accomplished by using our Clerk API Keys:

Next, we'll store the CLERK_SECRET_KEY as a Python environment variable within the .env file, making it accessible from our script.

Lastly, we'll need to secure the database connection string from our existing Supabase instance. This can be achieved by storing it as an environment variable. You can retrieve this string from Project Settings > Database.

Our final .env file should look like this:

CLERK_SECRET_KEY="{YOUR_CLERK_SECRET_KEY}"
PSQL_CONNECTION_STRING="{YOUR_SUPABSE_PSQL_CONNECTION_STRING}"

Step 3: Preparing the Migration Script

In the following I will show you how I set up my migration script. Feel free to edit and adjust the parts that will be slightly different for you.

Setting up env and imports

To get started we will import the following libraries and set up our environment variables form a .env file:

import os
import psycopg2
import psycopg2.extras  # Import for RealDictCursor
from dotenv import load_dotenv
import requests
import time
from tqdm import tqdm  # For the progress bar
 
 
load_dotenv(
    dotenv_path=os.path.join(os.path.dirname(__file__), ".env.local"),
    override=True,
    verbose=True,
)

Make sure to adjust the .env.local and path to your env file.

Connecting to Supabase and fetching all Users

Next we connect to the instance and run the a sql query to get all the users:

# Get the connection string from the environment variable
connection_string = os.getenv("PSQL_CONNECTION_STRING")
 
# Connect to the PostgreSQL database
conn = psycopg2.connect(connection_string)
 
# Create a cursor object using RealDictCursor
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
 
sql_query = """
WITH user_data AS (
    SELECT
        id,
        email,
        encrypted_password,
        created_at,
        username
    FROM auth.users
    LEFT JOIN usernames ON usernames.user_id = users.id
),
user_profiles AS (
    SELECT
        user_id,
        first_name,
        last_name
    FROM public.profiles
)
SELECT
    user_data.id::TEXT AS external_id,
    user_data.username,
    user_profiles.first_name,
    user_profiles.last_name,
    ARRAY[user_data.email] AS email_address,
    encrypted_password AS password_digest,
    to_char(user_data.created_at at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS created_at,
    'bcrypt' AS password_hasher,
    ARRAY[]::TEXT[] AS phone_number,
    ARRAY[]::TEXT[] AS web3_wallet,
    NULL AS password,
    CAST(true AS BOOLEAN) AS skip_password_checks,
    CAST(true AS BOOLEAN) AS skip_password_requirement,
    NULL AS totp_secret,
    ARRAY[]::TEXT[] AS backup_codes,
    '{}'::jsonb AS public_metadata,
    '{}'::jsonb AS private_metadata,
    '{}'::jsonb AS unsafe_metadata
FROM user_data
LEFT JOIN user_profiles ON user_data.id = user_profiles.user_id
"""
 
# Execute the SQL query
cur.execute(sql_query)
 
# Fetch the results
results = cur.fetchall()  # Fetching only the first row
 
# Close the cursor and connection
cur.close()
conn.close()

The SQL query logic works in the following way:

Get the main user_data in one CTE
Get all the user_profiles info from a separate table, e.g. first_name and last_name in a second CTE.
Piece all the info together and add any missing parts

Make sure to adjust the sql query as needed. E.g. if you have no username or other auth types.

The final SQL Select directly corresponds with the needed post body:

SELECT
    user_data.id::TEXT AS external_id,
    user_data.username,
    user_profiles.first_name,
    user_profiles.last_name,
    ARRAY[user_data.email] AS email_address,
    encrypted_password AS password_digest,
    to_char(user_data.created_at at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS created_at,
    'bcrypt' AS password_hasher,
    ARRAY[]::TEXT[] AS phone_number,
    ARRAY[]::TEXT[] AS web3_wallet,
    NULL AS password,
    CAST(true AS BOOLEAN) AS skip_password_checks,
    CAST(true AS BOOLEAN) AS skip_password_requirement,
    NULL AS totp_secret,
    ARRAY[]::TEXT[] AS backup_codes,
    '{}'::jsonb AS public_metadata,
    '{}'::jsonb AS private_metadata,
    '{}'::jsonb AS unsafe_metadata
FROM user_data
LEFT JOIN user_profiles ON user_data.id = user_profiles.user_id

One of the most awesome things is that we can straight away export the encrypted_password to Clerk.dev, since Supabase is using bcrypt as mentioned here:

So all we need to do is just specify the following in SQL:

'bcrypt' AS password_hasher

Connecting to Clerk.dev BackendAPI and creating new users

Finally using the set up environment variable in Step 1, we connect to Backend API and iterate over the rows to create each user in Clerk.dev.

As specified in the Rate limits section we can only make 20 requests per 10 seconds. That is why I included the wait time.

# API endpoint
url = "https://api.clerk.com/v1/users"
 
# Authorization token (replace with your actual token)
token = f"Bearer {os.environ.get('CLERK_SECRET_KEY')}"
 
# Headers for the POST request
headers = {"Content-Type": "application/json", "Authorization": token}
 
# For rate limiting, if you can do 20 requests per 10 seconds,
# you need to wait 0.5 second between each request
wait_time = 0.5
 
# Send the POST request
for result in tqdm(results):
    response = requests.post(url, json=result, headers=headers)
    time.sleep(wait_time)  # Wait for a bit to respect the rate limit
 
    data = response.json()
 
    if "errors" in data and len(data.get("errors")) > 0:
        if data["errors"][0].get("message") == "That email address is taken. Please try another.":
            print(f"User with email {result.get('email_address')[0]} already exists")
            continue
        else:
            print(data["errors"])
            raise Exception("Error creating user")
    else:
        print(f"User with email {result.get('email_address')[0]} created successfully")

Congrats, you successfully migrated Auth from Supabase to Clerk.dev.

Migrating user authentication from Supabase to Clerk.dev may initially seem challenging, but as we've seen, it's an achievable task with just a few steps. By following this guide, you've learned to prepare your Clerk.dev environment, extract user data from Supabase, and programatically create users in Clerk.dev.

See all posts