Final Pieces of DB Migrations

[joncodo]

Previous ticket

https://github.com/merico-dev/lake/issues/806

Related tickets

#994

What works now

• Test run migrations
• Makefile has a new command for make models-test
• models/test has tests for many models
• models/factory has methods to generate models
• Test run on a fresh DB each time
• Migrations.md has more details
• Finish DB tests DB Tests #972
• We need to remove the Automigrate line in each init file. Automigrate is the thing we are trying to remove since it does not support DB migrations. (15 m)
• Migrations get run with make dev (1h)
• Migrations get run with docker compose commands (3h)
• Update the main README.md with a new section on how migrations work (link to Migrations.md)
• Move GitHub init tables into its own migrations folder
• Do this for all plugins (Move all table creation for plugin tables to the respective plugin/migration folder)
• Loop through all plugin folders. Run migrations from all plugin folders

Things left to do

• Get the tests passing in CI (make sure the db is updated and migrated in ci. Run the model tests in CI as a new workflow stage in GitHub actions)
• Try to hook up domain layer migrations with custom db and remove the schema migrations table create in the makefile. Maybe create the schema migrations table with db.automigrate?
• DB URL as a single path and use URL.parse. https://pkg.go.dev/net/url#Parse
• Do not hard code the Test for the test env. Can we just set the DB_NAME.
• When running tests in the makefile, you can just prefix the command with DB_URL=xxx make test
• HANDLE ERRORS IN THE migrations files .go (db/init.go)
• Get make models-test working again

QA workflow: @kevin-kline

User Workflow

Notes from @kevin-kline :
I cannot test the full User workflow because there is an issue building Docker images with my M1 machine: https://docs.docker.com/desktop/mac/apple-silicon/#known-issues

• As a user, I want to run the application from scratch and not have to run anything but docker-compose up. I should see the DB created and all tables present with no data.
• As a user that already has collected data into my database locally, and has run 2 out of 3 migration scripts, I want to run docker-compose up and see the 3rd migration be run. I also do not want to lose any data.

Developer Workflow

Notes from @kevin-kline:
Please read individual issues for more details on QA process and conclusion. This will provide context for changes I pushed to the branch.

• As a plugin developer, I need to see enough docs on how to add migrations to my plugin
• As a plugin developer when I create migrations in my plugin, I want those migrations run with docker-compose
• QA - "As a lake developer, I want migrations run when I run make dev" #1008
• QA "As a developer with no database, I need to be able to create the databases from scratch with a make command" #1009
• QA: "As a developer that wants to run all tests locally, When make test-models is run, a test_db is ready and all migrations are run by the tests." #1010
• As a developer that wants to run model tests, each time the tests are run, there is a fresh db with no data in it to prevent unwanted test case failures

[kevin-kline]

New notes from @joncodo @klesh

• Try to hook up domain layer migrations with custom db and remove the schema migrations table create in the makefile. Maybe create the schema migrations table with db.automigrate?
• Do not hard code the Test for the test env. Can we just set the DB_NAME. No IF(env.TEST == true)
  if config.V.Get("TEST") == "true" {
  connectionString = "merico:merico@tcp(localhost:3306)/lake_test"
  }

Instead
In the makefile:
Create a new .env.test and ask configure to load this file instead

• HANDLE ERRORS IN THE migrations files .go (db/init.go)
• DB URL as a single path and use URL.parse. https://pkg.go.dev/net/url#Parse

[Startrekzky]

Future discussion can be seen in #1057