https://supertokens.com/ logo
db migration
c

Chimanos

05/09/2023, 12:31 PM
Hi ! Is there any known procedure to upgrade the core when using Docker ? I installed 5.0 this morning (by changing my compose file with
5.0
); and am getting :
Unknown column 'use_static_key' in 'field list'
r

rp

05/09/2023, 12:36 PM
hey @Chimanos - checkout the changelog on our supertokens-core repo. It has SQL commands you can run to get your db corrected.
c

Chimanos

05/09/2023, 12:46 PM
It works, but it will not be that familiar to apply this in a production environment. Any plans to auto-migrate or at least provide some tools to do so in the docker setup ?
r

rp

05/09/2023, 12:46 PM
we do want to add this feature for sure. Maybe in a few months when we have time.
But until then, we will always provide some script / clear instrs on who to do the db related migrations in the changelog
c

Chimanos

05/09/2023, 12:59 PM
Ok thanks 🙂 Just as a side note : the SQL query provided in the CHANGELOG might potentially be dangerous for the future : In the new version of the database creation SQL script (from scratch), the new column doesn't have a default :
sql
    use_static_key BOOLEAN NOT NULL,
whereas the migration propose setting the default to
false
/
true
depending on the
access_token_signing_key_dynamic
config. This leads to a database structure state which is different if created from scratch or migrated. If a further INSERT omits the
use_static_key
column for some reason; the behaviors will be different. I understand why it's set up like so (to auto-migrate the nullish columns); but I believe this
DEFAULT
could/should be dropped afterward :
sql
ALTER TABLE session_info ADD COLUMN use_static_key BOOLEAN NOT NULL DEFAULT(false);
ALTER TABLE session_info ALTER COLUMN use_static_key DROP DEFAULT;
(untested)
r

rp

05/09/2023, 1:17 PM
Right. This makes a lot of sense. Thanks for pointing this out. I’ll let @porcellus change the Changelog
c

Chimanos

05/09/2023, 1:32 PM
Great !
For what it's worth, the 4.5 upgrade CHANGELOG doesn't include the SQL script. I'm assuming this is the added tail at the end of the from-scratch script
sql
CREATE TABLE IF NOT EXISTS totp_users (
    user_id VARCHAR(128) NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS totp_user_devices (
    user_id VARCHAR(128) NOT NULL,
    device_name VARCHAR(256) NOT NULL,
    secret_key VARCHAR(256) NOT NULL,
    period INTEGER NOT NULL,
    skew INTEGER NOT NULL,
    verified BOOLEAN NOT NULL,
    PRIMARY KEY (user_id, device_name),
    FOREIGN KEY (user_id) REFERENCES totp_users(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS totp_used_codes (
    user_id VARCHAR(128) NOT NULL,
    code VARCHAR(8) NOT NULL,
    is_valid BOOLEAN NOT NULL,
    expiry_time_ms BIGINT UNSIGNED NOT NULL,
    created_time_ms BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, created_time_ms),
    FOREIGN KEY (user_id) REFERENCES totp_users(user_id) ON DELETE CASCADE
);

CREATE INDEX totp_used_codes_expiry_time_ms_index ON totp_used_codes(expiry_time_ms);
r

rp

05/09/2023, 3:37 PM
Oh yea. That’s cause the core add those tables automatically.
When you upgrade.