Skip to content

Commit 8e684c8

Browse files
coadlercoadler
authored
feat: run all migrations in a transaction (#10966)
Updates coder/customers#365 This PR updates our migration framework to run all migrations in a single transaction. This is the same behavior we had in v1 and ensures that failed migrations don't bring the whole deployment down. If a migration fails now, it will automatically be rolled back to the previous version, allowing the deployment to continue functioning.
1 parent 60d0aa6 commit 8e684c8

File tree

109 files changed

+243
-372
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

109 files changed

+243
-372
lines changed

‎coderd/database/dump.sql

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000030_template_version_created_by.up.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
BEGIN;
21

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

@@ -12,5 +11,3 @@ SET
1211
)
1312
WHERE
1413
created_by IS NULL;
15-
16-
COMMIT;

‎coderd/database/migrations/000035_linked_user_id.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2,8 +2,6 @@
22
-- the oauth_access_token, oauth_refresh_token, and oauth_expiry
33
-- columns of api_key rows with the values from the dropped user_links
44
-- table.
5-
BEGIN;
6-
75
DROP TABLE IF EXISTS user_links;
86

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

2119
ALTER TABLE users DROP COLUMN login_type;
22-
23-
COMMIT;

‎coderd/database/migrations/000035_linked_user_id.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
CREATE TABLE IF NOT EXISTS user_links (
42
user_id uuid NOT NULL,
53
login_type login_type NOT NULL,
@@ -70,5 +68,3 @@ FROM
7068
user_links
7169
WHERE
7270
user_links.user_id = users.id;
73-
74-
COMMIT;

‎coderd/database/migrations/000057_api_key_token.up.sql

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1 +1,20 @@
1-
ALTER TYPE login_type ADD VALUE IF NOT EXISTS 'token';
1+
CREATE TYPE new_logintype AS ENUM (
2+
'password',
3+
'github',
4+
'oidc',
5+
'token'
6+
);
7+
8+
ALTER TABLE users
9+
ALTER COLUMN login_type DROP DEFAULT,
10+
ALTER COLUMN login_type TYPE new_logintype USING (login_type::text::new_logintype),
11+
ALTER COLUMN login_type SET DEFAULT 'password'::new_logintype;
12+
13+
ALTER TABLE user_links
14+
ALTER COLUMN login_type TYPE new_logintype USING (login_type::text::new_logintype);
15+
16+
ALTER TABLE api_keys
17+
ALTER COLUMN login_type TYPE new_logintype USING (login_type::text::new_logintype);
18+
19+
DROP TYPE login_type;
20+
ALTER TYPE new_logintype RENAME TO login_type;

‎coderd/database/migrations/000058_template_acl.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,4 @@
1-
BEGIN;
2-
31
DROP TABLE group_members;
42
DROP TABLE groups;
53
ALTER TABLE templates DROP COLUMN group_acl;
64
ALTER TABLE templates DROP COLUMN user_acl;
7-
8-
COMMIT;

‎coderd/database/migrations/000058_template_acl.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
ALTER TABLE templates ADD COLUMN user_acl jsonb NOT NULL default '{}';
42
ALTER TABLE templates ADD COLUMN group_acl jsonb NOT NULL default '{}';
53

@@ -44,5 +42,3 @@ SET
4442
WHERE
4543
templates.organization_id = organizations.id
4644
);
47-
48-
COMMIT;

‎coderd/database/migrations/000059_file_id.down.sql

Lines changed: 1 addition & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
-- Add back the storage_source column. This must be nullable temporarily.
42
ALTER TABLE provisioner_jobs ADD COLUMN storage_source text;
53

@@ -30,12 +28,10 @@ AND
3028
a.hash = b.hash;
3129

3230
-- Drop the primary key on files.id.
33-
ALTER TABLE files DROP CONSTRAINT files_pkey;
31+
ALTER TABLE files DROP CONSTRAINT files_pkey;
3432
-- Drop the id column.
3533
ALTER TABLE files DROP COLUMN id;
3634
-- Drop the unique constraint on hash + owner.
3735
ALTER TABLE files DROP CONSTRAINT files_hash_created_by_key;
3836
-- Set the primary key back to hash.
3937
ALTER TABLE files ADD PRIMARY KEY (hash);
40-
41-
COMMIT;

