How to run SQL commands in magento

Although I wouldn’t recommend running accessing the database “directly” via SQL commands, it might be useful as a last resource to do so. But, how would one be able to implement it?

By using the ResourceConnection class in \Magento\Framework\App we would be able to establish a connection to the database and get an object that implements the AdapterInterface defined in \Magento\Framework\DB\Adapter\AdapterInterface.

This interface defines all types of methods that implement a wide range of SQL commands, ranging from selects to updates, drops and creating tables.
Here is simple example of the select and update commands. The select fetches the value of the is_in_stock field in the cataloginventory_stock_item table for a given product.
The update sets the value of the stock_status field in table cataloginventory_stock_status to a given value for a certain product.

class RunSQLCommands
{    
	protected $_resource;
	
	public function __construct(
		\Magento\Framework\App\ResourceConnection $resource
	)
	{
		$this->_resource = $resource;
	}
	
	public function runSelect($id)
	{
		$connection  = $this->_resource->getConnection();
		$tableName = $connection->getTableName("cataloginventory_stock_item");
			  
		$select = $connection->select()->from($tableName, 'is_in_stock')->where('product_id = :id');
		$bind = [':id' => (string)$id];

		$is_in_stock = (int) $connection->fetchOne($select, $bind);
	}

	public function runUpdate($value)
	{
		$connection  = $this->_resource->getConnection();
		$tableName = $connection->getTableName("cataloginventory_stock_status");
			  
		$data = ["stock_status" => $value];
		$where = ['product_id = ?' => (int)$id];
		$connection->update($tableName, $data, $where);
	}
}