How to make Auto Database Diagrams in Coda (a community journey)

[Logan_Krantz]

Hi team, I am trying to make a pack that generates a diagram of all pages, tables and relationships in a document. (I believe in self documenting software)

Small Question
How can I take a document as a parameter and get a list of all tables. And a detailed list of all columns in that table?

Wider Context
My goal is to create a pack that uses a formula or button to created Entity Relationship Diagram for each of my Docs. (Example of an Entity Relationship Diagrams below)

The actual diagram is not that hard to make using tools like Using the QuickChart GraphViz API | QuickChart wrapped in the Image() function.

The hard part is getting a list of all tables, and a list of all columns. (Ideally using column type or column formulas to determine if there is a relationship)

Closing thoughts

• Its possible there is an existing tool or simpler way. If so please let me know.
• Its possible its more complex and I need to get the API involved. Thoughts?

Cheers for your help!

[Scott_Collier-Weir]

@Rickard_Abraham to the rescue!

Also - you could take in a doc_id or url and then use codas api to extract all the connections - but it would be some heavy work

[Rickard_Abraham]

Thanks! Yeah I’ve got this project going

Analyze the columns in your Coda doc!

Hi everyone! I’ve noticed that there’s a missing element when it comes to visualizing column formulas in a doc. Especially when there are numerous interconnected columns, I often find myself clicking delete on a column simply to figure out What is this column even connected to? Oh, right! This solo project of mine has turned out to be incredibly useful, and I’m excited to share it with you! You can check it out here: Coda Analyzer [codagraph]

It’s hitting every single table with the API.

Unfortunately there’s no support for getting the formulas of external formulas and buttons atm, so I’m waiting for an update there before returning to this project

Formula response for Controls and Formulas endpoint

For the Columns endpoint we have the lovely formula response, letting us see the actual formula. In the Controls and Formulas endpoint, the closest we have is the value response, which is the result of the formula. Please add a formula response to these endpoints as well!

[Eric_Koleda]

Hi @Logan_Krantz - You can get information about the tables and columns in a doc using the Coda REST API:

https://coda.io/developers/apis/v1#tag/Tables/operation/listTables

Packs can make requests to the Coda REST API, and there is a special type of authentication you can use to authenticate with the API:

Authenticating with other services - Coda Pack SDK

[Logan_Krantz]

Hi Team, I am proud to announce that I put on some good music and programmed like a mad man.

You can take a look at my new pack here:

Auto Database Diagrams in Coda Pack, extend Coda... - Coda

Create synced database diagrams:

A super easy to use formula:

Was it worth my Saturday? Let me know what you think

[joost_mineur]

Hello @Logan_Krantz ,

Nice pack - I particularly like the diagrams.

Testing your pack I am running into a few issues:

Does your pack only allow to keep track of the tables/columns of only 1 document

when my docs are larger, I get a time-out error when making a diagram. I don’t know what is happening under the hood, so perhaps this is a limitation we have to live with? Did you try it on larger documents (say, with 75 tables)?

Greetings, Joost

[Eric_Koleda]

Great Pack @Logan_Krantz! I love the attention to detail on the Pack listing, as well as the featured doc. I could see this being useful for a lot of folks.

@joost_mineur - All regular sync tables have a limit of one table per-doc. You can however add multiple syncs to the table, allowing you to bring in different sets of data:

If for UX reasons it is important to have distinct tables then @Logan_Krantz would have to change the table to by a dynamic sync table instead, like the pattern employed by Cross-doc.

[Logan_Krantz]

Hi @joost_mineur , Thanks for having a look.

1. The synced tables can be synced to more then one table at a time. Thank @Eric_Koleda for your quick answer above.

2. Ill have to take a look in the new year at timeout limitations. My biggest doc only had 20 tables but thanks I’d definitely like to support bugger documents. Would you be opposed to removing the data and sharing me a copy of the doc so I can see the errors you are getting?

Finally, @Eric_Koleda Ill have a read of dynamic sync tables and see if its something I am interested in supporting

[joost_mineur]

Helo @Logan_Krantz ,

Unfortunately, stripping the data out of my doc is almost impossible, with a 100+ tables, text colums, hidden stuff, etc.

But I will try to cook up something to demo where it goes wrong. I will be a couple of weeks, but I will try to make some time for this.

Greetings, Joost

[Logan_Krantz]

No problem, it’s a busy time of year. Shoot me a message or post here when you have a sample, and I’ll see what I can do!
Thanks for being my first “stress test“

[Chris_Williams]

@Logan_Krantz The pack errored out for me as well; we have around 60-70 tables with many hidden columns. Let me know once you’re able to apply a fix - I love the idea behind your pack and know it will help our team understand connection points between tables.

[Logan_Krantz]

Hey Chris, thanks for having a play.
With 70 tables I assume the GET request is simply exceeding the maximum url length.

I should be able to update it to a POST request without this limitation in the new year.

Can you send me a screenshot of your error message?

[Chris_Williams]

@Logan_Krantz Sure, here’s a screenshot:

[Scott_Collier-Weir]

Late to the party!

Love what @Logan_Krantz and @Rickard_Abraham are doing but I wanted to jump in with my own spin!

I just launched a DB Diagram pack that enables you to create an interactive and editable database diagram.

Check out the pack here

Database Diagram Pack, extend Coda with Database... - Coda

Or learn how to use it here

[Rickard_Abraham]

Heck yeah!!
dbdiagram.io looks amazing, love that you can interact with it (you couldn’t do that with eraser.io which I used)

[Scott_Collier-Weir]

The biggest downfall is that you have to do the manual step of copy/pasting every time you want an UPDATED diagram.

I really wanted dbdiagram to have an API so I could send the code snippet generated by the pack to dbdiagram and have it generate the image for me.

But it doesn’t

If you find any other service that generates db diagrams using DBML (database markup language) and has an API, let me know! We should do a collaborative!

[Rickard_Abraham]

Ah right, still has great potential! Eraser.io has recently gotten an API but it’s not interactive

[Logan_Krantz]

I am loving the progress we are making in automatic coda diagram!
Thanks Scott! I love the look of DBML as an alternative to the older DotML that I am using.
Souch talent in this thread

[Logan_Krantz]

Thanks Chris for the image. It looks like all the API calls are exceeding the maximum time coda is willing to wait for the pack.

I can to look into Async API calls to speed it up and extending that time limit as well.

[Rickard_Abraham]

Haha yeah! The three diagramigos