Yii2/PHP: Abstracting Database Access for InfluxDB and MySQL

Question

In my Yii2/PHP project I need to have both databases integrated:

• MySQL for meta data, Web-UI, ...
• InfluxDB for measurement data (heavy loads of timeserie data)

To reduce complexity I'd like to start with MySQL only and add InfluxDB later.

My idea is to create an abstraction/superclass for both databases (for measurement data only) which allow to do implementation and perform tests with MySQL and enable speedup with InfluxDB at a later stage in the project.

The abstraction should have methods for:

• database connection management
• writing data
• reading data (raw data, aggregations)

Since I am no InfluxDB expert (yet): Does this architecture make sense or are both datamodels and schemes fundamentally different so an abstraction would be worthless? Are there projects out there to learn from?

Answer 1

I'm not sure trying to fit MySQL and InfluxDB in the same mould would make a lot of sense.

A better approach IMHO, would be to have some sort of helper class for your computations (i.e.: Stats::getViews(), Stats::getVisitors(), ..) first using MySQL, and later rewrite it to use InfluxDB, keeping the same methods signatures and responses formats.

Answer 2

First, you need to configure your databases like below this example take two mysql db:

return [
'components' => [
    'db1' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db1name', //maybe other dbms such as psql,...
        'username' => 'db1username',
        'password' => 'db1password',
    ],
    'db2' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db2name', // Maybe other DBMS such as psql (PostgreSQL),...
        'username' => 'db2username',
        'password' => 'db2password',
    ],
],
];

Then you can simply:

// To get from db1
Yii::$app->db1->createCommand((new \yii\db\Query)->select('*')->from('tbl_name'))->queryAll()

// To get from db2
Yii::$app->db2->createCommand((new \yii\db\Query)->select('*')->from('tbl_name'))->queryAll()

If you are using an active record model, in your model you can define:

public static function getDb() {
    return Yii::$app->db1;
}

//Or db2
public static function getDb() {
    return Yii::$app->db2;
}

Then:

If you have set db1 in the getDb() method, the result will be fetched from db1 and so on.

ModelName::find()->select('*')->all();