sequential numbering with postgres or redshift schemas

[rkulla]

I'm just started using this fork because it has support for Redshift. However, I use schemas to organize tables with databases like redshift and postgres but the new sequential numbering of files, instead of timestamps, means that I have to put all my migration files in the same directory, otherwise the counter will start again at 00001 for each directory.

Say I have a single database in redshift or postgres, and it has multiple schemas CREATE SCHEMA foo; and CREATE SCHEMA bar;. If I wanted to organize my goose migrations by folders, I might have:

foo/
bar/

If i then ran cd foo && goose create add_cars sql then cd ../bar && goose_create add_trains sql, goose will create foo/00001_add_cars.sql and bar/00001_add_trains.sql. So running:

$ cd foo
$ goose postgres "user=rkulla dbname=postgres sslmode=disable" up
goose: no migrations to run. current version: 1
$ cd ../bar
$ goose postgres "user=rkulla dbname=postgres sslmode=disable" up
goose: no migrations to run. current version: 1

would only apply the migration under foo/ but not the one under bar/, because both migrations start with 00001_ and postgres or redshift only get ONE goose_db_version table to share amongst the different schema names. This makes them harder to organize unless I do goose create add_cars_foo sql' and goose create add_trains_bar sql, then run commands like ls *_foo.sqlandls *_bar.sql`.

It's not that big of a deal right now I guess, but I'm wondering if there's a better way and if it's really worth not using timestamps. IIRC, Ruby on Rails's migration feature used to use sequential numbers but developers complained because it caused a lot of conflicts when 2 different developers working on the same project made separate changes but both generated a migration with the same number. So Rails switched to UTC timestamps.

[duffn]

I also prefer the timestamp naming convention. I'd suggest that both are offered in goose. If you prefer sequential, then default to that, but offer UTC timestamps with create NAME sql ts perhaps.

[VojtechVitek]

@rkulla sounds like a bug. Goose binary should see the subdirectories and give you latest_version_number++ properly. Anyone, who's using sub-dirs too and wants to solve this?

---

I'm strongly against Timestamp-based versioning, since it causes much worse unpredictable schema conflicts than sequential versioning. See discussion in #27.

In short:

Given two developers work on different git branches and they both create migrations that change the schema in "account" table. Let's say they both rename a column, change a default value and change an index constraint, for example.

Sequential versions

1. First dev merges his PR to master. Travis passes.
2. Second dev's Pull Request's Travis job will start blowing up on a goose migration name conflict (given the Travis builds off the PR branch merged into master using the "Build branch updates" Travis feature). The job will fail on conflicting version 0005:

0005_first_developer_account_changes.sql
0006_first_developer_some_more_changes.sql

0005_second_developer_different_account_changes.sql

Thus, the second dev will need to resolve the conflict (rename the migration) and will probably rebase against master to update his PR.

Timestamp-based versions

2017...08_first_developer_account_changes.sql
2017...15_first_developer_some_more_changes.sql
^ gets merged and deployed first

2017...03_second_developer_different_account_changes.sql
^ gets merged second ............. and will be ignored (because its version name is not higher than the latest applied version)

** Note that this is how goose work by design from its very beginnings in https://bitbucket.org/liamstask/goose/ repo.

---

As you can see, the sequential versions provide more safety during the development cycle. (Even though they might seem annoying and unnecessary from the beginning.)

But more importantly, they provide predictable order of schema migrations.

The predictable order has more benefits, though:

1. You can start storing the initial (pre-migration) and final (after-migration) schemas as golden files in your git repository. Very handy for PR reviews! With timestamp-based versions, you'd have to resolve git conflicts in these files anyway.
2. Also, you can configure your CI (Travis) to test goose up && goose reset && goose up as part of your test suite. From that point, you'll always make sure that your database schema is in a good shape, even when you need to roll-back for some reason. With timestamp-based versions, some migrations could be silently ignored .. and this test would not blow up.

---

Hope this makes sense.

I'm not saying sequential versioning is perfect - but it has some real benefits over timestamp-based versions.

I'd prefer if we thought about some tooling around conflicts (ie. bump version of all files that were not applied yet to resolve conflicts or something), rather than falling back to timestamps.

[rkulla]

@VojtechVitek I see. Thanks for the quick response.

I'm not particularly adamant about timestamps if the sequential versioning would work with sub-dirs. I do have some older timestamp based migrations that were made with liamstask/goose that would now be a mix of timestamps and sequential numbers. Do you think that would pose a problem if i don't rework them as sequential numbers, for people "migrating" from the old goose to this fork?

[VojtechVitek]

@rkulla it will still work. But goose create from this repo won't give you timestamp by default, though.

If you want to switch to sequential versions, you can always bump the latest migration to something like 10000000001 (something higher than latest timestamp) and go from there.

[pawelma]

