We are looking to compare the database schema generated by these migrations with the design of the database. We can do this using the ERD generated from the API database and comparing it to the target ERDs created in the Dataflow database.
The dataflow/target ERD is split into two files. The first file has the capital planning tables. The second file has the city council and community district tables. This second file also shows a borough table; it is a placeholder for the borough table as it exists in the API database.
The way to get your API Database to have these changes depends on whether you already had it set up. If you are creating your database from scratch, then simply follow the setup instructions in the readme. This version of the database will automatically be applied during setup. If you already had the database set up and need to integrate these changes then 1) run the database with docker compose up and then 2) apply these changes with npm run drizzle:migrate. A good indicator as to whether you already had the database set up is whether you have a db-volume folder. If you would like to start from scratch, remove this folder and go through the set up process from the start. If you get a connection error when running these migrations, check whether the database port in your .env matches the database local-machine port configured in the compose file (8010).
After setting up your API database with these changes, use pgAdmin to generate an ERD. After connecting pgAdmin to your database, right click on the 'zoning' database and select the option that mentions creating an ERD. From here, compare the API ERD you generated in pgAdmin with the ones on the data-flow branch. There are deviations in some of the column names. The API database prefixed 'project' and 'commitment' with 'capital'. However, the structures should match.
Note on commits
We typically have 1 commit per ticket/PR. I would like to keep this as two commits when merging to main. The first commit contains the schema changes to clean up small errors. The second commit contains the actual migrations.
Generate migration for capital planning schemas
closes #216
How to review
We are looking to compare the database schema generated by these migrations with the design of the database. We can do this using the ERD generated from the API database and comparing it to the target ERDs created in the Dataflow database.
The dataflow/target ERD is split into two files. The first file has the capital planning tables. The second file has the city council and community district tables. This second file also shows a borough table; it is a placeholder for the borough table as it exists in the API database.
The way to get your API Database to have these changes depends on whether you already had it set up. If you are creating your database from scratch, then simply follow the setup instructions in the readme. This version of the database will automatically be applied during setup. If you already had the database set up and need to integrate these changes then 1) run the database with
docker compose upand then 2) apply these changes withnpm run drizzle:migrate. A good indicator as to whether you already had the database set up is whether you have adb-volumefolder. If you would like to start from scratch, remove this folder and go through the set up process from the start. If you get a connection error when running these migrations, check whether the database port in your.envmatches the database local-machine port configured in the compose file (8010).After setting up your API database with these changes, use pgAdmin to generate an ERD. After connecting pgAdmin to your database, right click on the 'zoning' database and select the option that mentions creating an ERD. From here, compare the API ERD you generated in pgAdmin with the ones on the data-flow branch. There are deviations in some of the column names. The API database prefixed 'project' and 'commitment' with 'capital'. However, the structures should match.
Note on commits
We typically have 1 commit per ticket/PR. I would like to keep this as two commits when merging to main. The first commit contains the schema changes to clean up small errors. The second commit contains the actual migrations.