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 ;-)
Migrations com Phinx
By Jucy Cabrera
Migrations com Phinx
- 1,314