Doctrine DBAL with Symfony2One symfony dbal query the most common and symfnoy tasks for any application involves persisting and reading information to and from a database. Although the Symfony Framework doesn't integrate any component to work with databases, it provides tight integration with a third-party library called Symfony dbal query. Doctrine's sole goal is to give you powerful tools to queery database interactions easy and flexible. In this chapter, you'll learn how to start leveraging Doctrine in your Symfony projects to give you rich database interactions. Doctrine is totally decoupled from Symfony and using it is optional.
Simplifying database interactions with Doctrine DBAL | The Dev Files
One of the most common and challenging tasks for any application involves persisting and reading information to and from a database.
Although the Symfony Framework doesn't integrate any component to work with databases, it provides tight integration with a third-party library called Doctrine. Doctrine's sole goal is to give you powerful tools to make database interactions easy and flexible.
In this chapter, you'll learn how to start leveraging Doctrine in your Symfony projects to give you rich database interactions. Doctrine is totally decoupled from Symfony and using it is optional. The easiest way to understand how Doctrine works is to see it in action.
In this section, you'll configure your database, create a Product object, persist it to the database and fetch it back out. Before you really begin, you'll need to configure your database connection information.
Defining the configuration via parameters. The parameters defined in that file are referenced by the main configuration file when setting up Doctrine:. By separating the database information into a separate file, you can easily keep different versions of the file on each server. You can also easily store database configuration or any sensitive information outside of your project, like inside your Apache configuration, for example. One mistake even seasoned developers make when starting a Symfony project is forgetting to set up default charset and collation on their database, ending up with latin type collations, which are default for most databases.
They might even remember to do it the very first time, but forget that it's all gone after running a relatively common command during development:.
You can also change the defaults for Doctrine so that the generated SQL uses the correct character set. We recommend against MySQL's utf8 character set, since it does not support 4-byte unicode characters, and strings containing them will be truncated.
This is fixed by the newer utf8mb4 character set. If you want to use SQLite as your database, you need to set the path where your database file should be stored:. Suppose you're building an application where products need to be displayed. Without even thinking about Doctrine or databases, you already know that you need a Product object to represent those products.
Create this class inside the Entity directory of your AppBundle:. The class - often called an "entity", meaning a basic class that holds data - is simple and helps fulfill the business requirement of needing products in your application.
This class can't be persisted to a database yet - it's just a simple PHP class. Once you learn the concepts behind Doctrine, you can have Doctrine create simple entity classes for you. This will ask you interactive questions to help you build any entity:. Doctrine allows you to work with databases in a much more interesting way than just fetching rows of scalar data into an array.
Instead, Doctrine allows you to fetch entire objects out of the database, and to persist entire objects to the database. For Doctrine to be able to do this, you must map your database tables to specific PHP classes, and the columns on those tables must be mapped to specific properties on their corresponding PHP classes. You'll provide this mapping information in the form of "metadata", a collection of rules that tells Doctrine exactly how the Product class and its properties should be mapped to a specific database table.
A bundle can accept only one metadata definition format. The table name is optional and if omitted, will be determined automatically based on the name of the entity class.
Doctrine allows you to choose from a wide variety of different field types, each with their own options. For information on the available field types, see the Doctrine Field Types Reference section. For example, if your entity's class name is Group , then, by default, the corresponding table name would be group. This will cause an SQL error in some database engines. See Doctrine's Reserved SQL keywords documentation for details on how to properly escape these names. Alternatively, if you're free to choose your database schema, simply map to a different table name or column name.
When using another library or program e. Doxygen that uses annotations, you should place the IgnoreAnnotation annotation on the class to indicate which annotations Symfony should ignore. For example, to prevent the fn annotation from throwing an exception, add the following:. Even though Doctrine now knows how to persist a Product object to the database, the class itself isn't really useful yet.
Since Product is just a regular PHP class with private properties, you need to create public getter and setter methods e. Add these methods manually or with your own IDE. You now have a usable Product class with mapping information so that Doctrine knows exactly how to persist it.
Of course, you don't yet have the corresponding product table in your database. Fortunately, Doctrine can automatically create all the database tables needed for every known entity in your application. To do this, run:. Actually, this command is incredibly powerful. It compares what your database should look like based on the mapping information of your entities with how it actually looks, and executes the SQL statements needed to update the database schema to where it should be. In other words, if you add a new property with mapping metadata to Product and run this command, it will execute the "ALTER TABLE" statement needed to add that new column to the existing product table.
An even better way to take advantage of this functionality is via migrations , which allow you to generate these SQL statements and store them in migration classes that can be run systematically on your production server in order to update and track changes to your database schema safely and reliably. Whether or not you take advantage of migrations, the doctrine: It should not be used in a production environment.
Your database now has a fully-functional product table with columns that match the metadata you've specified. Now that you have mapped the Product entity to its corresponding product table, you're ready to persist Product objects to the database.
From inside a controller, this is pretty easy. Add the following method to the DefaultController of the bundle:. If you're following along with this example, you'll need to create a route that points to this action to see it work. In fact, since Doctrine is aware of all your managed entities, when you call the flush method, it calculates an overall changeset and executes the queries in the correct order.
It utilizes cached prepared statement to slightly improve the performance. For example, if you persist a total of Product objects and then subsequently call flush , Doctrine will execute INSERT queries using a single prepared statement object. See Transactions and Concurrency. Whether creating or updating objects, the workflow is always the same. In the next section, you'll see how Doctrine is smart enough to automatically issue an UPDATE query if the entity already exists in the database.
Doctrine provides a library that allows you to programmatically load testing data into your project i. For information, see the " DoctrineFixturesBundle " documentation. Fetching an object back out of the database is even easier. For example, suppose you've configured a route to display a specific Product based on its id value:. You can achieve the equivalent of this without writing any code by using the ParamConverter shortcut. See the FrameworkExtraBundle documentation for more details.
When you query for a particular type of object, you always use what's known as its "repository". You can think of a repository as a PHP class whose only job is to help you fetch entities of a certain class. You can access the repository object for an entity class via:. You can also use AppBundle: This string is a shortcut you can use anywhere in Doctrine instead of the full class name of the entity i.
As long as your entity lives under the Entity namespace of your bundle, this will work. Of course, you can also issue complex queries, which you'll learn more about in the Querying for Objects section. You can also take advantage of the useful findBy and findOneBy methods to easily fetch objects based on multiple conditions:. When rendering a page requires to make some database calls, the web debug toolbar at the bottom of the page displays the number of queries and the time it took to execute them:.
If the number of database queries is too high, the icon will turn yellow to indicate that something may not be correct. Click on the icon to open the Symfony Profiler and see the exact queries that were executed. Once you've fetched an object from Doctrine, updating it is easy. Suppose you have a route that maps a product id to an update action in a controller:.
Deleting an object is very similar, but requires a call to the remove method of the entity manager:. As you might expect, the remove method notifies Doctrine that you'd like to remove the given object from the database.
DQL is similar to SQL except that you should imagine that you're querying for one or more objects of an entity class e. Product instead of querying for rows on a table e. When querying in Doctrine, you have two main options: Imagine that you want to query for products that cost more than The biggest difference is that you need to think in terms of selecting PHP objects, instead of rows in a database.
For this reason, you select from the AppBundle: Take note of the setParameter method. When working with Doctrine, it's always a good idea to set any external values as "placeholders": The getResult method returns an array of results. To get only one result, you can use getOneOrNullResult:.
The DQL syntax is incredibly powerful, allowing you to easily join between entities the topic of relations will be covered later , group, etc. For more information, see the official Doctrine Query Language documentation. Instead of writing a DQL string, you can use a helpful object called the QueryBuilder to build that string for you. This is useful when the actual query depends on dynamic conditions, as your code soon becomes hard to read with DQL as you start to concatenate strings:.