migraine: A CLI for managing migrations in backend projects with PostgreSQL

migraine is a command-line interface (CLI) tool designed to help you manage migrations in your backend project using a PostgreSQL database.

Table of Contents

• Installation
  • Using Homebrew
• Prerequisites
• Commands
  • Initialize migraine
  • Create a new migration
  • Run migrations
  • Rollback
  • Help and Version
• Migrations
  • Writing migrations

Installation

System Requirements:

• macOS(Intel/Apple Silicon) and Linux supported

Homebrew

We recommend using Homebrew on macOS/linux{distro that supports it} If you don't have Homebrew installed on your macOS/linux use the command below to install it

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

1. Clone the Homebrew tap

brew tap tesh254/migraine https://github.com/tesh254/homebrew-migraine

2. Install migraine

brew install migraine

To update the package to a new version use

brew update

brew upgrade migraine

Prerequisites

• migraine currently supports only PostgreSQL databases.
• While Migraine doesn't generate SQL queries for you, a planned feature will allow AI-generated SQL queries based on a prompt flag.
• migraine currently works with PostgreSQL connection strings in your .env file, but a feature is in development to use more credential-specific variables as flags or fetch them from a vault.

Commands

Initialize Migraine

Initialize Migraine, creating a migrations folder and a migrations table in your database. By default, it uses .env as your environment file and the database environment variable as DATABASE_URL.

migraine --init

Example with a custom environment file name:

migraine --init --env ".env.local"

Example with a custom database URL environment variable:

migraine --init --dbVar "DATABASE_URL"

Example with both custom environment file and database URL environment variable:

migraine --init --env ".env.local" --dbVar "DATABASE_URL"

Create a New Migration

Create a new migration file to house your SQL code for execution in the database.

migraine --migration --new "<migration_name>"

Example:

migraine --migration --new "create_user_table"

Run Migrations

Run all your migrations, skipping those that have already been executed.

migraine --migration --run

Rollback

Rollback the most recent migration. Use this option cautiously, especially if there are foreign key constraints, as it may fail.

migraine --rollback

Help and Version

Display Migraine's usage and current version.

migraine --help

migraine --version

Migrations

When you run migraine --init, a ./migrations folder is created, along with the _migraine_migrations table to store all your migrations in one place for tracking.

Note: After creating and running migrations with migraine --migration --run, it is recommended not to delete or modify any .sql file within the ./migrations folder. Migraine relies on the chronology of each file to determine the execution order. Similarly, do not modify the migrations table created in your database.

Writing Migrations

After running migraine --init successfully, you can create a new migration file by running:

migraine --migration --new "<migration_name>"

You can name your migration in formats like "create user table" or "create_user_table" (don't forget to use quotes for migraine to recognize it as a migration name).

A new file with the migration name will be created in the migrations folder in this format: <unix_time>_<migration_name_formatted>.sql.

If you open the file for editing, it should have the following structure:

--migraine-up


--migraine-down

• --migraine-up: contains SQL that makes changes to the database.
• --migraine-down: contains SQL that rolls back the changes made by --migraine-up.

Place your SQL queries immediately after these comments to help migraine distinguish between making changes and rolling them back.

Example:

--migraine-up
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

--migraine-down
DROP TABLE users;

Future Major Improvements

• Adding SQL generation through AI
• Postgresql monitoring features