Buy
Buy

Installing Doctrine

Friends! Welcome back to the third episode in our starting in Symfony 4 series! We've done some really cool stuff already, but it's time to explore deeper in an epic search for intelligent life... and also.. and database! Yea - what good is our cool interstellar space news site... without being able to insert and query for data?

But... actually... Symfony does not have a database layer. Nope, for this challenge, we're going to rely one of Symfony's BFF's: an external library called Doctrine. Doctrine has great integration with Symfony and is crazy powerful. It also has a reputation for being a little bit hard to learn. But, a lot has improved over the last few years.

Code with Me!

If you also want to be best-friends-forever with Doctrine, you should totally code along with me. Download the course code from this page. When you unzip it, you'll find a start/ directory that has the same code that you see here. Open the README.md file for details on how to get the project setup... and of course, a space poem.

The last step will be to open a terminal, move into the project and run:

php bin/console server:run

to start the built in web server. Then, float over to your browser, and open http://localhost:8000 to discover... The Space Bar! Our inter-planetary, and extraterrestrial news site that spreads light on dark matters everywhere.

In the first two episodes, we already added some pretty cool stuff! But, these articles are still just hard-coded. Time to change that.

Installing Doctrine

Because Doctrine is an external library, before we do anything else, we need to install it! Thanks to Symfony flex, this is super easy. Open a new terminal tab and just run:

composer require doctrine

This will download a "pack" that contains a few libraries, including doctrine itself and also a migrations library to help manage database changes on production. More on that soon.

And... done! Hey! That's a nice message. Because we're going to be talking to a database, obviously, we will need to configure our database details somewhere. The message tells us that - no surprise - this is done in the .env file.

Configuring the Database Connection

Move over to your code and open the .env file. Nice! The DoctrineBundle recipe added a new DATABASE_URL environment variable:

22 lines .env.dist
... lines 1 - 15
###> doctrine/doctrine-bundle ###
# Format described at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/configuration.html#connecting-using-a-url
# For an SQLite database, use: "sqlite:///%kernel.project_dir%/var/data.db"
# Configure your db driver and server_version in config/packages/doctrine.yaml
DATABASE_URL=mysql://db_user:db_password@127.0.0.1:3306/db_name
###

Let's set this up: I use a root user with no password locally. Call the database the_spacebar:

22 lines .env.dist
... lines 1 - 15
###> doctrine/doctrine-bundle ###
# Format described at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/configuration.html#connecting-using-a-url
# For an SQLite database, use: "sqlite:///%kernel.project_dir%/var/data.db"
# Configure your db driver and server_version in config/packages/doctrine.yaml
DATABASE_URL=mysql://root:@127.0.0.1:3306/symfony4_space_bar
###

Of course, this sets a DATABASE_URL environment variable. And it is used in a new config/packages/doctrine.yaml file that was installed by the recipe. If you scroll down a bit... you can see the environment variable being used:

parameters:
# Adds a fallback DATABASE_URL if the env var is not set.
# This allows you to run cache:warmup even if your
# environment variables are not available yet.
# You should not need to change this value.
env(DATABASE_URL): ''
doctrine:
dbal:
... lines 10 - 17
# With Symfony 3.3, remove the `resolve:` prefix
url: '%env(resolve:DATABASE_URL)%'
... lines 20 - 31

There are actually a lot of options in here, but you probably won't need to change any of them. These give you nice defaults, like using UTF8 tables:

... lines 1 - 7
doctrine:
dbal:
... lines 10 - 12
charset: utf8mb4
... lines 14 - 31

Or, consistently using underscores for table and column names:

... lines 1 - 7
doctrine:
... lines 9 - 19
orm:
... line 21
naming_strategy: doctrine.orm.naming_strategy.underscore
... lines 23 - 31

If you want to use something other than MySQL, you can easily change that. Oh, and you should set your server_version to the server version of MySQL that you're using on production:

... lines 1 - 7
doctrine:
dbal:
# configure these for your database server
driver: 'pdo_mysql'
server_version: '5.7'
... lines 13 - 31

This helps Doctrine with a few subtle, version-specific changes.

Creating the Database

And... yea! With one composer require command and one line of config, we're setup! Doctrine can even create the database for you. Go back to your terminal and run

php bin/console doctrine:database:create

Say hello to your new database! Well, it's not that interesting: it's completely empty.

So let's add a table, by creating an entity class.

