How to add data migration support to Silex

Add Doctrine data migrations to Silex application

There are quite a few blog articles about using doctrine migrations in different project types and this article was closest to what we need in Silex project.

Install doctrine migration module

We add a few required components to composer.json and execute update command.

We need to have symfony/console module already installed and prepared for extending with new commands, as I wrote in this article.

Add migration commands to console.php program.  You can find them in vendor folder under:  “/lib/Doctrine/DBAL/Migrations/Tools/Console/Command” path.

Add migrations configuration files:

Migrations configuration define base settings for migration generation and execution environment.

Database configuration is needed that migrations can really do the job.

Default table options

How to set default create options for tables and columns such as default collation sequence and character set ?

‘defaultTableOptions’ => [
‘charset’=> ‘utf8mb4’,
‘collate’ => ‘utf8mb4_slovenian_ci’
],

To set custom character set and collation just add “defaultTableOptions” array with your specific  default settings in connection configuration array (file: migrations-db.php).

Console command interface

You can test it immediately with console command:

>php console.php list migration

2015-11-18 21_47_07-Command Prompt

With data migrations you can easily update your project database with a programmatic interface with version support.  Your database schema can grow without risk of loosing control over it.

Sample script

Empty class is generated with “migrations:generate” command. After initial generation you code migration changes manually.

After generating first migration file the file is saved in configured migrations folder (./app/Migrations/).

2015-11-19 00_53_25-silex03 - NetBeans IDE 8.0.2

Run migrations

Your migration scripts will not running automatically, you need to do it by yourself.  To execute open migrations you need to run command:  “migrations:migrate” .

 

Doctrine DBAL – working with database

In this article I am going to cover using MySql database in Symfony 2 application.

I will use the simplest possible database access technology PHP offers – PDO,  but in slightly extended form of Doctrine DBAL. So if you want to use Doctrine ORM , this article is not right place to start.

Before we begin

We need some sample data in the database,  so we create a database “dbsamples” with “country” table and import data from this csv file. Because I will not use full Doctrine ORM ,  generating db schema is not an option, I will create database manually.

You can use phpMyAdmin for that,  it is usually installed by default. If you need true utf8 database, you need to set character set to utf8mb4.

Connecting to the database

Because we use Symfony standard framework, we already have  connection parameters in a configuration file (“parameters.yml”).

I added one new parameter here, an array of custom driver options (“database_options”). By default all received data to php are stringified, it means you get all data in strings. To receive native data types from the database (int as integer for example), you need to set those options and use prepared statements. But this options will only work if you have luck and your hosting installed and enabled  “mysqlnd” native driver, check your hosting for the driver type.

In the config.yml file, this parameters are mapped to dbal connection  parameters:

When we are in the symfony controller file (as in \AppBundle\Controller\DefaultController.php for example), we get connection directly from the container:

or more specific for DBAL connection :

but because aquired connections are not pooled (php connections do not support connection pool yet), is actually the same if you create new connection by yourself :

Selecting data

Selecting data in DBAL is easy and intuitive, just use connections executeQuery method. If you use parameters in SQL statement, this will create prepared statement for you and bind parameters automatically. You should always use prepared statements because they are more secure and more efficient as plain text statements.

But there is even better way, using QueryBuilder object and assembling SQL statement with PHP code.

Fetch data to types

To get result from a statement, we need to execute one of the “fetch” methods.

By default we receive each result row as an array, or as array of arrays for multiple records. We can even fetch directly to an instance of an class:

Don’t forget we have all the power of PDO library at our disposal.

With little help from variable type hinting , we get full code completion on received object instance in the IDE:

2015-09-27 11_05_12-Untitled - paint.net 4.0.6Well, that’s of course possible only in case we use same exact names in database and in php class :

Custom columns mapping

Well, you really need to have a very good reason to select different names for columns in database, but sometimes you are not original owner of database. In that case you could introduce mapping function like this:

Prepare sample for mapping

We create new table “currency” in the database  :

and write new class “Currency” in php :

We do not want push to far in this direction, we could start developing a new ORM or something and that is not our intention. We want to retain full power of SQL language in the application, without need to write to much PHP or SQL code manually.

Usage example:

Mapping array contain database columns first (as keys) and class properties second (as values).

Inserting data

Inserting data is straightforward :

Because insert command expect array as second parameter, we simply convert object instance ($country)  to associative array.  After record is inserted, we inspect last inserted value in auto numbered column and return value to the program.

Update data

Update is very similar to insert, except we need to add third parameter to find existing record :

Delete data

And deleting is event simpler:

I didn’t mentioned that all commands are available as query builder methods.

Working with transactions

There is full transaction support directly in php code. We must explicitly start transaction and commit after last database command. In case of errors we need to call rollback on the used connection.

If you think this is little verbose and want to shorten the code, you actually can, just use closure with “transactional” function :