Generate Mermaid Diagrams for Your Database

[nelsonic]

https://dev.to/sualeh/how-to-generate-mermaid-diagrams-for-your-database-33bn 👀 Appears to require running a python script ... 💭 Really wish there was automatic SQL import that would generate valid Mermaid ... 🙏

This looks promising: https://github.com/KarnerTh/mermerd and under active development. 🤞

Todo

• [x] Make time to investigate / try this.
• [ ] Ideally we could:
  1. Auto-generate the Mermaid for the Schema on GitHub CI after the Elixir/Phoenix tests are complete.
  2. Upload the resulting Mermaid either to GitHub via automated PR or S3 if that proves too difficult
  3. Have a static web page where anyone can inspect the latest ERD deployed for the App.

[ndrean]

Isn't Ecto.ERD good enough for your usage?

[nelsonic]

@ndrean great question. Probably. Thanks for the reminder. 🎉

[ndrean]

exports to mermaid but I don't like these diagrams. Dot is ok.

[ndrean]

I made a "self reminder post" on this

[nelsonic]

@ndrean great post. Thanks for making it and sharing. 🚀 I've been creating the ERDs manually using the GUI tool ... e.g: /mvp/BUILDIT.md#2-create-schemas ⏳ Knew there had to be a better way; plenty of people face this problem. 👍

[ndrean]

I do this kind of post just for myself because I tend to forget so glad it is understandable. For your use case, it's just 2 lines in the terminal: mix ecto..gen.erd and dot ....

[ndrean]

In return, any guidance on your site for Changesets?

[nelsonic]

Good question. I don't think we have anything yet. Do you have a specific question about them? 💭 I think all our use of changesets is super-basic and we haven't written a tutorial that focusses on them, yet. 🤔 But it highly likely the question will come up so if you have a specific issue you're facing. Please share. 🙏

[ndrean]

Well, a few problems 🤯 When I apply a changeset for an on-the-fly validation (phx-change) with a multiple input form, the error span position is difficult to position. But worse, I don't understand how this works when you then do a phx-submit with an invalidated form. I do something liike changeset.valid? ... true -> save, false -> return changeset. However, if I submit an invalid form, the first submit returns the error, but if I submit again twice an invalid form, then the button disappears... I am almost sure I am not clear at all

def handle_event("up_date", %{"new_event" => %{"event_date" => date}} = _params, socket) do
    changeset = NewEvent.changeset(%NewEvent{}, %{"event_date" => date})
    %{user_id: user_id, place: place} = socket.assigns

    case changeset.valid? do
      true ->
        :ok = async_create_event(%{...})
        {:noreply, socket}

      false ->
        {:error, changeset} = Ecto.Changeset.apply_action(changeset, :insert)
        # changeset = Map.put(changeset, :action, :insert)
        {:noreply, assign(socket, :changeset, changeset)}
    end
  end  

[ndrean]

https://github.com/ndrean/live_map/blob/main/lib/live_map_web/live/forms/new_event.ex is my repo, my first little Elixir app🔥

[ndrean]

First invalid submit:

second submit..no more button!

[nelsonic]

@ndrean looks like a super interesting project. ⭐ Definitely add more context/docs to the README.md both for yourself and for others. 📝

[ndrean]

Humm, no tests, no writings... awful I know but I also have to learn how to write tests!! ..... I have some notes prepared indeed to explain and remember what I did😁, I will put it in the Readme, you are right. The best is to run the app! Right now, this "changeset" problem drives me mad😤

[nelsonic]

Only an insanely curious and time-rich person will run an App that has an incomplete README.md. 💭 I'm curious, but time-poor so I need badges to reassure me that everything is working, ✅ before I spend any time running random projects I discover on the interwebs. 😜

Anyway ... going to try and use your recommended script to create an ERD now. 🤞

[ndrean]

Yes! this badges GitHub Workflow Status too!!! So many things to learn but that's the goal isn't it?

[ndrean]

Added an embryo of explanations/notes in the Readme🥳, then tests, then github flows

[ndrean]

Bug found / fixed !!! 🙏.

It stupidly put (as an experiment but hey why not) the attribute phx-disable-with: "loading..." in the form.

First invalid submit returns nicely:

When I submit twice an invalid form, Phoenix decides to put phx-page-loading disabled and I am blocked:

-> 🪳

Morality: get rid of phx-disable-with

[nelsonic]

Instructions pertinent to the OP of this issue:

1. Add the following line to the deps in your mix.exs:

{:ecto_erd, "~> 0.5", only: :dev},

See: https://github.com/fuelen/ecto_erd

2. Run:

mix deps.get

3. Generate the mermaid diagram with the following command:

mix ecto.gen.erd --output-path=ecto_erd.mmd

See: https://hexdocs.pm/ecto_erd/Mix.Tasks.Ecto.Gen.Erd.html for docs.

[nelsonic]

