0

Background

I am working with multiple postgres schemas that basically identify as individual tenants sharing the app. Every request that the backend receives contains an instance identifying header which allows us to set the schema for each request.

For example, my database looks like this:

  • postgres database
    • common
    • tenantOne
    • tenantTwo

Problem

However, I have run into a problem when working with Symfony Messanger (DSN transport - doctrine://default). An example scenario is as follows:

  1. Request comes in and sets the instance. This essentially runs a SQL command on the current Doctrine Connection: 'SET SEARCH_PATH TO ' . $schema_name . ', global'

  2. Process creates a message. The message bus dispatches this message as normal.

  3. The message gets written to the messenger_messages table on the current instance

  4. When the message is consumed by the worker, it is not being set to a schema, so cannot handle a message.

Desired Behaviour

  1. Write ALL messages to the common schema
  2. Worker consumes the message on the correct schema.

For #1 I am thinking that the schema can be set before the message is written to the database. But unsure where is best. My concern also is that if we change the schema with the current doctrine connection, it will need to be reset back so that the rest of the process can continue on the correct schema.

Is there a way to define which schema all messages should be written to without switching it and reverting it in middleware or event listeners?

For #2, I was thinking a stamp to pass the current instance with the message. This means my handler can use this to set the schema when it is received and handled?

It would be great to get some thoughts on how this can be handled neatly. A multi-tenancy application is common, but I cannot find anything anywhere in the docs where I can control the schema within a database in which a message is written (or the messenger_messages table can be stored).

Luke
  • 555
  • 1
  • 5
  • 23
  • What's happen if you create a second Doctrine connection to another simple database, and assign it to Messenger DSN!? – Francisco May 12 '22 at 13:12
  • Or set a second DSN connection with another user AND set default schema for the user: ALTER USER messenger_user SET SEARCH_PATH = Desired schema!?!?!?, AND assign it DSN to Messenger transport – Francisco May 12 '22 at 13:18
  • Did you consider creating custom connection for your messages and use it as transport in Messenger? – qdequippe May 12 '22 at 13:50
  • @qdequippe So, a second doctrine connection that only talks to the common schema or is configured on init with the common schema as the search path? This would only be for the message transport into the database, and not the handling. Is this what you meant? – Luke May 12 '22 at 14:02
  • first one solution "a second doctrine connection that only talks to the common schema" and you can use it for the transport and it will be used for the handling also. And in your handler feel free to use any other entity manager (a dynamic one for example) ? – qdequippe May 12 '22 at 14:05
  • @qdequippe so the handler can switch instances I guess when it is being processed. But if the connection starts on common, then it should all work. – Luke May 12 '22 at 14:41
  • "table_name" is one of the configuration options for the coctrine transpot. Set it to "common.messenger_messages". to ignore the SEARCH_PATH. – martti Aug 24 '23 at 22:31

0 Answers0