stripe / pg-schema-diff

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

Update CLI with multi-schema support and database schema source #108

Closed bplunkett-stripe closed 5 months ago

bplunkett-stripe commented 5 months ago

Description

In a follow-up PR, I'm going to update the statement modifiers to follow logfmt. The current way they are specified is awful.

Motivation

https://github.com/stripe/pg-schema-diff/issues/105

Testing

Normal (notice the some_schema related operations):

 ~/stripe/pg-schema-diff │ bplunkett/up…hema-support  go run ./cmd/pg-schema-diff plan  --dsn "host=localhost user=postgres password=postgres database=somedb" --schema-dir ~/stripe/temp/examplesql

################################ Generated plan ################################
1. CREATE TABLE "public"."foobar" (
        "id" integer,
        "some_other_column" character varying(255) COLLATE "pg_catalog"."default",
        "fizz" character varying(255) COLLATE "pg_catalog"."default"
);
        -- Statement Timeout: 3s

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

3. ALTER TABLE "public"."foobar" REPLICA IDENTITY FULL;
        -- Statement Timeout: 3s

4. CREATE INDEX CONCURRENTLY some_idx ON public.foobar USING btree (id);
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s

5. DROP TABLE "some_schema"."foobar";
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s
        -- Hazard DELETES_DATA: Deletes all rows in the table (and the table itself)

6. DROP SCHEMA "some_schema";
        -- Statement Timeout: 3s

Include schemas (public only):

 ~/stripe/pg-schema-diff │ bplunkett/up…hema-support  go run ./cmd/pg-schema-diff plan  --dsn "host=localhost user=postgres password=postgres database=somedb" --schema-dir ~/stripe/temp/examplesql --include-schema public

################################ Generated plan ################################
1. CREATE TABLE "public"."foobar" (
        "id" integer,
        "some_other_column" character varying(255) COLLATE "pg_catalog"."default",
        "fizz" character varying(255) COLLATE "pg_catalog"."default"
);
        -- Statement Timeout: 3s

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

3. ALTER TABLE "public"."foobar" REPLICA IDENTITY FULL;
        -- Statement Timeout: 3s

4. CREATE INDEX CONCURRENTLY some_idx ON public.foobar USING btree (id);
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s

Exclude schemas (public only):

go run ./cmd/pg-schema-diff plan  --dsn "host=localhost user=postgres password=postgres database=somedb" --schema-dir ~/stripe/temp/examplesql --exclude-schema some_schema

################################ Generated plan ################################
1. CREATE TABLE "public"."foobar" (
        "id" integer,
        "some_other_column" character varying(255) COLLATE "pg_catalog"."default",
        "fizz" character varying(255) COLLATE "pg_catalog"."default"
);
        -- Statement Timeout: 3s

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

3. ALTER TABLE "public"."foobar" REPLICA IDENTITY FULL;
        -- Statement Timeout: 3s

4. CREATE INDEX CONCURRENTLY some_idx ON public.foobar USING btree (id);
        -- Statement Timeout: 20m0s
        -- Lock Timeout: 3s

Comparing two databases:

go run ./cmd/pg-schema-diff plan  --dsn "host=localhost user=postgres password=postgres database=somedb"  --include-schema public  --schema-source-dsn "host=localhost user=postgres password=postgres database=postgres"

################################ Generated plan ################################
1. CREATE TABLE "public"."foobar" (

);
        -- Statement Timeout: 3s

No source specified:

 ~/stripe/pg-schema-diff │ bplunkett/up…hema-support  go run ./cmd/pg-schema-diff plan  --dsn "host=localhost user=postgres password=postgres database=somedb"                          INT х │ 15:17:22
Error: either --schema-dir or --schema-source-dsn must be set
Usage:
  pg-schema-diff plan [flags]