Buy

Database Config and Automatic Table Creation

We described the genus table to Doctrine via annotations, but this table doesn't exist yet. No worries - Doctrine can create it for us!

And actually, we don't even have a database yet. Doctrine can also handle this. Head to the terminal use the console to run:

./bin/console doctrine:database:create

But wait! Can Doctrine do this yet? We haven't told it anything about the database: not the name we want, the user or the password.

Configuring the Database

Where do we do that? The same place that everything, meaning all services are configured: app/config/config.yml. Scroll down to the doctrine key:

72 lines app/config/config.yml
... lines 1 - 43
# Doctrine Configuration
doctrine:
dbal:
driver: pdo_mysql
host: "%database_host%"
port: "%database_port%"
dbname: "%database_name%"
user: "%database_user%"
password: "%database_password%"
charset: UTF8
# if using pdo_sqlite as your database driver:
# 1. add the path in parameters.yml
# e.g. database_path: "%kernel.root_dir%/data/data.db3"
# 2. Uncomment database_path in parameters.yml.dist
# 3. Uncomment next line:
# path: "%database_path%"
... lines 60 - 72

Ah, this is what tells Doctrine all about your database connection.

But, the information is not hardcoded here - these are references to parameters that are defined in parameters.yml:

15 lines app/config/parameters.yml.dist
... lines 1 - 3
parameters:
database_host: 127.0.0.1
database_port: ~
database_name: symfony
database_user: root
database_password: ~
# You should uncomment this if you want use pdo_sqlite
# database_path: "%kernel.root_dir%/data.db3"
... lines 12 - 15

Update the database_name to aqua_note and on my super-secure local machine, the database user is root with no password.

Go Deeper!

Find out more about these parameters in our Symfony Fundamentals Series.

Back to the terminal! Now hit enter on the command:

./bin/console doctrine:database:create

Database created. To create the table, run:

./bin/console doctrine:schema:update --dump-sql

This looks great - CREATE TABLE genus with the two columns. But this didn't execute the query yet - the --dump-sql option is used to preview the query if you're curious. Replace it with --force.

./bin/console doctrine:schema:update --force

So hey guys, this is really cool - we can be totally lazy and let Doctrine do all the heavy database-lifting for us. This doctrine:schema:update command is actually more powerful than it looks - it's going to "wow" us in a few minutes.

But first, let's learn how to insert data into the new table.

