0.01
No release in over 3 years
Low commit activity in last 3 years
Simple standalone migrations you can use with plain SQL
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
 Dependencies

Development

~> 0.4.3
~> 3.3
~> 0.33
~> 0.10.0
~> 1.3.10

Runtime

~> 1.7
~> 10.0
~> 4.22.0
 Project Readme

sql_migrations

Gem Version Build Status

Simple standalone migrations you can use with plain SQL.

This gives you possibility to execute migrations, seed datebase (on production and in test environment) in non-Ruby projects. sql-migrations can work with multiple different databases, and support many db adapters.

Why ?

This is particularly useful in old projects that don't have migration support, and you really want to use Continuous Delivery strategy. Without migrations you wouldn't be able to setup your test environment for automated testing (functional tests, unit tests, integration tests).

For example, if you work on old Zend 1 project, and you want to take benefit from using Continuous Deployment/Continuous Integration mechanisms - you may find this project useful.

Install

sql-migrations are created using Ruby, but you can use this software with non-Ruby projects, like PHP or Java. This is standalone mechanism.

  1. First - install Ruby environment, with rbenv or rvm.

  2. If your project is not using ruby, create your Gemfile:

    source 'https://rubygems.org'
    gem 'mysql2'
    gem 'sql_migrations'

    It is possible to use all database adapters that are supported by Sequel. Adapters supported by Sequel, by now, are:

    ADO, Amalgalite, CUBRID, DataObjects, DB2, DBI, Firebird, FoundationDB SQL Layer, IBM_DB, Informix, JDBC, MySQL, Mysql2, ODBC, OpenBase, Oracle, PostgreSQL, SQLAnywhere, SQLite3, Swift, and TinyTDS

    If you are using PostgreSQL use

    gem 'pg'
  3. Run bundle install

  4. Create database config file in db/config/databases.yml

    production:
      databases:
        default:
          adapter: mysql2
          encoding: utf8
          database: default_db_prod
          username: prod_user
          password: prod_pass
          host: 192.168.1.1
      options:
        separator: ;
    
    development:
      databases:
        default:
          adapter: mysql2
          encoding: utf8
          database: default_db_dev
          username: dev_user
          password: dev_pass
          host: 127.0.0.1
        second_db:
          adapter: mysql2
          encoding: utf8
          database: second_db_dev
          username: test_user
          password: test_pass
          host: 127.0.0.1
      options:
        separator: ;
    
    test:
      databases:
        default:
          adapter: mysql2
          encoding: utf8
          database: <%= ENV['DB_DEFAULT_NAME'] %>
          username: <%= ENV['DB_DEFAULT_USER'] %>
          password: <%= ENV['DB_DEFAULT_PASS'] %>
          host:     <%= ENV['DB_DEFAULT_HOST'] %>
        second_db:
          adapter: mysql2
          encoding: utf8
          database: <%= ENV['DB_SECOND_NAME'] %>
          username: <%= ENV['DB_SECOND_USER'] %>
          password: <%= ENV['DB_SECOND_PASS'] %>
          host:     <%= ENV['DB_SECOND_HOST'] %>
      options:
        separator: ;

    As shown above, it is possible to use ERB-like syntax to use environment variables in config.

    default database configuration is optional, nonetheless if supplied it will not be necessary to create additional subdirectory to store migrations (look below).

    If you have multi-statement migrations you should provide separator configuration variable in options block. options key is optional in YAML.

  5. Migrations/seeds can be executed using rake tasks. So you will need to create Rakefile:

    require 'bundler'
    Bundler.require
    
    SqlMigrations::Config.load!('db/config/databases.yml')
    SqlMigrations.load_tasks!

    You can also create Rake tasks by yourself:

    require 'sql_migrations'
    SqlMigrations::Config.load!('databases.yml')
    
    namespace :db do
      desc 'Migrate database'
      task :migrate do
        puts '[i] Running migrations ...'
        SqlMigrations.migrate
      end
    
      desc 'Seed database'
      task :seed do
        puts '[i] Seeding databases ...'
        SqlMigrations.seed
      end
    
      desc 'Prepare databases -- migrate and seed'
      task :prepare do
        Rake::Task['db:migrate'].invoke
        Rake::Task['db:seed'].invoke
      end
    end
  6. It's ready !

Usage

  1. Valid migration/seeds file names match against regexp /(\d{8})_(\d{6})_(.*)?\.sql/. So valid filenames would be:

    20150303_180100_test_migration.sql
    20150303_180100_whatever_description_of_seed.sql
    

    You can put plain SQL into that files.

  2. It is possible to create migration files and seed files inside following directory structure:

    db/
      migrations/
        20150303_180100_test_migration.sql
      seeds/
        20150303_180100_whatever_description_of_seed.sql
    

    If you want to use multiple databases, create also database directories:

    db/
      migrations/
        default/
        second_db/
      seeds/
        default/
        second_db/
    

    default/ directory is optional, you can put migrations/seed data for default database in base directories:

    db/
      migrations/
        20150303_180100_test_migration_for_default_database.sql
        second_db/
          20150303_180101_test_migration_for_second_db.sql
    
  3. sql-migrations will create table sqlmigrations_schema for each database specified in YAML config.

  4. If everything is set up properly, you should see new rake tasks:

    rake -T
    

    This should give output

    rake sqlmigrations:db:migrate    # Run migrations
    rake sqlmigrations:db:seed       # Seed database
    rake sqlmigrations:db:scripts    # List all scripts found
    
  5. Then, run tasks:

    # this will execute migrations
    rake sqlmigrations:db:migrate   
    
    # this will seed database with initial data
    rake sqlmigrations:db:seed 
    
    # this will list all migrations/seed files that where found
    rake sqlmigration:db:scripts
    
  6. Environment variables

    If you want to run migration on different database (for example test) specify ENV:

    ENV=test rake sqlmigrations:db:migrate
    ENV=test rake sqlmigrations:db:seed
    

    or in production:

    ENV=production rake sqlmigrations:db:migrate
    ENV=production rake sqlmigrations:db:seed
    

TODO

  1. More tests
  2. Generator for databases.yml
  3. Generator for migrations

License

This is free software licensed under MIT license, see LICENSE file