Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Persisting to the Database

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

We have a beautiful entity class and, thanks to the migrations that we just executed, we have a corresponding question table in the database. Time to insert some data!

Think "Classes", not "Tables"

One of the key philosophies of Doctrine is that it doesn't want you to think about tables and columns. Doctrine wants you to think about classes and properties... and then leave all the details of saving and querying to a database table up to it to worry about.

So instead of asking:

How can I insert a new row in the question table?

We need to think:

Let's create a Question object, populate it with data and then ask Doctrine to save it.

Creating a Question Endpoint

To play with all of this, let's add a new, sort of, fake page - /questions/new. When we go there, I want a new question to be added to the database.

Open up src/Controller/QuestionController.php, which already holds the homepage and show page. At the bottom, add public function and... let's call it new(). Above, say @Route() with /questions/new.

... lines 1 - 10
class QuestionController extends AbstractController
{
... lines 13 - 55
/**
* @Route("/questions/new")
*/
public function new()
{
... line 61
}
}

To keep things simple, return a new Response() - the one from HttpFoundation - with Time for some Doctrine magic!

... lines 1 - 7
use Symfony\Component\HttpFoundation\Response;
... lines 9 - 10
class QuestionController extends AbstractController
{
... lines 13 - 55
/**
* @Route("/questions/new")
*/
public function new()
{
return new Response('Time for some Doctrine magic!');
}
}

There's no Doctrine logic yet, but this should work. At the browser, hit enter and... woh! It doesn't work! There's no error, but this is not the page we expected. It looks like the question show page. And, in fact, if you look down on the web debug toolbar... yea! The route is app_question_show!

The problem is that the url /questions/new does match this route! It look like "new" is the slug. Routes match from top to bottom and Symfony stops as soon as it finds the first matching route. So the easiest fix is to just move the more specific route above this one.

... lines 1 - 10
class QuestionController extends AbstractController
{
... lines 13 - 25
public function homepage()
{
... line 28
}
... line 30
/**
* @Route("/questions/new")
*/
public function new()
{
return new Response('Time for some Doctrine magic!');
}
/**
* @Route("/questions/{slug}", name="app_question_show")
*/
public function show($slug, MarkdownHelper $markdownHelper)
{
... lines 44 - 61
}
}

This doesn't happen too often, but this is how I handle it.

Now when we go refresh... got it!

Creating the Question Object

Ok: time to work! Eventually - in a future tutorial - this page will render a form where the user can fill out all the information about their question. When they submit, we will save that question to the database.

But we're not going to talk about Symfony forms yet. Instead, let's "fake it" inside the controller. Let's create a Question object, set some hardcoded data on it and ask Doctrine to save it.

And because there is nothing special about our entity class, instantiating it looks exactly like you would expect: $question = new Question() and I'll auto-complete this so that PhpStorm adds the Question use statement.

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
... lines 38 - 59
}
... lines 61 - 85
}

Next, call $question->setName('Missing pants') - an unfortunate magical side effect of an incorrect spell.

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
$question->setName('Missing pants')
... lines 39 - 59
}
... lines 61 - 85
}

And ->setSlug('missing-pants') with a random number at the end so that each one is unique.

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
$question->setName('Missing pants')
->setSlug('missing-pants-'.rand(0, 1000))
... lines 40 - 59
}
... lines 61 - 85
}

For the main part of the question, call ->setQuestion() and, because this is long, I'll use the multiline syntax - <<<EOF - and paste in some content. You can copy this from the code block on this page or use any text.

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
$question->setName('Missing pants')
->setSlug('missing-pants-'.rand(0, 1000))
->setQuestion(<<<EOF
Hi! So... I'm having a *weird* day. Yesterday, I cast a spell
to make my dishes wash themselves. But while I was casting it,
I slipped a little and I think `I also hit my pants with the spell`.
When I woke up this morning, I caught a quick glimpse of my pants
opening the front door and walking out! I've been out all afternoon
(with no pants mind you) searching for them.
Does anyone have a spell to call your pants back?
EOF
);
... lines 52 - 59
}
... lines 61 - 85
}

