amazon-web-services flask private subnet vpc

Deploy Flask Microblog in private ec2 with private RDS

for a project with AWS I am trying to install a microblog made with flask on an ec2. The architecture, for now, consists of

A bastion host deployed in public subnet with ip.
An ec2 deployed in private subnet without ip.
A RDS (Mysql) deployed in private subnet without public access.
An EFS mounted on the ec2 of the private subnet.

All elements are in the same VPC.

The security groups are

Bastion Host - SSH(22) inbound rule from my ip
EC2 - SSH(22) inbound rule from bastion host and Mysql/Aurora(3306) inbound rule from RDS
RDS - Mysql/Aurora(3306) inbound rule from private EC2
EFS - NFS inbound rule (2049) from private EC2

I have accessed the bastion by ssh and from the bastion I have accessed the ec2 of the private subnet, I have installed the necessary packages for the application. I have created an .env with the RDS access data(DATABASE_URL=mysql+pymysql://db-user:db-password@db-host:db-port/db- database) and tried to run flask db upgrade, but I get the errors I put below. Is there something that I have missed or that is not configured like this? I am new to AWS. Thanks!!!

[2024-02-21 16:35:56,754] INFO in __init__: Microblog startup
Traceback (most recent call last):
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3282, in raw_connection
    return self.pool.connect()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/impl.py", line 282, in _do_get
    return self._create_connection()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 901, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 358, in __init__
    self.connect()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
    self._request_authentication()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 954, in _request_authentication
    auth_packet = self._read_packet()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1049, "Unknown database 'db-final-database'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.9/site-packages/flask/cli.py", line 1050, in main
    cli.main()
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/decorators.py", line 33, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/flask_migrate/cli.py", line 150, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/usr/local/lib/python3.9/site-packages/flask_migrate/__init__.py", line 111, in wrapped
    f(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/flask_migrate/__init__.py", line 200, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/usr/local/lib/python3.9/site-packages/alembic/command.py", line 403, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.9/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "migrations/env.py", line 94, in <module>
    run_migrations_online()
  File "migrations/env.py", line 76, in run_migrations_online
    connection = engine.connect()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3258, in connect
    return self._connection_cls(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2422, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3282, in raw_connection
    return self.pool.connect()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/impl.py", line 282, in _do_get
    return self._create_connection()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 901, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 358, in __init__
    self.connect()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
    self._request_authentication()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 954, in _request_authentication
    auth_packet = self._read_packet()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1049, "Unknown database 'db-final-database'")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

I have tried this and it seems to connect but does not transmit data

$ nc -vz db-final-database.csfitfdo13cc.us-east-1.rds.amazonaws.com 3306
Ncat: Version 7.93 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.0.68:3306.
Ncat: 0 bytes sent, 0 bytes received in 0.04 seconds.

Solution

The different uses of the term 'database' are the problem here. It's common for them to be confused.

You created an RDS DB Instance running MySQL. That's an AWS resource. It's not a MySQL database.

You need to actually create a MySQL database. To do that, you connect a MySQL client to the RDS DB Instance and then use SQL (or other) to create one or more databases.