Flask-SQLAlchemy integration

[revmischa]

Trying to use this with Flask-SQLAlchemy:

    # configure options for create_engine
    engine_opts = app.config.get("SQLALCHEMY_ENGINE_OPTIONS", {})

    if app.config.get("AURORA_DATA_API_ENABLED"):
        # configure sqlalchemy-aurora-data-api
        rds_secret_arn = app.config.get("RDS_SECRET_ARN")
        aurora_cluster_arn = app.config.get("AURORA_CLUSTER_ARN")
        db_name = app.config.get("DATABASE_NAME")
        conn_url = f"postgresql+auroradataapi://:@/{db_name}"
        app.config["DATABASE_URL"] = conn_url

        # augment connect_args
        connect_args = engine_opts.get("connect_args", {})
        connect_args["aurora_cluster_arn"] = aurora_cluster_arn
        connect_args["secret_arn"] = rds_secret_arn
        engine_opts["connect_args"] = connect_args
    app.config["SQLALCHEMY_ENGINE_OPTIONS"] = engine_opts

Getting this error:

    "errorMessage": "(psycopg2.ProgrammingError) invalid dsn: invalid connection option \"aurora_cluster_arn\"\n\n(Background on this error at: http://sqlalche.me/e/f405)",
    "errorType": "ProgrammingError",
    "stackTrace": [
        "  File \"/var/task/TEMPLATE/commands.py\", line 55, in init_handler\n    from TEMPLATE.app import app\n",
        "  File \"/var/task/TEMPLATE/app.py\", line 4, in <module>\n    app = create_app()\n",
        "  File \"/var/task/TEMPLATE/create_app.py\", line 36, in create_app\n    configure_database(app)\n",
        "  File \"/var/task/TEMPLATE/create_app.py\", line 108, in configure_database\n    raise ex\n",
        "  File \"/var/task/TEMPLATE/create_app.py\", line 105, in configure_database\n    db.session.execute(\"SELECT 1\").scalar()\n",
        "  File \"/var/task/sqlalchemy/orm/scoping.py\", line 162, in do\n    return getattr(self.registry(), name)(*args, **kwargs)\n",
        "  File \"/var/task/aws_xray_sdk/ext/sqlalchemy/util/decorators.py\", line 61, in wrapper\n    res = func(*args, **kw)\n",
        "  File \"/var/task/sqlalchemy/orm/session.py\", line 1268, in execute\n    return self._connection_for_bind(bind, close_with_result=True).execute(\n",
        "  File \"/var/task/sqlalchemy/orm/session.py\", line 1130, in _connection_for_bind\n    engine, execution_options\n",
        "  File \"/var/task/sqlalchemy/orm/session.py\", line 431, in _connection_for_bind\n    conn = bind._contextual_connect()\n",
        "  File \"/var/task/sqlalchemy/engine/base.py\", line 2242, in _contextual_connect\n    self._wrap_pool_connect(self.pool.connect, None),\n",
        "  File \"/var/task/sqlalchemy/engine/base.py\", line 2280, in _wrap_pool_connect\n    e, dialect, self\n",
        "  File \"/var/task/sqlalchemy/engine/base.py\", line 1547, in _handle_dbapi_exception_noconnection\n    util.raise_from_cause(sqlalchemy_exception, exc_info)\n",
        "  File \"/var/task/sqlalchemy/util/compat.py\", line 398, in raise_from_cause\n    reraise(type(exception), exception, tb=exc_tb, cause=cause)\n",
        "  File \"/var/task/sqlalchemy/util/compat.py\", line 152, in reraise\n    raise value.with_traceback(tb)\n",
        "  File \"/var/task/sqlalchemy/engine/base.py\", line 2276, in _wrap_pool_connect\n    return fn()\n",
        "  File \"/var/task/sqlalchemy/pool/base.py\", line 363, in connect\n    return _ConnectionFairy._checkout(self)\n",
        "  File \"/var/task/sqlalchemy/pool/base.py\", line 760, in _checkout\n    fairy = _ConnectionRecord.checkout(pool)\n",
        "  File \"/var/task/sqlalchemy/pool/base.py\", line 492, in checkout\n    rec = pool._do_get()\n",
        "  File \"/var/task/sqlalchemy/pool/impl.py\", line 139, in _do_get\n    self._dec_overflow()\n",
        "  File \"/var/task/sqlalchemy/util/langhelpers.py\", line 68, in __exit__\n    compat.reraise(exc_type, exc_value, exc_tb)\n",
        "  File \"/var/task/sqlalchemy/util/compat.py\", line 153, in reraise\n    raise value\n",
        "  File \"/var/task/sqlalchemy/pool/impl.py\", line 136, in _do_get\n    return self._create_connection()\n",
        "  File \"/var/task/sqlalchemy/pool/base.py\", line 308, in _create_connection\n    return _ConnectionRecord(self)\n",
        "  File \"/var/task/sqlalchemy/pool/base.py\", line 437, in __init__\n    self.__connect(first_connect_check=True)\n",
        "  File \"/var/task/sqlalchemy/pool/base.py\", line 639, in __connect\n    connection = pool._invoke_creator(self)\n",
        "  File \"/var/task/sqlalchemy/engine/strategies.py\", line 114, in connect\n    return dialect.connect(*cargs, **cparams)\n",
        "  File \"/var/task/sqlalchemy/engine/default.py\", line 482, in connect\n    return self.dbapi.connect(*cargs, **cparams)\n",
        "  File \"/opt/python/psycopg2/__init__.py\", line 125, in connect\n    dsn = _ext.make_dsn(dsn, **kwargs)\n",
        "  File \"/opt/python/psycopg2/extensions.py\", line 174, in make_dsn\n    parse_dsn(dsn)\n"
    ]
}

[revmischa]

If I try configuring it purely using environment variables, after I perform my first query:
I verified that AURORA_CLUSTER_ARN is arn:aws:secretsmanager:eu-west-1:11:cluster:TEMPLATE-x in environment
(botocore.errorfactory.BadRequestException) An error occurred (BadRequestException) when calling the BeginTransaction operation: Invalid cluster arn: arn:aws:secretsmanager:eu-west-1:11:cluster:TEMPLATE-x\n[SQL: select version()]"

on AWS lambda

[kislyuk]

Your option names seem to be really confused. For example, you specify an option called DATABASE_URL, but you should be specifying SQLALCHEMY_DATABASE_URI. Your error message is coming from a psycopg2 driver, not from this package. And in the last error you pasted, the cluster ARN is somehow getting mangled with SQL - I have no idea where the SQL is coming from, but there is no place in this package that mixes the two.

The following snippet works for me:

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+auroradataapi://:@/' + os.environ["AURORA_DB_NAME"]
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    "connect_args": {
        "aurora_cluster_arn": "arn:aws:rds:us-east-1:NNNNNNNNNNNN:cluster:database-1",
        "secret_arn": "arn:aws:secretsmanager:us-east-1:NNNNNNNNNNNN:secret:database-1"
    }
}
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username


db.create_all()

[revmischa]

My app converts DATABASE_URL to SQLALCHEMY_DATABASE_URI (for compatibility with platforms like Heroku). I don't know why psycopg2 is being used instead of this driver. The combining of SQL and cluster ARN definitely looks odd, I agree. I thought it may be a bug somewhere. I double-checked the connection string variable and it doesn't include the SQL.