azimuttapp / azimutt Goto Github PK

Currently Azimutt projects are saved into browser localStorage or IndexedDB. This is nice for privacy but not for collaboration.

The goal is to allow for some remote storage, either in Azimutt servers or private servers (to keep privacy).

Today Azimutt is a web app which parse a database schema and allow to explore it.
That's cool but it requires to open a browser, go to the website, click explore and upload the schema.
Not really a streamlined process when you are working on your app.

You can improve this by installing the progressive web app, so you can launch the app from your desktop and land directly on the app.

We can bring this one step further with a CLI installation. The first time you do a npm install -g azimutt and then you can do azimutt open structure.sql with the relative path of the file a load it. Or just azimutt open to launch the app.

A CLI can also open some local use cases such as extracting the schema from a real database, which is not possible right now with a web app. For example azimutt open postgresql://localhost/test?user=fred&password=secret&ssl=true could open your database, extract the schema and load it to Azimutt.

What do you think about this ?
Do you see any other way of having Azimutt close to you when you need it ?

Please add 👍 reaction if you want this!

Currently Azimutt focus mainly on the database schema, ignoring completely the data inside.
It's easier in term of performance but also privacy and we already have a lot to do ^^

But having access to the data can allow Azimutt to leverage new features:

• search can look into the values so it can be easier to find the wanted table/column
• data statistics can give you interesting information:
  • for string: cardinality (enum values for example), % of null values, some samples
  • for numbers: min, max, average

As such information can be huge and everything is in the browser, Azimutt can only keep a sampling, for example 100 random values for each table. This will not be a perfect information but still can help you a lot.

