Migrations para aplicações PHP com Phinx

Juciellen Cabrera

Analista de Sistemas na 4Linux | Rankdone

Instrutora de PHP

@PHPWomenBR

@jucycabrera

jucarol17@gmail.com

 

Como você atualiza a base de dados da sua aplicação ?

Não precisa ser tão trabalhoso né?

O que uma migration?

Phinx é uma ferramenta via linha de comando para gerenciamento de migrações de banco de dados

  • Fácil de instalar
  • Migrations usando PHP ou SQL
  • Boa documentação
  • Integração com ferramentas de deploy
  • Ferramenta open source
curl -s https://getcomposer.org/installer | php

php composer.phar require robmorgan/phinx

php composer.phar install --no-dev

Instalação

vendor/bin/phinx init .

Phinx by CakePHP - https://phinx.org. 0.10.6

created ./phinx.yml

Iniciando o Phinx

Configurando

paths:
migrations: %%PHINX_CONFIG_DIR%%/db/migrations

environments:
default_migration_table: phinxlog
default_database: development

development:
adapter: pgsql
host: localhost
name: db_test
user: SEU_USUARIO
pass: SENHA_SEGURA
port: 5432
charset: utf8

version_order: creation

phinx.yml

Não esqueça de criar o diretório para as migrations ;-)

db/migrations no nosso caso

Comandos

  • status
  • create
  • migrate
  • rollback
vendor/bin/phinx status -e development
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using environment development
ordering by creation time

There are no available migrations. Try creating one using the create command.

Listando migrations

status - primeira vez

Criando migration

vendor/bin/phinx create CreateTableUsers
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using migration base class Phinx\Migration\AbstractMigration
using default template
created db/migrations/20190424000332_create_table_users.php

create

<?php

use Phinx\Migration\AbstractMigration;

class CreateTableUsers extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
     *
     * The following commands can be used in this method and Phinx will
     * automatically reverse them when rolling back:
     *
     *    createTable
     *    renameTable
     *    addColumn
     *    addCustomColumn
     *    renameColumn
     *    addIndex
     *    addForeignKey
     *
     * Any other destructive changes will result in an error when trying to
     * rollback the migration.
     *
     * Remember to call "create()" or "update()" and NOT "save()" when working
     * with the Table class.
     */
    public function change()
    {

    }
}

 Classe AbstractMigration

  • table
  • hasTable
  • execute
  • fetchRow
  • fetchAll

Onde escrever a migration?

  • change
  • up
  • down

change

  • quando usar classe Table
  • migrate/rollback

Classe Table

  • addColumn
  • hasColumn
  • changeColumn
  • addForeignKey
  • save
  • update

Escrevendo migration

  • name
  • email
  • password
  • active
  • created_at
  • updated_at
public function change()
{
    $users = $this->table('users');
    $users
        ->addColumn('name', 'string')
        ->addColumn('email', 'string')
        ->addColumn('password', 'string')
        ->addColumn('active', 'boolean', [
            'null' => false,
            'default' => true
        ])
        ->addColumn('created_at', 'datetime',[
            'default'=>'CURRENT_TIMESTAMP'
        ])
        ->addColumn('updated_at', 'datetime', ['null' => true])
        ->addIndex(['email'], ['unique' => true])
        ->create();
}

change

Vamos executar a migration

Executando migration

vendor/bin/phinx migrate -e development
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using environment development
using adapter pgsql
using database palestra_phinx

 == 20190424000332 CreateTableUsers: migrating
 == 20190424000332 CreateTableUsers: migrated 0.2231s

All Done. Took 0.2408s

migrate

palestra_phinx=> \d
                Lista de relações
 Esquema |     Nome     |   Tipo    |    Dono     
---------+--------------+-----------+-------------
 public  | phinxlog     | tabela    | jucycabrera
 public  | users        | tabela    | jucycabrera
 public  | users_id_seq | sequência | jucycabrera
(3 registros)

palestra_phinx=> \d phinxlog
                           Tabela "public.phinxlog"
     Coluna     |            Tipo             | Collation | Nullable | Default 
----------------+-----------------------------+-----------+----------+---------
 version        | bigint                      |           | not null | 
 migration_name | character varying(100)      |           |          | 
 start_time     | timestamp without time zone |           |          | 
 end_time       | timestamp without time zone |           |          | 
 breakpoint     | boolean                     |           | not null | false
Índices:
    "phinxlog_pkey" PRIMARY KEY, btree (version)


palestra_phinx=> select * from phinxlog ;
    version     |  migration_name  |     start_time      |      end_time       | breakpoint 
