Some feedback after testing this tool

[Yarith]

Hello,

i have tested this tool with our database schema. I had following problems and toughts while using it:

1. ⬜ Simple export of the schema and then attempt in importing has failed because of
   1. ⬜ DEFERRABLE FOREIGN KEY CONSTRAINT
   2. ⬜ plpgsql functions were not ignored and caused trouble.
   3. ⬜ In my sql i had one CREATE TABLE followed by a bunch of CREATE FUNCTION. For some reason this one table was discarded. If i move another table above the functions, it get discarded too. Discarded means, it is not in the list of available tables.
   4. ⬜ If you have a lot of parsing errors then you have no chance to close all the notifications, because the error message is too wide. Those all already known in Popup window navigation buttons showing errors are not functional #13 The close button of the pop-up showing the errors could be hard to access #14 Close all button for notifications #17
2. ⬜ After manually modifying the generate sql table, i was able to use it without errors, but i noticed following:
   1. ⬜ If you are in the process of aligning all the tables, you need to constantly switch between the "Drag tool" and the "Select tool". Sometimes zoom in and out with the ctrl key. If i have zoomed out, i have a lot of space, but i can not read the table names anymore.
3. ⬜ If you click by accident on "Show all (18 tables)" they will be placed one over the other. Better table layout algorithm #22
   1. ⬜ Then if you find your starting table, for example by hiding and showing it via the main menu, you can not hide all those opened 18 tables.
   2. ⬜ A revert button for something like this would be good too. I know this would be a lot of work without using a lot of memory. Found it Undo/redo #24
   3. ⬜ The z-index of the tables are not moved to the top, if you interact with them.
   4. ⬜ If you have a table and want to bring a reference in front, but it is already on the canvas, then only the notification "the table is already shown". Now i found out, you can hide and show it with the main menu, to get it to the front.
   5. ⬜ I don't know what the best way is to improve the user experience when you click on this "Show all (18 tables)" by accident.
4. ⬜ You wrote on the homepage that no information are sent to any server. It seems, if you have parsing errors, some information are currently sent to some analytics servers. I have removed all those analytics stuff, before i tested it locally, so you should have received none. I am not really sure if my claim is even correct, if not then sorry.
5. ✅ With the multiple layouts you can easily prepare the relations for every aspect in the database schema. If you have a member, he could have some documents, measurements, tests, exercise plans and more. Every aspect could be quite complex or have even sub aspects. It is nice that the tool already enables us to prepare for every aspect only the relevant tables. In my fork i could change the storage from localStorage to an own storage on the server. It seems you have already planned another form with named groups of multiple tables Table groups #30
6. ⬜ How can i update the database structure without losing all the created layouts?
7. ⬜ A list of relations that are not in any layouts would be nice. This would help to find forgotten tables or unnecessary tables/relations. This is really important to find out, as soon as the layouts are updated after reloading the database schema (if it gets implemented).
8. ⬜ Notification sidebar seems to need some improvement. If it is better you can use it for more stuff.
   1. ⬜ Showing and hiding the whole notifications area would be nice. Or an extra bar, you can manually open/close, if the toast disappeared, before you could read it. It seems those toasts are not automatically disappearing right now.
   2. ⬜ For example, if a table got removed, but was used in a table, you can put a notification about it. Now the user can think about it and fix it. Maybe the tables got only renamed. Maybe missing tables should not just disappear, more like "invalid" marked. At this stage i don't know if you want to implement "project updates with new version of database schema". Just wrote down, because it came to my mind right now.
9. ⬜ Export function so i can sent it to an external database expert per email could be useful too.
10. ⬜ The menu is not always in front and can be hidden by some tables.

I planned to put this tool on our development server which hosts some documents. Like this it would be too hard to introduce coworkers into it. I wanted to automatically fetch the current database schema from our development PostgreSQL database and have it preloaded. So i only need to send a link to our index.html file and everyone can access it easily by clicking on the link.

I don't know how much i would use this tool. Preparing and aligning all those layouts would be quite time consuming. You have planned with #22 better positioning, but in our case, the tool would have not enough knowledge to place them correctly. Maybe if it is possible to easily update the manually created layouts with a newer database schema, it is not so bad anymore.

I hope this write down helps you to further improve this tool.

[loicknuchel]

Hi @Yarith

Thanks you so much for such a detailed feedback, this is very valuable to me ❤️
I edited your message to add numbers to your points in order to make it easier to discuss. I hope you don't mind.