Such data can be found in the schema.sql if you choose to export it with the values (as INSERT INTO commands) or can be read in the database in the case of the CLI (#25).

If you want this feature, please leave a 👍 reaction on this issue.

Right now Azimutt is browser based and still quite capable (PWA available also). But still quite limited also.

The goal of this issue is to package and distribute Azimutt in a desktop app (Electron or Tauri) to unlock interesting features:

• local database connection
  • no more need to proxy schema extraction on server
  • perform data search
  • display table/column data stats
  • run your queries
• read local files (so Azimutt could automatically refresh the source from local files)
• write local files (so Azimutt schema could be saved in specific place, dropbox or git repo, to allow collaboration)

Currently Azimutt display the exact type for each column of the table, this is nice but sometimes not necessary and take a lot of space in the UI, for example character varying(255) or timestamp without time zone.

This issue suggest to add a display setting so users can choose to display simplified types instead of exact ones.
Having a way to reveal the underlying exact type would be nice but I don't see how as tooltip is already taken by the default value.

Here are a few conversions:

bigint => Int
bigint[] => Int[]
boolean => Bool
bytea => Byte
character varying(xx) => String
character varying(xx)[] => String[]
date => Date
double precision => Float
inet => IP
integer => Int
json => Json
jsonb => Json
numeric => Float
smallint => Int
text => String
time without time zone => Time
timestamp without time zone => DateTime
uuid => Uuid

Use Azimutt as a browser extension: when you are on github and watch a .sql file (ex: gospeak.sql), add a third button next to "Raw" and "Blame" with "Azimutt" label to replace the file content with an Azimutt browser.

Could be done on other locations also.

If you like the idea please send a 👍 reaction on this issue so it could be prioritized.

I was just wanted to let you know that there is a runtime error when trying in Firefox:

Currently Azimutt is mostly a database schema explorer. We can "easily" add a database builder to allow people to create or evolve diagrams right into Azimutt.

As Azimutt already have multiple source, the goal here is to allow to create/edit user source written in AML (see: https://azimutt.app/blog/aml-a-language-to-define-your-database-schema)

For that we will need a text editor (https://microsoft.github.io/monaco-editor for example) and an AML parser.

Lot of UI adjustments will be needed also:

• show tables on the diagram when source is editing
• add virtual relations to a chosen source

In the goal of having the right tool at the right moment, I think it would be quite nice to have an IDE plugin that could open a SQL file in an Azimutt explorer. Just like a markdown rendering plugin.

In addition, like the CLI, this plugin could be able to load the schema of a real database, simplifying the usage.

If you like the idea, please send the 👍 reaction but also comment with the IDE you want to see supported (or react on existing comment)

I managed to import my schema with no issue, but I can't move some tables that I display which makes it hard to create a layout (especially when they appear on top of each other).

There are no errors appearing in the console as far as I can tell.

Popup window navigation buttons showing errors are not functional

Tested with Firefox

When exploring new things, you often discover interesting or unexpected things, having a place where to write them could help a lot later investigations to not start over the same investigations.
Also, as not everything can be parsed and immediately loaded into the schema, it could be very helpful to be able to add some notes about such information.

If you think this should be prioritized in the Azimutt roadmap, please let us know by adding a 👍 reaction to this issue. And if you have specific needs in mind, don't hesitate to comment here with the kind of information you want to add. Maybe we can go a bit further than a basic textarea. Do you need key/value properties ? Rich text formatting ?

Today Azimutt uses foreign keys as relations between columns (and so tables).
This is great but they are not the only way to do joins.
Some possible joins are either not declared (cf wordpress schema) or could not be declared, for performance reasons or flexibility (ex: polymorphic relations in Rails).

Virtual relations are relations present in the database but not declared as foreign keys in the schema.

With virtual relations, a user should be able to create new relations in his/her schema:

• click on "create virtual relation"
• click on the column referencing an other one
• click on the referenced column (both tables should be visible)

With this, columns could have multiple outgoing relations (ex: Rails polymorphic relations).

If you like this idea and want to see it in Azimutt, please leave a 👍 reaction to this issue.
Also don't hesitate to as questions or suggest different implementations on this.

When looking at a database, you often need different levels of abstraction.

Today Azimutt let you search and display tables and follow relations, showing tables with their columns, even if they are hidden.
The idea here is two have an other visualization mode, showing only the tables names and their relations.
With this, you could look a much more tables and better visualize the clusters or links between some of them.

If you think this could be useful, please add a 👍 reaction to this issue

Often some tables work in close relation with some others. In fact, it's quite common to have a feature or a business domain which use several tables.
It could be useful to group such tables in a cluster and represent them visually.

MySQL Workbench has a similar feature:

Such clusters could also hide tables inside and have their relations linked to it directly (hide useless details for a clearer understanding).

Other inspirations:

• https://twitter.com/dbdiagram/status/1255356100402176000
• https://twitter.com/dbdiagram/status/1133213114458038275

If you think this feature can be useful for you, please react with a 👍 to let us know.

Currently table colors are defined by their name, which is nice to get started but to have a beautiful diagram it could be useful to allow users to choose the wanted color.

In a first iteration, let's allow only predefined colors so it will be easy to implement.

There is a feature to easily hide columns using specific name, regexes or through 'intelligent' checkboxes:

I can easily hide a new column or a set of column, but clearing the text input or unchecking one of the checkboxes will not make it re-appear on the screen. I have to reload the page.

That can be especially problematic when writing a regex like .+_at, it will have hidden all columns for good when I type in the +.

Using Firefox 93 on Fedora 35 with wayland.

A very interesting idea from @Yarith in his feedback is to move dragged tables to the top. We could also apply this to selected ones.
This would allow to very easily see a table without breaking the layout.

What I suggest: when a table is dragged or selected, increase its z-index by the table count so it's above all the others.

My schema for a SQL Server database contains ADD CONSTRAINT for primary and foreign keys. When I try to import this schema, I get several error messages:

• Can't parse add constraint: 'ADD CONSTRAINT [PK_dbo.Travels] PRIMARY KEY ([Travel_ID])'
• Can't parse add constraint: 'ADD CONSTRAINT [FK_dbo.Travels_dbo.Booklets_Booklet_ID] FOREIGN KEY ([Booklet_ID]) REFERENCES ...
• Statement not handled: '/* ---------- Travels */ CREATE TABLE [Travels] ( [Travel_ID] int ...

A subset from my schema if you want to test:

/* ---------- Booklet
*/

CREATE TABLE [Booklets] (
  [Booklet_ID] int identity(1,1) not null
, [Title] nvarchar(100) not null
, [Year] nvarchar(20) not null
, [Notes] ntext
, [IsReadOnly] bit not null
);

ALTER TABLE [Booklets] 
    ADD CONSTRAINT [PK_dbo.Booklets] PRIMARY KEY ([Booklet_ID]);


/* ---------- Travels
*/

CREATE TABLE [Travels] (
  [Travel_ID] int identity(1,1) not null
, [Booklet_ID] int not null
, [Position] int not null
, [Title] nvarchar(100) not null
, [Subtitle] nvarchar(100)
, [TravelType] int not null
, [Notes] ntext
);

ALTER TABLE [Travels] 
    ADD CONSTRAINT [PK_dbo.Travels] PRIMARY KEY ([Travel_ID]);

ALTER TABLE [Travels] 
    ADD CONSTRAINT [FK_dbo.Travels_dbo.Booklets_Booklet_ID] FOREIGN KEY ([Booklet_ID]) REFERENCES [Booklets]([Booklet_ID])
    ON UPDATE NO ACTION ON DELETE CASCADE;

They are 2 quite different things under the hood, so I guess they also should look different.

Hello 👋,

I am curious about this project. I am trying to launch it on my machine in order to explore some databases. Badly, CSS issues visibly occur after launching elm-spa server:

For information:

• installation/start logs.
• my current commit is 55246b2.

Maybe that the problem exists between chair and keyboard 😇. Thanks in advance for your help.

The close button of the pop-up showing the errors could be hard to access

Add a view button on source to show its content (mainly tables & relations)

Parsing my postgres schema generated a large number of errors. None of them were important so I was happy to ignore them all but I was left with a large number of notifications to close. A close all button would be really handy.

(happy to open a PR if we agree the design)

From https://twitter.com/ergestx/status/1525458482085912576

Let's implement an undo/redo algorithm 🎉

Two buttons should be added in the command bar (bottom right), and keyboard shortcuts should be ctrl+z for undo and ctrl+maj+z for redo.
Use: https://package.elm-lang.org/packages/elm-community/undo-redo/latest

If you see any other shortcut that could trigger an undo or redo, let's comment here ;)
If you want this feature, please leave a 👍 reaction on this issue.

I have comments on my tables with SQL code like
COMMENT ON TABLE foo IS 'bar';

If bar is a very long comment it often overlaps visually with other tables and sometimes the comment is hidden behind those other tables. Since the comment is only shown on mouse hover, it should always be on top of everything else, so the user can actually read the whole comment.

As title says, the search result dropdown list does not go away when clicking on the main canvas, only when clicking on the menu bar.

Cool app btw!

ddl.zip

Seems everything is defined in the title ^^

Just one thing, I think having this state as query string or hash would be nicer than path.

Tested on Google Chrome Version 94.0.4606.81

In the settings sidebar, there is a list of source with delete and refresh buttons. This issue propose to add a "view" button. This button would open a modal and list all the tables and relations (in different tabs) for this source.

This would be useful to know what is inside each source, especially for the user defined ones!

Looked for it but can't find it.

Hope it's clear enough ;)

The current search algorithm is based on custom heuristics based on matching part in table and column names but also number of columns or length of the name.
It was great so kick-start it but now the app have matured, we need a better search.

The plan is to use a full-test search library (ex: flexsearch, elm-text-search or elasticlunr) to power it, indexing:

• table name
• table comment
• column names
• column comments
• column values (when integrated with INSERT INTO commands)
• key names (primary key, foreign key, index, unique, check)

This search engine will look for tables, but also columns and relations, depending on the settings.

Please leave a 👍 reaction if you think this is important and should be prioritized.

I dumped the schema from TaskLite and tried to load it, but get a lot of errors 😅. It's not very long, but I guess I'm making good use of SQLite specific features 😛.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

CREATE TABLE `task_to_tag` (
`ulid` text not null primary key,
`task_ulid` text not null,
`tag` text not null,
foreign key(`task_ulid`) references `tasks`(`ulid`),
constraint `no_duplicate_tags` unique (`task_ulid`, `tag`)
);

CREATE TABLE `task_to_note` (
`ulid` text not null primary key,
`task_ulid` text not null,
`note` text not null,
foreign key(`task_ulid`) references `tasks`(`ulid`)
);

CREATE TABLE IF NOT EXISTS "tasks" (
  ulid text not null primary key,
  body text not null,
  modified_utc text not null,
  awake_utc text,
  ready_utc text,
  waiting_utc text,
  review_utc text,
  due_utc text,
  closed_utc text,
  state text check(state in (NULL, 'Done', 'Obsolete', 'Deletable')),
  group_ulid text,
  repetition_duration text,
  recurrence_duration text,
  priority_adjustment float,
  user text,
  metadata text
);

CREATE VIEW `tasks_view` as
select
`tasks`.`ulid` as `ulid`,
`tasks`.`body` as `body`,
`tasks`.`modified_utc`as `modified_utc`,
`tasks`.`awake_utc` as `awake_utc`,
`tasks`.`ready_utc` as `ready_utc`,
`tasks`.`waiting_utc` as `waiting_utc`,
`tasks`.`review_utc` as `review_utc`,
`tasks`.`due_utc` as `due_utc`,
`tasks`.`closed_utc` as `closed_utc`,
`tasks`.`state` as `state`,
`tasks`.`group_ulid` as `group_ulid`,
`tasks`.`repetition_duration` as `repetition_duration`,
`tasks`.`recurrence_duration` as `recurrence_duration`,
group_concat(distinct `task_to_tag`.`tag`) as `tags`,
group_concat(distinct `task_to_note`.`note`) as `notes`,
ifnull(`tasks`.`priority_adjustment`, 0.0)
  + case   when `awake_utc` is null then 0.0
  when `awake_utc` >= datetime('now') then -5.0
  when `awake_utc` >= datetime('now', '-1 days') then 1.0
  when `awake_utc` >= datetime('now', '-2 days') then 2.0
  when `awake_utc` >= datetime('now', '-5 days') then 5.0
  when `awake_utc` <  datetime('now', '-5 days') then 9.0
 end
  + case   when waiting_utc is null then 0.0
  when waiting_utc >= datetime('now') then 0.0
  when waiting_utc <  datetime('now') then -10.0
 end
  + case   when review_utc is null then 0.0
  when review_utc >= datetime('now') then 0.0
  when review_utc <  datetime('now') then 20.0
 end
  + case   when due_utc is null then 0.0
  when `due_utc` >= datetime('now', '+24 days') then 0.0
  when `due_utc` >= datetime('now',  '+6 days') then 3.0
  when `due_utc` >= datetime('now') then 6.0
  when `due_utc` >= datetime('now',  '-6 days') then 9.0
  when `due_utc` >= datetime('now', '-24 days') then 12.0
  when `due_utc` <  datetime('now', '-24 days') then 15.0
 end
  + case   when state is null then 0.0
  when state == 'Done' then 0.0
  when state == 'Obsolete' then -1.0
  when state == 'Deletable' then -10.0
 end
  + case count(`task_to_note`.`note`)
      when 0 then 0.0
      else 1.0
    end
  + case count(`task_to_tag`.`tag`)
      when 0 then 0.0
      else 2.0
    end
as `priority`,
`tasks`.`user`as `user`,
`tasks`.`metadata`as `metadata`
from
`tasks`
left join task_to_tag on tasks.ulid = task_to_tag.task_ulid
left join task_to_note on tasks.ulid = task_to_note.task_ulid

group by `tasks`.`ulid`;

CREATE VIEW `tags` as
select task_to_tag_1.tag,
       (count(task_to_tag_1.tag) - ifnull(closed_count, 0)) as open,
       ifnull(closed_count, 0) as closed,
       round(cast(ifnull(closed_count, 0)
                  as
                  float) / count(task_to_tag_1.tag),
             6) as progress
from task_to_tag as task_to_tag_1
     left join (select tag, count(tasks.ulid) as closed_count
                from tasks
                     left join task_to_tag
                     on tasks.ulid is task_to_tag.task_ulid
                where closed_utc is not null
                group by tag)
               as task_to_tag_2
     on task_to_tag_1.tag is task_to_tag_2.tag
group by task_to_tag_1.tag
order by task_to_tag_1.tag asc;

CREATE TRIGGER `set_modified_utc_after_update`
after update on `tasks`
when `new`.`modified_utc` is `old`.`modified_utc`
begin
  update `tasks`
set `modified_utc` = datetime('now')
where `ulid` = `new`.`ulid`
;
end;

CREATE TRIGGER `set_closed_utc_after_update`
after update on `tasks`
when old.state is not new.state and (
new.state is 'Done'
or new.state is 'Obsolete'
or new.state is 'Deletable'
)
begin
  update tasks
set closed_utc = datetime('now')
where ulid = new.ulid
;
end;
COMMIT;

When loading a project, check the last update date of the source and warn the users if they are old (like > 2 weeks) with a button to refresh it.
If source can be automatically refreshed (database connection or remote file), it can be directly triggered, otherwise open the dialog to refresh it.

SQL schema are really great and they are even better when augmented with other data sources, for example Rails models.
It's not realistic that I could write all the useful parsers so having a well defined format to import a schema or metadata for a schema could allow user to write their own parsers and import what they need in Azimutt.

We could have some tags, notes or even properties attached to any table, column or relation.

Please leave a 👍 reaction if you want this, and also please comment to explain your use case, so we could work together to extend the data model of Azimutt if you need more specific things :D

Today table placement is random (as auto layout is quite hard, but still planned).
But as mentionned by @Yarith in his feedback, we can easily do much better.

When opening a linked table (foreign key):

Place it at the right of the current one.
For example: same top coordinate and left = right side + 50 px.

When opening one or multiple tables linking the current one (primary key dropdown):

Place them on the left of the current one.
For example:

• for the first one, some top coordinate and left = left side - 500px
• for the other ones: top = top of the previous + 55 px (to have the table header visible) and same left coordinate than the first one.

Here is an example:

Read has a minor typo in the Architecture section where the PageComponents namespace is written PageComonents.

Table colors are algorithmically defined but it would be nice if users could change them, either in the UI or programmatically.

Azimutt goal is to explore a database schema, but once you want to evolve it it would be useful to draft the changes directly inside it.

But these draft changes should not break the real schema. For that, we can leverage the multi-source feature to create a user source per draft change, allowing to toggle it as needed.
In a first implementation, this source could be filled with a text input having the wanted changes (ALTER TABLE...). This way, no complex UI edits!

Open questions;

• does the tables, columns and relations from this source should be highlighted? Should we add a color for a source?

Thanks to ActiveRecord, Ruby on Rails has very clear ways of defining the database model, which are much more flexible that what is possible in SQL (ex: polymorphic relations).
Azimutt could parse files of a Rails project, looking for models and load them as a SQL schema.
Even better, we can merge the structure.sql and rails models schemas to have an unified view of the real data model of the application.

Inspiration: https://github.com/voormedia/rails-erd

If you want Azimutt to support this, please let us know with the 👍 reaction!

Discovering important feature of an app may be hard if you don't know where to look. I don't like product tour as they interrupt UX and get in the way of users but gamified checklist of things to do seems quite nice!

Inspiration example from Tweet Hunter:

So, let's create a notification icon in the top right navbar and give a list of tasks to achieve to the user. Progress will be stored in a "user" key in the storage so they won't see it at each project ^^

Interesting tasks:

• search for a table
• open a table using a foreign key
• open a table from incoming relations
• hide a column
• create a layout
• do a find path search
• use a shortcut

Extract schema from a database using a database connection (more reliable and convenient than parsing SQL ^^).

On the web we will proxy the connection through our backend, on desktop app, it can be done locally.

For that, we should create a new kind of source with all the handled database possibilities and perform queries on them.

Having a nice diagram is not possible without a good positioning.

Currently, Azimutt has the simplest possible layout algorithm: random position.
So it's up to the user to make a clear schema.

Of course Azimutt could do better here, several options:

1. Place tables on a grid

Instead of choosing a random position, place tables on a grid (with 50x50 squares for example) and try ti fit a table where it does not intersect with others, preferably on the current viewport but otherwise extend it (unzoom) to fit it.

2. Use a graph layout algorithm

Dagre is the layout lib used by mermaid and it's quite good and could also be used in Azimutt.

3. Use force layout algorithm

Force layouts allows to group tables with many relations together. I already tried a PR (#37) but without much success so far.

Others

• https://www.cs.ru.nl/bachelors-theses/2018/Bart_van_de_Put___4400496___Scoring_Entity-Relationship_Diagrams_Drawn_by_a_Computer_Algorithm.pdf
• https://www.yworks.com/pages/drawing-orthogonal-diagrams

Azimutt implementations

Each implementation has its own merits and every one could be implemented so the user can select the one that fit better for his/her schema. They also can be used to position a newly show table or to recompute every table position on the existing schema.

Do you think this feature is important ? (leave a 👍 reaction if so)
If you have some experience is graph positioning, especially for Entity-Relationship diagram, I would love your input on this 😄

Create a table details pane to show more information than currently displayed on the diagram such as:

• from which source it comes from with lines
• in which layout/group it's included
• any other interesting info that don't fit into the diagram

Let's define a simple DSL to define database schema 🎉

Proposal: https://azimutt.app/blog/aml-a-language-to-define-your-database-schema

Even if mobile is not a real target for Azimutt, it will probably be used to try it out quickly so it could be great to have at least basic features working (storage and drag).

Why it's not a real target:

• it's much easier to have a big screen to see your diagram
• you need to import your database schema, you probably don't have it on mobile

Why it will be used:

• people clicking on a Azimutt link on their mobile could try Azimutt with a sample schema

What are the problems:

• storage: Failed to execute 'transaction' on 'IDBDatabase': The database connection is closing (in Twitter webview on iOS)
• drag: popups "Already dragging..."

It's more a marketing/conversion feature but still important

Easily exploring your schema is cool, but what about doing it collaboratively with your colleagues.
You can already do this by sharing your screen but it would be much more powerful to have some real-time collaboration features (similar to figjam or excalidraw).

This subject is still quire blurry but if you are interested in it, please leave a comment explaining why you want it and which difference it can make. And of course, don't forget to add a 👍 reaction!