Blog: Propel Adds Support For Database Schemas in Version 1.6

The Propel Team – 15 December 2010

For complex models showing a large number of tables, database administrators often like to group tables into “SQL schemas”, which are namespaces in the SQL server. Starting with Propel 1.6, it is now possible to assign tables to SQL schemas using the schema attribute in the <database> of the <table> tag:

<database name="my_connection">
  <table name="book" schema="bookstore">
    <column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
    <column name="title" type="VARCHAR" required="true" />
    <column name="author_id" type="INTGER" />
    <foreign-key foreignTable="author" foreignSchema="people" onDelete="setnull" onUpdate="cascade">
      <reference local="author_id" foreign="id" />
    </foreign-key>
  </table>
  <table name="author" schema="people">
    <column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
    <column name="name" type="VARCHAR" required="true" />
  </table>
</database>

Tip: This feature is only available in PostgreSQL, MSSQL, and MySQL. The schema attribute is simply ignored in Oracle and SQLite.

Propel also supports foreign keys between tables assigned to two different schemas. For MySQL, where “SQL schema” is a synonym for “database”, this allows for cross-database queries.

The Propel documentation contains a new tutorial about the SQL schema attributes and usage, called Using SQL Schemas.