How to create a new schema from a flask application

Question

I have flask application,I'm trying to achieve multi-tenancy by multiple schema for users. When a user sign up, i'm trying to create a new schema with the username from the application and creating new two table in that particular schema.

i can create a new schema into the database using psql console using

postgres=# CREATE SCHEMA schema_name;

How can i create the schema from the flask application?

Sarathlal N · Accepted Answer · 2019-12-02T06:28:16.223

I have achieved the same. My aim was to create a SaSS app with a shared DB & separate schema in Flask.

So when users signup for my premium plan, my code will create a schema for my user.

First import the required modules.

from sqlalchemy import create_engine, MetaData, Table

In the user sign-up route, I have added the below code. The org_id is a random string & it is my schema. Also, I have to create a post table inside that schema. I also need to save the schema name in my user table & it helps to filter result later in that schema only.

# Create schema
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
if not engine.dialect.has_schema(engine, org_id):
    engine.execute('CREATE SCHEMA IF NOT EXISTS %s' % org_id)

    # Create table inside this new schema
    if engine.dialect.has_schema(engine, org_id):
        meta = MetaData()
        post = Table('post', meta,
            db.Column('id', db.Integer, primary_key=True),
            db.Column('body', db.String(140)),
            db.Column('timestamp', db.DateTime, index=True, default=datetime.utcnow),
            db.Column('user_id', db.Integer),
            schema=org_id
        )
        post.create(engine)

Basically I'm a beginner in flask & python. So I'm not sure what is the correct method.

How to create a new schema from a flask application

1 Answers1