Migration Format Examples

This page can serve as examples of generating migrations as well as document format

##Column type alias list these column types will be replaced when generating sql.

money     ~   decimal(16,2)  
date      ~   datetime  
long      ~   bigint  
string    ~   nvarchar(255)  
boolean   ~   bit  
text      ~   nvarchar(MAX)  
image     ~   varbinary(MAX)  
guid      ~   uniqueidentifier  
reference ~   foreign key

##Create Table > hump -g User first_name:string last_name:string dob:datetime company:string

{
    "up": {
        "create_table": {
            "name": "User",
            "timestamps": true,
            "columns": [
                { "name": "first_name", "type": "string" },
                { "name": "last_name", "type": "string" },
                { "name": "dob", "type": "datetime" },
                { "name": "company", "type": "string" }
            ]
        }
    },
    "down": {
        "drop_table": "User"
    }
}

##Another Create Table (with reference) > hump -g Post title:string body:text User:reference

using the reference to create a foreign key

{
    "up": {
        "create_table": {
            "name": "Post",
            "timestamps": true,
            "columns": [
                { "name": "title","type": "string" },
                { "name": "body","type": "text" },
                { "name": "User","type": "reference" }
            ]
        }
    },
    "down": {
        "drop_table": "Post"
    }
}

##Add Column to an existing table > hump -g AddFavoriteNumberToUser favorite_number:int

{
    "up": {
        "add_column": {
            "table": "User",
            "columns": [{
                "name": "favorite_number",
                "type": "int"
            }]
        }
    },
    "down": {
        "remove_column": {
            "table": "User",
            "column": "favorite_number"
        }
    }
}

##Change the datatype of a column > hump -g ChangeUser favorite_number:long

{
    "up": {
        "change_column": {
            "name": "ChangeUser",
            "columns": [{
                "name": "favorite_number",
                "type": "long"
            }]
        }
    },
    "down": {
        "change_column": {
            "name": "ChangeUser",
            "columns": [{
                "name": "favorite_number",
                "type": "int" // had to manually change this, it doesn't check the db
            }]
        }
    }
}

##Simple Add Index > hump -g AddIndexToUser company ###Notice no down is generated, this will be done automatically for you when sql is generated.

{
    "up": {
        "add_index": {
            "table_name": "User",
            "columns": ["company"]
        }
    }
}

##Multi Column Add Index > hump -g AddIndexToUser last_name first_name

{
    "up": {
        "add_index": {
            "table_name": "User",
            "columns": ["last_name", "first_name"]
        }
    }
}

##Removing Indexes Rolling Up > hump -g RemoveIndexFromUser last_name first_name

you must specify the column names so the same index name can be generated to remove the index

{
    "up": {
        "add_index": {
            "table_name": "User",
            "columns": ["last_name", "first_name"]
        }
    }
}

##Perfectly crafted sql statement > hump -g sql "CREATE VIEW MyView AS SELECT * FROM User WHERE favorite_number = 7"

{
    "up": "CREATE VIEW MyView AS SELECT * FROM User WHERE favorite_number = 7",
    "down": ""
}

##SQL Files > hump -g file MyBigStoredProcedure ###The sql file is also generated for you to add your custom SQL.

{
    "up":{
        "file": "..\\sql\\MyBigStoredProcedure.sql"
    }
}