Skip to content

feat: run all migrations in a transaction #10966

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 9 commits into from
Dec 1, 2023
Merged

feat: run all migrations in a transaction #10966

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
b49ee44
feat: run all migrations in a transaction
coadler Nov 30, 2023
31524f3
fix migrations
coadler Nov 30, 2023
785ff59
lint
coadler Nov 30, 2023
9a87f47
fmt
coadler Dec 1, 2023
d9b7db6
revert
coadler Dec 1, 2023
307b999
add comments
coadler Dec 1, 2023
0cb53fd
hold migration lock on migration table creation
coadler Dec 1, 2023
480b99d
remove comment
coadler Dec 1, 2023
3ca5ebf
document the correct way to create a new enum value
coadler Dec 1, 2023
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion coderd/database/dump.sql
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

3 changes: 0 additions & 3 deletions coderd/database/migrations/000030_template_version_created_by.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,3 @@
BEGIN;

ALTER TABLE ONLY template_versions ADD COLUMN IF NOT EXISTS created_by uuid REFERENCES users (id) ON DELETE RESTRICT;

Expand All @@ -12,5 +11,3 @@ SET
)
WHERE
created_by IS NULL;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000035_linked_user_id.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,8 +2,6 @@
-- the oauth_access_token, oauth_refresh_token, and oauth_expiry
-- columns of api_key rows with the values from the dropped user_links
-- table.
BEGIN;

DROP TABLE IF EXISTS user_links;

ALTER TABLE
Expand All @@ -19,5 +17,3 @@ ALTER TABLE
ADD COLUMN oauth_expiry timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL;

ALTER TABLE users DROP COLUMN login_type;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000035_linked_user_id.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

CREATE TABLE IF NOT EXISTS user_links (
user_id uuid NOT NULL,
login_type login_type NOT NULL,
Expand Down Expand Up @@ -70,5 +68,3 @@ FROM
user_links
WHERE
user_links.user_id = users.id;

COMMIT;
21 changes: 20 additions & 1 deletion coderd/database/migrations/000057_api_key_token.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1 +1,20 @@
ALTER TYPE login_type ADD VALUE IF NOT EXISTS 'token';
CREATE TYPE new_logintype AS ENUM (
'password',
'github',
'oidc',
'token'
);

ALTER TABLE users
ALTER COLUMN login_type DROP DEFAULT,
ALTER COLUMN login_type TYPE new_logintype USING (login_type::text::new_logintype),
ALTER COLUMN login_type SET DEFAULT 'password'::new_logintype;

ALTER TABLE user_links
ALTER COLUMN login_type TYPE new_logintype USING (login_type::text::new_logintype);

ALTER TABLE api_keys
ALTER COLUMN login_type TYPE new_logintype USING (login_type::text::new_logintype);

DROP TYPE login_type;
ALTER TYPE new_logintype RENAME TO login_type;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000058_template_acl.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,8 +1,4 @@
BEGIN;

DROP TABLE group_members;
DROP TABLE groups;
ALTER TABLE templates DROP COLUMN group_acl;
ALTER TABLE templates DROP COLUMN user_acl;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000058_template_acl.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

ALTER TABLE templates ADD COLUMN user_acl jsonb NOT NULL default '{}';
ALTER TABLE templates ADD COLUMN group_acl jsonb NOT NULL default '{}';

Expand Down Expand Up @@ -44,5 +42,3 @@ SET
WHERE
templates.organization_id = organizations.id
);

COMMIT;
6 changes: 1 addition & 5 deletions coderd/database/migrations/000059_file_id.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

-- Add back the storage_source column. This must be nullable temporarily.
ALTER TABLE provisioner_jobs ADD COLUMN storage_source text;

Expand Down Expand Up @@ -30,12 +28,10 @@ AND
a.hash = b.hash;

-- Drop the primary key on files.id.
ALTER TABLE files DROP CONSTRAINT files_pkey;
ALTER TABLE files DROP CONSTRAINT files_pkey;
-- Drop the id column.
ALTER TABLE files DROP COLUMN id;
-- Drop the unique constraint on hash + owner.
ALTER TABLE files DROP CONSTRAINT files_hash_created_by_key;
-- Set the primary key back to hash.
ALTER TABLE files ADD PRIMARY KEY (hash);

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000059_file_id.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -9,8 +9,6 @@
-- This migration also adds a 'files.id' column as the primary
-- key. As a side effect the provisioner_jobs must now reference
-- the files.id column since the 'hash' column is now ambiguous.
BEGIN;

