2022 02 28

2022-02-28: user.username must be free of spaces and lowercased.

Starting with the go-starter-2022-02-28 release, all user.usernames must be trimmed, lowercased and free of spaces. We can't provide an automation within your database to apply this migration.

Thus, here are the required steps to do this manually within your database.

Sample users

-- Sample users table
SELECT
    id,
    username
FROM
    users;

-- Result:
-- id                                    username
-- 76a79a2b-fbd8-45a0-b35b-671a28a87acf  "user2@example.com "
-- d9c0dee9-239e-4323-979a-a5354d289627  " user 2@example.com"
-- f6ede5d8-e22a-4ca5-aa12-67821865a3e5  "uSSer1@example.com"

All the above sample usernames are considered garbage starting with this release. BEFORE you do anything, you should check if you are able to update these usernames without producing collisions (two separate users, who would receive the same username).

Find potential username collisions

-- Find whitespace/uppercase collisions in the username field.
-- "If we trim + lowercase the above usernames, will there be collisions with other / between usernames"
-- Returns the count, the valid username and all collisions.
-- NOTE: If this query does not yield any results, you should be safe to automatically rename all user.username, as there are no collisions
SELECT
    count(id),
    LOWER(REPLACE(username, ' ', '')) AS valid_username,
    jsonb_agg(jsonb_build_object(id, username)) AS collisions
FROM
    users
GROUP BY
    valid_username
HAVING
    count(id) > 1;

-- Result:
-- count  valid_username       collisions
-- 2      "user2@example.com"  [{"76a79a2b-fbd8-45a0-b35b-671a28a87acf": " user2@example.com"}, {"d9c0dee9-239e-4323-979a-a5354d289627": "user 2@example.com"}]

If you receive results (like we do, as seen above) - you'll need to make a decision what should happen with these usernames (e.g. if they are really the same user, delete one - or contact the user about this issue).

Find affected users and print UPDATE statements

To find the lowercased and spaces free versions of all affected users, use the following:

-- Find whitespaces and uppercase letters in username field.
-- Returns the id, invalid (current) username, the new valid username and a sample query to manually execute the UPDATE.
-- 
-- NOTE: If you get duplicate key errors executing a returned update_query:
-- * you may have a whitespace/uppercase collision in the username
-- * check the query regarding collisions below
SELECT
    id,
    username AS invalid_username,
    LOWER(REPLACE(username, ' ', '')) AS valid_username,
    FORMAT('UPDATE users SET username = ''%s'' WHERE id = ''%s'';', LOWER(REPLACE(username, ' ', '')), id) AS update_query
FROM
    users
WHERE
    LOWER(REPLACE(username, ' ', '')) != username;

-- Result:
-- id                                    invalid_username       valid_username       update_query
-- 76a79a2b-fbd8-45a0-b35b-671a28a87acf  "user2@example.com "   "user2@example.com"  UPDATE users SET username = 'user2@example.com' WHERE id = '76a79a2b-fbd8-45a0-b35b-671a28a87acf';
-- d9c0dee9-239e-4323-979a-a5354d289627  " user 2@example.com"  "user2@example.com"  UPDATE users SET username = 'user2@example.com' WHERE id = 'd9c0dee9-239e-4323-979a-a5354d289627';
-- f6ede5d8-e22a-4ca5-aa12-67821865a3e5  "uSSer1@example.com"   "usser1@example.com" UPDATE users SET username = 'usser1@example.com' WHERE id = 'f6ede5d8-e22a-4ca5-aa12-67821865a3e5';

This will give you all the required UPDATE statements for proper usernames.