108

We host a multitenant app with SQLAlchemy and postgres. I am looking at moving from having separate databases for each tenant to a single database with multiple schemas. Does SQLAlchemy support this natively? I basically just want every query that comes out to be prefixed with a predetermined schema... e.g

select * from client1.users

instead of just 

select * from users

Note that I want to switch the schema for all tables in a particular request/set of requests, not just a single table here and there.

I imagine that this could be accomplished with a custom query class as well but I can't imagine that something hasn't been done in this vein already.

eleddy
  • 1,354
  • 2
  • 9
  • 8

12 Answers12

94

If you want to do this at the connection string level then use the following:

dbschema='schema1,schema2,public' # Searches left-to-right
engine = create_engine(
    'postgresql+psycopg2://dbuser@dbhost:5432/dbname',
    connect_args={'options': '-csearch_path={}'.format(dbschema)})

But, a better solution for a multi-client (multi-tenant) application is to configure a different db user for each client, and configure the relevant search_path for each user:

alter role user1 set search_path = "$user", public
PPrice
  • 2,243
  • 17
  • 12
  • 2
    While this works for sqlalchemy, it fails for alembic (native migrations tool). Revision generation script completely messes up if `search_path` is used for schema control. The advised way as per https://github.com/sqlalchemy/alembic/issues/569 is to not touch the `search_path` and instead define schema explicitly in your models and migrations. – alexykot May 31 '19 at 17:30
  • Hi, does this approach works for mysql? – Espoir Murhabazi Nov 22 '21 at 17:10
93

well there's a few ways to go at this and it depends on how your app is structured. Here is the most basic way:

meta = MetaData(schema="client1")

If the way your app runs is one "client" at a time within the whole application, you're done.

But what may be wrong with that here is, every Table from that MetaData is on that schema. If you want one application to support multiple clients simultaneously (usually what "multitenant" means), this would be unwieldy since you'd need to create a copy of the MetaData and dupe out all the mappings for each client. This approach can be done, if you really want to, the way it works is you'd access each client with a particular mapped class like:

client1_foo = Client1Foo()

and in that case you'd be working with the "entity name" recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName in conjunction with sometable.tometadata() (see http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table.tometadata).

So let's say the way it really works is multiple clients within the app, but only one at a time per thread. Well actually, the easiest way to do that in Postgresql would be to set the search path when you start working with a connection:

# start request

# new session
sess = Session()

# set the search path
sess.execute("SET search_path TO client1")

# do stuff with session

# close it.  if you're using connection pooling, the
# search path is still set up there, so you might want to 
# revert it first
sess.close()

The final approach would be to override the compiler using the @compiles extension to stick the "schema" name in within statements. This is doable, but would be tricky as there's not a consistent hook for everywhere "Table" is generated. Your best bet is probably setting the search path on each request.

Philip Couling
  • 13,581
  • 5
  • 53
  • 85
zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • Thanks! I'll try a couple things and then see which works best and report back but I think path is the way to go. – eleddy Feb 16 '12 at 18:44
  • @zzzeek I have a mirror question of this but for Alembic, could really use your input: http://stackoverflow.com/questions/21109218/alembic-support-for-multiple-postgres-schemas – dtheodor Jan 14 '14 at 08:50
  • 7
    Incidentally, I managed to do this for the declarative syntax like: ``Base = declarative_base(); Base.metadata.schema = 'ebay'``. There's probably a better way, though. – Catherine Devlin Mar 21 '14 at 23:23
  • 3
    Important: A rollback will also rollback the `set search_path`. Subsequent commands will be issued against the default schema. Issue an explicit commit after the set to avoid this. http://www.postgresql.org/docs/current/static/sql-set.html – Doctor J Jun 10 '15 at 22:54
  • For posterity, there's a correct way to do what @CatherineDevlin did above, and it looks like `meta = Metadata(schema="ebay"); Base = declarative_base(metadata = meta);` It is specified at https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#explicit-schema-name-with-declarative-table – ketil Jan 23 '23 at 09:23
54

It can now be done using schema translation map in Sqlalchemy 1.1.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

    __table_args__ = {'schema': 'per_user'}

On each request, the Session can be set up to refer to a different schema each time:

session = Session()
session.connection(execution_options={
    "schema_translate_map": {"per_user": "account_one"}})

# will query from the ``account_one.user`` table

session.query(User).get(5)

Referred it from the SO answer here.

Link to the Sqlalchemy docs.

Abhishek J
  • 2,386
  • 2
  • 21
  • 22
9

You may be able to manage this using the sqlalchemy event interface. So before you create the first connection, set up a listener along the lines of

from sqlalchemy import event
from sqlalchemy.pool import Pool

def set_search_path( db_conn, conn_proxy ):
    print "Setting search path..."
    db_conn.cursor().execute('set search_path=client9, public')

event.listen(Pool,'connect', set_search_path )

Obviously this needs to be executed before the first connection is created (eg in the application initiallization)

