Allow viewing table despite issues with column/grid

[IvanDrag0]

Checklist

• [X] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker compose
  • Budibase Version: latest single image

Describe the bug If something happens to a table that causes Budibase to not be able to load it, the interface shows a screen saying that there's an issue with the grid or column in the table. The issue is that the screen doesn't say what the issue is or what column is the invalid one (the logs do not show this information either). The only way to fix this is to either try to fix it by modifying the JSON of the internal database or "resetting" the table information by fetching tables without the corrupt table and then refetching them with it. The latter causes all links to it to break, forcing the user to recreate all relationships and their tables.

To Reproduce Steps to reproduce the behavior:

1. Create a table in Budibase using an external data source.
2. Remove/modify a column outside of Budibase.
3. Try accessing the table again using Budibase.

Expected behavior Budibase should either still allow the user to load the table and fix the issue or make an easier way to fix it manually using the internal database.

Screenshots If applicable, add screenshots to help explain your problem.

App Export If possible - please attach an export of your budibase application for debugging/reproduction purposes.

Desktop (please complete the following information):

• OS: Linux Mint 21
• Browser Firefox

Additional context Add any other context about the problem here.

[linear[bot]]

BUDI-8456 Allow viewing table despite issues with column/grid

[ConorWebb96]

Hey @IvanDrag0,

If you refetch your tables after modifying it outside of Budibase does this fix your issue? This issue is likely a difference in the schema that Budibase has compared to what the database has. Make sure to keep them up-to-date if you are making changes.

[IvanDrag0]

Hi @ConorWebb96,

Thank you for taking a look at the issue. There are actually multiple issues happening when you start editing the database tables in the "wrong" order. To demonstrate, I created a new blank Budibase app and created a new PostgreSQL database using this sample database.

After fetching all the tables, I've created a new relationship column in the "address" table called "city" with a many-to-one relationship to the "city" table.

This created a new relationship display column called "city" and a new relationship column called "fk_city_address" and a new foreign key called "address_fk_city_address_foreign". Then inside Budibase (haven't done anything external yet), I deleted the "fk_city_address" column (trying to delete the relationship without deleting the display column first). As soon as I did that, I got the following screen when trying to access both the "address" and the "city" tables:

Deleting the relationship column also deleted the "address_fk_city_address_foreign" foreign key.

When I re-fetched both tables, the fk_city_address and the display column from both tables weren't there anymore.

Now if I repeat the process and this time delete the relationship display column (again inside Budibase), the relationship display column from both tables are removed and Budibase doesn't show the relationship under the datasource -> Relationships tab.

However, Budibase still shows the "fk_city_address" column in the "address" table and the table still contains the "address_fk_city_address_foreign" foreign key.

Now if I try to manually recreate the relationship using the datasource -> Relationships tab to get the relationship display column (without deleting the "fk_city_address")

I get a generic error.

The only way for me to get back to where I can use both tables again is to remove the relationship column (losing whatever data I've had there) and re-fetch the tables. Everything I've done so far was within Budibase.

After a bit of digging, it looks like after removing the relationship column "fk_city_address" without removing the relationship display column first, Budibase is still trying to query the database using the removed column (which probably explains the error). Looking at the PostgreSQL logs after Budibase shows the error on both tables.

When trying to access the "address" table it shows:

And when trying to access the "city" table, it shows:

[ConorWebb96]

Hey @IvanDrag0,

I've had a more in-depth look at this, this morning. I had a look through the schema used in the tutorial you linked. Can I ask why you are creating a relationship within Budibase whenever a relationship between these two tables has already been made in the restore.sql?

This line here creates the relationship ALTER TABLE ONLY public.address ADD CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES public.city(city_id);. All you have to do is make Budibase aware of this preconfigured relationship, rather than creating a new one from scratch. This is done in the datasource relationship area you've mentioned previously.

With the above being said, yes this does seem to be a bug. I've created a simple schema below.

CREATE TABLE city (
    city_id SERIAL PRIMARY KEY,
    city_name VARCHAR(100) NOT NULL
);

CREATE TABLE address (
    address_id SERIAL PRIMARY KEY,
    street VARCHAR(255) NOT NULL,
    postal_code VARCHAR(20),
    city_id INT NOT NULL,
    FOREIGN KEY (city_id) REFERENCES city (city_id)
);

1. Import the above schema to a Postgres DB
2. Create a new app in Budibase
3. Connect to the Postgres database in Budibase.
4. Fetch the above tables
5. Create a relationship through add a new column rather than hooking up a pre-existing relationship.
6. Delete this created relationship in Budibase's UI.
7. Leaves the created foreign key field (rather than deleting it)
8. Click your Postgres datasource
9. Add the same relationship as before using the Budibase generated foreign key
10. See error on save.

[IvanDrag0]

Hi @ConorWebb96 ,

Thank you for the detailed analysis.

I've had a more in-depth look at this, this morning. I had a look through the schema used in the tutorial you linked. Can I ask why you are creating a relationship within Budibase whenever a relationship between these two tables has already been made in the restore.sql?

It was mainly to try to quickly replicate the issue I was having to make sure that I wasn't doing anything incorrectly which is why I used an existing schema). Under normal circumstances, I would proceed as you suggested and create a new relationship within Budibase using the existing keys.