I'm trying to create multiple databases using Flask-SQLAlchemy and its bind feature. Creating a single database using SQLALCHEMY_DATABASE_URI works just fine. But, when I add SQLALCHEMY_BINDS it fails with the following error:

File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  database "customer1" does not exist

This is my config.py file:

class BaseConfig:
    """Base configuration"""
    SECRET_KEY = 'random_key'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    TESTING = False

class DevelopmentConfig(BaseConfig):
    """Development configuration"""
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL')
    SQLALCHEMY_BINDS = { 
        'customer1': 'postgres://postgres:postgres@auth-db:5432/customer1' 
    }

And this is my init.py file

db = SQLAlchemy()
migrate = Migrate()

def create_app():

    # instantiate app
    app = Flask(__name__)
    app_settings = os.getenv('APP_SETTINGS')
    app.config.from_object(app_settings) 

    db.init_app(app)
    migrate.init_app(app, db)

    from project.api.auth import auth_blueprint
    app.register_blueprint(auth_blueprint)

    @app.shell_context_processor
    def ctx():
        return { 'app': app, 'db': db }

    return app

I'm creating my db via the command line through my manage.py file:

@cli.command('recreate_db')
def recreate_db():
    db.drop_all()
    db.create_all()
    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        logging.info(f'{e}')

I'm creating a multi-tenant app and the model I'm trying to create should be created in both databases; my model.py file is like this:

class Widgets(db.Model):
    __tablename__ = 'widgets'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)

I've look all over for an answer and I've found several but even after trying all them, I continue to get this error. Am I missing something? Flask-migrate doesn't work either but I suspect I need to fix this error first. Any help would be appreciated.

1

There are 1 answers

0
S.Hashiba On BEST ANSWER

db.drop_all() and db.create_all() only drop and create all table.(https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.create_all)

If you want to create database (customer1), you should use sqlalchemy-utils. link below is usage.
How to create a new database using SQLAlchemy?