Question about Production Migrations

[MarkLee425]

Hi all. I have been using this library for over a year and since I am using Docker Compose for Postgres, I have been making two separated migrations folders for development and production use: /migrations/dev, /migrations/prod.
For /migrations/dev, it is using this library for convenience.
For /migrations/prod, it is using raw sql so that I can directly exec it with pg_dump inside the docker container in GitHub Actions or other CICD scripts.

So I am wondering if there are any better solutions to do that in production mode? Maybe programmatic API? Please share me some insights on that. Thanks.

TLDR
Reasons of using raw sql for production:

• Dont want to install packages inside vps directly to reduce server disk usage.
• It can be done in a separated docker container for migrations, but the cons is larger server disk usage again and increase complexity.

If you are using direct npm install, build and start in vps without concern about infra cost, feel free to skip this discussion :)

[MarkLee425]

If there are no better solutions, I am thinking if we can do for converting /migrations/*.ts into /migrations/prod/*.sql so that it can be more developer friendly.

[Shinigami92]

Yeah, sounds interesting.
I think it is possible already right now to log a /migrations/*.ts generated sql statements as this is what the integration tests are checking.
So now we would just need to write that output into .sql files.

Are you interested in exploring and creating a PR? @brenoepics could possibly help a bit, as he is very active right now in the project.

[brenoepics]

That might work, but I think we should make it distinct from our existing SQL migrations.

Here’s an example of a SQL migration using node-pg-migrate:

-- Up migration
CREATE TABLE testz (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX test_name_idx ON testz(name);

-- Down migration
DROP INDEX IF EXISTS test_name_idx;
DROP TABLE IF EXISTS testz;

The up and down steps are separated by a comment, so for this new export/dump/transpile command, we’ll probably need either:

• an up/down parameter, or
• generate both directions, but into separate folders like:

/migrations/*.ts → /migrations/prod/up/*.sql  
                 → /migrations/prod/down/*.sql