This post is a continuation from that link.
Now we have schemas, but how can we create a new table in the schemas?
I've been using a function to handle migrations in the schemas, it can be used for anything. create table,changes in the table, triggers,functions,index,etc...
Ok, that is the function you need to run in your database.
After that, you only need to run
select migrate_schema('your code')
In my case,
select migrate_schema('CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );');
A tip, you can ignore schemas, take a look in the function. In line 13 I ignore the public schema and in line 11 I ignore schemas that begin with pg_
Another thing, I've been using the goose to handle my migrations. I planned to write about that, but fortunately, the Sho Minagawa wrote a great post.
We need to run more complex statements (PL/pgSQL). So, If you decide to use the tool, remember to add these lines in the migration code:
-- +goose StatementBegin
/// your statement here
-- +goose StatementEnd
When we use schemas, the goose looks for its migrations table within the schema. In my case, I want to keep my migrations table in the public schema, so I forked the project and change the instruction to check the table only in the public schema. Right now I have no plan to send a merge request, to the project, maybe it can be useful only to me. In this case, use my fork.
that's all folks.