Migrations para aplicações PHP com Phinx

Juciellen Cabrera

Analista de Sistemas na 4Linux | Rankdone

Instrutora de PHP

@PHPWomenBR

@jucycabrera

jucarol17@gmail.com

 

Era uma vez você

Que tem uma aplicação de pé

E precisa criar uma uma nova tabela

E alimentar essa tabela com registros

Como você faria?

Problemas

  • Deploy da aplicação depende das alterações no banco
  • Fazer aplicação direto no banco de dados
  • DBA precisa estar disponível
  • Entre outros problemas

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

O que você acha de criar um script pra automatizar as alterações?

Migration

É um script que vai efetuar alterações em um banco de dados

E agora quem poderá nos defender?

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

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

php composer.phar require robmorgan/phinx

php composer.phar install --no-dev

Instalando

vendor/bin/phinx init .

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

created ./phinx.yml

Iniciando o Phinx

Configurando

phinx.yml

Configurando

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

phinx.yml | Diretório de migrations

 

Configurando

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

phinx.yml | Ambientes

 

Configurando

environments:
    
    default_migration_table: phinxlog

phinx.yml | Tabela de log

 

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

db/migrations no nosso caso

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 MinhaMigration

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
<?php

use Phinx\Migration\AbstractMigration;

class CreateTableUsers extends AbstractMigration
{
    
    public function change()
    {

    }
}

Onde escrever a migration?

  • change
  • up
  • down

change

  • alteração de estrutura
  • classe Table
  • migrate/rollback

Onde escrever a migration?

 Classe AbstractMigration

  • table
  • hasTable
  • execute
  • fetchRow
  • fetchAll

Classe Table

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

Escrevendo migration

public function change()
{
    $users = $this->table('users');
    $users->addColumn('name', 'string');
    $users->addColumn('email', 'string');
    $users->addColumn('password', 'string')
        ->addColumn('active', 'boolean', [
            'null' => false,
            'default' => true
        ]);
    $users->addColumn('created_at', 'datetime',[
            'default'=>'CURRENT_TIMESTAMP'
        ]);
    $users->addColumn('updated_at', 'datetime', [
            'null' => true]);
    $users->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

vendor/bin/phinx migrate -e development

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)

Nossa tabela foi criada

e phinxlog também

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)

users

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)

phinxlog

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)

phinxlog

up / down

  • Execução de comando
  • Inserção de dados
  • up - migrate
  • down - rollback

Onde escrever a migration?

Up | Down

<?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'");
    }
}

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

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 :-D

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

Tome cuidado!!!

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

Made with Slides.com