-- Drop the primary key on hash.
ALTER TABLE files DROP CONSTRAINT files_pkey;

Expand Down Expand Up @@ -38,5 +36,3 @@ WHERE
ALTER TABLE provisioner_jobs ALTER COLUMN file_id SET NOT NULL;
-- Drop storage_source since it is no longer useful for anything.
ALTER TABLE provisioner_jobs DROP COLUMN storage_source;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000062_group_avatars.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1 @@
BEGIN;

ALTER TABLE groups DROP COLUMN avatar_url;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000062_group_avatars.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1 @@
BEGIN;

ALTER TABLE groups ADD COLUMN avatar_url text NOT NULL DEFAULT '';

COMMIT;
6 changes: 1 addition & 5 deletions coderd/database/migrations/000063_resource_type_group.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1 @@
BEGIN;

ALTER TYPE resource_type ADD VALUE IF NOT EXISTS 'group';

COMMIT;
ALTER TYPE resource_type ADD VALUE IF NOT EXISTS 'group';
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Should we also do the new table + rename dance here as well?
Seems safer to just always do it that way instead of someone suddenly adding a new migration referencing this enum type and wondering why their new migration doesn't work.

Copy link
Contributor Author

@coadler coadler Dec 1, 2023 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I only went back historically and fixed usage of ADD VALUE if we reference the newly added value later in a different migration. It is otherwise fine to use within a transaction.

Edit: Ah sorry I didn't fully read. I see what you mean but I wasn't sure if it was worth going back to every type. It was really a pain to try and figure out for each enum what the exact values were at the time of the migration. I don't really mind following up though.

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I'll fix all of them in a follow up 👍

4 changes: 0 additions & 4 deletions coderd/database/migrations/000066_app_slug.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

-- add "slug" column to "workspace_apps" table
ALTER TABLE "workspace_apps" ADD COLUMN "slug" text DEFAULT '';

Expand All @@ -12,5 +10,3 @@ ALTER TABLE "workspace_apps" ALTER COLUMN "slug" DROP DEFAULT;

-- add unique index on "slug" column
ALTER TABLE "workspace_apps" ADD CONSTRAINT "workspace_apps_agent_id_slug_idx" UNIQUE ("agent_id", "slug");

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000067_app_display_name.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