Leave a comment!

  • 2018-10-26 Diego Aguiar

    Thanks for sharing your solution!

  • 2018-10-26 Teolan

    For me is work only if I do this 3 steps:

    1. I add this two lines to my.cnf
    default_authentication_plugin=mysql_native_password
    validate_password.policy=LOW

    2. restart mysql server

    3. Create a new user/password and change it in .env file

    Without the step 3, the root user is keeping the Authentication Type: "caching_sha2_password"
    After create a new user, then this user will use Authentication Type: "mysql_native_password"

    PS
    This all is because of new Authentication Type in MySql 8.0.13 Server
    but will be supported in a future PHP release by default.

  • 2018-10-01 Diego Aguiar

    Hey Quang nguyen

    I can see that you request a password for login in, can you double check that you provided your DB's password? Since you are using Symfony4, your DB connection parameter lives in ".env" file at the root of your project

    Cheers!

  • 2018-09-28 Quang nguyen

    Hi,

    I have some trouble when using "php bin/console doctrine:database:create". My error do not like anyone below. It is "An exception occurred in driver: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)".

    I am not sure what is happening now. I am using "root" user in Mysql server 8.0.12, and I allow it to performs all tasks. Currently, I am running symfony 4 on Homestead with vagrant on VirtualBox. Do you have any ideas about this error?

  • 2018-09-28 Victor Bocharsky

    Hey Thao,

    Great catch! And thanks for sharing it we others!

    Cheers!

  • 2018-09-28 Thao Truong

    Figured it out. I had to run this query:
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

  • 2018-09-28 Thao Truong

    I use mysql 8.0 and I keep getting this error:
    "php bin/console doctrine:database:create
    2018-09-28T01:27:27+00:00 [error] Error thrown while running command "doctrine:database:create". Message: "An exception occurred in driver: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client""

  • 2018-09-06 shing

    This is brilliant. Full respect to the symfony team for being so patient.

    You made alot of great points which sent me down the road of looking at bundle vs bundleless and ways people organise code. Its not a trivial subject. It is alot to think about and have something that can make sense to me and others but also not overcomplicate and overwhelm.

    But i think overall, maybe there is a middle ground, where things r organised in transportable domains.

    👍Thank u and keep up the good stuff!

  • 2018-09-06 weaverryan

    Hey Mylan!

    Oh no! That's not a fun error to have! Here's what's going on:

    1) In your .env file, that DATABASE_URL config you posted is your connection parameters to your database. The current value - which is the config I use for my computer - says that you have a database running on your local machine (ip address 127.0.0.1) and you connect to it with the user "root" and no password.

    2) The error -"Connection refused" is usually because you do not have a MySQL server running locally. Have you installed MySQL? If you did, make sure it's running :).

    Cheers!

  • 2018-09-06 Mylan

    Hi,

    I've a error that say's " An exception occurred in driver: SQLSTATE[HY000] [2002] Connection refused " after i run the comment ./bin/console doctrine:database:create . And in PHPstorm if have the comment "DATABASE_URL=mysql://root:@127.0.0.1/the_spacebar
    " to connect the DB.

    Please help me out

  • 2018-09-06 Victor Bocharsky

    Hey Shing,

    Yeah, probably no glob patters accepted in this part of config, so you would need to define all the mappings manually. Well, if you can see a pattern of where your entities live - you probably can create a compiler pass and write a custom PHP code that will iterate over all the folders in src/ and look for Entity/ folder. And if it found - add it as mapping. I *think* it should work, but it sounds like a hacky way to dynamically do what you want. See https://symfony.com/doc/cur... for more info.

    Another question, why do you need to separate your entities? It's not a good practice because most probably your entities are tied with each other, i.e. have relations. For example, are you sure that all your "post" entities do not relate to "profile" entities and "comment" entities? Probably no. I bet your Post entity has OneToMany relation to Comment entity, etc. so they have hard relations. That's why the best practice is to have one folder for all your entities in simple projects, just do not overcomplicate things. But of course, if those entities in different folders are not related to each other - it makes sense then, but it's really a rare case for big and complex applications.

    Cheers!

  • 2018-09-05 shing

    Hi Diego,

    Cool. Should have tested it. I see now, if the mappings pointing in the right way getDoctrine()->getRepository() doesnt work.

    So if i have multiple folders like
    src/UserProfile/Entity ,
    src/Post/Entity
    src/Comment/Entity

    What is a good way of defining all the mappings? It does not seem to accept glob patterns

    Thanks so much

  • 2018-09-04 Diego Aguiar

    He @shing

    You are correct, if you are not using the default folder for your entities, then you will have to specify where they live so Doctrine can find them and do its job :)

    Cheers!

  • 2018-09-04 shing

    Hi,

    I'm wondering what this config means.


    # config/packages/doctrine.yaml
    doctrine:
    orm:
    mappings:

    under https://symfony.com/doc/mas... it says
    'Explicit definition of all the mapped entities is the only necessary configuration for the ORM and there are several configuration options that you can control. The following configuration options exist for a mapping:'

    I know that this mapping affects bin/console make:entities --regenerate
    And also if u have multiple entity_managers.
    Does it have an effect if u have entities outside of dir: '%kernel.project_dir%/src/Entity'
    Do i have to map all folders that contains entity?

    thanks in advance

  • 2018-06-12 Diego Aguiar

    Hey Leora Wenger

    Using another port for your DB connection is not considered an error, it's just a detail of the server, but now that you know how to solve this problem, you shouldn't be too afraid of configuring a DB in another computer :)

    Have a nice day.

  • 2018-06-11 Leora Wenger

    Diego, yes, at least I am speeding along with the Doctrine series now! But I think the original database connection issue is related to MAMP. I am afraid that even if I uninstalled it, there might something remaining in my system that might make the problem worse. So for now, I will run it whenever I do database connections on this mac (the password for the mysql on the command line and the mysql in MAMP are different - not changing anything now - the MAMP one is the one working in the .env file).

  • 2018-06-11 Diego Aguiar

    Hmm, interesting, so it uses a different port. Sorry I can't offer you a tutorial about it, I'm not a Mac user :p
    but, look's like you already nailed it.
    By the way, I believe you can set up any user/password you like now

    Cheers!

  • 2018-06-11 Leora Wenger

    Finally figured something out - if I run MAMP, and I use the password that MAMP suggests, and I set the port to 8889 (because that showed up for mysqld when I ran 'sudo lsof -i -n -P | grep TCP' ). And for the first time it said created the database 'the_spacebar' - can someone offer a course on how to configure all these different ports?

  • 2018-06-11 Leora Wenger

    I got rid of the password all together for now, but that did not seem to help. Just makes it easier to login at the command line.

  • 2018-06-11 Leora Wenger

    It *is* password. Don't worry, I don't use that in real life cases. Just for learning.

    "If that's not the case, is there a chance that your mysql is listening to other port than default (3306)?" - how do I test this? I can get MAMP to work. I also tried a PDO Sqlite tutorial, and that one worked as well. What else can I try?

  • 2018-06-11 Diego Aguiar

    Hey Leora Wenger

    You need to change the "password" part for your real password (unless the word password is actually your password). If that's not the case, is there a chance that your mysql is listening to other port than default (3306)?

  • 2018-06-11 Leora Wenger

    Hi, Victor. So now I can get into mysql on the command line using:
    mysql -u root -p
    But this still gives the same error (this is what is in the .env file)
    DATABASE_URL=mysql://root:password@127.0.0.1:3306/the_spacebar

  • 2018-06-11 Victor Bocharsky

    Hey Leora,

    Hm, interesting.. do you have "resolve" filter in url: "'%env(resolve:DATABASE_URL)%'"? I suppose you have in your .env the next, right?

    DATABASE_URL=mysql://root:@127.0.0.1:3306/symfony4_space_bar

    Btw, could you show the command how you connect to the database in your command line? Did you enter any password?

    Cheers!

  • 2018-06-08 Leora Wenger

    I am trying to connect to the database, but I get the same error each time I try: An exception occurred in driver: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client
    I was able to log into mysql with my password on the command line, but it won't let me connect with the code in the tutorial.

  • 2018-06-07 Diego Aguiar

    Hmm, interesting. Double check that "mysql.dll" exists inside the "ext" folder.
    Do you have this problem only for that dependency? Maybe you don't have MySql installed

  • 2018-06-07 Daniel

    Hi, Diego! Thanks for your answer. In fact I'm using windows.

    I've done some progress: I've installed wamp server and added inside php.ini:
    extension=mysql
    extension=mysqli

    However I get this message on running bin/console server:run:

    PHP Warning: PHP Startup: Unable to load dynamic library 'mysql' (tried: ext\mysql (The specified module could not be found.), ext\php_mysql.dll

  • 2018-06-06 Diego Aguiar

    Hey Daniel

    It depends on your OS, if you use Linux like me, you can take advantage of the "apt-get" package for installing your libraries.
    Check this post: https://www.digitalocean.co...
    I haven't followed it, but I believe it can help you in getting started.

    Cheers!

  • 2018-06-06 Daniel

    Hi, I don't have the driver for MySQL. I'm new to databases. Which is your preferred way of installing drivers?