sql_migrations

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.

First - install Ruby environment, with rbenv or rvm.
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'
```
Run bundle install

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.

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

It's ready !

Usage

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.

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

sql-migrations will create table sqlmigrations_schema for each database specified in YAML config.

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

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

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

More tests
Generator for databases.yml
Generator for migrations

License

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