Databases and Propel
Edit this pageWarning: You are browsing the documentation for Symfony 2.4, which is no longer maintained.
Read the updated version of this page for Symfony 7.0 (the current stable version).
Databases and Propel
One of the most common and challenging tasks for any application involves persisting and reading information to and from a database. Symfony does not come integrated with any ORMs but the Propel integration is easy. To install Propel, read Working With Symfony2 on the Propel documentation.
A Simple Example: A Product
In this section, you'll configure your database, create a Product
object,
persist it to the database and fetch it back out.
Code along with the Example
If you want to follow along with the example in this chapter, create an
AcmeStoreBundle
via:
1
$ php app/console generate:bundle --namespace=Acme/StoreBundle
Configuring the Database
Before you can start, you'll need to configure your database connection
information. By convention, this information is usually configured in an
app/config/parameters.yml
file:
1 2 3 4 5 6 7 8
# app/config/parameters.yml
parameters:
database_driver: mysql
database_host: localhost
database_name: test_project
database_user: root
database_password: password
database_charset: UTF8
Note
Defining the configuration via parameters.yml
is just a convention. The
parameters defined in that file are referenced by the main configuration
file when setting up Propel:
These parameters defined in parameters.yml
can now be included in the
configuration file (config.yml
):
1 2 3 4 5 6
propel:
dbal:
driver: "%database_driver%"
user: "%database_user%"
password: "%database_password%"
dsn: "%database_driver%:host=%database_host%;dbname=%database_name%;charset=%database_charset%"
Now that Propel knows about your database, Symfony can create the database for you:
1
$ php app/console propel:database:create
Note
In this example, you have one configured connection, named default
. If
you want to configure more than one connection, read the PropelBundle configuration section.
Creating a Model Class
In the Propel world, ActiveRecord classes are known as models because classes generated by Propel contain some business logic.
Note
For people who use Symfony with Doctrine2, models are equivalent to entities.
Suppose you're building an application where products need to be displayed.
First, create a schema.xml
file inside the Resources/config
directory
of your AcmeStoreBundle
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
<?xml version="1.0" encoding="UTF-8" ?>
<database
name="default"
namespace="Acme\StoreBundle\Model"
defaultIdMethod="native">
<table name="product">
<column
name="id"
type="integer"
required="true"
primaryKey="true"
autoIncrement="true" />
<column
name="name"
type="varchar"
primaryString="true"
size="100" />
<column
name="price"
type="decimal" />
<column
name="description"
type="longvarchar" />
</table>
</database>
Building the Model
After creating your schema.xml
, generate your model from it by running:
1
$ php app/console propel:model:build
This generates each model class to quickly develop your application in the
Model/
directory of the AcmeStoreBundle
bundle.
Creating the Database Tables/Schema
Now you have a usable Product
class and all you need to persist it. Of
course, you don't yet have the corresponding product
table in your
database. Fortunately, Propel can automatically create all the database tables
needed for every known model in your application. To do this, run:
1 2
$ php app/console propel:sql:build
$ php app/console propel:sql:insert --force
Your database now has a fully-functional product
table with columns that
match the schema you've specified.
Tip
You can run the last three commands combined by using the following
command: php app/console propel:build --insert-sql
.
Persisting Objects to the Database
Now that you have a Product
object and corresponding product
table,
you're ready to persist data to the database. From inside a controller, this
is pretty easy. Add the following method to the DefaultController
of the
bundle:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
// src/Acme/StoreBundle/Controller/DefaultController.php
// ...
use Acme\StoreBundle\Model\Product;
use Symfony\Component\HttpFoundation\Response;
public function createAction()
{
$product = new Product();
$product->setName('A Foo Bar');
$product->setPrice(19.99);
$product->setDescription('Lorem ipsum dolor');
$product->save();
return new Response('Created product id '.$product->getId());
}
In this piece of code, you instantiate and work with the $product
object.
When you call the save()
method on it, you persist it to the database. No
need to use other services, the object knows how to persist itself.
Note
If you're following along with this example, you'll need to create a route that points to this action to see it in action.
Fetching Objects from the Database
Fetching an object back from the database is even easier. For example, suppose
you've configured a route to display a specific Product
based on its id
value:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
// ...
use Acme\StoreBundle\Model\ProductQuery;
public function showAction($id)
{
$product = ProductQuery::create()
->findPk($id);
if (!$product) {
throw $this->createNotFoundException(
'No product found for id '.$id
);
}
// ... do something, like pass the $product object into a template
}
Updating an Object
Once you've fetched an object from Propel, updating it is easy. Suppose you have a route that maps a product id to an update action in a controller:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
// ...
use Acme\StoreBundle\Model\ProductQuery;
public function updateAction($id)
{
$product = ProductQuery::create()
->findPk($id);
if (!$product) {
throw $this->createNotFoundException(
'No product found for id '.$id
);
}
$product->setName('New product name!');
$product->save();
return $this->redirect($this->generateUrl('homepage'));
}
Updating an object involves just three steps:
- fetching the object from Propel (line 6 - 13);
- modifying the object (line 15);
- saving it (line 16).
Deleting an Object
Deleting an object is very similar to updating, but requires a call to the
delete()
method on the object:
1
$product->delete();
Querying for Objects
Propel provides generated Query
classes to run both basic and complex queries
without any work:
1 2 3 4 5
\Acme\StoreBundle\Model\ProductQuery::create()->findPk($id);
\Acme\StoreBundle\Model\ProductQuery::create()
->filterByName('Foo')
->findOne();
Imagine that you want to query for products which cost more than 19.99, ordered from cheapest to most expensive. From inside a controller, do the following:
1 2 3 4
$products = \Acme\StoreBundle\Model\ProductQuery::create()
->filterByPrice(array('min' => 19.99))
->orderByPrice()
->find();
In one line, you get your products in a powerful oriented object way. No need to waste your time with SQL or whatever, Symfony offers fully object oriented programming and Propel respects the same philosophy by providing an awesome abstraction layer.
If you want to reuse some queries, you can add your own methods to the
ProductQuery
class:
1 2 3 4 5 6 7 8 9
// src/Acme/StoreBundle/Model/ProductQuery.php
class ProductQuery extends BaseProductQuery
{
public function filterByExpensivePrice()
{
return $this
->filterByPrice(array('min' => 1000));
}
}
But note that Propel generates a lot of methods for you and a simple
findAllOrderedByName()
can be written without any effort:
1 2 3
\Acme\StoreBundle\Model\ProductQuery::create()
->orderByName()
->find();
Relationships/Associations
Suppose that the products in your application all belong to exactly one
"category". In this case, you'll need a Category
object and a way to relate
a Product
object to a Category
object.
Start by adding the category
definition in your schema.xml
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
<?xml version="1.0" encoding="UTF-8" ?>
<database
name="default"
namespace="Acme\StoreBundle\Model"
defaultIdMethod="native">
<table name="product">
<column
name="id"
type="integer"
required="true"
primaryKey="true"
autoIncrement="true" />
<column
name="name"
type="varchar"
primaryString="true"
size="100" />
<column
name="price"
type="decimal" />
<column
name="description"
type="longvarchar" />
<column
name="category_id"
type="integer" />
<foreign-key foreignTable="category">
<reference local="category_id" foreign="id" />
</foreign-key>
</table>
<table name="category">
<column
name="id"
type="integer"
required="true"
primaryKey="true"
autoIncrement="true" />
<column
name="name"
type="varchar"
primaryString="true"
size="100" />
</table>
</database>
Create the classes:
1
$ php app/console propel:model:build
Assuming you have products in your database, you don't want to lose them. Thanks to migrations, Propel will be able to update your database without losing existing data.
1 2
$ php app/console propel:migration:generate-diff
$ php app/console propel:migration:migrate
Your database has been updated, you can continue writing your application.
Saving Related Objects
Now, try the code in action. Imagine you're inside a controller:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
// ...
use Acme\StoreBundle\Model\Category;
use Acme\StoreBundle\Model\Product;
use Symfony\Component\HttpFoundation\Response;
class DefaultController extends Controller
{
public function createProductAction()
{
$category = new Category();
$category->setName('Main Products');
$product = new Product();
$product->setName('Foo');
$product->setPrice(19.99);
// relate this product to the category
$product->setCategory($category);
// save the whole
$product->save();
return new Response(
'Created product id: '.$product->getId().' and category id: '.$category->getId()
);
}
}
Now, a single row is added to both the category
and product
tables. The
product.category_id
column for the new product is set to whatever the id is
of the new category. Propel manages the persistence of this relationship for
you.
Fetching Related Objects
When you need to fetch associated objects, your workflow looks just like it did
before. First, fetch a $product
object and then access its related
Category
:
1 2 3 4 5 6 7 8 9 10 11 12 13
// ...
use Acme\StoreBundle\Model\ProductQuery;
public function showAction($id)
{
$product = ProductQuery::create()
->joinWithCategory()
->findPk($id);
$categoryName = $product->getCategory()->getName();
// ...
}
Note, in the above example, only one query was made.
More Information on Associations
You will find more information on relations by reading the dedicated chapter on Relationships.
Lifecycle Callbacks
Sometimes, you need to perform an action right before or after an object is inserted, updated, or deleted. These types of actions are known as "lifecycle" callbacks or "hooks", as they're callback methods that you need to execute during different stages of the lifecycle of an object (e.g. the object is inserted, updated, deleted, etc).
To add a hook, just add a new method to the object class:
1 2 3 4 5 6 7 8 9 10
// src/Acme/StoreBundle/Model/Product.php
// ...
class Product extends BaseProduct
{
public function preInsert(\PropelPDO $con = null)
{
// do something before the object is inserted
}
}
Propel provides the following hooks:
preInsert()
code executed before insertion of a new objectpostInsert()
code executed after insertion of a new objectpreUpdate()
code executed before update of an existing objectpostUpdate()
code executed after update of an existing objectpreSave()
code executed before saving an object (new or existing)postSave()
code executed after saving an object (new or existing)preDelete()
code executed before deleting an objectpostDelete()
code executed after deleting an object
Behaviors
All bundled behaviors in Propel are working with Symfony. To get more information about how to use Propel behaviors, look at the Behaviors reference section.
Commands
You should read the dedicated section for Propel commands in Symfony2.