Upgrade your PostgreSQL database to v3
Migration must be handled manually or else you will lose all your data. Do NOT use automated tools as is. Read this guide carefully as some parts depend on your current structure (especially the table names), and feel free to ask questions on our Discord server if you have any questions.
Update the adapter
Install the latest version of the PostgreSQL adapter package.
npm install @lucia-auth/adapter-postgresql
Initialize the adapter:
import { NodePostgresAdapter, PostgresJsAdapter } from "@lucia-auth/adapter-postgresql";
// previously named `pg` adapter
new NodePostgresAdapter(pool, {
// table names
user: "auth_user",
session: "user_session"
});
// previously named `postgres` adapter
new PostgresJsAdapter(sql, {
// table names
user: "auth_user",
session: "user_session"
});
Update session table
The main change to the session table is that the idle_expires
and active_expires
columns are replaced with a single expires_at
column. Unlike the previous columns, it's a DATETIME
column.
Check your table names before running the code.
START TRANSACTION;
ALTER TABLE user_session ADD COLUMN expires_at TIMESTAMPTZ;
UPDATE user_session SET expires_at = to_timestamp(idle_expires / 1000);
ALTER TABLE user_session
DROP COLUMN active_expires,
DROP COLUMN idle_expires,
ALTER COLUMN expires_at SET NOT NULL;
Do a final check and commit the transaction.
COMMIT;
You may also just delete the session table and replace it with the new schema.
Replace key table
You can keep using the key table, but we recommend using dedicated tables for each authentication method.
OAuth
The SQL below creates a dedicated table oauth_account
for storing all user OAuth accounts. This assumes all keys where hashed_password
column is null are for OAuth accounts. You may also separate them by the OAuth provider.
CREATE TABLE oauth_account (
provider_id TEXT NOT NULL,
provider_user_id TEXT NOT NULL,
user_id TEXT NOT NULL REFERENCES auth_user(id),
PRIMARY KEY (provider_id, provider_user_id)
);
INSERT INTO oauth_account (provider_id, provider_user_id, user_id)
SELECT SUBSTRING(id, 1, POSITION(':' IN id)-1), SUBSTRING(id, POSITION(':' IN id)+1), user_id FROM user_key
WHERE hashed_password IS NULL;
Email/password
The SQL below creates a dedicated table password
for storing user passwords. This assumes the provider ID for emails was email
and that you're already storing the users' emails in the user table.
CREATE TABLE password (
id SERIAL PRIMARY KEY,
hashed_password TEXT NOT NULL,
user_id TEXT NOT NULL REFERENCES auth_user(id)
);
INSERT INTO password (hashed_password, user_id)
SELECT hashed_password, user_id FROM user_key
WHERE SUBSTRING(id, 1, POSITION(':' IN id)-1) = 'email';
Alternatively, you can store the user's credentials in the user table if you only work with email/password.
START TRANSACTION;
ALTER TABLE auth_user ADD COLUMN hashed_password TEXT;
UPDATE auth_user SET hashed_password = user_key.hashed_password FROM user_key
WHERE user_key.user_id = auth_user.id
AND user_key.hashed_password IS NOT NULL;
ALTER TABLE auth_user ALTER COLUMN hashed_password SET NOT NULL;
COMMIT;