‎coderd/database/migrations/000059_file_id.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -9,8 +9,6 @@
99
-- This migration also adds a 'files.id' column as the primary
1010
-- key. As a side effect the provisioner_jobs must now reference
1111
-- the files.id column since the 'hash' column is now ambiguous.
12-
BEGIN;
13-
1412
-- Drop the primary key on hash.
1513
ALTER TABLE files DROP CONSTRAINT files_pkey;
1614

@@ -38,5 +36,3 @@ WHERE
3836
ALTER TABLE provisioner_jobs ALTER COLUMN file_id SET NOT NULL;
3937
-- Drop storage_source since it is no longer useful for anything.
4038
ALTER TABLE provisioner_jobs DROP COLUMN storage_source;
41-
42-
COMMIT;

‎coderd/database/migrations/000062_group_avatars.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1 @@
1-
BEGIN;
2-
31
ALTER TABLE groups DROP COLUMN avatar_url;
4-
5-
COMMIT;

‎coderd/database/migrations/000062_group_avatars.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1 @@
1-
BEGIN;
2-
31
ALTER TABLE groups ADD COLUMN avatar_url text NOT NULL DEFAULT '';
4-
5-
COMMIT;

‎coderd/database/migrations/000063_resource_type_group.up.sql

Lines changed: 1 addition & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1 @@
1-
BEGIN;
2-
3-
ALTER TYPE resource_type ADD VALUE IF NOT EXISTS 'group';
4-
5-
COMMIT;
1+
ALTER TYPE resource_type ADD VALUE IF NOT EXISTS 'group';

‎coderd/database/migrations/000066_app_slug.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
-- add "slug" column to "workspace_apps" table
42
ALTER TABLE "workspace_apps" ADD COLUMN "slug" text DEFAULT '';
53

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

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

‎coderd/database/migrations/000067_app_display_name.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
-- Select all apps with an extra "row_number" column that determines the "rank"
42
-- of the display name against other display names in the same agent.
53
WITH row_numbers AS (
@@ -30,5 +28,3 @@ ALTER TABLE "workspace_apps" RENAME COLUMN "display_name" TO "name";
3028

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

‎coderd/database/migrations/000067_app_display_name.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,5 @@
1-
BEGIN;
2-
31
-- rename column "name" to "display_name" on "workspace_apps"
42
ALTER TABLE "workspace_apps" RENAME COLUMN "name" TO "display_name";
53

64
-- drop constraint "workspace_apps_agent_id_name_key" on "workspace_apps".
75
ALTER TABLE ONLY workspace_apps DROP CONSTRAINT IF EXISTS workspace_apps_agent_id_name_key;
8-
9-
COMMIT;

‎coderd/database/migrations/000068_update_template_version_created_by.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
UPDATE
42
template_versions
53
SET
@@ -14,5 +12,3 @@ WHERE
1412
created_by IS NULL;
1513

1614
ALTER TABLE template_versions ALTER COLUMN created_by SET NOT NULL;
17-
18-
COMMIT;

‎coderd/database/migrations/000072_add_agent_connection_timeout_and_troubleshooting_url.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,5 @@
1-
BEGIN;
2-
31
ALTER TABLE workspace_agents
42
DROP COLUMN connection_timeout_seconds;
53

64
ALTER TABLE workspace_agents
75
DROP COLUMN troubleshooting_url;
8-
9-
COMMIT;

‎coderd/database/migrations/000072_add_agent_connection_timeout_and_troubleshooting_url.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
ALTER TABLE workspace_agents
42
ADD COLUMN connection_timeout_seconds integer NOT NULL DEFAULT 0;
53

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

119
COMMENT ON COLUMN workspace_agents.troubleshooting_url IS 'URL for troubleshooting the agent.';
12-
13-
COMMIT;

‎coderd/database/migrations/000090_sqlc_upgrade_fix_nullable_values.down.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
21
ALTER TABLE parameter_schemas ALTER COLUMN default_source_scheme DROP NOT NULL;
32

43
ALTER TABLE parameter_schemas ALTER COLUMN default_destination_scheme DROP NOT NULL;
5-
COMMIT;

‎coderd/database/migrations/000090_sqlc_upgrade_fix_nullable_values.up.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,5 @@
1-
BEGIN;
21
UPDATE parameter_schemas SET default_source_scheme = 'none' WHERE default_source_scheme IS NULL;
32
ALTER TABLE parameter_schemas ALTER COLUMN default_source_scheme SET NOT NULL;
43

54
UPDATE parameter_schemas SET default_destination_scheme = 'none' WHERE default_destination_scheme IS NULL;
65
ALTER TABLE parameter_schemas ALTER COLUMN default_destination_scheme SET NOT NULL;
7-
COMMIT;

‎coderd/database/migrations/000093_add_workspace_agent_invert_delay_login_until_ready_to_login_before_ready.down.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,6 @@
1-
BEGIN;
21
ALTER TABLE workspace_agents RENAME COLUMN login_before_ready TO delay_login_until_ready;
32
ALTER TABLE workspace_agents ALTER COLUMN delay_login_until_ready SET DEFAULT false;
43

54
UPDATE workspace_agents SET delay_login_until_ready = NOT delay_login_until_ready;
65

76
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).';
8-
COMMIT;

