How to sync DB code with service layer after switching branches

[Sean Colsen]

Question for Brent or Anish,

1. In authoring this PR, I changed the signature of the msar.search_records_from_table DB function by adding an argument.

2. A failing test told me that I needed to add the new function signature to the all_mathesar_objects table. This was new to me.

   One upon a time, I had written some code to drop all msar functions. I see that we're no longer doing that. And I can see how we're using the all_mathesar_objects to figure out what to drop.

   Okay, fine. I added the new function signature to the table. The test passed. All seemed well.

3. But then I switched back to another branch, absent-mindedly not realizing that my DB code was now out of sync with my service layer code.

4. When I realized I needed to sync my DB code up again, I tried doing it from the UI via our "Reinstall Mathesar Schemas" feature. Previously (when we were dropping all msar functions) this feature worked under this scenario. But now, it didn't work. I should have copied the error messaged, but I was in a hurry, so I didn't. But it took me a while to resolve. I tried restarting Docker. That didn't work. I tried dropping the msar schema manually (thinking I'd be able to coax Mathesar into re-installing it, should it be missing entirely). Nope. That only caused more pain. It turned out that Mathesar didn't even let me remove the database connection because it couldn't connect to it (perhaps an unrelated bug?). In the end I just wipe out my docker state. I probably burned 45 minutes in all.

So my question is: what should I have done? When my DB state is out of sync with my service layer state and I want to go backwards in the chain of development (because I switched branches), what's the quickest and easiest way to get things synced up?

[Brent Moran]

You're correct that we don't support downgrading via the UI, since that would allow the UI to knock out functions that the service layer doesn't know about. This is because we're prioritizing safety over DX in this case. To downgrade:

1. In psql, from the relevant database: 
• DROP SCHEMA msar, __msar CASCADE;
2. In the UI, from the relevant database page: Use the "Reinstall Mathesar Schemas" flow, but manually enter credentials (should be mathesar, mathesar in the dev setup, assuming defaults).

I double-checked that this process works; it does for me. Let me know if you have any other problems.

Brent Moran

Technical Strategy Lead, Mathesar

[Sean Colsen]

Thanks!

This is interesting. I almost tried putting the credentials in there, but I figured, "nah, that shouldn't make a difference." Why does this work? I'm just curious to understand it better.

[Brent Moran]

The trick is, when no credentials are submitted, we determine the credentials to use based on whichever ones own the schemata on the database. When the schemata are missing, there are no such credentials. So, we need to ask the user to provide them. I think that asking in this case is the right thing to do, since we wouldn't want to install Mathesar with any old credentials (that could cause issues due to schema ownership down the road).

One improvement that we've discussed, but not prioritized, is to catch the error and automatically prompt the user to try again with specified credentials.

Brent Moran

Technical Strategy Lead, Mathesar

[Anish Umale]

I deal with this issue based on how many and what changes I am making to function signatures:

• If I am only adding new function(s) in a branch X that doesn't exist in develop and I want to switch, I just drop the functions I added in branch X and the switch to develop is seamless, I don't have to worry about reinstalling.
• While updating a function signature(your case), you could've dropped the new function, switched branches and then hit "reinstall mathesar schemas" like Brent suggested.

Basically, as long as we don't find any objects that are not mentioned in all_mathesar_objects list we are good to drop everything and reinstall.

The trick is, when no credentials are submitted, we determine the credentials to use based on whichever ones own the schemata on the database. When the schemata are missing, there are no such credentials. So, we need to ask the user to provide them.

This is new to me! I usually go about reinstalling schemas by clicking "Connect to an Existing Database" and filling in the existing credentials, mathesar_dev_db, 5432, mathesar, mathesar, mathesar. And because of the way our models are set up a new entry doesn't get created on our Database page.

[Sean Colsen]

Thanks Brent and Anish! This is helpful to understand!

[Zack Krida]

@Anish Umale Could you make an issue for adding this information to some of our documentation, either the wiki or more likely the DEVELOPER_GUIDE.md file?

--

Zack Krida

Product & Community Lead | Mathesar

za...@mathesar.org | Schedule a call