How To Execute Native SQL Query With Doctrine

So, it seems like you are in big trouble. Otherwise, why will you want to execute a doctrine native SQL query? In this tutorial, I will describe and show how you can perform such operations. But that doesn’t necessarily mean I am encouraging you to use it all the time. I will still strongly recommend finding your way within the doctrine’s ORM, DBAL And DQL implementation as much as possible. However, you may face some UN-avoidable situations where you can’t help without native SQL support, as I faced recently.

My Scenario For Choosing Doctrine Native SQL Query Support:

My application was in a stable database schema/entity implementation and was live. For refactoring purposes, I had to change entities in DB schema for adding/removing columns/properties. Now, as the application is live, I need to transfer all my old data to the new schema, thus needing a bulk migration script. Here is what I could do – retrieve data from the old database, process them accordingly and insert them into the new database table(s). But did you notice we changed our entities per the new schema and thus can’t retrieve data from the old database in ORM style?

Hmm, so, now what? Well, here is what came into my mind to use the doctrine native SQL query support. Using this, after retrieving data from the old database, we can either insert/process them as a new entity or in native support, what suits best for the requirement.

This is not the only case where we may face similar issues; you may find others as well. For the sake of such a scenario, let’s go and learn the basics.

Getting Database Connection From Entity Manager:

The first step to using doctrine is to get the connection object from the entity manager. Let’s see the basic initialization of the doctrine entity manager and retrieving connection instance in PHP:

As you are possibly using doctrine already, you can skip initialization. Best way to use them as a separate wrapper class for your application as the one I showed before while integrating doctrine with codeigniter.

//**********priliminary doctrine set up. You can do it separate generalized class/function as well********

$config = new \Doctrine\ORM\Configuration;
// Cache Setup
$apcCache = new \Doctrine\Common\Cache\ApcCache;
$config->setMetadataCacheImpl($apcCache);
$config->setQueryCacheImpl($apcCache);
$config->setResultCacheImpl($apcCache);

// Proxy Setup
$config->setProxyDir('proxy');
$config->setProxyNamespace('Proxies');
$config->setAutoGenerateProxyClasses(true);

// Mapping Setup
$driverImpl = $config->newDefaultAnnotationDriver(__DIR__);
$config->setMetadataDriverImpl($driverImpl);

// Connection Setup
$connectionOptions = array(
	'dbname' => "database_name",
	'user' => "db_user_name",
	'password' => "db_password",
	'host' => "db_host",
	'driver' => 'pdo_mysql');

// Initialize Entity Manager
$entityManager = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);

//**** Basic Initialization Ends Here*********

//retrieve the connection object
$connection = $entityManager->getConnection();Code language: PHP (php)

Prepare And Execute SQL Query:

there is a helpful method named ‘prepare’ which will help us to create an SQL statement suitable for execution under Doctrine. Then, the generalized ‘execute’ method will run the SQL query, whether it’s “insert”/”delete”/”update” or a select statement. Following is the code snippet example:

$statement = $oldConnection->prepare("SELECT * FROM mytable");
$statement->execute();Code language: PHP (php)

Now, if the query is of insert/update or delete, we should be done and fine. But in the case of a select query, we need to fetch the data, which should be something like as below:

//fetch all results in associative array format
$results = $statement->fetchAll();

//fetch single row
$result = $statement->fetch();

//total row count
$result = $statement->rowCount();Code language: PHP (php)

Friendly Support For CRUD Operations:

As we have seen, we can now run the raw SQL query; we can use some more friendly methods for database update operations, which are directly applied on the connection object. Let’s see few examples:

//insert data from an associative array
$connection->insert('table_name', $associative_array);

//update row from an associative array
$connection->update('table_name', $associative_array,$identifier);

//delete a row
$connection->delete('table_name', $identifier);Code language: PHP (php)

Running Transaction In Doctrine Native SQL Query Mode:

You may sometimes need to run multiple queries in a single transaction to optimize the database operations. As you can see above, in my case, optimization is crucial. Thus I needed a way to do such operations to reduce the total number of database connectivity and make the process faster. You might need it for other purposes like rollback if some operations failed etc cases as well. Let’s see some code examples of how they can be achieved:

$connection = $em->getConnection();
$connection->transactional (function ($connection) {
    $connection->insert('table1', $associative_array1);
    $connection->insert('table2', $associative_array2);
    ...
    $connection->insert('tableN', $associative_arrayN);
});Code language: PHP (php)

The above example is for insert operation, though you can use it for update/delete or ‘execute’ method etc as well too.

Final Words:

Hopefully, this small tutorial on doctrine native SQL query support will be helpful for you in some situations. Read more on doctrine documentation on this topic as further reference. Let me know if you are facing any issues while trying the PHP code examples above. Happy coding 🙂