‎coderd/database/migrations/000093_add_workspace_agent_invert_delay_login_until_ready_to_login_before_ready.up.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,6 @@
1-
BEGIN;
21
ALTER TABLE workspace_agents RENAME COLUMN delay_login_until_ready TO login_before_ready;
32
ALTER TABLE workspace_agents ALTER COLUMN login_before_ready SET DEFAULT true;
43

54
UPDATE workspace_agents SET login_before_ready = NOT login_before_ready;
65

76
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).';
8-
COMMIT;

‎coderd/database/migrations/000096_agent_resolved_directory.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,2 @@
1-
BEGIN;
2-
31
ALTER TABLE ONLY workspace_agents
42
DROP COLUMN IF EXISTS expanded_directory;
5-
6-
COMMIT;

‎coderd/database/migrations/000096_agent_resolved_directory.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,5 @@
1-
BEGIN;
2-
31
ALTER TABLE ONLY workspace_agents
42
ADD COLUMN IF NOT EXISTS expanded_directory varchar(4096) DEFAULT '' NOT NULL;
53

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

‎coderd/database/migrations/000097_license_not_null_uuid.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,4 @@
1-
BEGIN;
2-
31
-- We need to assign uuids to any existing licenses that don't have them.
42
UPDATE licenses SET uuid = gen_random_uuid() WHERE uuid IS NULL;
53
-- Assert no licenses have null uuids.
64
ALTER TABLE ONLY licenses ALTER COLUMN uuid SET NOT NULL;
7-
8-
COMMIT;

‎coderd/database/migrations/000103_add_apikey_name.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,4 @@
1-
BEGIN;
2-
31
DROP INDEX idx_api_key_name;
42

53
ALTER TABLE ONLY api_keys
64
DROP COLUMN IF EXISTS token_name;
7-
8-
COMMIT;

‎coderd/database/migrations/000103_add_apikey_name.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
ALTER TABLE ONLY api_keys
42
ADD COLUMN IF NOT EXISTS token_name text NOT NULL DEFAULT '';
53

@@ -13,5 +11,3 @@ WHERE
1311
CREATE UNIQUE INDEX idx_api_key_name ON api_keys USING btree (user_id, token_name)
1412
WHERE
1513
(login_type = 'token');
16-
17-
COMMIT;

