feature: add functionality to dump database schema

mfridman commented 3 years ago

The proposal is to enable goose to drop the current database schema. This is usually done with tools like pg_dump.

Now, this would require the user to have this executable in their $PATH or we can invoke a lightweight container such as postgres:14-alpine, if neither is available then fail.

I don't think this should live inside the github.com/pressly/goose/v3 package, and instead should be its own subdir package, such as pgutil or something. I imagine we should expose a few of these mysqlutil. (can't come up with a better name, suggestions welcome to not use {dialect}util)

In the goose binaries, the behaviour could be to dump a schema file after running a modifying migration. The file could be written to the same folder as -dir as schema.sql

timuckun commented 2 years ago

+1

mfridman commented 1 year ago

I've had to implement the command in this https://github.com/pressly/goose/issues/345#issuecomment-1121375847 a few times now, would be nice to have goose do this, for consistency.

mfridman commented 9 months ago

This was another useful snippet a user shared in Gophers Public Slack:

pg_dump --schema-only \
  --no-comments \
  --quote-all-identifiers \
  -T public.goose_db_version \
  -T public.goose_db_version_id_seq | sed \
    -e '/^--.*/d' \
    -e '/^SET /d' \
    -e '/^[[:space:]]*$/d' \
    -e '/^SELECT pg_catalog./d' \
    -e '/^ALTER TABLE .* OWNER TO "postgres";/d' \
    -e 's/"public"\.//' \
      > ./schema/schema.sql

pressly / goose

feature: add functionality to dump database schema #278