Import ERD into Lucid Chart

Purpose

To outline how to easily visualize the Benchling schemas in LucidChart by exporting from a customer tenant and leveraging the Entity Relationship Diagram (ERD) of LucidChart

Steps

1. In an Insights block paste the SQL Code template below and replace <INSERT_ORG_NAME> (Tip: use command+ F in insights block).
2.  Run the query and copy the output to your clipboard or download the csv if the output is larger than 1000 rows.
3. Lucidchart > Import Data > entity-relationship-diagram
4. Select "From SQL database" in the dropdown
5. Skip choosing a database by clicking NEXT
6. Paste the contents of your clipboard in the paste output in plain text or upload the csv file you downloaded.

with primarykeys as (select distinct 'postgresql' as dbms, 
'warehouse' as table_catalog, 
a.schema_type as table_schema, 
name as table_name, 
'id' as column_name, 
1 as ordinal_position, 
'text' as data_type, null as character_maximum_length, 
'PRIMARY KEY' as constraint_type, 
a.schema_type as table_schema, 
null as table_name , null as column_name 
from <INSERT_ORG_NAME>.schema a 
where archive_purpose$ is null)

select 'postgresql' as dbms, 
'warehouse' as table_catalog, 
a.schema_type as table_schema, 
a.name as table_name, 
b.name as column_name, 
b.position+2 as ordinal_position, 
b.type as data_type, 
null as character_maximum_length, 
case when c.id is not null then 'FOREIGN KEY' else null end as constraint_type, 
c.schema_type as table_schema, 
c.name as table_name, case when c.id is not null then 'id' else null end as column_name
from <INSERT_ORG_NAME>.schema a  
join <INSERT_ORG_NAME>.schema_field b on a.id=b.schema_id
left join <INSERT_ORG_NAME>.schema c on b.target_schema_id=c.id
where a.archive_purpose$ is null and b.archive_purpose$ is null
union all 
select * from primarykeys