document database: entity-relationship diagram, comments, etc.

[bbest]

Hi @mfrants, @edweber, @evsatt,

Here are some tips on how to document the PostgreSQL database...

Use COMMENT ON SQL for tables and columns

• How to Document PostgreSQL Databases, eg:

  -- comment on table
  COMMENT ON TABLE actor IS ‘Table containing actors’;
  
  --comment on column
  COMMENT ON COLUMN actor.actor_id IS ‘id of the actor’;

Entity-Relationship (ER) Diagram

• DBeaver Documentation. Note option to Show Comments and Export as graphML.
• SchemaCrawler - Free database schema discovery and comprehension tool

Use in R

By pulling into R, we can render these in the Quarto book Documenting CalCOFI at calcofi.io/docs and the R package calcofi4r .

Show Comments

• Metadata : from PostgreSQL comments to R labels – r.iresmi.net, eg with custom functions and Hmisc::label():

  # view table cog with Comments under column headers
  View(cog)

  

Show ER Diagram

• networkD3 to visualize interactive networks, including graphML

[bbest]

• r.iresmi.net - Metadata : from PostgreSQL comments to R labels
• pgtools/R/add_pgcomments.R at master · eugejoh/pgtools

[bbest]

• 5  Database – CalCOFI.io Docs
• API endpoints /db_tables, /db_columns in CalCOFI/api: plumber.R
• calcofi4r::cc\_db\_catalog(): Show CalCOFI database catalog as interactive table
• workflow: Clean-up Database