8.8. Don't Create the Model Twice

The trade-off of using an ORM is that you must define the data structure twice: once for the database, and once for the object model. Fortunately, symfony offers command-line tools to generate one based on the other, so you can avoid duplicate work.

8.8.1. Building a SQL Database Structure Based on an Existing Schema

If you start your application by writing the schema.yml file, symfony can generate a SQL query that creates the tables directly from the YAML data model. To use the query, go to your root project directory and type this:

> symfony propel-build-sql

A lib.model.schema.sql file will be created in myproject/data/sql/. Note that the generated SQL code will be optimized for the database system defined in the phptype parameter of the propel.ini file.

You can use the schema.sql file directly to build the tables. For instance, in MySQL, type this:

> mysqladmin -u root -p create blog
> mysql -u root -p blog < data/sql/lib.model.schema.sql

The generated SQL is also helpful to rebuild the database in another environment, or to change to another DBMS. If the connection settings are properly defined in your propel.ini, you can even use the symfony propel-insert-sql command to do this automatically.

8.8.2. Generating a YAML Data Model from an Existing Database

Symfony can use the Creole database access layer to generate a schema.yml file from an existing database, thanks to introspection (the capability of databases to determine the structure of the tables on which they are operating). This can be particularly useful when you do reverse-engineering, or if you prefer working on the database before working on the object model.

In order to do this, you need to make sure that the project propel.ini file points to the correct database and contains all connection settings, and then call the propel-build-schema command:

> symfony propel-build-schema

A brand-new schema.yml file built from your database structure is generated in the config/ directory. You can build your model based on this schema.

The schema-generation command is quite powerful and can add a lot of database-dependent information to your schema. As the YAML format doesn't handle this kind of vendor information, you need to generate an XML schema to take advantage of it. You can do this simply by adding an xml argument to the build-schema task:

> symfony propel-build-schema xml

Instead of generating a schema.yml file, this will create a schema.xml file fully compatible with Propel, containing all the vendor information. But be aware that generated XML schemas tend to be quite verbose and difficult to read.

propel.database.createUrl = mysql://login:passwd@localhost
propel.database.url       = mysql://login:passwd@localhost/blog

; Base classes are autoloaded in symfony
; Set this to true to use include_once statements instead
; (Small negative impact on performance)
propel.builder.addIncludes = false

; Generated classes are not commented by default
; Set this to true to add comments to Base classes
; (Small negative impact on performance)
propel.builder.addComments = false

; Behaviors are not handled by default
; Set this to true to be able to handle them
propel.builder.AddBehaviors = false