data.all in local environment starts in a broken db state

[zsaltys]

Describe the bug

With the current 2.0 branch of data.all if we start data.all in a local dev environment then it will start with HALF the tables missing. This is the first issue and requires running alembic migrations to fix.

Upon running alembic migrations we find that they don't run with the error:

sqlalchemy.exc.ProgrammingError: (pg.ProgrammingError) ERROR: column "privateSubnetIds" of relation "vpc" already exists

coming from:

INFO [alembic.runtime.migration] Running upgrade bd271a2780b2 -> 5d5102986ce5, Add subnet ids columns

We can then attempt to drop all the tables with:

python backend/migrations/drop_tables.py

And run the migrations again but then data.all won't start because permission table will be missing permissions such as MANAGE_GLOSSARY, MANAGE_DATASETS

How to Reproduce

Start data.all in dev environment from scratch and attempt to upgrade the local database with latest migrations

Expected behavior

1. 2.0 branch local dev environment should start with all the tables installed
2. If I drop all the tables and attempt to rerun all the migrations, this should work and data.all should start without issues
3. https://awslabs.github.io/aws-dataall/deploy-locally/ should be updated to include explaining the alembic migrations and how to run them to update to the latest state and how to add new ones.
4. Ideally there should be tests that test that data.all can start with migrations rebuilt from scratch

Your project

No response

Screenshots

No response

OS

NA

Python version

NA

AWS data.all version

2.0

Additional context

No response

[dlpzx]

Hi @zsaltys, thanks for opening the issue. I am trying to reproduce the problem.

[dlpzx]

Test 1: build docker images from scratch

After cleaning up all images and everything from Docker locally I recreated the db image.

As a result I obtain 39 tables in the schema dkrcompose:

If I now go to an actual deployment of data.all for the current main branch and check the amount of tables, I see that the aurora database contains 49 tables:

The missing tables in the local deployment are:

1. alembic_version
2. dataset_quality_rule
3. dataset_table_profiling_job
4. group_member
5. item_tags
6. redshiftcluster
7. redshiftcluster_dataset
8. redshiftcluster_datasettable
9. tag
10. user

I am not sure if you are referring to these tables. Most of them are deprecated and we could clean them up with an alembic migration script but they should not be affecting your local deployment

[dlpzx]

Test 2: drop tables and re-create them using alembic locally

Using the commands provided in the makefile I am dropping the local tables locally and re-creating them. I can successfully re-create 49 tables.

Issue found! 🐛
Checking the permissions created in the new schema we can compare it with a real deployment and we see that there are permissions missing.

In the AWS deployment:

[dlpzx]

I am picking up this issue again for with the current version of the code (2.2)

[dlpzx]

1. Start local dockerized application [Needs-more-info]

• When we run docker-compose up the postgres container is created with no tables or schemas
• in the GraphQL container, local_graphql_server.pyuses sqlalchemy declarative_base to create all tables with this function: Base.metadata.create_all(engine.engine)

This means that

• alembic scripts are not executed
• the number of tables depends on the modules that are enabled

@zsaltys I do not need to run alembic when starting the docker containers. There might be mismatches in comparison with the alembic migration tables but I cannot see that any data.all functionalities are affected.
Can you show me the tables that are missing?

2. Drop and rerun all migrations [Investigation in progress]

In the local environment it is not needed to work with alembic. I would only run alembic locally to test migration scripts that need to be added directly with alembic commands. Similar to the scripts that are executed in the CodeBuild test migrations and in the actual db-migration stage.

Having said that, I think we need to do an exercise of clean-up and make sure that the local development starts off with all the tables and permissions needed.

<style> </style>

Tables	In RDS	In dkrcompose
activity	X	X
alembic_version	X
consumptionrole	X	X
dashboard	X	X
dashboardshare	X	X
datapipeline	X	X
datapipelineenvironments	X	X
dataset	X	X
dataset_bucket	X	X
dataset_profiling_run	X	X
dataset_quality_rule	X
dataset_storage_location	X	X
dataset_table	X	X
dataset_table_column	X	X
dataset_table_profiling_job	X
environment	X	X
environment_group_permission	X	X
environment_parameters	X	X
feed_message	X	X
glossary_node	X	X
group	X	X
group_member	X
item_tags	X
keyvaluetag	X	X
notification	X	X
organization	X	X
organization_group	X	X
permission	X	X
redshiftcluster	X
redshiftcluster_dataset	X
redshiftcluster_datasettable	X
resource_policy	X	X
resource_policy_permission	X	X
sagemaker_notebook	X	X
sagemaker_studio_domain	X	X
sagemaker_studio_user_profile	X	X
share_object	X	X
share_object_item	X	X
stack	X	X
tag	X
task	X	X
tenant	X	X
tenant_policy	X	X
tenant_policy_permission	X	X
term_link	X	X
user	X
vote	X	X
vpc	X	X
worksheet	X	X
worksheet_query_result	X	X

3. More documentations [In separate PR]

https://awslabs.github.io/aws-dataall/deploy-locally/ should be updated to include explaining the alembic migrations and how to run them to update to the latest state and how to add new ones. --> fully agree. We can tackle this in a separate PR to the github pages

4. Tests [Done]

It is already implemented in the db-migration tests in the quality gate: deploy/stacks/pipeline.py:464

[dlpzx]

Updates from offline discussion

Action points for this issue:

• Design the best way to develop migration scripts locally
• Update documentation (README or GitHub pages) and add a piece on how to develop migration scripts locally
• Clean-up RDS tables that are no longer needed

[noah-paige]

Handled the above fix in #1033 - closing this issue, please re-open if any additional comments / concerns