Refactor migration scripts for safe column removals and roleId handling #17

New Issue

MrUnknownDE · 2026-04-05T17:00:32+02:00

MrUnknownDE commented

2026-04-05 17:00:32 +02:00

Originally created by @marcschaeferger on 4/2/2026

Community Contribution License Agreement

By creating this pull request, I grant the project maintainers an unlimited,
perpetual license to use, modify, and redistribute these contributions under any terms they
choose, including both the AGPLv3 and the Fossorial Commercial license terms. I
represent that I have the right to grant this license for all contributed content.

Description (generated by Copilot)

This pull request improves the robustness and idempotency of the 1.17.0 database migration scripts for both PostgreSQL and SQLite backends. The changes ensure that migrations can be safely run on databases that may already have some schema changes applied (such as from release candidate upgrades), preventing crashes and errors due to missing columns or constraints.

PostgreSQL migration script improvements (server/setup/scriptsPg/1.17.0.ts):

Added guards when querying roleId columns from userOrgs and userInvites to handle cases where the columns may have already been removed, preventing migration failures on partially-upgraded schemas.
Changed DROP CONSTRAINT and DROP COLUMN statements to use IF EXISTS, making the migration idempotent and preventing errors if the constraints or columns are already absent. [1] [2] [3]

SQLite migration script improvements (server/setup/scriptsSqlite/1.17.0.ts):

Checked for the existence of roleId columns in userOrgs and userInvites tables before querying, ensuring migrations work even if those columns were already dropped in previous upgrades.
Used CREATE TABLE IF NOT EXISTS and CREATE UNIQUE INDEX IF NOT EXISTS for new tables and indexes to avoid errors if the migration is re-run. [1] [2] [3]
Added DROP TABLE IF EXISTS statements before creating temporary tables to ensure a clean migration state. [1] [2]

How to test?

Run Migration

*Originally created by @marcschaeferger on 4/2/2026* ## Community Contribution License Agreement By creating this pull request, I grant the project maintainers an unlimited, perpetual license to use, modify, and redistribute these contributions under any terms they choose, including both the AGPLv3 and the Fossorial Commercial license terms. I represent that I have the right to grant this license for all contributed content. ## Description (generated by Copilot) This pull request improves the robustness and idempotency of the 1.17.0 database migration scripts for both PostgreSQL and SQLite backends. The changes ensure that migrations can be safely run on databases that may already have some schema changes applied (such as from release candidate upgrades), preventing crashes and errors due to missing columns or constraints. **PostgreSQL migration script improvements (`server/setup/scriptsPg/1.17.0.ts`):** * Added guards when querying `roleId` columns from `userOrgs` and `userInvites` to handle cases where the columns may have already been removed, preventing migration failures on partially-upgraded schemas. * Changed `DROP CONSTRAINT` and `DROP COLUMN` statements to use `IF EXISTS`, making the migration idempotent and preventing errors if the constraints or columns are already absent. [[1]](diffhunk://#diff-a329ba1abb0c9766012185262aefb8f56a6e6bc9a4119a66a74995a9008d2550L108-R127) [[2]](diffhunk://#diff-a329ba1abb0c9766012185262aefb8f56a6e6bc9a4119a66a74995a9008d2550L119-R141) [[3]](diffhunk://#diff-a329ba1abb0c9766012185262aefb8f56a6e6bc9a4119a66a74995a9008d2550L179-R198) **SQLite migration script improvements (`server/setup/scriptsSqlite/1.17.0.ts`):** * Checked for the existence of `roleId` columns in `userOrgs` and `userInvites` tables before querying, ensuring migrations work even if those columns were already dropped in previous upgrades. * Used `CREATE TABLE IF NOT EXISTS` and `CREATE UNIQUE INDEX IF NOT EXISTS` for new tables and indexes to avoid errors if the migration is re-run. [[1]](diffhunk://#diff-db717df51f45b096d2c284f5c3c4563878c122a0b64af0bb4f71877e7aeb65faL112-R139) [[2]](diffhunk://#diff-db717df51f45b096d2c284f5c3c4563878c122a0b64af0bb4f71877e7aeb65faL124-R154) [[3]](diffhunk://#diff-db717df51f45b096d2c284f5c3c4563878c122a0b64af0bb4f71877e7aeb65faL150-R178) * Added `DROP TABLE IF EXISTS` statements before creating temporary tables to ensure a clean migration state. [[1]](diffhunk://#diff-db717df51f45b096d2c284f5c3c4563878c122a0b64af0bb4f71877e7aeb65faL124-R154) [[2]](diffhunk://#diff-db717df51f45b096d2c284f5c3c4563878c122a0b64af0bb4f71877e7aeb65faR187) ## How to test? Run Migration

MrUnknownDE closed this issue

2026-04-05 17:00:32 +02:00

Sign in to join this conversation.

Branches Tags

main

dev

dependabot/npm_and_yarn/dev-minor-updates-b4e5d6b9c5

revert-2766-feature/systemd-install-instructions

dependabot/npm_and_yarn/prod-patch-updates-05702d39f2

dependabot/npm_and_yarn/next-16.2.1

dependabot/npm_and_yarn/recharts-3.8.1

alerting-rules

private-site-ha

dependabot/docker/docker/library/node-25-slim

ssh

delete-account

msg-delivery

org-only-idp

cicd

patch

site-targets-auto-login

No Label

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/pangolin#17