The last field is $askedAt. Let's add some randomness to this: if a random number between 1 and 10 is greater than 2, then call $question->setAskedAt(). Remember: askedAt is allowed to be null in the database... and if it is, we want that to mean that the user hasn't published the question yet. This if statement will give us a nice mixture of published and unpublished questions.

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
$question->setName('Missing pants')
->setSlug('missing-pants-'.rand(0, 1000))
->setQuestion(<<<EOF
... lines 41 - 49
EOF
);
if (rand(1, 10) > 2) {
... line 54
}
... lines 56 - 59
}
... lines 61 - 85
}

Also remember that the $askedAt property is a datetime field. This means that it will be a DATETIME type in MySQL: a field that is ultimately set via a date string. But in PHP, instead of dealing with strings, thankfully we get to deal with DateTime objects. Let's say new \DateTime() and add some randomness here too: sprintf('-%d days') and pass a random number from 1 to 100.

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
$question->setName('Missing pants')
->setSlug('missing-pants-'.rand(0, 1000))
->setQuestion(<<<EOF
... lines 41 - 49
EOF
);
if (rand(1, 10) > 2) {
$question->setAskedAt(new \DateTime(sprintf('-%d days', rand(1, 100))));
}
... lines 56 - 59
}
... lines 61 - 85
}

So, the askedAt will be anywhere from 1 to 100 days ago.

Ok! Our Question object is done! Add a dd($question) at the bottom:

... lines 1 - 4
use App\Entity\Question;
... lines 6 - 11
class QuestionController extends AbstractController
{
... lines 14 - 34
public function new()
{
$question = new Question();
$question->setName('Missing pants')
->setSlug('missing-pants-'.rand(0, 1000))
->setQuestion(<<<EOF
... lines 41 - 49
EOF
);
if (rand(1, 10) > 2) {
$question->setAskedAt(new \DateTime(sprintf('-%d days', rand(1, 100))));
}
dd($question);
... lines 58 - 59
}
... lines 61 - 85
}

then move over, refresh and... hello nice, boring Question object! Notice that the id property is still null because we haven't saved it to the database yet.

The EntityManagerInterface Service

So... how do we ask Doctrine to save this? When we installed Doctrine, one of the packages we downloaded was DoctrineBundle. From the Symfony Fundamentals course, you might remember that the main thing that a bundle gives us is new services in the container. And even though Doctrine is super powerful, it turns out that there is just one Doctrine service that we'll use 99% of the time. This one service is capable of both saving and fetching... which... is really all Doctrine does.

To find the service, head to your terminal and run:

php bin/console debug:autowiring doctrine

This returns several services, but most are lower level. The one we want - which is the most important service by far in Doctrine - is EntityManagerInterface.

Let's go use it! Back in the controller, add a new argument to autowire this: EntityManagerInterface $entityManager.

... lines 1 - 6
use Doctrine\ORM\EntityManagerInterface;
... lines 8 - 12
class QuestionController extends AbstractController
{
... lines 15 - 35
public function new(EntityManagerInterface $entityManager)
{
... lines 38 - 65
}
... lines 67 - 91
}

persist() and flush()

Below, remove the dd(). How do we save? Call $entityManager->persist() and pass the object to save. And then $entityManager->flush().

... lines 1 - 6
use Doctrine\ORM\EntityManagerInterface;
... lines 8 - 12
class QuestionController extends AbstractController
{
... lines 15 - 35
public function new(EntityManagerInterface $entityManager)
{
... lines 38 - 57
$entityManager->persist($question);
$entityManager->flush();
... lines 60 - 65
}
... lines 67 - 91
}

Yes, you need both lines. The persist() call simply says:

Hey Doctrine! Please be "aware" of this Question object.

The persist line does not make any queries. The INSERT query happens when we call flush(). The flush() method says:

Yo Doctrine! Please look at all of the objects that you are "aware" of and make all the queries you need to save those.

