Magento – Magento 2 – read and write connection for custom query

databasedatabase connectionmagento-2.2.5

I am using Magento 2.2.5 EE. I need to write some custom queries Select and Insert

I know how to write custom query, its simple:

Create new connection:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

Apply select query through this:

$tableName = $resource->getTableName('employee'); //gives table name with prefix
//Select Data from table
$sql = "Select * FROM " . $tableName;
$result = $connection->fetchAll($sql);

And Insert command using this:

//Insert Data into table
$sql = "Insert Into " . $tableName . " (emp_id, emp_name, emp_code, emp_salary) Values ('','XYZ','ABD20','50000')";
$connection->query($sql);

In magento 1.9, we just define "core_read" and "core_write" for our custom queries, it was that simple.

My question is, how Magento knows when to insert on master DB (write) and when to get data from reading replica (read)? I mean the object for both commands is the same.

Best Answer

First, you should update your question so that one can find that you are talking about multiple DB configurations.

You can't use direct queries for this type of configuration. As it is stated in the documentation.Split database performance solution (Magento Commerce only)

Configuration options Because of the way the split database performance solution is designed, your custom code and installed components cannot do any of the following:

- Write directly to the database (instead, you must use the Magento Commerce database interface)
- Use JOINs that affect the sales or quote databases
- Use foreign keys to tables in the checkout, sales, or main databases
Related Topic