Databases and Propel

Databases and Propel

One of the most common and challenging tasks for any application involves persisting and reading information to and from a database. Symfony2 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.

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, Symfony2 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 Symfony2 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
<?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:

  1. fetching the object from Propel (line 6 - 13);
  2. modifying the object (line 15);
  3. 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, Symfony2 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
<?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.

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 object
  • postInsert() code executed after insertion of a new object
  • preUpdate() code executed before update of an existing object
  • postUpdate() code executed after update of an existing object
  • preSave() 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 object
  • postDelete() code executed after deleting an object

Behaviors

All bundled behaviors in Propel are working with Symfony2. 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.

This work is licensed under a Creative Commons Attribution-Share Alike 3.0 Unported License .