So this is how saving looks: a persist() and flush() right next to each other. If you ever needed to, you could call persist() on 5 different objects and then call flush() once at the end to make all of those queries at the same time.

Anyways, now that we have a Question object, let's make the Response more interesting. I'll say sprintf with:

Well hallo! The shiny new question is id #%d, slug: %s

Passing $question->getId() for the first placeholder and $question->getSlug() for the second.

... lines 1 - 6
use Doctrine\ORM\EntityManagerInterface;
... lines 8 - 12
class QuestionController extends AbstractController
{
... lines 15 - 35
public function new(EntityManagerInterface $entityManager)
{
... lines 38 - 57
$entityManager->persist($question);
$entityManager->flush();
return new Response(sprintf(
'Well hallo! The shiny new question is id #%d, slug: %s',
$question->getId(),
$question->getSlug()
));
}
... lines 67 - 91
}

Ok, back at the browser, before saving, the Question object had no id value. But now when we refresh... yes! It has an id! After saving, Doctrine automatically sets the new id on the object. We can refresh over and over again to add more and more question rows to the table.

Let's go see them! If you ever want to make a query to see something, Doctrine has a handy bin/console command for that:

symfony console doctrine:query:sql 'SELECT * FROM question'

And... yes! Here is a dump of the 8 rows in the table.

Next: we know how to save. So how can we query to fetch data?

Leave a comment!

17
Login or Register to join the conversation
PZon Avatar

Hi.
Sorry I bother you, but I'm stuck on this chapter. When I try to run app with "new" method I got an error:

PDOException>Exception>DriverException
An exception occurred in the driver: could not find driver
in C:\xampp\htdocs\symfonycast\cauldron_overflow\vendor\doctrine\dbal\src\Driver\API\PostgreSQL\ExceptionConverter.php (line 87)

    // We have to match against the SQLSTATE in the error message in these cases.
    if ($exception->getCode() === 7 && strpos($exception->getMessage(), 'SQLSTATE[08006]') !== false) {
        return new ConnectionException($exception, $query);
    }
    return new DriverException($exception, $query);
}

}

Sorry I don't know what cause an error?
I would be grateful for your help.

Reply
Jesse-Rushlow Avatar
Jesse-Rushlow Avatar Jesse-Rushlow | SFCASTS | PZon | posted 13 days ago

Howdy!

No bother at all! This sounds like your PHP instance does not have the required PDO drivers to interact with a database. A quick way to check, in a terminal run php -i | grep "pdo" & you should see something similar to:

Configure Command =>  './configure'  '--with-openssl' '--with-pdo-pgsql' '--with-zlib' '--with-pdo-mysql' '--with-curl' '--with-zip' '--enable-intl' '--enable-mbstring' '--with-sodium' '--enable-gd'
API Extensions => pdo_mysql
pdo_mysql
pdo_mysql.default_socket => /tmp/mysql.sock => /tmp/mysql.sock
pdo_pgsql
pdo_sqlite

Couple of questions to get those installed if they're missing:

1) What version of PHP are you using? php -v Will show you.
2) What Operating System are you using
3) Do you remember how you installed PHP onto your computer? e.g. using apt-get install php or compiled PHP source code, etc..

Thank You!

Reply
PZon Avatar

Hi. Thanks for quick response.

Answers:
ad1.
c:\xampp\htdocs\symfonycast\cauldron_overflow>php -v<br />PHP 7.4.24 (cli) (built: Sep 21 2021 13:38:25) ( ZTS Visual C++ 2017 x64 )<br />Copyright (c) The PHP Group<br />Zend Engine v3.4.0, Copyright (c) Zend Technologies
ad2. Windows 11
ad3. I have installed PHP with XAMPP

And maybe some useful info regarding PDO:
PDO
PDO support => enabled
PDO drivers => mysql, sqlite
pdo_mysql
PDO Driver for MySQL => enabled
Client API version => mysqlnd 7.4.24
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.31.1


thanks a million

