I’m trying to set up postgres as a DB backend. If I run get-value, it returns the correct thing:
<code>$ airflow config get-value database sql_alchemy_conn
postgresql+psycopg2://airflow_user:airflow_pass@localhost/airflow_db
</code>
<code>$ airflow config get-value database sql_alchemy_conn
postgresql+psycopg2://airflow_user:airflow_pass@localhost/airflow_db
</code>
$ airflow config get-value database sql_alchemy_conn
postgresql+psycopg2://airflow_user:airflow_pass@localhost/airflow_db
However, the db command uses sqlite!
<code>$ airflow db migrate
DB: sqlite:////<path to airflow config>/airflow.db
Performing upgrade to the metadata database sqlite:////<path to airflow config>/airflow.db
[2024-09-06T11:58:58.492-0400] {migration.py:215} INFO - Context impl SQLiteImpl.
[2024-09-06T11:58:58.492-0400] {migration.py:218} INFO - Will assume non-transactional DDL.
[2024-09-06T11:58:58.493-0400] {migration.py:215} INFO - Context impl SQLiteImpl.
[2024-09-06T11:58:58.493-0400] {migration.py:218} INFO - Will assume non-transactional DDL.
[2024-09-06T11:58:58.493-0400] {db.py:1674} INFO - Creating tables
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
Database migrating done!
</code>
<code>$ airflow db migrate
DB: sqlite:////<path to airflow config>/airflow.db
Performing upgrade to the metadata database sqlite:////<path to airflow config>/airflow.db
[2024-09-06T11:58:58.492-0400] {migration.py:215} INFO - Context impl SQLiteImpl.
[2024-09-06T11:58:58.492-0400] {migration.py:218} INFO - Will assume non-transactional DDL.
[2024-09-06T11:58:58.493-0400] {migration.py:215} INFO - Context impl SQLiteImpl.
[2024-09-06T11:58:58.493-0400] {migration.py:218} INFO - Will assume non-transactional DDL.
[2024-09-06T11:58:58.493-0400] {db.py:1674} INFO - Creating tables
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
Database migrating done!
</code>
$ airflow db migrate
DB: sqlite:////<path to airflow config>/airflow.db
Performing upgrade to the metadata database sqlite:////<path to airflow config>/airflow.db
[2024-09-06T11:58:58.492-0400] {migration.py:215} INFO - Context impl SQLiteImpl.
[2024-09-06T11:58:58.492-0400] {migration.py:218} INFO - Will assume non-transactional DDL.
[2024-09-06T11:58:58.493-0400] {migration.py:215} INFO - Context impl SQLiteImpl.
[2024-09-06T11:58:58.493-0400] {migration.py:218} INFO - Will assume non-transactional DDL.
[2024-09-06T11:58:58.493-0400] {db.py:1674} INFO - Creating tables
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
Database migrating done!
Why isn’t the db command using the sql_alchemy_conn string and how do I get it to actually create the tables for postgres?
Okay, I think I figured it out. Not sure if this counts as a bug in airflow or I just have a weird setup.
I am setting the sql_alchemy_conn string by setting an environment variable, AIRFLOW__DATABASE__SQL_ALCHEMY_CONN in a .env file. Python is loading that .env using the dotenv package. This is, apparently, working for some of the airflow cli commands, but not db!
Instead, it’s picking up the default connection string from my virtual environment’s site-packages/airflow/config_templates/config.yml (which has default values for all the config options). It’s getting the sqlite connection string from there and not parsing/loading my .env.
In order to force it to use my .env variables, I used this answer. So now I’m running:
<code>$ env $(cat .env | xargs) airflow db migrate
</code>
<code>$ env $(cat .env | xargs) airflow db migrate
</code>
$ env $(cat .env | xargs) airflow db migrate
which is working great.