How to migrate Postgres databases

I’ve been working with SQL Server for years before moving to MongoDB. I loved Mongo for its simplicity but still wanted SQL so badly. People like SQL so much and even uses them in cloud services such as AWS Athena and Google Big Query. Then I decided to change to Postgres. I fell in love with it at the first sight and want to work with it in the long term.

One thing we all need when working with databases are schema management. ORM (Object-Relational Mapping) tools do it so well with up/down features. We write code in some specific languages (C#, Javascript) and those tools to execute those scripts to update database. I just want a simple tool having the similar feature but for SQL scripts so I’ve written one by myself as an npm package: @tqt/pg-migrate.

pg-migrate is a tool which manages databases’ schema & data with SQL scripts.

1. Use pg-migrate as a global command

npm install -g @tqt/pg-migrate

You need to have a migration folder structured as below. You can name it whatever you want however its 2 sub-folders up and down are required. Put your main scripts in the up folder and name them in the alphabetical order (the order you want it to run). In case you want to downgrade, you need to place their counterparts in the down folder with the same name.

migration-folder
--up
----001-add-sample-1.sql
----001-add-sample-2.sql
----001-add-sample-3.sql
--down
----001-add-sample-1.sql
----001-add-sample-2.sql
----001-add-sample-3.sql

Then run the script with the up command

pg-migrate up - migration-folder your-migration-folder - host host-name - database database-name - port port - user user-name - password password

For example

pg-migrate up - migration-folder ./db-migration - host localhost - database sample - port 5432 - user postgres - password postgres

After the command executes, a table named migration is created in your current database with all executed scripts.

Migration table

In case you want to migrate to a specific version but not the latest one, run

pg-migrate up 002-add-sample-2 - migration-folder ./db-migration - host localhost - database sample - port 5432 - user postgres - password postgres

To downgrade to a specific version, run the script with the down command

pg-migrate down 002-add-sample-2 - migration-folder ./db-migration - host localhost - database sample - port 5432 - user postgres - password postgres

Instead of using parameters, you can use environment variables. You also may use a connection string. Here is the list of all parameters:

Parameters

2. Use pg-migrate as a local command

Install the package as a dep dependency in your project

npm install - save-dev @tqt/pg-migrate

or using yarn

yarn add -D @tqt/pg-migrate

Then run

npx pg-migrate up - migration-folder your-migration-folder - host host-name - database database-name - port port - user user-name - password password

3: Run it in your code

Install the package as a dep dependency in your project

npm install - save-dev @tqt/pg-migrate

or using yarn

yarn add -D @tqt/pg-migrate

Then import and run it in your code

nodejs script

Originally published at https://thinhtran.pro on May 29, 2021.