How To Write Direct SQL Query in Magento 2 (Without Loading Model)

In this blog, we will learn to execute direct SQL query in Magento 2.

In many situations where you need to executing SQL query for entity would be easy and quick as a performance basis.

When you need to insert/update mass data of entity and you need to save each specific entity will take a long time with resource and therefore more possibility to make the system unusable. To overcome this issue, we suggest to execute a direct SQL query which could update a mass set of an entity few seconds.

<?php
namespace Mageclues\DirectSql\Helper;

use Magento\Framework\App\Helper\AbstractHelper;
use Magento\Framework\App\Helper\Context;
use Magento\Framework\App\ResourceConnection;

class Data extends AbstractHelper
{
    protected $_resourceConnection;
	
    public function __construct(Context $context, ResourceConnection $resourceConnection)
    {
        $this->_resourceConnection = $resourceConnection;
        parent::__construct($context);
    }
    public function executeDirectSqlQuery()
    {
        $connection = $this->_resourceConnection->getConnection();
        
	// get table name
        $table = $connection->getTableName('mc_store_table');
		
        //For select/read records query
        $query = "Select * FROM " . $table;
        $result = $connection->fetchAll($query);
        $this->_logger->log(print_r($result, true));
		
        $entity_id = 3;
        $query = "SELECT * FROM `" . $table . "` WHERE id = $entity_id ";
        $result1 = $connection->fetchAll($query);
        $this->_logger->log(print_r($result1, true));
		
        //For Insert record query
	$query = "INSERT INTO `" . $table . "`(`title`, `city`, `phone`) VALUES ('ABC StoreMart', 'NewYouk','+44 11111111')";
        $connection->query($query);
		
	// Insert with array form
	$tableColumn = ['title', 'city','phone'];
        $tableData[] = ['ABC StoreMart', 'NewYouk','+44 11111111'];
        $connection->insertArray($table, $tableColumn, $tableData);
		
        // For Update/edit record query
        $entity_id = 30;
        $query = "UPDATE `" . $table . "` SET `phone`= '+44 11111222' WHERE `entity_id` = ".$entity_id;
        $connection->query($query);
		
        // For Delete/remove record query
        $entity_id = 15;
        $query = "DELETE FROM `" . $table . "` WHERE entity_id =". $entity_id;
        $connection->query($query);
    }
}

We hope this technical blog is useful for you. we request you please share this blog so it’s helpful for other developers. please leave comment if you have any queries. thank you!