Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Migrations without loosing data plus converting – needed help and best practices (3 questions inside)

Hello!

I've some db version say 1.0.7.

I need total refactoring: rename/drop/add tables and columns, change columns type (say, from bigint(20) to tinyint(2) or from varchar(45) to bigint(20) unsigned). This will be version 2.0.0 of course.

But i need to use old data. In this case i have four questions:

  1. Can devtools migrations do this automatically?
  2. If yes, where i must write convertation logic? If no, where i can find full documentation of how to use phalcon DBAL to write my own cli migration?
  3. Can i do all the stuff with phalcon DBAL or i need to use raw sql queries instead?

Thanks!

btw, official documentation ( http://docs.phalconphp.com/en/latest/reference/migrations.html ) is VERY old and kind of useless.



13.2k

if you are mean to migrate database, some tool like navicat premium can be help. if you are mean to migrate code, I may say it will work fine if you did not change the columns' name.



15.4k

I mean to migrate both – database plus my phalcon-based api application.

Right now i have separate branch in app that uses new database and secondary database schema (that i've changed by my hands). It's working good on my test server.

Now i need to write script that will convert old database structure to new one (for automatically deploy to production). I see that Phalcon has migration tool for this but yet not understand how to use this ): So i'm asking help here.



45.6k
Accepted
answer

Hey

If you want migrate database , I recomend tool at http://sqitch.org/



15.4k

Thank you, this tool seems very useful! I think, this must be accepted answer for any topicstarter that greatly know raw sqls.

But.. i'm not so good with raw sqls.. ): Okay, i will learn that.

Hey

If you want migrate database , I recomend tool at http://sqitch.org/



2.8k
Accepted
answer
edited Feb '15

Since we all are using php, I can recommend phinx: http://www.phinx.org

It can be installed using composer (composer require robmorgan/phinx) and you can write the migrations in php:

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users->addColumn('username', 'string', array('limit' => 20))
              ->addColumn('password', 'string', array('limit' => 60))
              ->addIndex(array('username', 'email'), array('unique' => true))
              ->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        $users = $this->table('users');
        $users->removeColumn('username')
                //etc
              ->save();
    }
}


15.4k

Wow, this is great! And much more understandable synthax for me. Big thanks, Max!

Since we all are using php, I can recommend phinx: http://www.phinx.org

It can be installed using composer (composer require robmorgan/phinx) and you can write the migrations in php:

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
   /**
    * Migrate Up.
    */
   public function up()
   {
       $users = $this->table('users');
       $users->addColumn('username', 'string', array('limit' => 20))
             ->addColumn('password', 'string', array('limit' => 60))
             ->addIndex(array('username', 'email'), array('unique' => true))
             ->save();
   }

   /**
    * Migrate Down.
    */
   public function down()
   {
      $users = $this->table('users');
       $users->removeColumn('username')
              //etc
             ->save();
   }
}


1.7k

hmm amazing, actually i just copied the migration's files from dev tools and used it as a library in my project. Actually if migration is such a big hit someone shld zep it. =d

edited Jan '18

I'm actually embarking on a comparitive analysis of Phalcon's migration tools vs FlyawayDB vs Phinx. But no one has been back to this thread to update it. The updated docs look very useful and why use a third party tool if the framework has something better built in? I'm hoping it is anyways, we shall see. Anyways, check it out...

https://docs.phalconphp.com/en/3.2/db-migrations



15.4k

Thank you for answer, it seems that really changed between last version that i've used (latest 1.x) and 3.x – huge job done there!

Right now i'm building non-highload api.. so using Doctrine with their migrations – much better and easier: change entities, run command, everything works automatically without writing code manually. But it much slower, of course, then Phalcon's.

I'm actually embarking on a comparitive analysis of Phalcon's migration tools vs FlyawayDB vs Phinx. But no one has been back to this thread to update it. The updated docs look very useful and why use a third party tool if the framework has something better built in? I'm hoping it is anyways, we shall see. Anyways, check it out...

https://docs.phalconphp.com/en/3.2/db-migrations

Oh cool! Good to know, thank you! We are pretty set on migrating to Phalcon because of their speed, security and a number of other reasons. But I will still keep this in mind. If the migration tools aren't robust enough, we're also looking at FlyawayDB and Phinx to handle migrations. Our API has very high traffic.

My main concern right now is the version number using a decimal instead of a date/time stamp or something unique (checksum, etc.).

Thank you for answer, it seems that really changed between last version that i've used (latest 1.x) and 3.x – huge job done there!

Right now i'm building non-highload api.. so using Doctrine with their migrations – much better and easier: change entities, run command, everything works automatically without writing code manually. But it much slower, of course, then Phalcon's.

I'm actually embarking on a comparitive analysis of Phalcon's migration tools vs FlyawayDB vs Phinx. But no one has been back to this thread to update it. The updated docs look very useful and why use a third party tool if the framework has something better built in? I'm hoping it is anyways, we shall see. Anyways, check it out...

https://docs.phalconphp.com/en/3.2/db-migrations

edited Jan '18

Looks like someone else has this same concern re: Timestamp based migrations!

https://forum.phalconphp.com/discussion/17373/ts-based-migration

So I will be following up on this thread or others that are related.