-- Select all apps with an extra "row_number" column that determines the "rank"
-- of the display name against other display names in the same agent.
WITH row_numbers AS (
Expand Down Expand Up @@ -30,5 +28,3 @@ ALTER TABLE "workspace_apps" RENAME COLUMN "display_name" TO "name";

-- restore unique index on "workspace_apps" table
ALTER TABLE workspace_apps ADD CONSTRAINT workspace_apps_agent_id_name_key UNIQUE ("agent_id", "name");

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000067_app_display_name.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,9 +1,5 @@
BEGIN;

-- rename column "name" to "display_name" on "workspace_apps"
ALTER TABLE "workspace_apps" RENAME COLUMN "name" TO "display_name";

-- drop constraint "workspace_apps_agent_id_name_key" on "workspace_apps".
ALTER TABLE ONLY workspace_apps DROP CONSTRAINT IF EXISTS workspace_apps_agent_id_name_key;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000068_update_template_version_created_by.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

UPDATE
template_versions
SET
Expand All @@ -14,5 +12,3 @@ WHERE
created_by IS NULL;

ALTER TABLE template_versions ALTER COLUMN created_by SET NOT NULL;

COMMIT;
4 changes: 0 additions & 4 deletions .../database/migrations/000072_add_agent_connection_timeout_and_troubleshooting_url.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,9 +1,5 @@
BEGIN;

ALTER TABLE workspace_agents
DROP COLUMN connection_timeout_seconds;

ALTER TABLE workspace_agents
DROP COLUMN troubleshooting_url;

COMMIT;
4 changes: 0 additions & 4 deletions ...rd/database/migrations/000072_add_agent_connection_timeout_and_troubleshooting_url.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

ALTER TABLE workspace_agents
ADD COLUMN connection_timeout_seconds integer NOT NULL DEFAULT 0;

Expand All @@ -9,5 +7,3 @@ ALTER TABLE workspace_agents
ADD COLUMN troubleshooting_url text NOT NULL DEFAULT '';

COMMENT ON COLUMN workspace_agents.troubleshooting_url IS 'URL for troubleshooting the agent.';

COMMIT;
2 changes: 0 additions & 2 deletions coderd/database/migrations/000090_sqlc_upgrade_fix_nullable_values.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;
ALTER TABLE parameter_schemas ALTER COLUMN default_source_scheme DROP NOT NULL;

ALTER TABLE parameter_schemas ALTER COLUMN default_destination_scheme DROP NOT NULL;
COMMIT;
2 changes: 0 additions & 2 deletions coderd/database/migrations/000090_sqlc_upgrade_fix_nullable_values.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,5 @@
BEGIN;
UPDATE parameter_schemas SET default_source_scheme = 'none' WHERE default_source_scheme IS NULL;
ALTER TABLE parameter_schemas ALTER COLUMN default_source_scheme SET NOT NULL;

UPDATE parameter_schemas SET default_destination_scheme = 'none' WHERE default_destination_scheme IS NULL;
ALTER TABLE parameter_schemas ALTER COLUMN default_destination_scheme SET NOT NULL;
COMMIT;
2 changes: 0 additions & 2 deletions .../000093_add_workspace_agent_invert_delay_login_until_ready_to_login_before_ready.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,8 +1,6 @@
BEGIN;
ALTER TABLE workspace_agents RENAME COLUMN login_before_ready TO delay_login_until_ready;
ALTER TABLE workspace_agents ALTER COLUMN delay_login_until_ready SET DEFAULT false;

UPDATE workspace_agents SET delay_login_until_ready = NOT delay_login_until_ready;

COMMENT ON COLUMN workspace_agents.delay_login_until_ready IS 'If true, the agent will delay logins until it is ready (e.g. executing startup script has ended).';
COMMIT;
2 changes: 0 additions & 2 deletions ...ns/000093_add_workspace_agent_invert_delay_login_until_ready_to_login_before_ready.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,8 +1,6 @@
BEGIN;
ALTER TABLE workspace_agents RENAME COLUMN delay_login_until_ready TO login_before_ready;
ALTER TABLE workspace_agents ALTER COLUMN login_before_ready SET DEFAULT true;

UPDATE workspace_agents SET login_before_ready = NOT login_before_ready;

COMMENT ON COLUMN workspace_agents.login_before_ready IS 'If true, the agent will not prevent login before it is ready (e.g. startup script is still executing).';
COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000096_agent_resolved_directory.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,6 +1,2 @@
BEGIN;

ALTER TABLE ONLY workspace_agents
DROP COLUMN IF EXISTS expanded_directory;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000096_agent_resolved_directory.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,9 +1,5 @@
BEGIN;

ALTER TABLE ONLY workspace_agents
ADD COLUMN IF NOT EXISTS expanded_directory varchar(4096) DEFAULT '' NOT NULL;

COMMENT ON COLUMN workspace_agents.expanded_directory
IS 'The resolved path of a user-specified directory. e.g. ~/coder -> /home/coder/coder';

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000097_license_not_null_uuid.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,8 +1,4 @@
BEGIN;

-- We need to assign uuids to any existing licenses that don't have them.
UPDATE licenses SET uuid = gen_random_uuid() WHERE uuid IS NULL;
-- Assert no licenses have null uuids.
ALTER TABLE ONLY licenses ALTER COLUMN uuid SET NOT NULL;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000103_add_apikey_name.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,8 +1,4 @@
BEGIN;

DROP INDEX idx_api_key_name;

ALTER TABLE ONLY api_keys
DROP COLUMN IF EXISTS token_name;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000103_add_apikey_name.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

ALTER TABLE ONLY api_keys
ADD COLUMN IF NOT EXISTS token_name text NOT NULL DEFAULT '';

Expand All @@ -13,5 +11,3 @@ WHERE
CREATE UNIQUE INDEX idx_api_key_name ON api_keys USING btree (user_id, token_name)
WHERE
(login_type = 'token');

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000110_add_startup_logs.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

CREATE TABLE IF NOT EXISTS workspace_agent_startup_logs (
agent_id uuid NOT NULL REFERENCES workspace_agents (id) ON DELETE CASCADE,
created_at timestamptz NOT NULL,
Expand All @@ -14,5 +12,3 @@ ALTER TABLE workspace_agents ADD COLUMN startup_logs_overflowed boolean NOT NULL

COMMENT ON COLUMN workspace_agents.startup_logs_length IS 'Total length of startup logs';
COMMENT ON COLUMN workspace_agents.startup_logs_overflowed IS 'Whether the startup logs overflowed in length';

COMMIT;
3 changes: 0 additions & 3 deletions coderd/database/migrations/000114_workspace_proxy.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1 @@
BEGIN;
DROP TABLE workspace_proxies;

COMMIT;
3 changes: 0 additions & 3 deletions coderd/database/migrations/000114_workspace_proxy.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,3 @@
BEGIN;
CREATE TABLE workspace_proxies (
id uuid NOT NULL,
name text NOT NULL,
Expand All @@ -19,5 +18,3 @@ COMMENT ON COLUMN workspace_proxies.wildcard_hostname IS 'Hostname with the wild

-- Enforces no active proxies have the same name.
CREATE UNIQUE INDEX ON workspace_proxies (name) WHERE deleted = FALSE;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000118_workspace_proxy_token.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,6 +1,2 @@
BEGIN;

ALTER TABLE workspace_proxies
DROP COLUMN token_hashed_secret;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000118_workspace_proxy_token.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

-- It's difficult to generate tokens for existing proxies, so we'll just delete
-- them if they exist.
--
Expand All @@ -18,5 +16,3 @@ COMMENT ON COLUMN workspace_proxies.deleted

COMMENT ON COLUMN workspace_proxies.icon
IS 'Expects an emoji character. (/emojis/1f1fa-1f1f8.png)';

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000119_workspace_proxy_name_idx.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,8 +1,4 @@
BEGIN;

DROP INDEX IF EXISTS workspace_proxies_lower_name_idx;

-- Enforces no active proxies have the same name.
CREATE UNIQUE INDEX ON workspace_proxies (name) WHERE deleted = FALSE;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000119_workspace_proxy_name_idx.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,11 +1,7 @@
BEGIN;

-- No one is using this feature yet as of writing this migration, so this is
-- fine. Just delete all workspace proxies to prevent the new index from having
-- conflicts.
DELETE FROM workspace_proxies;

DROP INDEX IF EXISTS workspace_proxies_name_idx;
CREATE UNIQUE INDEX workspace_proxies_lower_name_idx ON workspace_proxies USING btree (lower(name)) WHERE deleted = FALSE;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000120_trigger_delete_user_apikey.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,9 +1,5 @@
BEGIN;

DROP TRIGGER IF EXISTS trigger_update_users ON users;
DROP FUNCTION IF EXISTS delete_deleted_user_api_keys;

DROP TRIGGER IF EXISTS trigger_insert_apikeys ON api_keys;
DROP FUNCTION IF EXISTS insert_apikey_fail_if_user_deleted;

COMMIT;
4 changes: 0 additions & 4 deletions coderd/database/migrations/000120_trigger_delete_user_apikey.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,3 @@
BEGIN;

-- We need to delete all existing API keys for soft-deleted users.
DELETE FROM
api_keys
Expand Down Expand Up @@ -51,5 +49,3 @@ CREATE TRIGGER trigger_insert_apikeys
BEFORE INSERT ON api_keys
FOR EACH ROW
EXECUTE PROCEDURE insert_apikey_fail_if_user_deleted();

COMMIT;
2 changes: 0 additions & 2 deletions coderd/database/migrations/000122_add_template_cleanup_ttls.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,2 @@
BEGIN;
ALTER TABLE ONLY templates DROP COLUMN IF EXISTS failure_ttl;
ALTER TABLE ONLY templates DROP COLUMN IF EXISTS inactivity_ttl;
COMMIT;
2 changes: 0 additions & 2 deletions coderd/database/migrations/000122_add_template_cleanup_ttls.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,2 @@
BEGIN;
ALTER TABLE ONLY templates ADD COLUMN IF NOT EXISTS failure_ttl BIGINT NOT NULL DEFAULT 0;
ALTER TABLE ONLY templates ADD COLUMN IF NOT EXISTS inactivity_ttl BIGINT NOT NULL DEFAULT 0;
COMMIT;
2 changes: 0 additions & 2 deletions coderd/database/migrations/000123_workspace_agent_subsystem.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,2 @@
BEGIN;
ALTER TABLE workspace_agents DROP COLUMN subsystem;
DROP TYPE workspace_agent_subsystem;
COMMIT;
Loading