I’m currently working on a Flask web application that’ll be used by multiple companies.
Since data across each company needs to be segregated, I used postgresql schemas
to make it all work.
What’s a Schema?
A postgresql database has one or more schemas which, in turn, contain one or more postgresql objects (tables, procedures, types, etc). Schemas effectively serve
as a namespace for objects in a database. When issuing a query, you can either use <tablename> or <schemaname>.<tablename>.
The Schema Search Path
When using an unqualified name (ie - <tablename>), the system looks for
the table on the schema search path and issues queries using the first match.
The current search path can be shown with the following command:
By default, this returns:
The first member of the default search path is the current user name, the second
member is public which is the schema to which tables are added by default.
To update the schema, we can update the schema path with the following command:
Determining the Users Company
Leveraging schemas and the schema search path provides an easy way to segregate
user data by company. All that remains is coming up with a way to determine the
users company on each request. There are several options:
Make the user enter the company name on login.
Store the users and the users company information in the ‘public’ schema
Use subdomains which contain the company name
In the example below, we’ll use option 2. Next week, I’ll write up a post on how
to use option 3 with Flask.