----------------+------------------+---------------------+---------------------+------------
 20190424000332 | CreateTableUsers | 2019-04-23 21:26:57 | 2019-04-23 21:26:57 | f
(1 registro)

Repare que a tabela phinxlog foi criada

palestra_phinx=> \d users
                                        Tabela "public.users"
   Coluna   |            Tipo             | Collation | Nullable |              Default              
------------+-----------------------------+-----------+----------+-----------------------------------
 id         | integer                     |           | not null | nextval('users_id_seq'::regclass)
 name       | character varying(255)      |           | not null | 
 email      | character varying(255)      |           | not null | 
 password   | character varying(255)      |           | not null | 
 active     | boolean                     |           | not null | true
 created_at | timestamp without time zone |           | not null | CURRENT_TIMESTAMP
 updated_at    | timestamp without time zone |           |          | 
Índices:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email" UNIQUE, btree (email)
public function change()
{
    $users = $this->table('users');
    $users
        ->addColumn('name', 'string')
        ->addColumn('email', 'string')
        ->addColumn('password', 'string')
        ->addColumn('active', 'boolean', [
            'null' => false,
            'default' => true
        ])
        ->addColumn('created_at', 'datetime',[
            'default'=>'CURRENT_TIMESTAMP'
        ])
        ->addColumn('updated_at', 'datetime', ['null' => true])
        ->addIndex(['email'], ['unique' => true])
        ->create();
}

Tabela users foi criada

up / down

  • up - migrate
  • down - rollback

Comando de execução

<?php

use Phinx\Migration\AbstractMigration;

class InsertDefaultUser extends AbstractMigration
{

    public function up()
    {
        $this->execute("
            INSERT INTO users (name, email, password, active, created_at)
            VALUES ('Platform User', 'platform_user@email.com', '123456', 't', NOW())          
        ");
    }

    public function down()
    {
        $this->execute("DELETE FROM users WHERE email = 'platform_user@email.com'");
    }
}

execute

Tome cuidado!!!

Buscando registros

public function down()
    {
        $user = $this->fetchRow("SELECT id FROM users 
            WHERE email = 'platform_user@email.com'");

        if(!$user){
            return;
        }

        $this->execute("DELETE FROM users WHERE id = {$user['id']}");
    }

fetchRow/fetchAll

vendor/bin/phinx status -e development
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using environment development
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name 
----------------------------------------------------------------------------------
     up  20190424000332  2019-04-23 22:34:13  2019-04-23 22:34:13  CreateTableUsers
   down  20190424013532                                            InsertDefaultUser

Listando migrations

status - migration pendente

Tem migration pra executar

vendor/bin/phinx status -e development
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using environment development
ordering by creation time

 Status  [Migration ID]  Started              Finished             Migration Name 
----------------------------------------------------------------------------------
     up  20190424000332  2019-04-23 22:34:13  2019-04-23 22:34:13  CreateTableUsers
     up  20190424013532  2019-04-23 22:39:55  2019-04-23 22:39:55  InsertDefaultUser

Listando migrations

status - tudo ok

Revertendo migration

vendor/bin/phinx rollback -e development
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using environment development
using adapter pgsql
using database palestra_phinx
ordering by creation time

 == 20190424000332 CreateTableUsers: reverting
 == 20190424000332 CreateTableUsers: reverted 0.1031s

All Done. Took 0.1478s

rollback

O que aparece quando dá erro?

vendor/bin/phinx migrate -e development
Phinx by CakePHP - https://phinx.org. 0.10.6

using config file ./phinx.yml
using config parser yaml
using migration paths 
 - /home/cabrera/Juciellen/Documentos/projetos/phinx/db/migrations
using seed paths 
using environment development
using adapter pgsql
using database palestra_phinx

 == 20190424013532 InsertDefaultUser: migrating

In PdoAdapter.php line 167:
                                                                              
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"        
  LINE 3: ... User', 'platform_user@email.com', '123456', 't', )         ...  
                                                               ^              
                                                                              

migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] 
[-t|--target TARGET] [-d|--date DATE] [-x|--dry-run] [--fake]

Faça as devidas correções e execute de novo

Recomendações

  • Método change apenas com classe Table
  • Uma migration pra cada tabela
  • Scripts simples
  • Seeds para dados de teste

Referências

  • https://phinx.org/
  • http://docs.phinx.org/en/latest/
  • https://blog.4linux.com.br/migrations-para-aplicacoes-php-com-phinx/

Obrigada ;-)

@jucycabrera

Sorteio Formação PHP da 4Linux

https://sorteio.4linux.com.br/

Made with Slides.com