Reply
Jesse-Rushlow Avatar
Jesse-Rushlow Avatar Jesse-Rushlow | SFCASTS | PZon | posted 12 days ago

Awesome! That's very helpful. I think instead of trying to install the Postgres PDO driver for PHP, you might be better off using MySQL/MariaDB seeing as XAMPP already has that configured for you.

In your .env file, you should have a DATABASE_URL="postgresql........." line and right above/below you'll see another that is commented out: # DATABASE_URL="mysql.........". Remove the comment from the mysql line and add it to the postgresql line. This tells Symfony to use MySQL/MariaDB instead of Postgres. You'll need to set the correct username, password, host, etc to match XAMPP's configuration. But this should get you on the right track.


If you really want to stick with Postgres, and that's not a bad thing, you'll need to install postgres for windows if it's not already installed: https://www.postgresql.org/download/windows/ I'd recommend either version 13 or 14 for now. Then uncomment the following lines in your php.ini file:

extension=php_pdo_pgsql.dll
extension=php_pgsql.dll 

And, if I'm not mistaken, add LoadFile "C:\xampp\php\libpq.dll" to your httpd.conf ensuring that the path is correct to libpq.dll.

Restarting XAMPP and running php -i | grep "pdo" will tell you if you have the PDO_PGSQL configured correctly.


I hope this helps, but do let use know if you have any trouble!

Reply
Tim C. Avatar

So, I'm trying to run all this stuff on a separate CENTOS box than my local machine, and for the most part everything is working as intended as I go through the tutorials. I have a tomcat running that I use as a URL instead of localhost.

But I can't for the life of me get the persist to the DB working.

I have the docker running.
I can connect to the docker shell and run MYSQL and everything as expected.
The problem comes when I try to persist to the DB. I get a connection refused error, which I am assuming is because it can't find the DB.

I've tried replacing the URL (@127.0.0.1:3306) with the URL I am running via apache (@http://cauldron.server.local:3306) but that hasn't worked.

I could use some help. Running using localhost just isn't an option for me.

Reply

Hey Tim,

Please, double-check the DB credentials you're using, i.e. check DATABASE_URL in your .env and .env.local files. Make sure that you entered the valid credentials - not only DB host and port, but also username and password. something like this:

DATABASE_URL="mysql://db_user:db_password@127.0.0.1:3306/db_name?serverVersion=5.7&charset=utf8mb4"

Try to use the correct IP instead of that cauldron.server.local, it may help as well. Btw, do you use MySQL as your DB provider? I'd also recommend you to double-check your DB credentials with a MySQL GUI client - fill the credentials you're using there and try to connect. Most probably your credentials are just invalid. But if you can connect there - just use the same credentials for DATABASE_URL (don't forget to make changes in all .env files where you set DATABASE_URL just in case).

There's another option to use Docker locally for running this project. Probably take a look?

Cheers!

Reply
Tim C. Avatar

No, I know the credentials are correct. I was just giving a shortened database_url above to show the area that I think needed to be looked at. As I said above I can connect through the docker shell using the same credentials that I have in the database_url. Truth is I don't even think it is getting far enough to worry about the credentials yet, it isn't even getting as far as finding the DB to ask for credentials.

I guess what I am asking is if I run the docker with the MySQL (Yes it is MySQL) is it going to use the same apache/tomcat and use the same URL as the project itself or do I need to set up a conf.d file for the dockerised port, or something else entirely? I am not the most knowledgeable when it comes to tomcat/apache and know just enough to get things mostly working for a project and I can /usually/ figure it out, but this one has me stumped.

UPDATE:
It's moot now. I found an online MySQL host and am using that now without worrying about Docker.

Reply
Jakub Avatar

Why symfony doesn't see Question.php as a part of a project? It doesn't want to autocomplete the use statement and it even don't see it at all

Reply

Hey HudyWeas,

I'm not sure, if you could provide a screenshot so I could better understand what you mean about "as a part of a project". But as I replied to another your comment - please, make sure you have PhpStorm's Symfony plugin installed and enabled for your project - it gives you most of the autocompletion in projects based on Symfony.

Cheers!

Reply
Assist software Avatar
Assist software Avatar Assist software | posted 1 year ago

Hello
I'm new to the symfony world and I encountered a problem at
$entityManager->persist($question);
I did exactly as in the tutorial and I came across this error:
An exception occurred in driver: could not find driver

Doctrine\DBAL\Exception\
DriverException

Doctrine\DBAL\Driver\PDO\
Exception
could not find driver

PDOException
could not find driver

I have the database on the docker because I did not install mysql locally
but I can connect to it
I don't understand what the problem is
I tried to add the extension pdo_mysql in php.ini but without success I use ubuntu and I am new in this operating system :)

