[ Python 常見問題 ] SQLAlchemy support of Postgres Schemas

Source From Here 
Question 
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? 

How-To 
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: 

view plaincopy to clipboardprint?

1. meta = MetaData(schema="client1")  

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

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: 

view plaincopy to clipboardprint?

1. # start request  
2.   
3. # new session  
4. sess = Session()  
5.   
6. # set the search path  
7. sess.execute("SET search_path TO client1")  
8.   
9. # do stuff with session  
10.   
11. # close it.  if you're using connection pooling, the  
12. # search path is still set up there, so you might want to   
13. # revert it first  
14. 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. 

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

view plaincopy to clipboardprint?

1. dbschema='schema1,schema2,public' # Searches left-to-right  
2. engine = create_engine(  
3.     'postgresql+psycopg2://dbuser@dbhost:5432/dbname',  
4.     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: 

view plaincopy to clipboardprint?

1. alter role user1 set search_path = "$user", public