Doctrine DBAL with Symfony2

Featured Image

I regularly use Doctrine ORM for database operations in my Symfony2 project, but there was one situation that I had to write database importer from SQLITE database into projects Mysql database. Importer should run only once and I didn’t want to make new entity classes for importer, but just make code to work while import is not finished.
That case I used Doctrine DBAL (database abstraction layer).
What we need to use DBAL in Symfony2?

Let’s start from our main configuration file:

#config.yml

#...

# Doctrine Configuration
doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                charset:  UTF8
                driver:   %database_driver%
                host:     %database_host%
                dbname:   %database_name%
                user:     %database_user%
                password: %database_password%
            sqlite:
                driver:  %sqlite_driver%
                path:    %sqlite_path%
    orm:
        auto_generate_proxy_classes: %kernel.debug%
        #auto_mapping: true
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
                mappings:
                   SurgeworksAdminBundle: { type: annotation, dir: Entity/ }
 
#...

For Sqlite is necessary just to specify driver and path to database file. I put the database file inside ‘app’ folder. My parameters.ini file looks like that:

[parameters]
    database_driver="pdo_mysql"
    database_host="localhost"
    database_name="databasename"
    database_user="someuser"
    database_password="somepassword"
    mailer_transport="smtp"
    mailer_host="localhost"
    mailer_user=""
    mailer_password=""
    locale="en"
    secret="somesecret"
    sqlite_driver=pdo_sqlite
    sqlite_path=%kernel.root_dir%/sqlite_database_name.db

That’s it from configuration side. Let’s show how to use it:

//...
//Get DBAL connection
//Notice: sqlite_connection is in form: name_connection  - where
//name is connection name defined in config file.
$conn = $this->getContainer()->get('doctrine.dbal.sqlite_connection');
$some_array = $conn->fetchAll('SELECT * FROM yourtablename');
//...

And basically, that’s it.
I always didn’t wand dealing much with insert strings in sql when you need to add some dynamically created data inside query:

// ...
$conn->exec(“INSERT INTO  sometable (somecolumn ) VALUES (. $somevalue .));
// …

What I found very interesting with Doctrine2 DBAL is much cleaner way to do this:

            $sql = 'INSERT INTO attributes (
                date_created,
                date_modified
                ) VALUES (?,?)';
            $stmt = $conn_backup->prepare($sql);
            $stmt->bindValue(1, $datecreated);
            $stmt->bindValue(2, $datemodified);
            $stmt->execute();

Of course there is more:

 // ..
$conn->insert('attributes', array('date_created' => $datecreated));
//..

You just have to choose method you like more …


3 comments

  1. Hello!

    Your example is very good and I would know if you been able to use the data entered in the login form and use the same username / password to make the connection to the database.

    I have tried different ways to do this and I could not make it work.

    You would have the kindness of show how we can do this? and if not too much to ask, make a blog post covering this topic?

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.