How To Execute Native SQL Query With Doctrine

So, seems like you are in big trouble? Otherwise why you will want to execute 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 to find your way within doctrine’s ORM, DBAL And DQL implementation, as much possible. However, you may face some UN-avoidable situation 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 live. For refactoring purpose, I had to change entities and in db schema for adding/removing columns/properties. Now, as the application was live, I do need to transfer all my old data to new schema thus need a bulk migration script. Now here is what I could do – retrieve data from old database, process them accordingly and insert them to new database table(s). But did you notice, we did changed our entities as per new schema and thus can’t retrieve data from old database in ORM style.

Hmm, so, now what? Well, here what came into my mind to use the doctrine native SQL query support. Using this, after retrieving data from old database, we can either insert/process them as 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 sake of such scenario, let’s go and learn the basics.

Getting Database Connection From Entity Manager:

First step to use doctrine is to get the connection object from entity manager. Lets see the basic initialization of 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();

Prepare And Execute SQL Query:

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

$statement = $oldConnection->prepare("SELECT * FROM mytable");
$statement->execute();

Now, if the query is of insert/update or delete, we should be done and fine. But in case of 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();

Friendly Support For CRUD Operations:

As we have seen, we can now run raw SQL query, we can use some more friendly methods for database update operations, which are directly applied on the connection object. Lets 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);

Running Transaction In Doctrine Native SQL Query Mode:

You may sometimes need to run multiple query in a single transaction to optimize the database operations. As you can see above in my case, optimization is crucial and thus I needed a way to do such operations to reduce 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. Lets see some code examples 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);
});

The above example is for insert opeartion 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 issue while trying with the php code examples above. Happy coding 🙂