Supabase Seed Users for local development & testing

seed.sql

-- supabase/seed.sql
--
-- create test users
INSERT INTO
    auth.users (
        instance_id,
        id,
        aud,
        role,
        email,
        encrypted_password,
        email_confirmed_at,
        recovery_sent_at,
        last_sign_in_at,
        raw_app_meta_data,
        raw_user_meta_data,
        created_at,
        updated_at,
        confirmation_token,
        email_change,
        email_change_token_new,
        recovery_token
    ) (
        select
            '00000000-0000-0000-0000-000000000000',
            uuid_generate_v4 (),
            'authenticated',
            'authenticated',
            'user' || (ROW_NUMBER() OVER ()) || '@example.com',
            crypt ('password123', gen_salt ('bf')),
            current_timestamp,
            current_timestamp,
            current_timestamp,
            '{"provider":"email","providers":["email"]}',
            '{}',
            current_timestamp,
            current_timestamp,
            '',
            '',
            '',
            ''
        FROM
            generate_series(1, 10)
    );

-- test user email identities
INSERT INTO
    auth.identities (
        id,
        user_id,
        identity_data,
        provider,
        last_sign_in_at,
        created_at,
        updated_at
    ) (
        select
            uuid_generate_v4 (),
            id,
            format('{"sub":"%s","email":"%s"}', id::text, email)::jsonb,
            'email',
            current_timestamp,
            current_timestamp,
            current_timestamp
        from
            auth.users
    );

Thanks for this template 🚀! I encountered this error while seeding the data by writing a query in the SQL Editor: ERROR: 23505: duplicate key value violates unique constraint "users_email_partial_key" DETAIL: Key (email)=([email protected]) already exists. Has anyone else faced this issue? For clarification, [email protected] does not exist in the table.”

@andostronaut you are probably trying to run the query to create identities with an existing user (other than the ones with @example.com) on your database.

Try to add this where clause to avoid this:

INSERT INTO
    auth.identities (
        id,
        user_id,
        provider_id,
        identity_data,
        provider,
        last_sign_in_at,
        created_at,
        updated_at
    ) (
        select
            uuid_generate_v4 (),
            id,
            id as user_id,
            format('{"sub":"%s","email":"%s"}', id::text, email)::jsonb,
            'email',
            current_timestamp,
            current_timestamp,
            current_timestamp
        from
            auth.users
        where
          email LIKE '%@example.com' -- here
    );

Thanks @gabrielrolfsen , i will try it

I created a variation on the above ...

Supabase Import Auth Users

https://gist.github.com/GitTom/27863f4b8811406d08fc76b3b1537848
Load new users from an import table into auth.users and auth.identities.

The main idea is to be usable for seeding a new database, but also for adding new users to existing data. It loads data from an import table, and saves any generated values (eg. the UUID for new users) back into the import table so you can use these in other test data and count on them remaining the same across resets.

(Note that to preserve the new user's uuid's across db resets you actually have to manually grab them from the import.users table and add them to the little INSERT script before each reset - I haven't automated that.)