So, let's dive in:

1. I know I don't have a perfect SQL parser, especially since I try to parse every dialect but it improves over time given the errors I find. I improved it a lot since your message, so maybe you can give it an other try and let me know if you still have errors (would be happy to fix). BTW, linked to your number 4. point, you are right, parsing errors are sent to sentry to allow me to fix the errors. It's a bit contradictory with the claim "no data is sent to the server" and I should specify it. This is for me the only way to know which error are happening and fix them. I don't think it's a big deal as it's just isolated small piece of SQL but I will add an option to let the user choose to send an error report or not.
   4. I know the error reporting is quite bad. I'm migrating the UI and it will be much better in the next few weeks :D
2. When you zoom out, the table name should grow to keep at least 10px height. Is it too small? Should I go for 12 or 15px? Or do you have a bug that prevent this behavior?
3. Yeah, I know the placement algorithm is a big and quite frustrating topic. I did some experiment but without any success for now and as you say, it can't be very smart and you will always need manual placement. But at least it can avoid too much superposition.
   1. Do you think I should add some buttons on the right of the table name in the dropdown to "hide this table", "hide all other tables" and a "hide all" at the end, just after the "show all" ?
   2. Yes, Undo/Redo is planned 😄
   3. Didn't thought about this behavior (when a table is moved, put it on top). I see how it could be nice but maybe also have some drawbacks when you don't want to change the order... What do you think?
   4. Nice trick ;) You also have an option to bring it to the front on the table settings or you can use a keyboard shortcut: ctrl+maj+up arrow. I should document more the hotkeys ^^
   5. Definitely an Undo feature will be nice in this case ;)
4. You are right, I must clarify this (see my answer to 1.). For the future, I will add a button for the user to choose to send the error report or not.
5. Oh nice! Don't hesitate to submit your feature ideas or even PRs so I can improve it :) Indeed table clusters can be a nice way to hide some complexity. I didn't thought of making a configurable storage, this is a really cool idea for users that need strong privacy. In the future, I plan to offer to save your projects in a server to share them with other people. Would be quite interested to discuss how to make this right with you as you have a team use case in mind.
6. I just added the feature: click on settings in the top right corner and then on refresh source.
7. Interesting. You seem to have a use of layouts I didn't thought about: map every part of your schema. This seems quite specific to me. Could you elaborate a little bit more so I can try to find a more general way to achieve what you need? BTW, I'm thinking of adding a detail panel to see more infos about table, relations or columns. Would a list of layout where the table/relation/column is used fulfill your needs? (go through tables and relations and see if some have no linked layout). Out of curiosity, how many tables do you have in your schema?
8. Yes, lot of room for improvements in notifications! You can now update your database schema and ideally later see it's diffs through time. I still have to figure out useful use cases and UX around this.
9. What do you want to export precisely? The UI and its layout? The JSON structure of the schema? The SQL? Not sure what you want to send to a database expert.
10. Yeah, the table settings menu is quite buggy right now. Hopefully, with my UI rewrite this will be fixed :)

Very interesting point. I'm thinking of allowing users to create an account, store some projects and allow them to share with other users. Also, if Azimutt can have access to the schema (github repo) or directly to the database (in dev for example), it could sync the schema periodically to have it always fresh. What do you think?
More generally, I really like the "only local" way of working but it prevent any sort of collaboration. Do you see any way to reconcile them?

I totally get that aligning all the schema can be quite time consuming and tedious. Obviously I'm thinking of making this better (auto layout but also sticky grid) but I don't think this should be its primary goal. I see Azimutt as an exploration tool, the one you go when you are looking to find, understand or even manage your schema. Something living that you can model as you need, not a perfect drawing tool to print schemas (even if it could be useful for this also ^^). But it's still very early and I'm still refining this vision with any feedback.

Again, thanks a lot for taking the time to share your thoughts on Azimutt. The project is just starting so it miss a lot of things but it keeps improving and with good feedback from users, I hope it will become an obvious choice to work with database schemas 🚀

[Yarith]

Hi @loicknuchel

After your reply i gave this tool another try. I updated to the latest version in the main branch. I started integrating it in our webserver which serves our documentation. At first i wanted to only change the script.js, but at the end some changes would be too difficult without changing it directly. Today i finished the integration. For some reason this post will get really long.