erDiagram
  apikeys {
    integer id PK
    bytea client_secret
    bytea client_id
    integer person_id
    integer status
    integer app_id
    timestamp inserted_at
    timestamp updated_at
  }
  apps {
    integer id PK
    bytea desc
    timestamp end
    bytea name
    bytea url
    integer person_id
    integer status
    timestamp inserted_at
    timestamp updated_at
  }
  logs {
    integer id PK
    integer app_id
    varchar auth_provider
    bytea email
    varchar msg
    integer person_id
    bytea request_path
    integer status_id
    integer user_agent_id
    timestamp inserted_at
    timestamp updated_at
  }
  people_roles {
    integer id PK
    integer app_id
    integer person_id
    integer role_id
    integer granter_id
    timestamp revoked
    integer revoker_id
    timestamp inserted_at
    timestamp updated_at
  }
  permissions {
    integer id PK
    varchar desc
    varchar name
    integer person_id
    timestamp inserted_at
    timestamp updated_at
  }
  people {
    integer id PK
    varchar auth_provider
    bytea email
    bytea email_hash
    bytea familyName
    bytea givenName
    varchar locale
    bytea password_hash
    bytea picture
    bytea username
    bytea username_hash
    integer status
    integer tag
    integer key_id
    integer app_id
    integer github_id
    timestamp inserted_at
    timestamp updated_at
  }
  roles {
    integer id PK
    varchar desc
    varchar name
    integer person_id
    integer app_id
    timestamp inserted_at
    timestamp updated_at
  }
  sessions {
    integer id PK
    integer app_id
    varchar auth_provider
    timestamp end
    integer person_id
    integer user_agent_id
    timestamp inserted_at
    timestamp updated_at
  }
  status {
    integer id PK
    varchar text
    varchar desc
    integer person_id
    timestamp inserted_at
    timestamp updated_at
  }
  user_agents {
    integer id PK
    varchar name
    bytea ip_address
    bytea ip_address_hash
  }
  schema_migrations {
    integer version
    timestamp inserted_at
  }
  apps ||--|{ apikeys : ""
  apps ||--|{ people_roles : ""
  people ||--|{ people_roles : ""
  people ||--|{ status : ""
  roles ||--|{ people_roles : ""

[nelsonic]

I was - perhaps naively - hoping that the resulting diagram would be reasonably optimised, but it's not. And the default GitHub rendering means the font size is minuscule even fully zoomed in.

Need to re-assess this with fresh eyes in the morning. 💭

[ndrean]

Can you make two pages? the 5 orphans and the other one with your triple joint table plus 2 tables 1<n. Authentication is hell!!

[nelsonic]

Yeah, auth is unnecessarily complicated. We are trying to simplify it. ⏳ Our ONE Environment Variable™ approach in auth_plug is the best we've come up with so far. 💭

Need to fully document our initial implementation of auth so we can re-build it: https://github.com/dwyl/auth/issues/207 🚧 Hence my desire to streamline - hopefully automate - the creation of ERDs. 🤞

[ndrean]

My two cents. What is the usage of the "apps" table where you declare an "owner" (I imagine) with its apikeys in this context? Did you implement an RBAC?

[nelsonic]

@ndrean mega appreciate your feedback on auth, 👌 perhaps we need an issue in that repo instead of this one ... 💭

[ndrean]

Do you really want to manage passwords? Don't you think that a passwordless process with a magic link should be the way to go? and then just produce a JWT or a session header for authorisation? Or also rely on social media credentials (they did the job for you to double-check the email).

[nelsonic]

@ndrean we prioritise "social" (OAuth) login on https://auth.dwyl.com

Again, very much appreciate your feedback: https://github.com/dwyl/auth/issues 🙏

[ndrean]

The last identity login should just be passwordless I believe

[ndrean]

I also wanted to implement the Facebook login, but it seems hell to do server-side, whilst you also need HTTPS to use their snippet🤯

[nelsonic]

For clarity: I despise passwords and they are widely regarded as inferior security. But when you login to your GMail or other account, you still use a password ... 🙄 Encouraging people to click links in their email opens them to Phising ... I know many apps do "Magic Links" and we know how to do them ... even Banking Apps! 🤦‍♂️ But I tend to avoid them in practice because non-technical users should not be encouraged to click links in their email. The worst is links in SMS where Phishing is endemic!

Anyway ... back to the topic ERDs ... Going to stick with doing it manually for now as none of the auto-generation tools produce a good-looking image. 🙄 https://github.com/dwyl/auth/issues/153#issuecomment-1277942468

[ndrean]

You are absolutely right about phising... but the user is warned, it is not a surprise for him. The only experience I had with passwords was just horrible. Looking at your schema. Quite dense 😬 . I understand rendering is not easy. But can't you separate concerns, I mean processes? Say authentication plug, then authorisation plug (you seem to have an RBAC southeast), then log plug northwest, then app credentials plug northeast?