#Migrating users from SQL database

1 messages · Page 1 of 1 (latest)

proper violetBOT
#

Hi @void dune. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

#

To migrate users from your SQL database to Convex while using Convex Auth, you need to ensure that the user records in Convex are linked to the correct authentication identities. This way, when a user signs in, they access the right account.

Key Steps

  1. Export Users from SQL: Write a script to export your users from the SQL database.

  2. Prepare Convex User Records: For each user, you need to insert a record into the Convex users table. The critical part is to set the identifier field (such as tokenIdentifier or subject) to match what Convex Auth will use when the user signs in.

    • If you use the tokenIdentifier (recommended for multi-provider support), it should match the value Convex Auth will generate for that user.
    • If you use the subject (e.g., the provider's user ID), ensure it matches the value in the authentication provider.

    Example schema:

    users: defineTable({
  name: v.string(),
  tokenIdentifier: v.string(),
}).index("by_token", ["tokenIdentifier"]),

    Example mutation:

    export const store = mutation({
  args: {},
  handler: async (ctx) => {
    const identity = await ctx.auth.getUserIdentity();
    if (!identity) {
      throw new Error("Called storeUser without authentication present");
    }
    // ...see full example in the docs
  },
});

    See full example in the Convex docs

#

  1. Generate the Correct Identifiers: The most important part is to ensure that the identifier you store for each user matches what Convex Auth will use when the user signs in. This usually means:

    • For email/password or OAuth, the identifier is often a combination of the provider and the user’s unique ID or email.
    • You may need to consult your auth provider’s documentation or inspect the JWTs to determine the exact value.

  2. Insert Users into Convex: Use a script or migration to insert each user into the Convex users table with the correct identifier.

  3. Test Sign-In: After migration, when a user signs in via Convex Auth, Convex will look up the user by the identifier. If it matches, the user will access their migrated account.

Additional Notes

  • If you need to customize account linking (e.g., to handle multiple providers or special cases), you can implement the createOrUpdateUser callback in Convex Auth. This gives you full control over how users are created and linked during sign-in. See the advanced account linking docs for details.
  • If you want to perform additional writes after user creation, you can use the afterUserCreatedOrUpdated callback, but only if you’re not using createOrUpdateUser already see API reference.

Summary

  • Export users from SQL.
  • Insert them into Convex with the correct identifier (tokenIdentifier or subject).
  • Ensure the identifier matches what Convex Auth will use.
  • Optionally, customize account linking with createOrUpdateUser if needed.

This approach ensures that when users sign in, they are matched to the correct account in Convex Auth and have access to their data.

#

@void dune Kapa.ai is still learning and improving, please let me know how I did by reacting below.

void dune
#

@proper violet should I migrate ALL the users, or just when they sign in ? also, how to trigger the createOrUpdate ?

proper violetBOT
#

Let me check the knowledge base for you again. One moment! :books:

#

You have two main options for migrating users from your SQL database to Convex Auth:

1. Migrate All Users Upfront

  • Pros: All user data is available in Convex immediately.
  • Cons: You may migrate users who never sign in again, and you need to ensure all identifiers are correct.
  • How: Write a script to insert all users into the Convex users table, making sure to set the correct identifier fields (like tokenIdentifier or subject) that Convex Auth will use to match users on sign-in. This is a straightforward approach but may require more upfront work and data mapping.

2. Migrate Users On-Demand (When They Sign In)

  • Pros: Only active users are migrated, and you can ensure identifiers match exactly at sign-in.

  • Cons: Requires logic to handle migration during the sign-in process.

  • How: You can implement this by customizing the Convex Auth flow using the createOrUpdateUser callback. When a user attempts to sign in and does not exist in Convex, you can fetch their data from your SQL database, create their Convex user record, and return the new user ID. This ensures the user is created in Convex only when they actually sign in.

    The createOrUpdateUser callback is called during the sign-in process, before account creation and token generation. You can use it to control account linking and perform any necessary migration logic:

    export const { auth, signIn, signOut, store, isAuthenticated } = convexAuth({
  providers: [/* your providers */],
  callbacks: {
    async createOrUpdateUser(ctx, args) {
      // Check if user exists in Convex
      // If not, fetch from SQL and insert into Convex
      // Return the Convex user ID
    },
  },
});

    See the API reference for details.

#

How to Trigger createOrUpdateUser

  • The createOrUpdateUser callback is automatically triggered by Convex Auth during the sign-in process, before the user document is created or updated. You do not need to call it manually; it is part of the Convex Auth flow when a user signs in using any provider configured in your app.
    See the API reference for callback details.

Summary:

  • Migrating on sign-in (using createOrUpdateUser) is often more efficient and ensures only active users are migrated.
  • Migrating all users upfront is simpler if you want all data available immediately.
  • The createOrUpdateUser callback is the recommended way to handle on-demand migration and is triggered automatically during sign-in.

If you need more details or code examples, let me know!

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: