Run migrations in schemas in PostgreSQL

Hy everyone,

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.