Symfony3 Blog Demo Remix (Part 2): Updating to MySQL

Pre Requisites

Continuing from where we left off on the last post, we are going to be downloading and installing the Symfony Blog Demo that is based on the newest version of Symfony at the time of this writting, 3.2. I won't get too much into detail about installing and configuring the Symfony framework because there are extensive tutorials on how to get up and running — I'm not re-inventing the wheel. For starters, check out the documentation on the Symfony website and use Google if you have issues. You can find the docs here. The rest of the tutorial assumes that you have a fresh working copy of the Symfony Blog Demo application using Symfony 3.

Now then, a little background...

The demo comes stock with a number of Doctrine entities that make life easier for us developers especially when we are writing queries and testing code that requires these entities. The entities that come with the demo are Comment, Post, and User (under src/AppBundle/Entity).

Converting to MySQL

The demo blog comes stock with a SQLite implementation. While this does fine for learning the basics, in a real world application the SQLite file would become large, unmanageable, and a performance issue as well. We most certainly want to switch our app over to another database implementation. The good news is that this is not a big deal thanks to Symfony's database configuration. The app/config/parameters.yml file is used to define sensitive data about your application such as the type of database used, the username, and the password to connect to it.

PLEASE NOTE: This tutorial will not go through how to set up an empty MySQL database on your localhost nor will it teach you how to install MySQL to begin with. There are plenty of tutorials out there to do this, use Google!

So go ahead and create a blank database called symfony_demo. Then in your app/config/parameters.yml file, copy and paste this code:

app/config/parameters.yml

# This file is auto-generated during the composer install
parameters:
    database_url: 'mysql://root@127.0.0.1:3306/symfony_demo'
    mailer_transport: smtp
    mailer_host: 127.0.0.1
    mailer_user: null
    mailer_password: null
    locale: en
    secret: secret_value_for_symfony_demo_application

Now that the database params are in the system, we have to configure Symfony to actually use MySQL as the database instead of SQLite. Open the app/config/config.yml file and locate the entry under doctrine:dbal node and update these keys:

driver: "pdo_mysql"
dbname: "symfony_demo"

Comment out the line that defines the doctrine:dbal:path setting as we no longer need to define a path because MySQL doesn't use a file to store data.

When you are done, you should have something that looks similar to this in your config.yml file:

doctrine:
    dbal:
        # instead of configuring the database access options in this file, we pull
        # them from the app/config/parameters.yml file. The reason is that config.yml
        # stores options that change the application behavior and parameters.yml
        # stores options that change from one server to another
        driver: "pdo_mysql"
        dbname: "symfony_demo"
        # temp workaround for https://github.com/doctrine/dbal/issues/1106: define DB path here
        #path: "%kernel.root_dir%/data/blog.sqlite"

    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true

After you've edited the config files to properly configure the new MySQL database (make sure the database itself is created as well), you need to clear the cache by running:

php bin/console ca:cl

At this point, everything should be ready for you to create the default database schema that will be used with your application. To do so, run this command:

php bin/console doctrine:schema:create 

NOTE: If this command throws an error, try deleting the var/cache directory and run it again.

At this point, go ahead and delete the old database folder that was used for SQLite. It is located here: app/data. Now you can run the command to start the server:

  php bin/console server:run

This will start the PHP server on localhost:8000. Open a browser and make sure everything looks good. You should notice one thing missing from the default installation... the sample data! We deleted the old database mechanism and all the data that went along with it. To recreate the sample data is as simple as running:

  php bin/console doctrine:fixtures:load

There! Now the sample data is loaded again. Verify this by refreshing your browser page.

To conclude...

In this tutorial, we have modified our stock Symfony3 demo blog to make use of a much more reliable and flexible database system than SQLite — MySQL. Now that is done, we can start focusing on creating our first real extension to the application, the post Category. This will be the topic for my next blog post so please check it out!

Complete series:

• Symfony 3: Remixing the Blog Demo (Part 1)
• (Part 3): Coming soon...

This tutorial is originally posted by the author on his blog. This version has been edited for clarity.