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.

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.

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 !

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
   

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

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