Database Tricks on SymfonyCloud

Video not working?

It looks like your browser may not support the H264 codec. If you're using Linux, try a different browser or try installing the gstreamer0.10-ffmpeg gstreamer0.10-plugins-good packages.

Thanks! This saves us from needing to use Flash or encode videos in multiple formats. And that let's us get back to making more videos :). But as always, please feel free to message us.

We just deployed to SymfonyCloud!!! Well, I mean, we did... but it doesn't... ya know... work yet. Because this is the production 500 error, we can't see the real problem.

No worries! Head back to your terminal. The symfony command has an easy way to check the production logs. It is...

symfony logs

This prints a list of all the logs. The app/ directory is where our application is deployed to - so the first item is our project's var/log/prod.log file. You can also check out the raw access log... or everything. Hit 0 to "tail" the prod.log file. And... there it is:

An exception has occurred... Connection refused.

Adding a Database to SymfonyCloud

I recognize this: it's a database error.... which... hmm... makes sense: we haven't told SymfonyCloud that we need a database! Let's go do that!

Google for "SymfonyCloud MySQL" to find... oh! A page that talks about exactly that. Ok, we need to add a little bit of config to 2 files. The first is .symfony/services.yaml. This is where you tell SymfonyCloud about all the "services" you need - like a database service, ElasticSearch, Redis, RabbitMQ, etc.

Copy the config for .symfony/services.yaml... then open that file and paste:

mydatabase:
# mariadb
type: mysql:10.2
disk: 1024

The database is actually MariaDB, which is why the version here is 10.2: MariaDB version 10.2.

Notice that we've used the key mydatabase. That can be anything you want: we'll reference this string from the other config file that we need to change: .symfony.cloud.yaml.

Inside that file, we need a relationships key: this is what binds the web container to that database service. Let's see... we don't have a relationships key yet, so let's add it: relationships and, below, add our first relationship with a special string: database set to mydatabase:mysql:

... lines 1 - 24
relationships:
database: "mydatabase:mysql"
... lines 27 - 42

This syntax... is a little funny. The mydatabase part is referring to whatever key we used in services.yaml - and then we say :mysql... because that service is a mysql type.

The really important thing is that we called this relationship database. Thanks to that SymfonyCloud will expose an environment variable called DATABASE_URL which contains the full MySQL connection string: username, host, database name and all:

29 lines .env
... lines 1 - 26
DATABASE_URL=mysql://root:@127.0.0.1:3306/blackfire
... lines 28 - 29

It's literally DATABASE_URL and not PIZZA_URL because we called the relationship database instead of pizza... which would have been less descriptive, but more delicious.

This is important because DATABASE_URL happens to be the environment variable that our app will use to connect to the database. In other words, our app will instantly have database config.

Back at the terminal, hit Ctrl+C to exit from logging. Let's add the two changes and commit them:

git add .
git commit -m "adding SfCloud database"

Now, deploy!

symfony deploy

Oh, duh - run with the --bypass-checks flag:

symfony deploy --bypass-checks

The deploy will still take some time - it has a lot of work to do - but it'll be faster than before. When it finishes... it dumps the same URL - that won't change. But to be even lazier than last time, let's tell the command to open this URL in my browser... for me:

symfony open:remote

Tunneling to the Database

And... we have a deployed site! Woo! The database is empty... but if this were a real app, it would start to be populated by real users entering their real Bigfoot sightings... cause Bigfoot is... totally real.

But... to make this a bit more interesting for us, let's load the fixture data one time on production.

This is a bit tricky because the fixture system - which comes from DoctrineFixturesBundle - is a Composer "dev" dependency... which means that it's not even installed on production. That's good for performance. If it were installed, we could run:

symfony ssh

To SSH into our container, and then execute the command to load the fixtures. But... that won't work.

No problem! We can do something cooler. Exit out of SSH, and run:

symfony tunnel:open

I love this feature. Normally, the remote database isn't accessible by anything other than our container: you can't connect to it from anywhere else on the Internet. It's totally firewalled. But suddenly, we can connect to the production database locally on port 30000. We can use that to run the fixtures command locally - but send the data up to that database. Do it by running:

DATABASE_URL=mysql://root:@127.0.0.1:30000/main php bin/console doctrine:fixtures:load

Ok, let's break this down. First, there is actually a much easier way to do all of this... but I'll save that for some future SymfonyCloud tutorial. Basically, we're running the doctrine:fixtures:load command but sending it a different DATABASE_URL: one that points at our production database. When you open a tunnel, you can access the database with root user, no password - and the database is called main.

The only problem is that this command... takes forever to run. I'm not sure exactly why - but it is doing all of this over a network. Go grab some coffee and come back in a few minutes.

When it finishes... yes! Go refresh the page! Ha! We have a production site with at least enough data to make profiling interesting.

Next, let's do that! Let's configure Blackfire on production! That's easy right? Just repeat the Blackfire install process on a different server... right? Yep! Wait, no! Yes! Bah! To explain, we need to talk about a wonderful concept in Blackfire called "environments".

Leave a comment!

This tutorial can be used to learn how to profile any app - including Symfony 5.

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "blackfire/php-sdk": "^1.20", // v1.20.0
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "sensio/framework-extra-bundle": "^5.4", // v5.5.1
        "symfony/console": "4.3.*", // v4.3.10
        "symfony/dotenv": "4.3.*", // v4.3.10
        "symfony/flex": "^1.9", // v1.9.10
        "symfony/form": "4.3.*", // v4.3.10
        "symfony/framework-bundle": "4.3.*", // v4.3.9
        "symfony/http-client": "4.3.*", // v4.3.10
        "symfony/orm-pack": "^1.0", // v1.0.7
        "symfony/security-bundle": "4.3.*", // v4.3.10
        "symfony/serializer-pack": "^1.0", // v1.0.2
        "symfony/twig-bundle": "4.3.*", // v4.3.10
        "symfony/validator": "4.3.*", // v4.3.10
        "symfony/webpack-encore-bundle": "^1.6", // v1.7.2
        "symfony/yaml": "4.3.*", // v4.3.10
        "twig/extensions": "^1.5" // v1.5.4
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.2", // 3.2.2
        "fzaninotto/faker": "^1.8", // v1.8.0
        "symfony/debug-pack": "^1.0", // v1.0.7
        "symfony/maker-bundle": "^1.13", // v1.14.3
        "symfony/test-pack": "^1.0" // v1.0.6
    }
}