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?

Vinay Sheoran · Accepted Answer · 2019-01-02 15:36:36Z

1

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();

answered Jan 2, 2019 at 15:36

Vinay Sheoran

5283 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WeSee Over a year ago

Thank you for your answer. At the later stage in my projekt I want have both databases, MySQL for meta data and InfluxDB for timeseries. My questions is about handling timeseries only, not a full replacement for data storage in my Yii2 app. How should this superclass/interface be structured/build?

machour · Accepted Answer · 2019-01-02 15:18:04Z

0

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.

answered Jan 2, 2019 at 15:18

machour

7341 gold badge7 silver badges17 bronze badges

2 Comments

WeSee Over a year ago

Why is abstraction a problem here? The same superclass or interfaces can help to switch later on quite easy. And both share similar logic: connect before access, handling errors, the same writing interfaces,...

Vinay Sheoran Over a year ago

I also agree with @WeSee

Collectives™ on Stack Overflow

Yii2/PHP: Abstracting Database Access for InfluxDB and MySQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related