How to Run Direct SQL Query in Magento 2

Running direct SQL queries in Magento 2 is not recommended unless it's absolutely necessary, as it can pose security risks and may not be compatible with future versions of Magento. Instead, you should use Magento's built-in data access methods and models whenever possible.

However, if you must run a direct SQL query in Magento 2, here's how you can do it:

Important Note: Always make a backup of your database before running any direct SQL queries, as they can potentially modify or delete data.

Let's assume you have a custom module named CustomModule_YourModule. In this example we'll use your_table_name as a table. You can replace it with your table name.

Inject the \Magento\Framework\App\ResourceConnection into your class where you want to run the SQL query. You can do this through dependency injection in the constructor.

Check the below example code for your reference. In this example we've created the few separated methods to perform CRUD operation with the direct run SQL query in Magento 2.

saveCopyzoom_out_map
<?php

namespace CustomModule\YourModule\Model;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Adapter\AdapterInterface;

class YourModel
{
    private ResourceConnection $resourceConnection;
    private AdapterInterface $connection;

    public function __construct(ResourceConnection $resourceConnection)
    {
        $this->resourceConnection = $resourceConnection;
        $this->connection = $resourceConnection->getConnection();
        
    }

    public function selectRecords()
    {
        $tableName = $this->connection->getTableName('your_table_name');
        
        $query = "Select * FROM " . $tableName;
        $result = $this->connection->fetchAll($query);
        
        return $result;
    }

    public function insertRecords()
    {
        $tableName = $this->connection->getTableName('your_table_name');
        
        $query = "INSERT INTO `" . $tableName . "`(`column1`, `column2`) VALUES ('Value1','Value2')";
        $result = $this->connection->query($query);
        
        return $result;
    }

    public function updateRecords()
    {
        $tableName = $this->connection->getTableName('your_table_name');
        
        $query = "UPDATE `" . $tableName . "` SET `column1`= 'Value2' WHERE id = 5 ";
        $result = $this->connection->query($query);
        
        return $result
    }


    public function deleteRecords()
    {
        $tableName = $this->connection->getTableName('your_table_name');
        
        $query = "DELETE FROM `" . $tableName . "` WHERE id = 5 ";
        $result = $this->connection->query($query);
        
        return $result
    }
}

That's it!

Remember that using direct SQL queries should be a last resort in Magento 2 development. It's preferable to use Magento's provided models, collections, and APIs to interact with the database to ensure better compatibility and security. Always be cautious when working with direct SQL queries to avoid data integrity issues and security vulnerabilities.

I hope this post helped you to find what you were looking for.

Bookmark it for your future reference. Do comment below if you have any other questions on that.

P.S. Do share this note with your team.