sqlmigrate #

A migration tool for Dart/Flutter projects that adds support for managing database migrations in a simple and practical way. sqlmigrate can be used both via the command line and as a development dependency in your Dart/Flutter application.

Features #

• Simple CLI: Manage migrations using straightforward commands.
• Dart/Flutter Integration: Use it as a development dependency in your project.
• Flexible Configuration: Configure via pubspec.yaml or an external YAML file.
• Initial Focus on Supabase: Currently supports PostgreSQL (with plans to add other databases in the future).
• Step-by-Step Execution: The up command applies one pending migration at a time, rather than all at once.

Installation #

Add sqlmigrate as a development dependency in your Dart/Flutter project:

dev_dependencies:
  sqlmigrate: ^<version>

Configuration #

Using pubspec.yaml #

Include the migration configurations in your pubspec.yaml file:

db_config:
  production:
    dialect: postgres
    datasource: dbname=${DB_NAME} user=${DB_USER} password=${DB_PASSWORD} sslmode=disable
    dir: migrations/postgres
    table: migrations

Notes:

• db_config: Mandatory configuration block.
• production: Environment name (can be any name, such as development, test, etc.).
• dialect: Database to be used for the environment.
• datasource: Database connection specifications. For PostgreSQL, include parameters like host=, dbname=, port=, user=, password=, and sslmode (options: disable, require, or verifyFull).
• dir: Directory where the SQL migration files are located.
• table (optional): Name of the table that will track the migrations in the database.

Using an External Configuration File #

Alternatively, you can use another YAML file for configuration (you don't need to define db_config). For example:

development:
  dialect: sqlite3
  datasource: test.db
  dir: migrations/sqlite3

production:
  dialect: postgres
  datasource: dbname=myapp sslmode=disable
  dir: migrations/postgres
  table: migrations

Use the --config flag in the CLI to specify the path to this file.

CLI Usage #

The sqlmigrate command-line interface offers the following commands:

Usage: sql-migrate <command> [arguments]

Global options:
  -h, --help       Prints this usage information.
  -v, --version    Prints the version.

Available commands:
  down   Undo a database migration.
  new    Create a new migration.
  up     Migrate the database to the most recent version available.

Important Flags #

• --env: Specifies the environment to use. If not provided, the CLI will use the first environment defined in your configuration.
• --config: Specifies the path to an alternative configuration file (if you don't want to use pubspec.yaml).

Migration Files Structure #

Migration SQL files must follow this format:

-- +migrate Up
create table users(id serial primary key, name varchar(255));

-- +migrate Down
drop table users;

Details:

• Up: Defines the changes to be applied to the database.
• Down: Defines how to revert the changes made by the migration.

Each file should be named following this format:
YYYYMMDDHHMMSS-migration-name.sql
For example: 20250213173434-create-schema.sql, where create-schema is the user-defined migration name.

Final Considerations #

• Current Support: The CLI currently supports only PostgreSQL. Support for other databases will be added in the future.
• Migration Execution: When executing the up command, the CLI applies only one pending migration at a time, rather than applying all pending migrations at once.

Contributions, feedback, and suggestions are welcome!

License #

This project is licensed under the MIT License.

Happy migrating!