psql jsonb migration example

000001_first.down.sql

-- empty

000001_first.up.sql

CREATE TABLE IF NOT EXISTS bucket (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  description text NOT NULL,
  url_slug text NOT NULL UNIQUE,
  http_target_url text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  bucket_id bigint NOT NULL,
  name text,
  payload text NOT NULL,
  meta jsonb NOT NULL DEFAULT '{}'::json, -- for http headers and such
  suspended bool NOT NULL DEFAULT false,
  copy_of bigint NULL REFERENCES event (id),
  concluded_by_event_action_id bigint,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS event_action (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_id bigint NOT NULL,
  success boolean NOT NULL,
  resp_status text,
  resp_bytes bytea,
  error text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS event_action_idx_event_id
    ON event_action (event_id);

000002_purer-http-schema.down.sql

ALTER TABLE event
  DROP COLUMN http_req_client_ip,
  DROP COLUMN http_req_host,
  DROP COLUMN http_req_method,
  DROP COLUMN http_req_uri,
  DROP COLUMN http_req_headers,
  DROP COLUMN http_req_body;

000002_purer-http-schema.up.sql

ALTER TABLE event
  ADD COLUMN http_req_client_ip inet NULL,
  ADD COLUMN http_req_host text NULL,
  ADD COLUMN http_req_method text NULL,
  ADD COLUMN http_req_uri text NULL,
  ADD COLUMN http_req_headers jsonb NULL,
  ADD COLUMN http_req_body text NULL;

-- Move data from old columns to new
UPDATE event
SET
  http_req_client_ip = ((meta->>'X-HM-Request-Remote-Client-IP'))::inet,
  http_req_host = meta->>'X-HM-Request-Host',
  http_req_method = meta->>'X-HM-Request-Method',
  http_req_uri = meta->>'X-HM-Request-URI',
  http_req_headers = meta - 'X-HM-Request-Remote-Client-IP' - 'X-HM-Request-Host' - 'X-HM-Request-Method' - 'X-HM-Request-URI',
  http_req_body = payload;

ALTER TABLE event
  ALTER COLUMN http_req_client_ip SET NOT NULL,
  ALTER COLUMN http_req_host SET NOT NULL,
  ALTER COLUMN http_req_method SET NOT NULL,
  ALTER COLUMN http_req_uri SET NOT NULL,
  ALTER COLUMN http_req_headers SET NOT NULL,
  ALTER COLUMN http_req_body SET NOT NULL;

000003_drop_old_http_columns.down.sql

ALTER TABLE event
  ADD COLUMN meta jsonb NULL DEFAULT '{}'::json,
  ADD COLUMN payload text NULL;

UPDATE event
SET
  meta = (
    FORMAT(
      '{"X-HM-Request-Remote-Client-IP":"%s","X-HM-Request-Host":"%s","X-HM-Request-Method":"%s","X-HM-Request-URI":"%s"}',
      http_req_client_ip::text, http_req_host, http_req_method, http_req_uri
    )::jsonb || http_req_headers
  ),
  payload = http_req_body;

ALTER TABLE event
  ALTER COLUMN meta SET NOT NULL,
  ALTER COLUMN payload SET NOT NULL;

000003_drop_old_http_columns.up.sql

ALTER TABLE event
  DROP COLUMN meta,
  DROP COLUMN payload;