Leave a comment!

  • 2016-12-07 weaverryan

    Hi again Boran!

    Hmm, let's try clearing out your database entirely and starting from scratch:

    bin/console doctrine:database:drop --force
    bin/console doctrine:database:create
    bin/console doctrine:migrations:migrate

    Let me know if that works! This error can happen if somehow the genus table (or any other tables) were added to your database via some method *other* than running your migrations, e.g. by running doctrine:schema:update. When this happens, it looks like you have *not* run any of your migrations (because you haven't), so it tries to run all of your migrations, but has an error when the genus table already exists. The trick is to keep a good workflow where you never run doctrine:schema:update and always use doctrine:migrations:migrate to keep your database structure updated.

    Cheers!

  • 2016-12-06 Boran Alsaleh

    hi weaverry ,

    could you please help me in this issue , when i try to make doctrine:migration:migrate
    i get the below error :

    Error An exception occurred while executing 'CREATE TABLE genus (id INT AUTO_INCREMENT NOT NULL, sub_family_id INT NOT NULL, name VARCHAR(255) NOT NULL, species_count INT NOT NULL, fun_fact VARCHAR(255) DEFAULT NULL, is_published TINYINT(1) NOT NULL, first_discovered_at DATE NOT NULL, INDEX IDX_38C5106ED15310D4 (sub_family_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB':

    SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'genus' already exists

    thanks ,

  • 2016-11-16 weaverryan

    Yo Boran!

    If that doesn't work, it could also be a "mysql.sock" problem, which is where your php.ini setting of "pdo_mysql.default_socket" points to a socket location (e.g. /tmp/mysql.sock), but your MySQL server creates the socket file somewhere else. This is kind of annoying, because different installations of MySQL put the mysql.sock file in different places, so you need to do some digging to find it, and then update your php.ini file (you can find its location by running php --ini from the command line and look for "Loaded Configuration File").

    Cheers!

  • 2016-11-16 Victor Bocharsky

    Hey Boran,

    Try to change your `database_host` in parameters.yml to the "127.0.0.1" instead of "localhost" which is by default, it should help. Let me know if it still doesn't work.

    Cheers!

  • 2016-11-15 Boran Alsaleh

    hey guys ,,

    I am getting this error every time I run $ ./bin/console doctrine:database:create

    ./bin/console doctrine:database:create

    [Doctrine\DBAL\Exception\ConnectionException]
    An exception occured in driver: SQLSTATE[HY000] [2002] No such file or dire
    ctory

    [Doctrine\DBAL\Driver\PDOException]
    SQLSTATE[HY000] [2002] No such file or directory

    [PDOException]
    SQLSTATE[HY000] [2002] No such file or directory

    doctrine:database:create [--shard SHARD] [--connection [CONNECTION]] [--if-not-exists] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

  • 2016-10-28 Victor Bocharsky

    Hey fmm8 ,

    There're could be a few reasons, but the all of them are related to invalid DB credentials. Please, double check your MySQL credentials like port, login, password, etc. Is your MySQL server running? Have you tried successfully to connect to the MySQL database in other project? Or probably connection to a DB with PhpMyAdmin, MySQL Workbench or similar utility? Which credentials do you use there?

    Cheers!

  • 2016-10-27 fmm8

    btw the yml files are properly indented.

  • 2016-10-27 fmm8

    Hi there,

    I am getting this error every time I run $ ./bin/console doctrine:database:create

    [Doctrine\DBAL\Exception\ConnectionException]
    An exception occured in driver: SQLSTATE[HY000] [2002] Connection refused

    [Doctrine\DBAL\Driver\PDOException]
    SQLSTATE[HY000] [2002] Connection refused

    [PDOException]
    SQLSTATE[HY000] [2002] Connection refused

    doctrine:database:create [--shard SHARD] [--connection [CONNECTION]] [--if-not-exists] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

    However, my configurations are as follow below;

    config.yml

    # Doctrine Configuration
    doctrine:
    dbal:
    default_connection: default
    connections:
    default:
    driver: pdo_mysql
    dbname: "%database_name%"
    user: "%database_user%"
    host: "%database_host%"
    password: "%database_password%"
    unix_socket: /tmp/mysql.sock
    charset: UTF8

    parameters.yml

    parameters:
    database_host: 127.0.0.1
    database_port: null
    database_name: aqua_note
    database_user: root
    database_password: null
    secret: 0b719bbdb89bd1ce9774b1069c9696d8ce3f1b08

    mailer_transport: smtp
    mailer_host: 127.0.0.1
    mailer_user: ~
    mailer_password: ~

    Can someone please help? I will really appreciate it.

    Thanks!

  • 2016-08-25 weaverryan

    Hey Kaiser!

    Ok, let's get you fixed up :). My guess is that you had one error, and may have caused other little errors while debugging the first. If we straighten everything out, we should be good! So let's look at each thing, one-by-one.

    First, about the database password. Obviously, it turns out that you don't have a password, which is why null worked. But, I agree that it's a little weird that when you *did* add a password, it still said password NO, instead of password YES. But honestly, I would just ignore that if it's working, because I'm guessing there was some small misconfiguration causing this confusion. I just tried locally - if I set my password to something, I get an access denied password YES.

    Second, about the database name. This seems similar to the database password! It seems that we're having some configuration confusion somewhere, but I'm not sure where. As you already know (based on your comment), when you set database_name in parameters.yml, this only works because that parameter is referenced in config.yml, under the doctrine.dbal.dbname key. So, if you are setting that key directly - which I know you tried - then there's other magic happening: this *should* set the database name. The fact that you're having this problem and the password problem makes me wonder if there isn't - somehow - some other database configuration somewhere else. Look in all of the files in app/config for a "doctrine" root key. In short: your configuration is not behaving as it should. And although you should not need to ever do this, you should try clearing your cache (bin/console cache:clear) just in case. Also, are you using a VM? I'm trying to think if it's possible that you're editing the files, but they're not actually updating on your VM (or something). It just doesn't make sense!

    Finally, about the "No Metadata Classes to Process". Assuming you have normal, default doctrine configuration (which, we're not sure is true at this point!) then this only happens if your entity class has a bug in it. Things to check:

    A) That your class matches your filename (so, Post.php)
    B) That the file lives in the Entity directory and the namespace is correct (your namespace IS correct)
    C) That there is an <?php tag on top (this happens more often than you would think!)
    D) That your annotations have /** (with two stars) - you're good here too!

    But also, remove the "mappings" config key you added. I'm guessing you did this while trying to debug, but it's not necessary: the auto_mapping: true above tells Doctrine to look in the Entity/ directory of all of your bundles :).

    Phew! Ok, let me know what you find out!

  • 2016-08-23 Kaiser

    Hi.

    I've been struggling with this part for a few days now and I've reached a point where i simply cant figure it out on my own.
    I changed the settings for my local connection in parameter.yml, but i kept getting an error saying access denied to root@localhost password: no. No matter what I put in parameter.yml this was my response.
    I changed my root password to null and boom. It worked. i could run doctrine:database:create however... It creates a DB called 'symfony' even though I changed the db_name first in parameter.yml and then hardcoded in config.yml.

    When i then try to run doctrine:schema:update --dump-sql it results in an error saying "No Metadata Classes to process."

    My EntityClass looks like this:


    namespace AppBundle\Entity;


    use Doctrine\ORM\Mapping as ORM;

    /**
    * @ORM\Entity
    * @ORM\Table(name="blog_posts")
    */
    class Post
    {
    /**
    * @ORM\Id
    * @ORM\GeneratedValue(strategy="AUTO")
    * @ORM\Column(type="integer")
    */
    private $id;

    /**
    * @ORM\Column(type="string", length=100)
    */
    private $title;

    /**
    * @ORM\Column(type="text")
    */
    private $description;

    /**
    * @ORM\Column(type="text")
    */
    private $content;

    /**
    * @ORM\Column(type="date")
    */
    private $date;

    }

    And I've trying to change the config.yml to contain this under orm:


    orm:
    auto_generate_proxy_classes: "%kernel.debug%"
    naming_strategy: doctrine.orm.naming_strategy.underscore
    auto_mapping: true
    mappings:
    AppBundle:
    type: annotation
    is_bundle: false
    dir: %kernel.root_dir%/../src/AppBundle/Entity/
    prefix: AppBundle\Entity
    alias: AppBundle

    The directory structure is as shown throughout these great tutorials :)
    I hope you can help me resolve this, or clearify the error for me. Which seems to me like the changes made in/for config.yml ain't being saved for some reason.?

  • 2016-08-11 weaverryan

    Hi Claire!

    Welcome to Symfony! :) You did a great job creating the database in sqlite. In fact, in the future, you might even be able to do a little less work - you shouldn't need to create the aqua_note_database file - Doctrine should even create that when you run doctrine:database:create :).

    But anyways, one tricky thing with sqlite is that it doesn't have a fancy "mysql" command line tool. There a few GUI tools for visualizing an SQLite database (just google for them - I don't have a favorite, and they're dependent on your OS). OR, there is a better idea :). Symfony has an bin/console command that will make a raw SQL query for you. Try this:


    bin/console doctrine:query:sql 'DESCRIBE genus'

    It's not quite as convenient as the "mysql" utility, since you need to prefix everything with the "bin/console doctrine:query:sql", but it works awesome!

    Let me know if it helps!

  • 2016-08-11 claire

    Hi,
    I'm really new to Symfony and have been really enjoying these tutorials. I have come across a problem and was wondering if you can point me in the right direction. I am using Sqlite and had to do do a few things differently to create a table. Under app, resources, I created a database directory and within that a 'aqua_note_database' as a datasource. Ran the ./bin/console doctrine:database:create and yay it created a database. But when you did 'mysql -u root ...', i tried to compensate with 'sqlite -u root...' and it is not recognizing the command.

    my question is, having to use sqlite, is the way I created a database ok? and what is the command for '-u root..'?

    Thank you :)

  • 2016-08-06 weaverryan

    Great tip - I use it too and love it!

  • 2016-08-05 3amprogrammer

    I find it very usefull to use https://github.com/bamarni/sym.... This way you dont have to look at some reference all the time when dealing with ./bin/console :)

  • 2016-07-08 weaverryan

    Oh man, lame! I didn't realize my message got cut off! I just updated it above - my <php killed the message at first :).

    Glad you got it working - and good working using PhpStorm :)

  • 2016-07-08 McLimey

    Don't worry. What ever I did fixed it. I think it helps that I'm using PHPstorm now, too =p

  • 2016-07-08 McLimey

    Ah, man! A cliff hanger!! Any chance I can get the rest of your thoughts on this? :p Glad to see I was on the right path, anyway ^_^

  • 2016-07-07 weaverryan

    Yo!

    Yep, you're thinking about it correctly. I'm guessing you get this error if you run the doctrine:schema:update command, or generally try to do anything related to Doctrine? Here's how it works:

    1) When the system loads, Doctrine needs to know about *all* of the entities in the system.
    2) By default, it looks in the Entity directory of all of your bundles and looks inside any classes there for the annotations

    So, the error you're getting is basically Doctrine saying "Hi! I don't see any annotated classes in your Entity directory".

    So, a few things to check for:

    1) Make sure you have the <?php tag in your Entity class. This sounds silly - but it's actually the most common thing to forget :)

    2) Make sure your directory is called Entity exactly

    3) Though I would have expected a different error, you do have a typo in your namespace - you have Entiry - change that to Entity :).

    Let me know how it goes!

  • 2016-07-06 dev_lime

    I'm guessing it can't spot my variables... but I have no idea how to resolve this >.>

  • 2016-07-06 dev_lime

    namespace AppBundle\Entiry;

    use Doctrine\ORM\Mapping as ORM;

    /**
    * @ORM\Entity
    * @ORM\Table(name="posts")
    */
    class Posts {

    /**
    * @ORM\Id
    * @ORM\GeneratedValue(stratergy="AUTO")
    * @ORM\Column(type="interger")
    */
    private $id;

    /**
    * @ORM\Column(type="string")
    */
    private $name;

  • 2016-07-06 dev_lime
  • 2016-07-06 dev_lime

    I've wandered off the beaten path a little and managed to get...not an error as such (Techincally) But I am getting this "No Metadata Classes to process."... any ideas? This is my PHP

  • 2016-05-26 Marcolic

    ok. I found what was my problem. The thing is I'm using cmder and I thought it was able to read path variables, which is not true. I just add to create aliases. Anayway thanks for your answer Ryan.

  • 2016-05-22 weaverryan

    Hi there!

    I'm not *too* good with Windows, unfortunately :). But, I can tell you that you should be able to execute the mysql command using the *long* version, even if your path isn't setup correclty:

    C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql

    Also, you can cheat! Symfony has a console command that allows you to run queries through it:

    php bin/console doctrine:query:sql "DESCRIBE genus"

    I hope that helps!

  • 2016-05-21 Marcolic

    Hello everyone. I used to use wamp for development, everything I know is self learning and I only dev for my pleasure.

    I'm using the server that symfony provides. I installed MySql, the database have been created and it seem to be working. The only thing is that I can't use 'mysql' as a command line so I tried to add that path:"C:\Program Files\MySQL\MySQL Server 5.7\bin" to my environnement variables but it still doesn't work. Any idea ? Btw I'm on Windows 8.1.

  • 2016-05-04 weaverryan

    Hi Yoni!

    I don't see any problem with this. In fact, it's important to make sure that your tables use utf8 in the beginning (changing them later is a pain!). I usually create my database with a utf8 charset, and then let my tables inherit that from the database. However, if you do what you have here, then you will definitely not have any problems.

    But actually, you can also set this globally. See the "Setting up the Database to be UTF8" title under this section: http://symfony.com/doc/current...

    That's the way to go :)

    Cheers!

  • 2016-05-04 Yoni L.

    Hi thank you for this, is there any problem to use utf8mb4 charset, in annotation i found that you can add collation info in table annotation like this:
    @ORM\Table(name="genus", options={"collate"="utf8mb4_general_ci", "charset"="utf8mb4"})
    is it a good practice or some kind of black magic?

  • 2016-03-15 weaverryan

    Hey Marco!

    Ah, this is a good step forward! The database is now running, and you're using the user "root", but the password is incorrect. The correct password depends on how you installed mysql - a common option is either a blank password (that's the default in parameters.yml) *or* "root" also for the password. Check the documentation for how you installed it and then update parameters.yml and try again.

    You're close!

  • 2016-03-15 Marco La Cugurra

    Hello,
    I guess I installed mySQL now I'm getting a different error

    [Doctrine\DBAL\Exception\ConnectionException]

    An exception occured in driver: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

    some help anyone please :)
    thank you

  • 2016-03-14 weaverryan

    Hey Marco!

    Hmm, connection refused us when there is no database running on the server. Do you have MySQL installed? Alternatively, you could use Sqlite, which doesn't require to have a database running. There are instructions in the config.yml file for how to use Sqlite, but here is a gist if it helps: https://gist.github.com/weaver.... But, installing/starting MySQL is also a good idea :).

    Cheers!

  • 2016-03-14 Marco La Cugurra

    Hello as I launch the ./bin/console doctrine:database:create I get this error :"[Doctrine\DBAL\Exception\ConnectionException]

    An exception occured in driver: SQLSTATE[HY000] [2002] Connection refused

    [Doctrine\DBAL\Driver\PDOException]

    SQLSTATE[HY000] [2002] Connection refused

    [PDOException]

    SQLSTATE[HY000] [2002] Connection refused

    "
    Can someone help me out with that? I guess there are probably some problems with my parameters. I'm not using MAMP by the way.
    this are my parameters:

    parameters:
    database_host: 127.0.0.1
    database_port: ~
    database_name: aqua_note
    database_user: root
    database_password: null
    mailer_transport: smtp
    mailer_host: 127.0.0.1
    mailer_user: null
    mailer_password: null
    secret: 03b3f955112e8fd6629f3379f556cd72c9008251