PS: sorry for my english

Reply

Hey Assist software

Which php version are you using? is it installed on the OS? if it's OS package, than you should install php-mysql extension and it will add PDO drive, you can do it with sudo apt-get install php-mysql

Cheers!

Reply
Maike L. Avatar
Maike L. Avatar Maike L. | posted 1 year ago

hi again,
I get this error when trying to flush:
An exception occurred in driver: SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'

this is my databse url:

DATABASE_URL="mysql:/root:password@127.0.0.1:3306/db_name?serverVersion=5.7"

my password is correct.

any ideas?

Reply

Hey Payman,

Yes, you clearly does not have access with the given credentials. Are you sure that those credentials are used for the Symfony environment you're trying to load your application with? If so, please, make sure they are really valid, like check the host, port, database name, user and password. Also, the way you wrote it is incorrect, it should have 2 slashes after "mysql", i.e.:


DATABASE_URL="mysql://root:password@127.0.0.1:3306/db_name?serverVersion=5.7"

I hope this helps! If not, probably you don't have needed permissions for root user, I mean it might not be allowed to access the DB from your PHP script. The easiest solution would be to create another user, e.g. name it "admin" and grant all the necessary permissions to it, then update the DATABASE_URL.

Cheers!

Reply
akincer Avatar
akincer Avatar akincer | posted 2 years ago

Using single quotes like in this video to query on the CLI resulted in the error:

Too many arguments, expected arguments "command" "sql".

Switching to double quotes fixed it though.

Reply

Hey there,

That's weird, maybe it's related to your shell, or you may have had a non-closed quote?

Reply
akincer Avatar

I'm doing this on Windows 10 so I'm sure it's the shell.

Reply

I'd think the same. Windows just don't like to work as expected :D

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

This tutorial also works great for Symfony 6!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.4.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "doctrine/doctrine-bundle": "^2.1", // 2.1.1
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.0.2
        "doctrine/orm": "^2.7", // 2.8.2
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.0
        "sensio/framework-extra-bundle": "^6.0", // v6.2.1
        "sentry/sentry-symfony": "^4.0", // 4.0.3
        "stof/doctrine-extensions-bundle": "^1.4", // v1.5.0
        "symfony/asset": "5.1.*", // v5.1.2
        "symfony/console": "5.1.*", // v5.1.2
        "symfony/dotenv": "5.1.*", // v5.1.2
        "symfony/flex": "^1.3.1", // v1.17.5
        "symfony/framework-bundle": "5.1.*", // v5.1.2
        "symfony/monolog-bundle": "^3.0", // v3.5.0
        "symfony/stopwatch": "5.1.*", // v5.1.2
        "symfony/twig-bundle": "5.1.*", // v5.1.2
        "symfony/webpack-encore-bundle": "^1.7", // v1.8.0
        "symfony/yaml": "5.1.*", // v5.1.2
        "twig/extra-bundle": "^2.12|^3.0", // v3.0.4
        "twig/twig": "^2.12|^3.0" // v3.0.4
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.1.*", // v5.1.2
        "symfony/maker-bundle": "^1.15", // v1.23.0
        "symfony/var-dumper": "5.1.*", // v5.1.2
        "symfony/web-profiler-bundle": "5.1.*", // v5.1.2
        "zenstruck/foundry": "^1.1" // v1.5.0
    }
}