So i created a fork and you can see the diff. One of the last changeset added a lot of noise to the comparision, because i switched from Cmd to Effect. Sadly the Ports module must stay on Cmd. If you want you can copy some changes to your repository.

Now about the points:

1. Yes this will improve over time.
2. You are right, there is a minimum font size. My problem was more about the switching between the tools.
3. Yeah, manual placement is for now the way to go. The main problem here is that the initial table is buried by the freshly opened tables.
   1. I forgot where the "show all (18 tables)" was. Found it again in the table list of a primary key. Fast way of hiding all tables would make sense. In this case maybe place the tables stacked.
   2. The undo will solve only the accident case.
   3. I meant it should only be visible on top while being dragged without destroying the actual ordering in the model. I have done it on my fork with changeset d1255ce when the popup of a table is currently visible.
   4. A shortcut is nice, but i don't know which key is maj. Is this a MacOS thing? Showing the shortcut on the menu like in the main menu for Find path between tables and Create a virtual relation would be nice. If they are not easily discoverable they will never be used.
4. I had the fear, that a bug in the parser will cause it pick up the rest of the file and send it to some server. So i have removed every external access and started it locally. We have multiple multiline functions and all of them would have been sent to a server.
5. I have added the ability for multiple storages in my fork. I needed a storage in the repository, another as a commonly shared one and kept the personal localStorage. In our case it enables us developers to document areas of the database. Gives the ability to our service team, to make own layouts in new projects, if some will be missing and then they can share it with us. We can then move them to the repository with a single click. The problem here is, that the modification is in another project, so the json need to be merged manually. I added a storage section to the project settings (88e2670, 1d0927b, 972cae7). We do not need account management. The repository will contain the valuable projects anyway.
   
6. Nice! I added an api endpoint to our server which delivers the current schema directly from a PostgreSQL database which contains always the current version. I don't know how to add the link manually, so i regex replaced it locally into the examples list.
7. What i meant here was solving the question: Are all tables and relations used by the project? It helps to clean up the schema or update your saved layouts. This is a little bit like working in code. Finding the layouts where tables or relations are used will solve it. You have quick navigation based on tables and can quickly see if something is missing. This is just an idea, because i love the idea of gurantees. If i can simply give a coworker a link (see 1e651dd) to a prepared layout and can be sure that it is still up-to-date, i think it will make everything easier to explain. We have not that many tables currently, are about 80, which should cover around 12 aspects.
8. As far as i see, the problems come mainly from too long lines and the not working scrollbar. Long lines come only from parsing errors (JSON and SQL).
9. At the time of writing i was in the situation. Probably i was thinking about the storage type "embedded into website". Is not really important. Such layouts are never self explanatory, especially if tables encode variants (depending on one field the used relations are different). Came to my mind, because we had the case to show a external database expert our current state and how to improve it. At the end there is nothing to improve which we were not already knowing.
10. Nice to hear. I have changed it in my fork with changeset d1255ce. While popup visible the table gets much higher z-index.

While i had worked on my fork i made some other changes:
11. Saving changes when switching projects b72bd51
12. Tables are placed to 0,0 when switching projects which has same tables currently visible 7fa9d1d
13. Projects lists are filtered by name instead of id c9cb7c9
14. Sometimes new projects get lost when immediately switch to another project 25a5ce3
15. Because of 11. the deleted projects were now saved again when switching away from it. Solved by clearing the active project. fa630c5
16. Dragging a table changes constantly the state of the selection. If you select multiple tables after the first drag all of them lost their selection. If you need to move the view or zoom out in between this can get really annoying. Solved with 2825dc7
17. Also added Ctrl rectangle selection. 4858efd
18. How i am communicating with the server to store the projects outside of localStorage. f0edc25

You can look at the changes yourself by downloading my fork. Everything should work normally except the new server storage feature.

It was really hard to integrate it to our server, because i needed to extend the base directory. The app lives inside the /sites/azimutt/ directory. Updating elm-spa costed some hours but was on the easier part. Also i needed to find all usages of resources in the elm code. Gadly they were only 3 kinds which could be find with regex replace.

Maybe if someone will host it beside other stuff in an existing server here my used regex expressions:

// Regex to prepend base directory to elm code files
/((src|href)(\s*=)?\s+"\/)((?!sites\/azimutt\/).*?")/m

