Yii2: Supporting MySQL and PostgreSQL

Question

My Yii2 application should allow customers to use MySQL or PostgreSQL as database backends. So I need to write code which runs for both databases.

I started supporting MySQL and need to eleminate MySQL specific code, e.g. in migrations:

public function up() {
    $this->execute('ALTER SCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci');
    $this->execute('ALTER SCHEMA CHARACTER SET utf8 COLLATE utf8_general_ci');
    $this->createTable('user', [ 
            'id' => $this->primaryKey(),
            ... 
        ],
       'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB'
    );
}

How to rewrite this piece of code?

Are there repos which have tackled this? What are best practices for MySQL and PostgreSQL compatibility?

Answer 1

If i understand correctly you are talking about writing migrations which can handle the db specific keywords or functions depending upon the currently selected or targetted databases, then you might have to create your own logic for that for the migrations to support it.

I use the following class as the base Migration class and extend the migrations from this class for the tableOptions and dropping column constraints. You can upgrade it and add the necessary operations/functions according to your needs. But it can create the logic to implement what you are asking.

<?php

namespace console\migrations;
use Yii;
class Migration extends \yii\db\Migration
{

    /**
     * @var string
     */
    protected $tableOptions;

    /**
     * @var string
     */
    protected $restrict = 'RESTRICT';

    /**
     * @var string
     */
    protected $cascade = 'CASCADE';

    /**
     * @var string
     */
    protected $noAction = 'NO ACTION';

    /**
     * @var mixed
     */
    protected $dbType;

    /**
     * @inheritdoc
     */
    public function init()
    {
        parent::init();

        switch ($this->db->driverName) {
            case 'mysql':
                $this->tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
                $this->dbType = 'mysql';
                break;
            case 'pgsql':
                $this->tableOptions = null;
                $this->dbType = 'pgsql';
                break;
            case 'dblib':
            case 'mssql':
            case 'sqlsrv':
                $this->restrict = 'NO ACTION';
                $this->tableOptions = null;
                $this->dbType = 'sqlsrv';
                break;
            default:
                throw new \RuntimeException('Your database is not supported!');
        }
    }

    /**
     * Drops the constraints for the given column
     *
     * @param string $table  the table name
     * @param string $column the column name
     *
     * @return null
     */
    public function dropColumnConstraints($table, $column)
    {
        $table = Yii::$app->db->schema->getRawTableName($table);
        $cmd = Yii::$app->db->createCommand(
            'SELECT name FROM sys.default_constraints
            WHERE parent_object_id = object_id(:table)
            AND type = \'D\' AND parent_column_id = (
                SELECT column_id
                FROM sys.columns
                WHERE object_id = object_id(:table)
                and name = :column
            )', [':table' => $table, ':column' => $column]
        );

        $constraints = $cmd->queryAll();
        foreach ($constraints as $c) {
            $this->execute('ALTER TABLE ' . Yii::$app->db->quoteTableName($table) . ' DROP CONSTRAINT ' . Yii::$app->db->quoteColumnName($c['name']));
        }
    }

}

Answer 2

I'm not that familiar with Postgre, but by the looks of the basic migration in app-advanced you can check the driverName and create different logic based on that.

Migration File

$tableOptions = null;
if ($this->db->driverName === 'mysql') {
    $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
}