Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Let's say you're starting a new Yii project and you've already created the database schema for it using something like phpMyAdmin or MySQL Workbench. Now you want to create an initial database migration so you can put the schema under version control, but you don't want to manually write the Yii code to create the tables, indexes, and foreign ke…
class InitialDbMigrationCommand extends CConsoleCommand
public function run($args) {
$schema = $args[0];
$tables = Yii::app()->db->schema->getTables($schema);
$addForeignKeys = '';
$dropForeignKeys = '';
$result = "public function up()\n{\n";
foreach ($tables as $table) {
$compositePrimaryKeyCols = array();
// Create table
$result .= ' $this->createTable(\'' . $table->name . '\', array(' . "\n";
foreach ($table->columns as $col) {
$result .= ' \'' . $col->name . '\'=>\'' . $this->getColType($col) . '\',' . "\n";
if ($col->isPrimaryKey && !$col->autoIncrement) {
// Add column to composite primary key array
$compositePrimaryKeyCols[] = $col->name;
$result .= ' ), \'\');' . "\n\n";
// Add foreign key(s) and create indexes
foreach ($table->foreignKeys as $col => $fk) {
// Foreign key naming convention: fk_table_foreignTable_col (max 64 characters)
$fkName = substr('fk_' . $table->name . '_' . $fk[0] . '_' . $col, 0 , 64);
$addForeignKeys .= ' $this->addForeignKey(' . "'$fkName', '$table->name', '$col', '$fk[0]', '$fk[1]', 'NO ACTION', 'NO ACTION');\n\n";
$dropForeignKeys .= ' $this->dropForeignKey(' . "'$fkName', '$table->name');\n\n";
// Index naming convention: idx_col
$result .= ' $this->createIndex(\'idx_' . $col . "', '$table->name', '$col', FALSE);\n\n";
// Add composite primary key for join tables
if ($compositePrimaryKeyCols) {
$result .= ' $this->addPrimaryKey(\'pk_' . $table->name . "', '$table->name', '" . implode(',', $compositePrimaryKeyCols) . "');\n\n";
$result .= $addForeignKeys; // This needs to come after all of the tables have been created.
$result .= "}\n\n\n";
$result .= "public function down()\n{\n";
$result .= $dropForeignKeys; // This needs to come before the tables are dropped.
foreach ($tables as $table) {
$result .= ' $this->dropTable(\'' . $table->name . '\');' . "\n";
$result .= "}\n";
echo $result;
public function getColType($col) {
if ($col->isPrimaryKey && $col->autoIncrement) {
return "pk";
$result = $col->dbType;
if (!$col->allowNull) {
$result .= ' NOT NULL';
if ($col->defaultValue != null) {
$result .= " DEFAULT '{$col->defaultValue}'";
} elseif ($col->allowNull) {
$result .= ' DEFAULT NULL';
return $result;

This comment has been minimized.

Copy link

omartech commented Sep 10, 2013

I noticed a small flaw.. if there are default values, it doesn't escape the quotes for them

so you end up with something like '...... default 'defaultval''


This comment has been minimized.

Copy link

selorm commented Oct 4, 2013

I also realised the same problem with respect to DEFAULT values...i think it has to do with line 69


This comment has been minimized.

Copy link

jzfgo commented Oct 21, 2013

@ line 73 replace:

return $result;


return addslashes($result);


@bmarston Thank you so much for this. Huge time saver.


This comment has been minimized.

Copy link

firecentaur commented May 9, 2014

I seam to be getting an error: .. and its behaviors do not have a method or closure named "getColType".


This comment has been minimized.

Copy link

pedroponte commented Sep 2, 2014

many thanks for this,

I've created this project for Yii automated generation of migration files, inspired by your code:

This allows a distributed team to easily update the db locally and then distribute it's updates with thee other developers automatically with the rest of the code via a versioning control system (I used git).

Further reading
This tool exports and generates the full db in xml format and migration code (safeUp/safeDown) for:

  • initial full db;
  • added/dropped tables, columns, and foreign keys (and fks related indexes);
  • updated column attributes
    the following modified column attributes are detected and exported:
  • type, length, zerofill, allow null, default value
    Please note:
  • indexes are not automatically exported;
  • new/dropped foreign keys generate/remove linked indexes automatically in the migration file
  • columns renamed are considered drop columns and add addd new columns;
  • column unsigned and comments are not automatically exported;
  • the foreign key name exported is not a match with the one from the db, but based on the namming convention
    Final notes:
  • see the Wiki for run instructions
  • to automate the migration after a git pull, use this code in your .git/hooks folder. Don't forget to read the README file

This comment has been minimized.

Copy link

linkdesu commented Jan 20, 2015

@nkm @bmarston Thanks a lot !


This comment has been minimized.

Copy link

asimzeeshan commented Oct 28, 2015

@pedroponte any chances you can migrate it to GITHUB?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.