One of the most exciting new features about Drupal 7 is the object orientated database layer, or Database: The Next Generation (DBTNG) as it was code named. For the first time in Drupal's history, you can now connect to several different databases of different server types at the same time. All using Drupal's database framework.
Out-of-the-box, Drupal 7 supports MySQL and PostgreSQL as it always has but adds new core support for SQLite. An attempt to make Drupal usable with smaller hosting plans that don't offer database hosting. In addition, there are also contrib drivers in the works for SQL Server, Oracle and MongoDB - the first non-SQL database driver for Drupal. And you can connect to all of them, all at the same time if you so dared!

The Drupal community see this mainly as a feature rich asset. Drupal can now run on almost any environment and almost any database server. Since Drupal's new database layer relies on PHP's PDO driver, Drupal's limitiation to database support is merely PHP's limitation to database support. Even still, since its object orientated, you could write your own driver should PHP not offer a PDO version.

Another reason why the new database layer is an asset to Drupal is because now Drupal can query third party systems at the database level with is own framework. Drupal's connection configuration is such that is allows you to setup connections to clusters of databases. Either Drupal's or someone elses (oh yeah, and Drupal 7 supports querying to database slaves!).

But there is a third ability I have a feeling the Drupal community isn't quite aware of just yet. Its never been possible before until Drupal 7 and I thinks its amazing! Drupal now has the ability to migrate an entire database from one server type to another! Thats right, Drupal can migrate itself from MySQL to PostgreSQL and back or even to SQLite! This opens up a lot of opportunity for market. Are you paying to much for hosting? Is your sight just a poster site? Migrate to SQLite. Have you inherited a client that runs PostgreSQL but you have no idea how to use it? Migrate to MySQL. Want to see if PostgreSQL runs your application better than any other server? Migrate to PostgreSQL.

This could be a way to choose the best tool for the job at the time! Imagine being able to test your configured application on all three databases before deploying to production to see which tool is best for the job? Or being able to elegantly scale? Start on SQLite, then when the database becomes to big for a single file, migrate your database to MySQL or PostgreSQL.

I had to try this functionality out. On this blog infact! This blog is proof of concept MySQL to PostgreSQL migration. This might be the time that I describe a little bit about how I went about doing it.

The problem with most database migration attempts is that there is no elegant mapping solution from one database server to another, not to mention the differences between versions. Usually, this is because migrations are attempted at the database level, as in, dump a database as SQL compliant as possible, run some script that tries to wrangle the data into compliant SQL for the destination database. Attempt a restore and cross fingers. Usually the script in the middle can't do a clever enough job to migrate the data and migrated data ends up partial at best.

With Drupal, however, most of the work is already done for us. This is because of the way modules in Drupal install tables in the database. They have to be described as PHP arrays first. This means that Drupal has a schema definition that is independent to any database server definition. It is also the exact same definition that is used to install tables with any of Drupal's supported database drivers.

It is worth noting, that Drupal's database schema is somewhat simplistic, as in, it doesn't support database level foreign keys (no reason why is couldn't now all databases are relational) or triggers or many database specific features. So keep in mind, if your app uses some custom feature of the database server it runs on, its not likely that you can migrate untill you come up with a solution for it.

Time for some code examples. The first thing you need to do is tell Drupal about the databases you're migrating between in the $database variable in settings.php:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'existing_db',
  'username' => 'drupal',
  'password' => '',
  'host' => 'localhost',
  'prefix' => '',
);
$databases['default']['pgsql'] = array(
  'driver' => 'pgsql',
  'database' => 'new_db',
  'username' => 'drupal',
  'password' => '',
  'host' => 'localhost',
  'prefix' => '',
);

This tells Drupal how to connect to each database. Next I would recommend would be to use Drupal's CLI tool 'Drush' to run the migration script - this way, the script doesn't need to be run through a browser or webserver and you don't need to worry about bootstrapping Drupal yourself:

    $ drush php-scr migrate.php

Finally the migration process itself is rather simple in theory:

1. Query existing database for enabled modules
2. Install existing modules schema in new database with drupal_install_schema($module)
3. For each table, select the full contains for the existing table and compile a multi-insert query to insert values into new table

Three simple steps to migrate a site from one database to another. Pretty neat ha? Lets take a look at the code in more depth:

$modules = module_list(TRUE);
db_set_active('pgsql');
foreach ($modules as $module) {
    drupal_install_schema($module);
}

Those 5 lines of code above cover steps one and two by installing all the module tables required in the new database. All that is left if to migrate the data itself:

db_set_active('default');
foreach ($modules as $module) {
    $schema = drupal_get_schema_unprocessed($module);
    _drupal_schema_initialize($module, $schema);
   foreach ($schema as $table => $info) {
       db_set_active('default');
       $rows = db_query("SELECT * FROM {$table}");
       db_set_active('pgsql');
       foreach ($rows as $row) {
           if (!isset($count) || ($count > 50)) {
                if (isset($insert)) {
                    $insert->execute();
                }
               $insert = db_insert($table);
               $count = 0;
           }
           $insert->fields((array) $row);
           $count++;
       }
       $insert->execute();
   }
}

This code is a bit more complex. It iterates through each table from the existing database and inserts the rows into the new database. It uses Drupal's multi-insert functionality so that it doesn't need to send a single insert to the server at a time. Instead, it sends sets of 50. The limit is implied incase the server can't handle the amount of data or if too much data gets stored in memory waiting to be sent to the server. There are somethings to watch out for. When migrating to PostgreSQL, remember to reset each sequence in PostgreSQL to +1 the max of the respective serial column. Otherwise trying to use your migrated site may see a bunch of errors crop up. Disclaimer: The code posted was theoretical and has not been tested. However, similar source code was used to migrate this site.