v0.5.0: Database migration guide

[pilcrowonpaper]

1. Create key table where:

key.id: user.provider_id
key.primary: true
key.user_id: user.id
key.hashed_password: user.hashed_password

2. Remove user.provider_id, user.hashed_password
3. Rename session.expires to session.active_expires

MongoDB

(Thanks to @SkepticMystic for helping out here)

Create new key collection from user:

db.users.aggregate([
  { 
    $project: {
      _id: '$provider_id',
      primary: { $and: [true] },
      user_id: '$_id',
      hashed_password: 1,
    }
  },
  { $out: 'key' }
])

Remove index from users.provider_id:

db.users.dropIndex("provider_id")

Remove fields in user:

db.users.updateMany({}, { $unset: { provider_id: 1, hashed_password: 1 }})

Rename field in session:

db.sessions.updateMany(
   { },
   { $rename: { 'expires': 'active_expires' } }
)

MySQL

Create new key table:

CREATE TABLE `key` (
  id VARCHAR(255) NOT NULL,
  user_id VARCHAR(15) NOT NULL,
  `primary` TINYINT UNSIGNED NOT NULL,
  hashed_password VARCHAR(255),
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
);

Transfer data from user to key:

INSERT INTO `key` (id, user_id, `primary`, hashed_password)
SELECT provider_id, id, 1, hashed_password
FROM user;

Remove columns in user:

ALTER TABLE user
DROP COLUMN provider_id,
DROP COLUMN hashed_password;

Rename columns in session:

ALTER TABLE session
RENAME COLUMN expires TO active_expires;

PostgreSQL

Create new key table:

CREATE TABLE public.key (
  id TEXT PRIMARY KEY,
  user_id TEXT REFERENCES public.user(id) NOT NULL,
  "primary" BOOLEAN NOT NULL,
  hashed_password TEXT
);

Transfer data from user to key:

INSERT INTO public.key (id, user_id, "primary", hashed_password)
SELECT provider_id, id, TRUE, hashed_password
FROM public.user;

Remove columns in user:

ALTER TABLE public.user
DROP COLUMN provider_id,
DROP COLUMN hashed_password;

Rename columns in session:

ALTER TABLE public.session
RENAME COLUMN expires TO active_expires;

SQLite

Create new key table:

CREATE TABLE key (
  id VARCHAR(255) NOT NULL,
  user_id VARCHAR(15) NOT NULL,
  hashed_password VARCHAR(255),
  "primary" INT2 NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
);

Transfer data from user to key:

INSERT INTO `key` (id, user_id, `primary`, hashed_password)
SELECT provider_id, id, 1, hashed_password
FROM user;

Create new user table (temporary):

CREATE TABLE temp_user (
  id VARCHAR(31) NOT NULL,
  PRIMARY KEY (id)
);

Transfer data to temporary user table:

INSERT INTO temp_user (id)
SELECT id 
FROM user;

Delete old user:

DROP TABLE user;

Rename temporary table to user:

ALTER TABLE temp_user RENAME TO user;

Rename columns in session:

ALTER TABLE session
RENAME COLUMN expires TO active_expires;

Supabase

Use psql to connect to your Supabase database and run raw sql queries (refer to the PostgreSQL section).

[SkepticMystic]

@pilcrowonpaper just noticed a small typo.
In the Mongo section, at the end of step 2, the closing bracket is missing )

[SkepticMystic]

In fact, testing it properly now, there are some errors:

1. It should be db.users not db.user
2. You first have to remove the unique index on provider_id before running the query. Otherwise the provider_id is no longer unique (cause it's undefined, I guess)

[SkepticMystic]

So the whole of step 2 should be:

1. Remove the index on provider_id, if it exists.
2. Run db.user.updateMany({}, { $unset: { provider_id: 1, hashed_password: 1 }})

[SkepticMystic]

Similarly, for Mongo step 3, the command should start with db.sessions, not db.session