DEV Community

Renato Suero
Renato Suero

Posted on

3

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.

CREATE OR REPLACE FUNCTION public.migrate_schema(change text)
RETURNS integer AS
$BODY$
DECLARE
v_schema text;
BEGIN
FOR v_schema IN
SELECT quote_ident(nspname)
FROM pg_namespace n
WHERE nspname !~~ 'pg_%'
AND nspname <> 'information_schema'
AND nspname <> 'public'
LOOP
EXECUTE 'SET LOCAL search_path = ' || v_schema;
EXECUTE change;
END LOOP;
return 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

After that, you only need to run

select migrate_schema('your code')
Enter fullscreen mode Exit fullscreen mode

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
);');
Enter fullscreen mode Exit fullscreen mode

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.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay