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)=([email protected]) already exists.
Has anyone else faced this issue? For clarification, [email protected] 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)=([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.)