Database

We use PostgreSQL as database.

pgadmin

The Rails console is great. But sometimes, it's easier to see database-related things in a nice UI. This is what pgadmin is for. Note that changing data directly there does not ensure any data integrity or data validations. These rules are only enforced on a higher level which is managed by Ruby. Therefore, pgadmin is probably just useful for viewing the tables and the data inside it and to get some statistics. If you really want to, you can of course change the data in pgadmin directly (in a local dev setup!) to see the effects.

Access on localhost:5050 with these credentials:

• Mail: admin@mampf.edu
• Pass: pgmampf

Once you have successfully logged in, you might be prompted to enter a password to connect to the database. Just enter no password and click ok. Note that passwords cannot be exported/imported as config, see here:

Password fields cannot be imported or exported.

That's why you get this "Enter password" popup (but only once when the pgadmin Docker container is freshly initialized).

---

You can find the tables under mampf > Schemas > public > Tables. Then, right-click on a table name and choose "View/Edit Data". You can also execute custom SQL queries.

Entity-relationship diagrams (ERD)

Entity-relationship diagrams (ERD) have long been a very powerful tool for gaining insights and an overview of how the tables in a database are structured and associated to each other. We use Rails ERD to create such diagrams for our codebase.

just utils erd

What to do when the mampf-experimental db schema is broken?

Via git branch -f experimental <commit-id> followed by git push -f origin experimental, we oftentimes put the experimental environment to "random" state to test things out. This might lead to database inconsistencies. For example, recently rails thought 3 migrations have not yet been applied, although they already were. To fix this, proceed systematically.

In order to see the current status of the mampf-experimental database, we regenerate its schema.rb from the actual database via rake db:schema:dump.

Now, let's manually examine what is already included in the database, even though it shows as if the migration wasn't executed: Last version rake db:version shows: 20240215100000. From there on we compare the schema.rb with the migration files to find:

- UP 20240319130000_create_feedbacks
- UP 20240329230000_create_annotations
- UP 20240329230010_add_annotation_related_fields
- DOWN 20240422200000_remove_emergency_link_from_lecture
- DOWN 20240530200000_add_deletion_date_to_user
- DOWN 20240605200000_remove_ghost_hash_from_user
- DOWN ...

This leads to the conclusion that the mmpf-experimental database is currently at 20240329230010 even though rake db:version showed 20240215100000. To fix this:

AI Prompt: My database in rails shows a specific version for rake db:version. However, when regenerating the schema.rb from the actual database via rake db:schema:dump, I find that this version is off by 3 newer migrations. How can I now tell rails that those migrations have already been run?

# in the rails console
ActiveRecord::Base.connection.execute("
  INSERT INTO schema_migrations (version) 
  VALUES ('20240319130000'), ('20240329230000'), ('20240329230010')
")
=> #<PG::Result:random_hash status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=3>

This worked judging from the output of rake db:migrate:status. Then, I did another rake db:schema:dump. The schema.rb now has the correct version: 2024_03_29_230010. From here on, I can run the pending migrations as usual: rake db:migrate. Done, yeah ;)