// Regex to extend the samples with our own database schema
// src\\Conf.elm
/(schemaSamples =\r?\n    Dict.fromList\r?\n        \[(\r|\n|.)*?)(\])/m

// Changes for the generated elm-spa files:
// generateed/Gen/Route.elm
// Regex to prefix the (Route -> String) function
/^(        joinAsHref : List String -> String\n        joinAsHref segments =\n            ")(.*?\/)(" \+\+ String.join "\/" segments)$/m

// Regex to prefix the route parsers
// generated/Gen/Params/**/*.elm
/^(parser =\n    .*?\()(.*?)(\))$/m

I played a little bit with the find path feature:
19. I see there is a complexity setting, but if i put it on 8 the browser freezes. Would it be possible to limit it only to maybe 50 entries? Maybe pagination would solve it too?
20. A fast way to exclude the tables from within the search result would be nice. It should then automatically refilter the results. Writing all the table names manually in the filter box is tedious.
21. If i put a comma into the exlude input boxes i can not remove it with backspace anymore. I need to Ctrl+Arrow Left once or Shift+Arrow Left twice so i can remove it.
22. If you close the experimental warning, then it does not notice changes on the filters anymore. The "Done" button is never changed to "Search". Looks like it also breaks the search box in the top left.
23. Limiting the search only within the current layout. Maybe only show one table outside of the layout at the begin/end of the path, so we know that there could be more.
24. Loading one or more specific paths from the search result into the current layout.
25. The generated sql query for each path is nice. Maybe add it also to the menu of each table. Generate SQL for the whole layout starting on this table.
26. Crazy idea: Generating a json output with respect to all the tables in the layout.
27. Crazy idea: If we have a json output, maybe an upserting command back to a different database from this json. This would enable easy moving or templating of database entries. I could generate a query/upsert pair for a specific user and everything hanging on it can be exported and later imported.

So sharing in my team is also solved for me by the different storage types in my fork. I don't think the other team members would create accounts only to have a quick look. Having the documentation inside the repository is really nice to work with as a developer.
But this sadly has two downsides: 1. the non-developers will never correctly use the repository clients (git/hg) and the ide (vscode+markdown), so they can not use our tech. They have not even installed it by default. 2. If you have multiple parallel versions, the server can only see one branch. Documentation updates are only for one branch visible. Was really annoying to put the documentation in the older one for new features.
At the end i still prefer this, because i can easily integrate other apps inside the server. The server itself compiles it from source and delivers it. Developing and distributing applications for internal use is really easy this way.

Yes of course Azimutt is an exploration tool. It has a really nice looking user interface and is written in elm, which makes it easy to integrate into intranet websites. Now I think managing the layouts will not be so time consuming anymore. The main feature is exploring the existing schema, another one could be exploring the effect of changes to it. I will definitely use it for living schema. The database schema is the core of the application and always up-to-date. It is nice to have now a non-overwhelming tool to revisit it any time.

The export feature of 9. is a little bit out of scope for now. I am more interested about 26. and 27. when it comes to export features. But export features in general are not so important. I need to start really using it, currently i was just playing around and validating its usefulness.

It was a really fun time to read and extend the source code. You have separated the modules, probably to fight against the cyclic module dependencies. Was at first really difficult to navigate, but after time it was not so annoying anymore. I think after my changes to Azimutt, it is working for me. Now after spending a little bit more than a week with it, i should move back to the other interesting projects.

A nice project and good work so far! 👍

[loicknuchel]

Hi @Yarith,

AWESOME! I'm quite happy to see you exploring the tool and bringing interesting use cases and solutions 🎉
You did so much work, this is impressive!

Thanks for your so detailed explanations, they are really helpful and I will probably come back to them along the next year to choose what to implement ^^

I'm currently in the process to move from bootstrap to tailwind but I also use this opportunity to improve the UI in many ways, so the code is very different. Your changes will not be compatible and maybe not easily to migrate. You can have a look to the code in the linked PR and a preview version. Feel free to leave some feedback comments it you have some :)

I won't answer to all your proposals, it would be a too huge answer (but I keep them in mind and will regularly come back to your comment in the coming months) but here are some cherry picks:

3. i. Your idea is quite brilliant, and so simple ^^ Will definitely do that. Here is the issue: Better table placement #58. Feel free to comment if you see things differently.

4. iii. Very good idea to move the dragged table(s) temporary on top. It would allow the trick to drag it just a little bit to make it visible shortly. Maybe doing the same thing for selected tables would make sense. I created Make selected and dragged tables more visible #59 for this.

