Generic db migration tool to generate the Upgrade sql statements or downgrade sql statements based on the given timestamp. This tool is not specific to any Database or doesn't maintain the version information.

It is inspired by https://github.com/amberframework/micrate, but this project only focused on generating single SQL file for upgrade or downgrade. This doesn't modify or execute the SQL code.

Clone this repo and run dub build. Copy the generated binary to /usr/local/bin.

git clone https://github.com/aravindavk/dbmigrations.git
cd dbmigrations
dub build --compiler=ldc2 -b release
sudo cp dbmigrations /usr/local/bin/

Create the migrations directory in your project directory. For example,

cd myproject
mkdir -p db/migrations

Now create the migration file by running the below command.

$ dbmigrations -d ./db/migrations scaffold -n users
Created ./db/migrations/20240524213223-users.sql

Open the file and add the SQL for upgrade under -- +UP and downgrade SQL under -- +DOWN section. Example,

-- +UP
-- SQL in section '+UP' is included when this migration is applied
CREATE TABLE users(
  id                         BIGSERIAL PRIMARY KEY,
  name                       VARCHAR NOT NULL,
  email                      VARCHAR NOT NULL,
  password_hash              VARCHAR NOT NULL,
  created_at                 TIMESTAMP,
  updated_at                 TIMESTAMP
);

-- +DOWN
-- SQL section '+DOWN' is included when this migration is rolled back
DROP TABLE users;

Run dbmigrations up to generate the migration file based on given timestamp.

$ dbmigrations -d ./db/migrations up
Included files:
20240524135055-users.sql

Migration file generated. Path: /tmp/dbmigration.sql
Execute the SQL file to run migration (upgrade/downgrade). For example, Postgres

  sudo -u postgres psql mydb -f /tmp/dbmigration.sql

To generate the migration file based on the given timestamp.

$ dbmigrations -d ./db/migrations up -t 20240524135847

$ dbmigrations -d ./db/migrations status
   pending  20240524135055-users.sql

$ dbmigrations -d ./db/migrations status -t 20240524135055
      done  20240524135055-users.sql

Generates the migration file based on the given timestamp. Rollback only the last migration after the given timestamp.

$ dbmigrations -d ./db/migrations down -t 20240524135055
Included files:
20240524135055-users.sql

Migration file generated. Path: /tmp/dbmigration.sql
Execute the SQL file to run migration (upgrade/downgrade). For example, Postgres

  sudo -u postgres psql mydb -f /tmp/dbmigration.sql

• Fork it (https://github.com/aravindavk/dbmigrations/fork)
• Create your feature branch (git checkout -b my-new-feature)
• Commit your changes (git commit -asm 'Add some feature')
• Push to the branch (git push origin my-new-feature)
• Create a new Pull Request

• Aravinda VK - Creator and Maintainer