stripe / pg-schema-diff

Go library for diffing Postgres schemas and generating SQL migrations
MIT License
270 stars 20 forks source link

Switch interfaces to accept conn pools #90

Closed bplunkett-stripe closed 5 months ago

bplunkett-stripe commented 5 months ago

Description

Switch to interfaces that accept conn pools. I would have just switched it directly to conn pools, but that limits backwards compatibility. In a major version update, I might get rid of the interface and just use conn pools.

Motivation

In a subsequent PR, we will parallelize schema fetching.

Testing

Passes acceptance tests.

CLI:

  go run ./cmd/pg-schema-diff apply  --dsn "host=localhost user=postgres password=postgres database=somedb" --schema-dir ~/stripe/temp/examplesql  --allow-hazards INDEX_DROPPED,DELETES_DATA,INDEX_BUILD
################################## Review plan ##################################
01. DROP INDEX CONCURRENTLY "message_idx";
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s
        -- Hazard INDEX_DROPPED: Dropping this index means queries that use this index might perform worse because they will no longer will be able to leverage it.

02. ALTER TABLE "public"."foobar" ADD COLUMN "fizz" character varying(255) COLLATE "pg_catalog"."default";
        -- Statement Timeout: 3s

03. ALTER TABLE "public"."foobar" ADD COLUMN "id" integer;
        -- Statement Timeout: 3s

04. ALTER TABLE "public"."foobar" ADD CONSTRAINT "some_constraint" CHECK((id > 0)) NOT VALID;
        -- Statement Timeout: 3s

05. ALTER TABLE "public"."foobar" VALIDATE CONSTRAINT "some_constraint";
        -- Statement Timeout: 3s

06. ALTER TABLE "public"."foobar" ADD CONSTRAINT "some_other_constraint" CHECK((id > 5)) NOT VALID;
        -- Statement Timeout: 3s

07. ALTER TABLE "public"."foobar" VALIDATE CONSTRAINT "some_other_constraint";
        -- Statement Timeout: 3s

08. ALTER TABLE "public"."foobar" ADD COLUMN "some_other_column" character varying(255) COLLATE "pg_catalog"."default";
        -- Statement Timeout: 3s

09. ALTER TABLE "public"."foobar" DROP COLUMN "created_at";
        -- Statement Timeout: 3s
        -- Hazard DELETES_DATA: Deletes all values in the column

10. ALTER TABLE "public"."foobar" DROP COLUMN "message";
        -- Statement Timeout: 3s
        -- Hazard DELETES_DATA: Deletes all values in the column

11. CREATE INDEX CONCURRENTLY some_idx ON public.foobar USING btree (id);
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s
        -- Hazard INDEX_BUILD: This might affect database performance. Concurrent index builds require a non-trivial amount of CPU, potentially affecting database performance. They also can take a while but do not lock out writes.

✔ Yes
############################# Executing statement 1 #############################
DROP INDEX CONCURRENTLY "message_idx";
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s
        -- Hazard INDEX_DROPPED: Dropping this index means queries that use this index might perform worse because they will no longer will be able to leverage it.

Finished executing statement. Duration: 6.698625ms
############################# Executing statement 2 #############################
ALTER TABLE "public"."foobar" ADD COLUMN "fizz" character varying(255) COLLATE "pg_catalog"."default";
        -- Statement Timeout: 3s

Finished executing statement. Duration: 2.931792ms
############################# Executing statement 3 #############################
ALTER TABLE "public"."foobar" ADD COLUMN "id" integer;
        -- Statement Timeout: 3s

Finished executing statement. Duration: 935.292µs
############################# Executing statement 4 #############################
ALTER TABLE "public"."foobar" ADD CONSTRAINT "some_constraint" CHECK((id > 0)) NOT VALID;
        -- Statement Timeout: 3s

Finished executing statement. Duration: 2.230958ms
############################# Executing statement 5 #############################
ALTER TABLE "public"."foobar" VALIDATE CONSTRAINT "some_constraint";
        -- Statement Timeout: 3s

Finished executing statement. Duration: 1.313709ms
############################# Executing statement 6 #############################
ALTER TABLE "public"."foobar" ADD CONSTRAINT "some_other_constraint" CHECK((id > 5)) NOT VALID;
        -- Statement Timeout: 3s

Finished executing statement. Duration: 1.143958ms
############################# Executing statement 7 #############################
ALTER TABLE "public"."foobar" VALIDATE CONSTRAINT "some_other_constraint";
        -- Statement Timeout: 3s

Finished executing statement. Duration: 617.917µs
############################# Executing statement 8 #############################
ALTER TABLE "public"."foobar" ADD COLUMN "some_other_column" character varying(255) COLLATE "pg_catalog"."default";
        -- Statement Timeout: 3s

Finished executing statement. Duration: 1.379ms
############################# Executing statement 9 #############################
ALTER TABLE "public"."foobar" DROP COLUMN "created_at";
        -- Statement Timeout: 3s
        -- Hazard DELETES_DATA: Deletes all values in the column

Finished executing statement. Duration: 1.005041ms
############################ Executing statement 10 ############################
ALTER TABLE "public"."foobar" DROP COLUMN "message";
        -- Statement Timeout: 3s
        -- Hazard DELETES_DATA: Deletes all values in the column

Finished executing statement. Duration: 833.041µs
############################ Executing statement 11 ############################
CREATE INDEX CONCURRENTLY some_idx ON public.foobar USING btree (id);
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s
        -- Hazard INDEX_BUILD: This might affect database performance. Concurrent index builds require a non-trivial amount of CPU, potentially affecting database performance. They also can take a while but do not lock out writes.

Finished executing statement. Duration: 3.896167ms
################################### Complete ###################################
Schema applied successfully