@VojtechVitek IMO timestamp based migrations are a lot better than sequential ones. Major issue with sequential ones are collisions after merge. Why can't you check all timestamp versions against goose_db_version? In #27 someone provided argument with initial migration as example but in real world this case is minor and conflicts after merge are real problem. Logic and storage schemat should be discussed between developers, not migration numbers.

[VojtechVitek]

@pawelma please read my comment at #63 (comment).

Timestamp based approach doesn't guarantee ordering, in which the migrations get run. I think of DB schema migrations as of git tree with well defined history. Git history is based on commit hashes rather than on timestamps for the very same reason.

Problem is not when you merge two branches together, but in which order you run the DB migrations from these branches. Version-based naming prevents any conflicts, as it errors out on version collisions. I totally understand how annoying is to rename files manually. I don't like it either. But we don't have anything better right now. I wish we could just pick up the order from git history one day. Or automate the renaming somehow.

I'll give you one more example with timestamp-based approach:
Imagine two developers working on their branches with 5 migrations each and merging their work to master branch at the same time. After the merge, the master branch will have 10 new timestamp-based migrations that don't have order defined. If these developers changed schema of some related DB tables, you'll get schema errors (ie. first developer renamed column or added new index but the other one just wanted to enforce NOT NULL or remove a foreign key). This is something that version-based approach avoids.

[pawelma]

@VojtechVitek I read it earlier and it didn't convince me :)

We can use any prefix which provides uniqueness and some sort of order. The order should matter but should not take over implementation. The order should matter in a longer period but in short time it shouldn't be forced. As you mentioned git model is a tree, history there can be linear but by default, it isn't and developers take care to resolve conflicts or make history linear.

I agree with the problem described in your example but sequential versioning doesn't solve it. Imagin same issue with sequential versioning. Developer tries to merge his changes into master, CI tests are failing because there is another migration with the same number - so developer fixes the number and I can bet that in 90% (or even more) cases he doesn't check the code. If there is no restriction for 'merge only when tests pass' he may merge broken code. Let's assume that there is such restriction or he waits for another test execution - the result is red again because the original error was hidden by migration framework (instead of 1 debugging step he had to go through 2). What I want to say: You are not able to protect developers from mistakes but you can make their lives easier. Good practices like well-written tests, working CI, etc... should solve such problems.

[VojtechVitek]

but by default, it isn't and developers take care to resolve conflicts or make history linear.

Yea, it would be nice if we could get the version number automatically somehow, like in git. Technically, goose binary could look into git repository and recognize number of commits since the initial commit for each migration merged into master. git rev-list --count $(git rev-list --max-parents=0 HEAD | tail -n 1).. or something like that. But I don't really have time to explore this further at this moment, it's not that easy.

Sequential versioning enforces consistency of the DB schema on each environment. It's a stability decision. You probably want to have the same DB state on your localhost, in your CI tests, and then on staging/production.

You are not able to protect developers from mistakes but you can make their lives easier.

I can rely on CI tests that enforce consistent and predictable DB schema, thus protecting developers and production devops from issues on production. That's imho more important than forcing them to rename bunch of files.

Yes, it is annoying. But how can we do it better?

Unfortunately, timestamps are not the answer.

---

However, if you still prefer timestamp-based approach, you can simply edit this line: https://github.com/pressly/goose/blob/master/create.go#L22 and build your custom goose binary.
Custom binaries are easy to set up and allow you to write Go migrations: See https://github.com/pressly/goose/tree/master/examples/go-migrations

[toudi]

could a hinting solution be concidered? Django uses sequential number migrations, however it also allows one to specify dependencies. Therefore by adding one line:

-- +goose depends-on 0004_foo

-- +goose Up
....
-- +goose Down
....

goose could know how to arange the history.

[croaker8]

I am running into serious problems when we have development going on on multiple branches. Scripts get created with the same sequence number on multiple branches. Then there is a conflict after a merge and deployment fails. And even worse if a different branch gets deployed with different numbering the state is hard to decipher. Not sure we will continue using goose.

[mvrhov]

@croaker8 : May I suggest that you add a timestamp into the sql/go file automatically so you can resolve the sequence problem. Even though you would have the time based sequences there is no guarantee that the migrations would be in order. And you would still run into the same problems e.g manually solving the order of them except there would be no merge conflicts.

Now on the other hand what @toudi suggested would solve this problem partially as the migrations would still be numbered out of the order and there would still be conflicts with merge however they might not be run out of order.

[eaglemoor]

We can use full name of file as version. It's give more information witch revision you apply. And the risk to create the same filename very low. And we don't need use timestamp in filename.

About prefix for sort. It's not a problem, we will have key '0001_create_user', '0001_create_fiends', some people can't create depending migration without parent migration.
If we have filename /migration/mike/0001_create_user and /migration/alex/0001_create_news all migrations will be apply, because they have different key