Export E-R Diagram with SQLFlow REST API

Gudu SQLFlow is an analysis software for analyzing SQL statements and discovering data lineage relationships. It is often used with metadata management tools and is a basic tool for enterprise data governance. Check this introduction blog if you don’t know much about Gudu SQLFlow.

SQLFlow is capable to convert SQL to Entity-Relation(ER) Diagram as well as to visualize the relations between tables and fields so that you can quickly understand the design model of the database and conduct efficient team communication. You can either go to https://sqlflow.gudusoft.com to visualize your database with E-R Diagram or you can use SQLFlow REST API to generate and to export the ER Diagram.

In this blog, we will show you how to generate the E-R diagram with SQLFlow REST API.

The API Endpoint

The related SQLFlow Api to generate ER diagram image is:

/sqlflow/generation/sqlflow/erdiagramSelectGraph/image

This API can directly take SQL statement text as input or it can read session id and generate the ER diagram based on the given session Id. Check the following parameter table:

Parameter Name	Type	Description
userId	string	the user id of sqlflow client
token	string	The token is only used when connecting to the SQLFlow Cloud server, it is not used when connect to the SQLFlow on-premise version.
sessionId	string	graph session Id
sqltext	string	SQL statement text
jobId	string	give the job Id if need to use the job settings
database	string	database
dbvendor	string	database vendor
defaultDatabase	string	default databse when there’s no metadata
defaultSchema	string	default schema
defaultServer	string	default server
normalizeIdentifier	boolean	whether normalize identifier
server	string	server to filter
table	string	table to filter
schema	string	schema to filter

Let’s say from database, we have got the following DDL:

CREATE VIEW vsal 
AS 
  SELECT a.deptno                  "Department", 
         a.num_emp / b.total_count "Employees", 
         a.sal_sum / b.total_sal   "Salary" 
  FROM   (SELECT deptno, 
                 Count()  num_emp, 
                 SUM(sal) sal_sum 
          FROM   scott.emp 
          WHERE  city = 'NYC' 
          GROUP  BY deptno) a, 
         (SELECT Count()  total_count, 
                 SUM(sal) total_sal 
          FROM   scott.emp 
          WHERE  city = 'NYC') b 
;

INSERT ALL
	WHEN ottl < 100000 THEN
		INTO small_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 100000 and ottl < 200000 THEN
		INTO medium_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 200000 THEN
		into large_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 290000 THEN
		INTO special_orders
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

create table scott.dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
);

create table scott.emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno) 
);

Generate ER diagram with SQL text

To generate ER diagram from SQL statement text, simply put the text in sqltext parameter.

Sample:

curl --location 'https://<sqlflow url>/gspLive_backend/sqlflow/generation/sqlflow/erdiagramSelectGraph/image' \
--header 'accept: image/*' \
--form 'userId="gudu|0123456789"' \
--form 'dbvendor="dbvoracle"' \
--form 'sqltext="CREATE VIEW vsal 
AS 
  SELECT a.deptno                  \"Department\", 
         a.num_emp / b.total_count \"Employees\", 
         a.sal_sum / b.total_sal   \"Salary\" 
  FROM   (SELECT deptno, 
                 Count()  num_emp, 
                 SUM(sal) sal_sum 
          FROM   scott.emp 
          WHERE  city = '\''NYC'\'' 
          GROUP  BY deptno) a, 
         (SELECT Count()  total_count, 
                 SUM(sal) total_sal 
          FROM   scott.emp 
          WHERE  city = '\''NYC'\'') b 
;

INSERT ALL
	WHEN ottl < 100000 THEN
		INTO small_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 100000 and ottl < 200000 THEN
		INTO medium_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 200000 THEN
		into large_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 290000 THEN
		INTO special_orders
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

create table scott.dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
);

create table scott.emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno) 
);"'

Hint: SQLFlow On-Premise is used in the above capture, hence token is not required and only userId is given in param. Please refer to this document to check how to get the userid and the token.

Result:

Generate ER diagram using session Id

Create SQLFlow Job

The following endpoint is to create a SQLFlow:

/sqlflow/job/submitUserJob

Wait few seconds and If we check our job list, we should have this demoJob2 in our job success list. You can verify that ethiter go to the SQLFlow interface or use another SQLFlow REST API /sqlflow/job/displayUserJobsSummary.

Great! After checking the SQLFlow web, we have confirmed the SQLFlow Job is succeeded.

Retrieve Data Session

Now we have our Job created and we need select our data now. To do that, invoke:

/sqlflow/job/displayUserJobGraph

Give the Job Id as the input and in the response of the above API, we will have the sessionId:

Generate ER diagram

With the sessionId, now we can invoke /sqlflow/generation/sqlflow/erdiagramSelectGraph/image to generate the selected data element.

curl --location 'https://<sqlflow url>/gspLive_backend/sqlflow/generation/sqlflow/erdiagramSelectGraph/image' \
--header 'accept: image/*' \
--form 'userId="gudu|0123456789"' \
--form 'dbvendor="dbvoracle"' \
--form 'sessionId="10fde6b3e234dbd3db95f27d8af255f64c863a096131ef416d998f0969534057_1681121651772"'