Release candidate version: Database migration guide

[pilcrowonpaper]

Prisma

1. Rename primary column

Make sure to update the Prisma schema

model Key {
  // ...
  primary_key Boolean
}

SQLite

ALTER TABLE `key`
RENAME COLUMN `primary` to `primary_key`;

PostgreSQL

ALTER TABLE public.key
RENAME COLUMN `primary` to `primary_key`;

MySQL

ALTER TABLE `key`
RENAME COLUMN `primary` to `primary_key`;

2. Rename models

Add Auth to model names.

model AuthUser {
  // ...
}

model AuthSession {
  // ...
}

model AuthKey {
  // ...
}

3. Rename references

Rename reference names (user to auth_session) and model names within the models (Session[] to AuthSession[]).

model AuthUser {
  // ...
  auth_session AuthSession[]
  auth_key     AuthKey[]
}

model AuthSession {
  // ...
  auth_user      AuthUser @relation(references: [id], fields: [user_id], onDelete: Cascade)
}

model AuthKey {
  // ...
  auth_user       AuthUser @relation(references: [id], fields: [user_id], onDelete: Cascade)
}

3.5 (Optional). Change table names

You may optionally change the table names mapped to each model with @@map():

model AuthUser {
  // ...
  @@map("auth_user")
}

Make sure update your tables with ALTER TABLE before migrating the Prisma schema.

ALTER TABLE user RENAME TO auth_user;

SQLite does not fully support foreign keys with ALTER TABLE. Refer to Kysely/SQLite section below.

4. Migrate Prisma

npx prisma migrate dev
pnpm prisma migrate dev
yarn prisma migrate dev

Mongoose

1. Rename primary column

db.keys.updateMany({}, { $rename: { primary: "primary_key" } });

2. Rename model name

const User = mongoose.model(
	"auth_user",
	new mongoose.Schema(
		{
			// ...
		},
		{
			// ...
		}
	)
);

const Session = mongoose.model(
	"auth_session",
	new mongoose.Schema(
		{
			// ...
		},
		{
			// ...
		}
	)
);

const Key = mongoose.model(
	"auth_key",
	new mongoose.Schema(
		{
			// ...
		},
		{
			// ...
		}
	)
);

3. Rename collections

db.users.renameCollection("auth_users");
db.sessions.renameCollection("auth_sessions");
db.keys.renameCollection("auth_keys");

Custom collection names

If you'd like to keep the current name, define the collection name within Mongoose schema with the collection option. Do NOT change the model name "auth_user".

const User = mongoose.model(
	"auth_user",
	new mongoose.Schema(
		{
			// ...
		},
		{
			// ...
			collection: "users"
		}
	)
);

4. Update auth_key schema

Rename primary field to primary_key:

const Key = mongoose.model(
	"auth_key",
	new mongoose.Schema(
		{
			// ...
			primary_key: {
				type: Boolean,
				required: true
			}
		},
		{
			// ...
		}
	)
);

Extra: Make sure field auth_key.expires exists

You can run the following command to set field expires to null if it doesn't exist.

db.auth_keys.update(
	{ expires: { $exists: false } },
	{ $set: { expires: null } }
);

Kysely/SQLite

DO NOT USE ALTER TABLE immediately!!!

1. Recreate tables with new names

Make sure to also copy custom user attributes. Column key(primary) will be renamed later.

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

CREATE TABLE auth_session (
    id VARCHAR(127) NOT NULL,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES auth_user(id)
);

CREATE TABLE auth_key (
    id VARCHAR(255) NOT NULL,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    "primary" INT2 NOT NULL,
    expires BIGINT,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES auth_user(id)
);

2. Copy data from old tables

Make sure to start with table user/auth_user.

INSERT INTO auth_user SELECT * FROM user;

INSERT INTO auth_session SELECT * FROM session;

INSERT INTO auth_key SELECT * FROM key;

3. Rename key(primary)

ALTER TABLE auth_key
RENAME COLUMN `primary` TO `primary_key`;

4. Drop old tables

DROP TABLE user;

DROP TABLE session;

DROP TABLE key;

Kysely/MySQL

ALTER TABLE user RENAME TO auth_user;

ALTER TABLE session RENAME TO auth_session;

ALTER TABLE `key` RENAME TO auth_key;

Kysely/PostgreSQL

ALTER TABLE public.user RENAME TO public.auth_user;

ALTER TABLE public.session RENAME TO public.auth_session;

ALTER TABLE public.key RENAME TO public.auth_key;