Flyway database migration to multiple schemas

Question

I've set up a migration script for my multi-tenant application. the problem was. it's only generating the scripts for the first tenant/schema I've specified on the parameter. Here's my sql script

drop table if exists ADMIN_ACCOUNT cascade;

drop table if exists PERSON_NAME cascade;

drop table if exists USER_ACCOUNT cascade;

create table ADMIN_ACCOUNT (
    id int8 not null,
    created_date timestamp,
    PASSWORD varchar(255),
    USERNAME varchar(255),
    membershipType varchar(255),
    NAME_ID int8,
    primary key (id)
);

create table PERSON_NAME (
    id int8 not null,
    created_date timestamp,
    FIRST_NAME varchar(255),
    LAST_NAME varchar(255),
    MIDDLE_NAME varchar(255),
    account_id int8,
    primary key (id)
);

create table USER_ACCOUNT (
    id int8 not null,
    created_date timestamp,
    PASSWORD varchar(255),
    USERNAME varchar(255),
    ROLE varchar(255),
    TENANT_CODE varchar(255),
    NAME_ID int8,
    primary key (id)
);

alter table ADMIN_ACCOUNT
add constraint FK_fil1krx8k0osj713tg44ia0vu
foreign key (NAME_ID)
references PERSON_NAME;

alter table PERSON_NAME
add constraint FK_hc1g7pa0rseytw9o1pcuo0mpw
foreign key (account_id)
references USER_ACCOUNT;

alter table USER_ACCOUNT
add constraint FK_ib2pk4at20vxm3onaoro6ry2r
foreign key (NAME_ID)
references PERSON_NAME;

Here's the command I have entered to flyway mvn compile flyway:migrate -Dflyway.schemas=tenant3,tenant4,tenant5 -Dflyway.baselineOnMigrate=true

It's only generating the tables for the first tenant which is tenant3.

Is this a bug? or am Missing something?

Answer 1

To create multiple identical schemas you have to invoke Flyway once for each schema, with the flyway.schemas property set the to correct value. Flyway will then set the correct schema as the default one, letting you run your migration scripts unchanged (as long as you don't prefix the object names).

Answer 2

Please find recommendations from flyway as in

http://flywaydb.org/documentation/faq.html#multiple-schemas

Hope this helps.