handling multiple engines within the same migration file

[toudi]

Hello.

I'm rather new to golang, I was previously (well, still am as a matter of fact ;)) using django and it's south migration framework. It is database agnostic, but I have no problem with writing SQL migrations. Obviously, the problem is with SQLite which doesn't support full ALTER TABLE range. south adresses this by first creating a new table (with the new structure) then copying values from old table to new one, removing old table and lastly renaming the new table. So my concern is, if I have to maintain migrations for both of the engines, I will end up with (possibly) 2 directories, i.e.:

migrations/sqlite migrations/postgreSQL

the problem is - probably 90% of the migrations would actually be a verbatim copy - since the ALTER TABLE is causing the problems.

Is there a way of introducing some sections / comments into the migration files themselves? this way I could have:

--- PostgreSQL
ALTER TABLE foo DROP COLUMN bar;
--- SQLite3
CREATE TABLE foo_new ( .... (without colum bar) );
INSERT INTO foo_new SELECT .... from foo;
DROP TABLE foo;
ALTER TABLE foo_new RENAME TO foo;
--- Firebird
ALTER TABLE foo DROP bar; 
// quite similar to PostgreSQL and yes, I am aware of the fact that in PostgreSQL
// the COLUMN keyword is optional, which would make the queries exactly alike ;)

if no comments [ sections ] would be in the file, then this would be backward-compatible

[mattes]

I don't think introducing custom markup is a good idea. But I'm always happy to discuss.

https://github.com/mattes/migrate/blob/master/FAQ.md#why-two-separate-files-up-and-down-for-a-migration

[toudi]

sure, I only meant this as an example. other example could theoretically be:

1.up.sql (generic version) 1-sqlite.up.sql (sqlite-3 specific version)

as I said, I am only beginning. I wanted to start some project which would use migrations and what I would usually do would be SQLite for development + PostgreSQL setup, however I would somehow like to avoid copying a lot of files between directories only because couple of the migrations would be different

if I understand correctly, then go code would know which db driver is in use, and I was wondering whether one could make advantage of that.

[mattes]

if I understand correctly, then go code would know which db driver is in use,

migrate assumes the migrations in a directory are homogeneous. meaning you will need one directory per migration type.

https://github.com/mattes/migrate/blob/master/source/parse.go#L21

[toudi]

right, so I suppose my best bet would be to have two directories and symlinks if the migration happens to be sharing syntax between the engines?

[mattes]

yeah, symlinks should work.