Skip to content

Instantly share code, notes, and snippets.

@neverendingqs
Last active February 3, 2020 21:53
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
Lucidchart MySQL ERD Import Query
SELECT
'mysql' dbms,
t.TABLE_SCHEMA,
t.TABLE_NAME,
c.COLUMN_NAME,
c.ORDINAL_POSITION,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
-- Add this
c.IS_NULLABLE,
--
n.CONSTRAINT_TYPE,
k.REFERENCED_TABLE_SCHEMA,
k.REFERENCED_TABLE_NAME,
k.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.COLUMN_NAME=k.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME AND k.TABLE_SCHEMA=n.TABLE_SCHEMA AND k.TABLE_NAME=n.TABLE_NAME
WHERE t.TABLE_TYPE='BASE TABLE' AND t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql','performance_schema');
@neverendingqs
Copy link
Author

It seems like you can modify the CSV before you import it into Lucidchart to get it to show nullable information. E.g.:

function process(row) {
  const { DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE } = row;

  const charPart = CHARACTER_MAXIMUM_LENGTH === null
    ? ''
    : `(${CHARACTER_MAXIMUM_LENGTH})`;

  const nullablePart = IS_NULLABLE === 'YES'
    ? '?'
    : '';

  row.DATA_TYPE = `${DATA_TYPE}${charPart}${nullablePart}`;

  row.CHARACTER_MAXIMUM_LENGTH = null;
  delete row.IS_NULLABLE;

  return row;
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment