Here is my first attempts with Grist. And I’m still learning the feasibility of Grist to persuade others in my team.
My situation: I’m trying the free plan of Grist (SaaS version). I established the relationship between 2 tables via Reference column type. When exporting into a .grist file and then opening this file with dbeaver, I expect to see this relationship (1-to-many) within the ER diagram of dbeaver. However it just visualizes 2 isolated tables (entity). Is this the expected behavior?
Or how does Grist preserve the relationship between tables in an exported sqlite file?
Hi @sohee, Grist doesn’t set foreign key constraints at the SQLite level when you use references. The nature of the relationship is encoded in metadata tables, the ones whose names start with _grist_, specifically the type column of _grist_Tables_column. There’s definitely a case to be made for setting foreign key constraints when we can, and it would make it easier to generate quick visualizations using external tools - thanks for flagging this.
Interesting. Was just looking at the meta tables. I’m curious why Grist is designed this way. With foreign keys, you are basically replicating the core foreign key constraints of Sqlite. Why not just use foreign key constraints as is? What is the benefit of doing this by encoding a new metadata table?
Hi @paul-grist . Thanks for your reply. So this is an expected behavior from the design of Grist.
Similar to @ddsgadget , i’d love to read more about the reason and benefits why did you design Grist in this way.
With the foreign key constraints at sqlite level, I would simply plug the .grist file into an instance of nocodb to serve as another front-end GUI. This might facilitate the switch to modern database GUI like Grist or Nocodb?
@sohee I’ve looked at all these online databases, and virtually all of them do what Grist does, i.e. ignore foreign key constraints at the database level and implement it at the application level. BTW, Nocodb doesn’t do this, but I can never get nocodb to work, so I gave up on that project. Anyway, I presume they all do this b/c it makes the application UI faster and easier to implement some features that wouldn’t be possible with a foreign key constraint. Is it a bad design choice? Who knows. Personally, it makes me gasp in horror, as I was trained on basic db theory. But, as long as application is battle-tested (and Grist is), I guess it doesn’t matter. I haven’t yet had any issues with Grist regarding this. The main issue, of course, is as you realized: The database isn’t really portable. You can backup the SQLite file, but if you import it into some other program, other than Grist, it is useless b/c none of the relationships are actually set at the database level. My own idea now, since I like Grist so much, and it has made me so productive, is to consider setting up webhooks to back up each document in Grist into a regular postgres database, to maintain all relationships. That also will facilitate some ability to move in case the database gets too large at Grist (100K row limit). Still not sure exactly how it will work, though.
What about a small utility that took a .grist file and updated its schema to include foreign key constraints based on _grist_Tables_column.type? Would that help?
Aha. Thank you @ddsgadget for the insight of the current implementation of this key constraint from other alternative solutions of online database. Beside of Grist, i leaned that only Nocodb allow this type of export and import a database into a frontend GUI. So imho they are already on their premier league now.
@paul-grist Honestly, I intend to pull data from Grist (as a frontend for multi-user data input) then visualize them in Power BI. It means that the relationship between tables can be modeled manually in Power BI. But this manual data modeling in Power BI leads to task-redundancy and error-proneness. Hence an utility or script to update the schema of the grist file based on the metadata table, would be definitely much better. Or Grist can also rewrite the foreign key constraints, in the background, into the schema data of sqlite file when the download request is sent. Both are obviously much better than the current export feature, for my use case
In case either of you know TypeScript - this method runs on a copy of a document just before it is downloaded, and would be a particularly easy spot to add this kind of change:
More generally, I’d guess adding the constraints could be done without too much fuss, especially since SQLite doesn’t enforce them by default. Otherwise we’d need to figure out what to do with invalid data, which in spreadsheet scenarios users tend to expect to be allowed (highlighted as erroneous, but allowed).
I wouldn’t be sure what to do with Reference Lists, as opposed to References, since they aren’t implemented with bridge tables but directly as a list.
I don’t have much experience in coding. So it looks quite advanced for me
Thanks for mentioning Reference List. Reading from your Help Center, it seems to be Grist’s implementation of many-to-many relationship. What do you mean by saying “directly as a list”? Which metadata table in the exported grist file should I look at to understand what did you say about the implementation of reference list?
I mean that the content of a Reference List column is an actual list. When constructing a many-to-many relationship in a database, a bridge table would be a more common way to go - a distinct table where each row has a reference to a source table record and a destination table record.
Yes, I’m trying not to use Reference Lists and sticking to the standard bridge table, so for my use case, anything that can keep the constraints for a Reference (as opposed to a List) would be great.
- this method runs on a copy of a document just before it is downloaded, and would be a particularly easy spot to add this kind of change: