My Hacktober 2022 Contributions mostly SQL DDL uml extensions

Something that has been on my TODO list is to write a tool for easy sql DDL generation using some open sourced tools.

I really like working with UML and entity relations for modeling tables and relationships in a database schema. Sadly only lucidcharts had the best functionality for exporting SQL DDL for easy create tables. There are some options specific to the database you are using, but not easily cross database type.

I found out that mermaid had an erDiagram type and that there are UML and entity relation objects in https://www.diagrams.net/ which uses drawio.

I worked on making changes to support easy importing of a database schema and also exporting it back out as sql DDL, pretty happy with my results.

Packages

• Mermaid sql package @funktechno/little-mermaid-2-the-sql

  • I added in the samples how to generate a mermaid erDiagram from an existing database using schemacrawler.
  • sample er diagram

    erDiagram
    %% comment 1
    Persons {
        int PersonID PK "NOT NULL"
        varchar255 LastName
        varchar255 FirstName
        varchar255 Address
        varchar255 City
    }
  
    %% comment 2
    Orders {
        int OrderID PK "NOT NULL"
        int PersonID FK "NOT NULL"
    }
  
    Persons ||--o{ Orders : "[Persons.PersonId] to [Orders.PersonId]"   
  

  • sample output
    

    CREATE TABLE "Persons" (
    "City" varchar(255),
    "Address" varchar(255),
    "FirstName" varchar(255),
    "LastName" varchar(255),
    "PersonID" int NOT NULL,
    PRIMARY KEY("PersonID")
    );
    
    CREATE TABLE "Orders" (
    "PersonID" int NOT NULL,
    "OrderID" int NOT NULL,
    PRIMARY KEY("OrderID"),
    FOREIGN KEY ("PersonId") REFERENCES "Persons"("PersonId")
    );
    

  • Tool for converting a SQL DDL to simple json models for easy import into drawio @funktechno/sqlsimpleparser

Drawio pull requests

• I put in 2 pull requests into drawio to enable sql import and export, from their readme they are closed contributions. So I hope that these changes make them in regardless. Very useful, you can also run the plugins manually. I used the desktop github repo for testing and running an electron debugger.
• pull requests
  1. export sql DLL plugin
     • Preview
  2. improved sql parser for sql import plugin
     • Preview