My Hacktober 2022 Contributions mostly SQL DDL uml extensions
Tags: hacktoberfest, sql, mermaid, drawio
Published: 2022-10-12 01:14:40

---
title: My Hacktober 2022 Contributions mostly SQL DDL uml extensions
published: true
description: Hacktoberfest 2022 contributions to SQL DDL and uml diagraming.
tags: #hacktoberfest #sql #mermaid #drawio
cover_image: https://res.cloudinary.com/practicaldev/image/fetch/s--4MsBCP27--/c_imagga_scale,f_auto,fl_progressive,h_420,q_auto,w_1000/https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0timznbif5rmc0nlb29b.png
---

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](https://www.npmjs.com/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
      ```mermaid
      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
        ```sql
        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](https://www.npmjs.com/package/@funktechno/sqlsimpleparser)
    
## [Drawio](https://github.com/jgraph/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](https://github.com/jgraph/drawio/pull/3092)
      * Preview
       ![sql export example](https://user-images.githubusercontent.com/6679425/194928919-aa1324e7-3f4b-4c23-be75-9f2282f22018.png)
  1. [improved sql parser for sql import plugin](https://github.com/jgraph/drawio/pull/3091)
      * Preview
      ![sql import fk preview](https://user-images.githubusercontent.com/6679425/194942883-30e74605-d16b-4195-b186-ab872bd5c65b.png)