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

Question

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

Roman Feb '15

Created Feb '15	Last Reply Jul '19	Replies 9	Views 1712	Votes 1

Roman 21.7k

Feb '15

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:

Can devtools migrations do this automatically?
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?
Can i do all the stuff with phalcon DBAL or i need to use raw sql queries instead?

Thanks!

btw, official documentation ( https://docs.phalcon.io/en/latest/reference/migrations.html ) is VERY old and kind of useless.

1

Thien Tran
58.4k

Accepted
answer

Feb '15

Hey

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

Max Galbusera
3.2k

Accepted
answer

edited Feb '15
Feb '15

Since we all are using php, I can recommend phinx: https://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();
    }
}

Ian Hu · Answer 1 · 2015-02-05T02:11:44-07:00

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.

Roman · Answer 2 · 2015-02-05T02:18:45-07:00

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.

Roman · Answer 3 · 2015-02-05T02:52:10-07:00

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 https://sqitch.org/

Roman · Answer 4 · 2015-02-05T04:50:33-07:00

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

Since we all are using php, I can recommend phinx: https://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();
   }
}

7thcubic · Answer 5 · 2015-02-05T11:07:22-07:00

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

Chris Willett · Answer 6 · 2018-01-26T16:08:38-07:00

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.phalcon.io/en/3.2/db-migrations

Roman · Answer 7 · 2018-01-26T16:18:26-07:00

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.phalcon.io/en/3.2/db-migrations

Chris Willett · Answer 8 · 2018-01-26T16:46:45-07:00

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.phalcon.io/en/3.2/db-migrations

Chris Willett · Answer 9 · 2018-01-26T16:50:53-07:00

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

https://forum.phalcon.io/discussion/17373/ts-based-migration

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

Fegalevac · Answer 10 · 2019-07-29T11:55:06-07:00

Guys, please tell me how to save data correctly and more efficiently? The fact is that I want to replace an old hard drive, that has dozens of programs, systems settings and so on, with a new drive.