Up/Down migrations support

[tchoutri]

Hi! I am a very satisfied user of postgresql-migration, and I wanted to thank you @andrevdm for doing the work of reviving the original tool.

I would like to suggest a design for Up/Down migrations that is close to existing tools in the space (thinking especially of dbmate¹ and Laravel²).

We can do it the "easy" way, which means that for file-based migrations we "split" the file into two sections:

1. The "up" migration (which is what we already have at the moment)
2. The "down" migration, its counterpart.

These sections would be signalled with an SQL comment like with dbmate:

-- migrate:up

create table if not exists …

-- migrate:down 

drop table if exists …

In terms of user-side interface, we could call

• migrate up to combine a createdb, migrate init and migrate migrate
• migrate down (or rollback) to rollback the latest migration, with an optional flag to specify the number of migrations to rollback from the last, and maybe an optional flag to specify a particular migration to rollback.

---

What do you say?

[divarvel]

+1 that would be very convenient.

We could keep the UI close to what migrate already does: find the latest applied migration, rollback those which need to be rolled back (ie in the migration table but not in the migration files), and then run the Up migrations. That would require storing full migrations in the table instead of just hashes though.

[andrevdm]

@tchoutri

I am a very satisfied user of postgresql-migration, and I wanted to thank you @andrevdm for doing the work of reviving the original tool.

It is a great tool, happy I can help

I would like to suggest a design for Up/Down migrations

I think down migrations could be a nice addition. The one thing I'd like be be careful of is adding too much complexity. postgresql-migration is pretty simple and I think its worth trying to keep that way, within reason.

We can do it the "easy" way, which means that for file-based migrations we "split" the file into two sections:

Agreed, I think that fits in nicely with the way things already work. If we use an appropriate delimiter then would not even be a need for a parser library dependency. Simply string match on the tag

In terms of user-side interface, we could call

• migrate up to combine a createdb, migrate init and migrate migrate

I'm not 100% sure about the first point there. Is that just an example of what an up could do or are you suggesting that it should have new up, createdb vs migrate commands too?

• migrate down (or rollback) to rollback the latest migration, with an optional flag to specify the number of migrations to rollback from the last, and maybe an optional flag to specify a particular migration to rollback.

Yeah, I think we should think of various options here. Getting this right is the crucial bit of this change.

@divarvel

That would require storing full migrations in the table instead of just hashes though.

I think I see why you are saying that, but I don't think we would need to store the full SQL. The SHA ensures that the files have not been changed. So if you are running a down using the file with the same SHA as the up was run with, then its not much different than having stored the SQL. I think sticking with the file based approach would be simpler and a smaller change

---

I like the idea of this but would like to thing of & discuss a few options before anyone starts on anything. I should have some time in the next few weeks to implement some of this, so the timing is good.

[tchoutri]

I'm not 100% sure about the first point there. Is that just an example of what an up could do or are you suggesting that it should have new up, createdb vs migrate commands too?

It's an example of an up command. By createdb I was referring to the CLI tool provided by PostgreSQL.

For reference, dbmate provides the following commands:

dbmate new       # generate a new migration file
dbmate up        # create the database (if it does not already exist) and run any pending migrations
dbmate create    # create the database
dbmate drop      # drop the database
dbmate migrate   # run any pending migrations
dbmate rollback  # roll back the most recent migration
dbmate down      # alias for rollback
dbmate status    # show the status of all migrations (supports --exit-code and --quiet)
dbmate dump      # write the database schema.sql file

[andrevdm]

Got it, thanks. That then is entirely in line with what I was thinking.

Its probably worth asking, for you what is the value in using postgresql-migration over dbmate?
(Obviously there is generating SQL from Haskell, but I think its worth asking anyway)

[tchoutri]

The database tooling that I am using (pg-transact and hspec-pg-transact) uses tmp-postgres. Which means that I need to have an in-Haskell solution for migration, since the temporary postgresql' Connection is generated on the Haskell side.

I tried to accommodate my setup to dbmate but then I am deprived of hspec-pg-transact, and the monad transformer juggling between hspec & pg-transact is quite painful for me. >_>

[andrevdm]

Thanks. For some of my migrations having the migration tool embedded is much simpler. Nice to hear of another use case.

[djfm]

So it seems it's been one year since the proposal and as far as I'm aware it's not been implemented has it?

For lack of time, or was it decided it was not in line with the philosophy of the project?

I'd quite like to use such a feature myself :)

And I could probably collaborate to implementing part of it if some more hands are needed.

[andrevdm]

Hi, yeah it has been a bit.

Its mainly time related. Its not a very hard feature but it changes args and will need a good bit of time and focus. I'm just not able to do that at the moment :(.

Also as it happens I'm mostly using other external migrations tools that work across multiple languages as that has been more important in my recent projects.

So for now, I have no actual plan to implement this. Sorry :(