5. iv. Totally agree, UX has still a lot of improvement areas. It's only a matter of priority ^^

6. Having a remote source is already supported by LoadRemoteFile message, but for now I don't have the UI do to it, I only use it for sample. It would be easy to implement it and I definitely plan to do it asap (once my UI migration is done, hopefully by the end of the year).

19-22. Yeah, it's because of JS mono-thread and the fact I don't split the computation. Having a max number of result is a good idea but with a breadth-first strategy, not sure it will get so much faster and in a depth-first one, not sure it will be relevant... But I can experiment on this. Clearly this feature is experimental and lot of work is still needed: search algo (needs more heuristics and breakpoints to avoid freeze), UX (tables and columns should have auto-complete and tag system to be more friendly than just text).

If you could choose 2 or 3 features, what would be the most important for you?
Maybe we can plan a chat during the first week of January?
I will (hopefully) have finished my UI refacto and I will work full time on Azimutt this week so a good time to make a lot of impactful changes ;)

[Yarith]

Hi @loicknuchel

About 19-22: In my cases the slowness comes not from calculating the results. I see after 4 Seconds already that i have 4700 results, but then it hangs for over 2 minutes. The profiler showed me that the problem is the activateTooltipsAndPopovers function. I have stopped here the profiler after 10 seconds, so it shows only about 6 seconds here. Was not able to open the profile when running full duration.

It tries to register it for all the table names, but you will never hover over all of them. So i changed it in my fork with changeset 00e7fbd. This removes also the need for the ActivateTooltipsAndPopovers port. Now you can just set data-bs-toggle on the element and it will be shown without calling the port. I have not yet worked with tailwind tooltips, maybe you need something similar there too?

Lets see which new features would be nice:

1. Better navigation between layouts
   1. Creating a list of not yet explored tables/relations. Which means unused in the current and any named layouts of the current project. Point 7 of previous comment.
   2. Clicking on a foreign key and seeing in which layouts it got already described.
   3. Having the ability to see all the layouts for a table and directly navigate to them.
2. Improvements to find path tool
   1. Prevent hanging without constraining it manually before the search. A temporary solution would be limit it to a maximum result count.
   2. Adding the tables to the current layout from a specific result in the "find path" tool. Point 24 of previous comment.
   3. Showing a list of found tables. A way to ignore some with a click.
   4. Showing a list of found relations. Set if it should be: ignored, go from A to B, go from B to A, any direction. Default: any direction
3. Generating queries based of the current layout (or maybe also multiple layouts?). Point 25 of previous comment.
   1. Probably same filters for tables and relations like in 1.3 and 1.4 would be useful too.
   2. Excluding specific columns
   3. Replace value of column with a function call. Generating new ids, anonymize personal information.
   4. I don't know. If i continue here, it will be a graphical query builder. 😅
4. Not important: Generating SQL pair for export/import data. Points 26 and 27 of previous comment. Maybe together with the filters of 3. This can be really difficult to develop. The tool would need to handle recursive dependencies. Also the tool is for multiple databases, which adds additional complexitity. As wrote previously, if this works great it may help on service tasks.

The storage feature will probably only be a feature in my fork for a while. All the other changes between 11-18 are just corrections that ensures the app works as expected. I ordered the features by priority instead of limiting them. I have not really many feature ideas at the moment.

For now i will use the current version. When the new version is available, i will take a look again. I had looked on sunday on it, but nothing was working. So i switched back to main branch. If all my fixes are not necessary in the new version it is good enough.

[loicknuchel]

I didn't look into performance for the find path, thanks for doing it, and it's quite reassuring that it's the popovers that cause this problem so "easy" to fix like you did 👍
For my refacto, I will try to have CSS only popovers so I expect to don't fall in this but if I need JS, I will definitively use what you did (I plan to remove all the bootstrap related ports ^^).

Thanks a lot for your feature list, the 1. and 2. seems really interesting, for the 3. and 4. I'm not sure they fit in the core things I want to provide with Azimutt but still worth investigating. I will come back to you when working on them :)

Yes, my new version for now just handle the create project part, I'm just starting to implement the "real app" ^^

[loicknuchel]

Hi @Yarith

I finally finished my migration and implemented a few more features, some from your suggestions 🎉

I would love to have your feedback on them if you have some time to look into them :)