The problem I see with the session.execute(...) solution is that this executes on a specific connection used by the session. However I cannot see anything in sqlalchemy that guarantees that the session will continue to use the same connection indefinitely. If it picks up a new connection from the connection pool, then it will lose the search path setting.

I am needing an approach like this in order to set the application search_path, which is different to the database or user search path. I'd like to be able to set this in the engine configuration, but cannot see a way to do this. Using the connect event does work. I'd be interested in a simpler solution if anyone has one.

On the other hand, if you are wanting to handle multiple clients within an application, then this won't work - and I guess the session.execute(...) approach may be the best approach.

Chris Crook
  • 243
  • 3
  • 7
  • 1
    Do you have an elegant way to pass 'client9' as an argument instead of hardcoding it? My current (hacky) workaround is to pass an `application_name` query arg to the db-url (`?application_name=bla`) and then inspecting it in `set_search_path` with `db_conn.dsn.split('application_name=')[1])`. – rkrzr Dec 21 '14 at 13:59
6

from sqlalchemy 1.1, this can be done easily using using schema_translation_map.

https://docs.sqlalchemy.org/en/11/changelog/migration_11.html#multi-tenancy-schema-translation-for-table-objects

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"})

result = connection.execute(user_table.select())

Here is a detailed reviews of all options available: https://github.com/sqlalchemy/sqlalchemy/issues/4081

echo
  • 2,666
  • 1
  • 25
  • 17
  • 1
    Unlike `set search_path`, this works for `create table`. No need to change `__table_args__` of each table model. – Ben Nov 27 '22 at 06:09
5

It's possible to solve this on DB level. I suppose you have a dedicated user for your application who is granted some privileges on the schema. Just set search_path for him to this schema:

ALTER ROLE your_user IN DATABASE your_db SET search_path TO your_schema;
Konstantin Smolyanin
  • 17,579
  • 12
  • 56
  • 56
4

There is a schema property in Table definitions

I'm not sure if it works but you can try:

Table(CP.get('users', metadata, schema='client1',....)
DaveShaw
  • 52,123
  • 16
  • 112
  • 141
christian
  • 49
  • 1
  • I'm looking for something on a more global scale so that I could switch all queries in all tables for a single request. I will update the question to reflect that. – eleddy Feb 15 '12 at 18:01
2

For anyone who is coming here, for a more general solution that can support MYSQL or Oracle, please refer to this guide.

So basically it set the schemas for the engine when the first connection to the database is made.

engine = create_engine("engine_url")

@event.listens_for(engine, "connect", insert=True)
def set_current_schema(dbapi_connection, connection_record):
    cursor_obj = dbapi_connection.cursor()
    cursor_obj.execute(f"USE {self.schemas_name}")
    cursor_obj.close()

the query to execute depends is specific to the database you are using, so for PSQL you will have a different query, for ORACLE, you will have a different, etc.

Espoir Murhabazi
  • 5,973
  • 5
  • 42
  • 73
1

I tried:

con.execute('SET search_path TO {schema}'.format(schema='myschema'))

and that didn't work for me. I then used the schema= parameter in the init function: 

# We then bind the connection to MetaData()
meta = sqlalchemy.MetaData(bind=con, reflect=True, schema='myschema')

Then I qualified the table with the schema name

house_table = meta.tables['myschema.houses']

and everything worked.

crankymax
  • 11
  • 4
1

You can just change your search_path. Issue

set search_path=client9;

at the start of your session and then just keep your tables unqualified.

You can also set a default search_path at a per-database or per-user level. I'd be tempted to set it to an empty schema by default so you can easily catch any failure to set it.

http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • 2
    that is... a great idea. high five! – eleddy Feb 16 '12 at 18:45
  • 1
    And remember that after a session.commit() a new transaction is started so the search_path will be reset. SA session events work great for setting the search_path for each new transaction. – Brett Aug 03 '13 at 01:54
0

I found none of the above answers worked with SqlAlchmeny 1.2.4. This is the solution that worked for me.

from sqlalchemy import MetaData, Table
from sqlalchemy import create_engine    

def table_schemato_psql(schema_name, table_name):

        conn_str = 'postgresql://{username}:{password}@localhost:5432/{database}'.format(
            username='<username>',
            password='<password>',
            database='<database name>'
        )

        engine = create_engine(conn_str)

        with engine.connect() as conn:
            conn.execute('SET search_path TO {schema}'.format(schema=schema_name))

            meta = MetaData()

            table_data = Table(table_name, meta,
                              autoload=True,
                              autoload_with=conn,
                              postgresql_ignore_search_path=True)

            for column in table_data.columns:
                print column.name
Joseph Lust
  • 19,340
  • 7
  • 85
  • 83
0

I use the following pattern.

engine = sqlalchemy.create_engine("postgresql://postgres:mypass@172.17.0.2/mydb")

for schema in ['schema1', 'schema2']:
    engine.execute(CreateSchema(schema))
    tmp_engine = engine.execution_options(schema_translate_map = { None: schema } )
    Base.metadata.create_all(tmp_engine)
Rahul
  • 963
  • 9
  • 14