‎coderd/database/migrations/000110_add_startup_logs.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
CREATE TABLE IF NOT EXISTS workspace_agent_startup_logs (
42
agent_id uuid NOT NULL REFERENCES workspace_agents (id) ON DELETE CASCADE,
53
created_at timestamptz NOT NULL,
@@ -14,5 +12,3 @@ ALTER TABLE workspace_agents ADD COLUMN startup_logs_overflowed boolean NOT NULL
1412

1513
COMMENT ON COLUMN workspace_agents.startup_logs_length IS 'Total length of startup logs';
1614
COMMENT ON COLUMN workspace_agents.startup_logs_overflowed IS 'Whether the startup logs overflowed in length';
17-
18-
COMMIT;

‎coderd/database/migrations/000114_workspace_proxy.down.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1 @@
1-
BEGIN;
21
DROP TABLE workspace_proxies;
3-
4-
COMMIT;

‎coderd/database/migrations/000114_workspace_proxy.up.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
BEGIN;
21
CREATE TABLE workspace_proxies (
32
id uuid NOT NULL,
43
name text NOT NULL,
@@ -19,5 +18,3 @@ COMMENT ON COLUMN workspace_proxies.wildcard_hostname IS 'Hostname with the wild
1918

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

‎coderd/database/migrations/000118_workspace_proxy_token.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,2 @@
1-
BEGIN;
2-
31
ALTER TABLE workspace_proxies
42
DROP COLUMN token_hashed_secret;
5-
6-
COMMIT;

‎coderd/database/migrations/000118_workspace_proxy_token.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
-- It's difficult to generate tokens for existing proxies, so we'll just delete
42
-- them if they exist.
53
--
@@ -18,5 +16,3 @@ COMMENT ON COLUMN workspace_proxies.deleted
1816

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

‎coderd/database/migrations/000119_workspace_proxy_name_idx.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,4 @@
1-
BEGIN;
2-
31
DROP INDEX IF EXISTS workspace_proxies_lower_name_idx;
42

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

‎coderd/database/migrations/000119_workspace_proxy_name_idx.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,7 @@
1-
BEGIN;
2-
31
-- No one is using this feature yet as of writing this migration, so this is
42
-- fine. Just delete all workspace proxies to prevent the new index from having
53
-- conflicts.
64
DELETE FROM workspace_proxies;
75

86
DROP INDEX IF EXISTS workspace_proxies_name_idx;
97
CREATE UNIQUE INDEX workspace_proxies_lower_name_idx ON workspace_proxies USING btree (lower(name)) WHERE deleted = FALSE;
10-
11-
COMMIT;

‎coderd/database/migrations/000120_trigger_delete_user_apikey.down.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,5 @@
1-
BEGIN;
2-
31
DROP TRIGGER IF EXISTS trigger_update_users ON users;
42
DROP FUNCTION IF EXISTS delete_deleted_user_api_keys;
53

64
DROP TRIGGER IF EXISTS trigger_insert_apikeys ON api_keys;
75
DROP FUNCTION IF EXISTS insert_apikey_fail_if_user_deleted;
8-
9-
COMMIT;

‎coderd/database/migrations/000120_trigger_delete_user_apikey.up.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
BEGIN;
2-
31
-- We need to delete all existing API keys for soft-deleted users.
42
DELETE FROM
53
api_keys
@@ -51,5 +49,3 @@ CREATE TRIGGER trigger_insert_apikeys
5149
BEFORE INSERT ON api_keys
5250
FOR EACH ROW
5351
EXECUTE PROCEDURE insert_apikey_fail_if_user_deleted();
54-
55-
COMMIT;

‎coderd/database/migrations/000122_add_template_cleanup_ttls.down.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,2 @@
1-
BEGIN;
21
ALTER TABLE ONLY templates DROP COLUMN IF EXISTS failure_ttl;
32
ALTER TABLE ONLY templates DROP COLUMN IF EXISTS inactivity_ttl;
4-
COMMIT;

‎coderd/database/migrations/000122_add_template_cleanup_ttls.up.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,2 @@
1-
BEGIN;
21
ALTER TABLE ONLY templates ADD COLUMN IF NOT EXISTS failure_ttl BIGINT NOT NULL DEFAULT 0;
32
ALTER TABLE ONLY templates ADD COLUMN IF NOT EXISTS inactivity_ttl BIGINT NOT NULL DEFAULT 0;
4-
COMMIT;

‎coderd/database/migrations/000123_workspace_agent_subsystem.down.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,2 @@
1-
BEGIN;
21
ALTER TABLE workspace_agents DROP COLUMN subsystem;
32
DROP TYPE workspace_agent_subsystem;
4-
COMMIT;

0 commit comments

Comments
 (0)