simple-db-migrate is a generic database migration tool inpired on Rails migrations.
Pull request Compare This branch is 78 commits behind guilhermechapiewski:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.


simple-db-migrate “quick” documentation

Quick start

simple-db-migrate is damn simple. The best way to understand how it works is installing and using it.

You can install it by typing:

    $ sudo easy_install simple-db-migrate

There are more detailed instructions and information about installing directly from source on the project website.

After installing, for usage tips type:

    $ db-migrate --help

Understanding how it works

The first thing you’ll need is a migration file. There are some example migration files on the “example” directory. The migration files have the following format:

    SQL_UP = """
    CREATE TABLE aleatory (
      id int(11) NOT NULL auto_increment,
      name varchar(255) default NULL,
      PRIMARY KEY  (id)
    SQL_DOWN = """
    DROP TABLE aleatory;

... where SQL_UP and SQL_DOWN are two strings that contains respectively the SQL statements to upgrade and downgrade the database schema.

You can use db-migrate to create the migrations by typing:

    $ db-migrate --create create_table_users

The file names need to respect the format “YYYYMMDDHHMMSS_migration_description.migration”. simple-db-migrate uses the YYYYMMDDHHMMSS information to track the database schema version and to decide the order of execution of the scripts. simple-db-migrate will go through all .migration files in your directory and execute all of them in their creation (date) order.

Second, you have to configure access to your database so simple-db-migrate can execute DDL. Just create a file named “simple-db-migrate.conf”, with the following content (there is also an example on the “example” directory):

    DATABASE_HOST = "localhost"
    DATABASE_USER = "root"
    DATABASE_NAME = "migration_example"

*** The MIGRATIONS_DIR directive may have relative (from the location of this file) or absolute paths separated by _:_ pointing to the migrations directories.

You don’t need to create the database. simple-db-migrate will create it for you.

After this two things you are ready to go. Just navigate to the directory where you create your configuration file and type:

    $ db-migrate

If you don’t want to navigate to the directory, you can specify it path instead. In this case you will also need to specify the path to the config file. Note that this also makes it possible to use any name you like for the config file:

    $ db-migrate --config=path/to/file.conf

Migrating to a specific version

If you want you can migrate your database schema to a specific version by informing the —migration (or -m) parameter. The version id is the YYYYMMDDHHMMSS identifier used at the migration file:

    $ db-migrate --migration=20090227000129

If you don’t specify any version, simple-db-migrate will migrate the schema to the latest version available on the migrations directories.

Configuration for many environments on same file

If you want to use the same configuration file to all your environments you can prefix the names with the name of your environment and specify it when executing, like the example bellow.

    DATABASE_HOST = "localhost"                              #default database host
    STAGING_DATABASE_HOST = ""               #staging database host
    PRODUCTION_DATABASE_HOST = ""         #production database host
    DATABASE_USER = "root"
    DATABASE_NAME = "migration_example"

    $ db-migrate --config=path/to/file.conf                  #will use default configurations
    $ db-migrate --config=path/to/file.conf --env=staging    #will use staging configurations, and default to keys not prefixed
    $ db-migrate --config=path/to/file.conf --env=production #will use production configurations, and default to keys not prefixed

Available configurations

You can set default values to internals configurations on your configuration file and overwrite (some of them) using the command line parameters. Bellow is a list of all configurations you can set on your configuration file.

Configuration description default value possible values
DATABASE_HOST hostname where is the database to connect
DATABASE_USER username used to connect to database and execute the commands
DATABASE_PASSWORD password used to connect to database and execute the commands
DATABASE_NAME database name used where the commands will be executed
DATABASE_ENGINE the database type where migrations will me executed mysql oracle,mysql
DATABASE_VERSION_TABLE the table name used to save database versions db_version any name supported by the database
UTC_TIMESTAMP create migrations files using UTC time to format the name False True,False
DATABASE_MIGRATIONS_DIR directories to look for migrations files separated by :
DATABASE_ENCODING encoding used on database utf-8 any valid encoding
DATABASE_SCRIPT_ENCODING encoding used on migrations files utf-8 any valid encoding
drop_db_first if True drop the database before execute migrations False True,False
force_execute_old_migrations_versions if True and current and destiny database versions are equals, execute any old migration not executed yet False True,False
force_use_files_on_down if True use SQL_DOWN from migrations files instead of that present on version table False True,False
label_version label to be applied to all executed migration when doing a upgrade on database
log_dir directory where will be created a file with a full log for the process, with the current time as name
new_migration name for the migration to be created any alpha numeric word, without spaces
paused_mode execute the migrations pausing after finish each one of the files False True,False
schema_version the desired version to the database, will do a upgrade or a downgrade to be sure that this will be the current version of database
show_sql if True show executed sql commands False True,False
show_sql_only if True only show the sqls, but not execute them False True,False

Roadmap, bug reporting and feature requests

For detailed info about future versions, bug reporting and feature requests, go to the project’s ticket system at Lighthouse.

Other questions

Mail me at “guilherme.chapiewski at” for further questions.