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 the great seed template!
I got this error while seeding the data:
failed to send batch: ERROR: null value in column "provider_id" of relation "identities" violates not-null constraint (SQLSTATE 23502)
After some digging, seems like newer version of Supabase added a required provider_id field in auth.identities. To fix this, simply insert provider_id with value id like so:

before:

-- 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
    );

After:

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

Thanks @khattaksd 😄!! I also had to use @fluid-design-io's method for it to work.

Any ideas if it's possible to seed an OAuth-based user (i.e. Google)? I signed in with Google and then simply copy the values from auth.users and auth.identities but after seeding a fresh database, the login throws an error related to the refresh_token...

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)=(user@example.com) already exists.
Has anyone else faced this issue? For clarification, user@example.com does not exist in the table.”

Thank you @fluid-design-io and @kerren for the feedback.
I created this as a note-to-self but it has proven useful to fellow developers.
I decided to publish a functional project using this seeder: https://github.com/khattaksd/vite-supabase-seeder-example
I do not plan to actively maintain it but surely it is far better than simple gist 💡

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)=(user@example.com) already exists. Has anyone else faced this issue? For clarification, user@example.com 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