Along similar lines, what do you think of replacing goose? Having looked at how
goose works I don't
think will be too difficult, its repository isn't maintained, and it has a
number of annoying bugs/lack
of features.
Goose at a very basic level merely runs sql statements under `--goose up` on
the goose up command,
and runs statements under `--goose down` on the goose down command, while
storing timestamp
numbers in the database so that it can keep track of the migrations.
Here are some additional problems with goose:
no line information on which sql command fails
solution: run the sql file yourself
\set doesn't work, leading to very verbose migrations
Seems Exec (from golang side) can't use it, but passing the file to
psql works.
These are weird, but not 'bad':
if UP has error, DOWN will look like it has returned OK, when nothing was ever
run
solution: ignore..
For the goose parser specifically:
comments occasionally are interpreted as sql queries
solution: must have sql after the comment
semicolons are required for goose, not necessarily for sql
solution: write sql that requires semicolons
I know there are other problems people have had with goose that I haven't run
into as well.
Goose should be more homogenous with sql and shouldn't even have its own parser.
I think that a good solution for replacing goose would be to use psql to apply
.sql files, then
keep track of timestamp data in a similar way. The main difference would be
that you wouldn't
put both your up and down migrations in the same file.
On 10/18/18, 5:49 PM, "Rawlin Peters" <[email protected]> wrote:
I'd like to propose we drop support for `goose down` in terms of doing
a Traffic Ops downgrade.
Right now whenever you upgrade Traffic Ops you also need to run
`db/admin.pl upgrade` to migrate the DB to the latest version. This
step runs all unapplied migrations since the last DB migration was
applied. However, if something goes wrong with the deploy and TO needs
to be rolled back, you have to run `db/admin.pl down` X times if your
TO upgrade ran X migrations in order to get back to the pre-upgrade
state of the DB. There are also certain steps in `db/admin.pl upgrade`
that cannot be reversed with a `goose down`, because they are done in
patches.sql or seeds.sql. So even if you `db/admin.pl down` the
correct number of times to get back to the _original_ schema version,
it's likely that your data has actually changed irreversibly (but
maybe not in a very bad way).
A much safer alternative to `db/admin.pl down` is to simply restore a
pre-upgrade copy of the DB. I think we should make that the
"supported" DB rollback process rather than the `goose down`. For dev
purposes I think it's fine to still include `goose down` steps in your
migrations, but I think we should build pre-upgrade DB copying into
the official upgrade process as well as restoration of the pre-upgrade
DB on rollback.
Manually saving off a copy of the pre-upgrade DB should already be a
step in everyone's TO upgrade process, but I'm proposing we actually
build this functionality into the upgrade process itself, drop support
for `goose down`, and add support for DB restoration upon rollback.
Initially I'd like to just get +1/-1 on this proposal, then we can
follow up and figure out the best way to implement it.
- Rawlin
