Symfony project with models stored in multiple databases

Question

I am writing Symfony project (using symfony 1.4 ver. with Propel as its ORM) where some data is stored in MySQL database and some other data is stored on another server in PostgreSQL database.

To be more precise I want to store some models in MySQL database and other models in PostgreSQL database at the same time and do it seamlessly without explicit database switching (I mean Propel will use proper database connection and SQL dialect to retrieve/store data). Models from MySQL part will not have relations with PostgreSQL.

Is it possible? If yes I also would like to know how to setup development environment (I want to access different MySQL/PostgreSQL DBs in developement and production environments).

UPD: I've found question on SO reagrding this problem: Multiple databases support in Symfony But i have to check if it works with recent versions of Symfony.

guiman guiman · Accepted Answer · 2011-06-06T15:18:55

i work with symfony every day and in fact you can have 2 databases in order to store unrelated parts of the model. You need to set up both connection in you database.yml (i'm unfamiliar with posgress so you will have to figure out how to set it up correclty):

  mysql_connection:
    class:        sfPropelDatabase
    param:
      phptype:    mysql
      classname:  MysqlPropelPDO
      dsn:        ''
      username:   user
      password:   pass
      encoding:   UTF-8
      pooling:    true

  postgress_connection:
    class:        sfPropelDatabase
    param:
      phptype:    postgres
      classname:  PostgresPropelPDO
      dsn:        ''
      username:   user
      password:   pass
      encoding:   UTF-8
      pooling:    true

Once you have done that, we should get started with the schema.yml file or files (as you will be using 2 databases i would suggest to have 2 files, one for the mysql and another for the postgres database):

mysql_schema.yml file:

//This is how you tell witch connection you are using for this tables
connection: mysql_connection 

classes:
  CLassName:
    tableName: table_name
    columns:
      id:
      name:
        type: varchar(255)
        required: true
  [...]

postgres_schema.yml file:

connection:  postgress_connection
    classes:
      ClassName:
        tableName: table_name
        columns:
          id:
          name:
            type: varchar(255)
            required: true
      [...]

Once you have finished setting up your schema files, you should be good to go, create all classes and start to have fun. Hope this helps

Symfony project with models stored in multiple databases

2 Answers