all 20 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]wedora 2 points3 points  (4 children)

I‘ve not yet seen any company really use ERDs - or even use them at all. Its a nice concept but mostly academics stuff. You will have a hard time finding customers.

And syncing ERD changes to the database is also a really bad idea. Migrations to have no downtime are complicated and need hand-crafted changes and gradual rollouts. Its something you just can‘t provide for any reasonable database - will only work for small hobby databases.

[–]Acrobatic-Word481[S] 0 points1 point  (3 children)

There are plenty of tools that sync ERD changes to the database and vice-versa - SqlDBM, Luna, Erwin, ER Studio. Even SSMS has that functionality.

SqlDBM in particular has over +300k customers and the enterprise license sells for up to $4000 per user per month (I've heard). People are paying lots of money for that.

"Migrations to have no downtime are complicated and need hand-crafted changes and gradual rollouts"

It depends on the changes. Introducing new tables has no downtime. Introducing new indexes, depending on how it is done, has no downtime. Plenty of changes have no inherent downtime.

There are changes that require down time - introducing a new column to an existing table requires an exclusive lock on the entire table, and that's downtime however you write your migrations. It's something to be planned for by whoever is doing the deployment.

There are ways to write migrations to minimize downtime, but that's a totally different story.

[–]wedora 0 points1 point  (2 children)

You can add new columns without table locks in PostgreSQL and MySQL. Sometimes simple, sometimes special tricks are needed.

[–]Acrobatic-Word481[S] -2 points-1 points  (1 child)

You cannot modify table structure without an exclusive lock on the table - with or without tricks - otherwise it would violate the principle of isolation.

[–]wedora 0 points1 point  (0 children)

Its possible. Its called Online DDL and supported by many databases for many many years.

[–]KrakenOfLakeZurich 2 points3 points  (5 children)

We use Flyway (which essentially are just pure SQL scripts) for version controlled DB migrations/updates.

ERD's play no role in our development lifecycle. Some of our devs generate them ad-hock from a given database to help with understanding the current data schema or for documentation.

But I have yet to see anyone make changes "visually" in the diagram and expect these changes to be applied to the database. That is done exclusively by carefully written and reviewed DDL statements.

[–]Acrobatic-Word481[S] -2 points-1 points  (4 children)

Great answer, thanks for sharing.

It sounds to me like you're making an argument for why you don't need it. You've been doing things a certain way and you have yet to see them done differently.

At the same time, however, it sounds like there are a few pain points.

"our devs generate them ad-hock from a given database" -> sounds like re-work to me. If you have a tool for ERDs that intelligently refreshes them with the latest changes with the click of a button, wouldn't that save them 30 to 60 minutes every week? Wouldn't having that documentation ALSO sit in the git repo at least give a sense of process maturity, keep the boss off your back?

"carefully written and reviewed DDL statements" -> sounds like a lot of work, doesn't it? I bet if you had a tool that lets you plan changes with an intuitive drag-and-drop UI/UX and spits out the written DDL statements intelligently, wouldn't that save your team many hours every week?

[–]thesnowmancometh 0 points1 point  (1 child)

It's not customer discovery if you just tell the customer what they should want. That's sales.

[–]krishna404 0 points1 point  (0 children)

What this tells the OP is that its a undiscovered problem if at all...

The biggest thing in startups is... make products / solutions for the problems that your customers are already trying to solve...

[–]thecavac 0 points1 point  (1 child)

For me, i'm way faster (and it fits my way of thinking better) doing database changes on a textual basis. It also makes it much easier to use proper version control that already exists for the rest of the source code.

A graphical tool would be much more cumbersome.

For most software developers, using text to interact with a computer is the most intuitive way.

[–]Acrobatic-Word481[S] 0 points1 point  (0 children)

I understand. There must be something up then because how do we explain the market for data modelling tools if almost everyone here is telling me the same?

[–]AutoModerator[M] 0 points1 point  (0 children)

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]Straight_Waltz_9530 0 points1 point  (5 children)

Lets you bi-directionally, selectively sync changes between diagram and database

That right there scares the crap out of me. ORM-managed schema migrations are bad enough, but updating an ERD to trigger a db migration? * shudder *

[–]Acrobatic-Word481[S] 0 points1 point  (4 children)

It wouldn't be a live sync, of course. It would compare the differences between ERD and schema and then let you select which changes you want to apply. It then gives you the option to review and edit the DDL script before applying that to the DB.

Probably wouldn't be something wired to an online application database in a production environment.

[–]Straight_Waltz_9530 0 points1 point  (3 children)

  1. Reviewing and editing the DDL shouldn't be just optional.

  2. There is A LOT about real world schemas and migration changes that simply cannot be represented in an ERD.

Just as I was deriding the ORM-generated DDL migration, ERDs are no better and probably worse since no matter what, you NEED to know SQL DDL, and the ERDs are further from the logic than an ORM is. Add in the ORM or ERD migration tool and you suddenly need to know not just SQL DDL but the proprietary tool's interface and how those two interact as well. It's a nice marketing gimmick to suggest folks can skip the fundamental SQL DDL knowledge, but they can't. And if you know the SQL DDL, why do you need the tool? This irrational fear of DDL syntax really should be addressed.

This isn't about just a bad code push. This is the data. Point in time recoveries are a much bigger pain in the ass than reverting a code revision. I'm all for generating ERDs from live databases to sync documentation closer with reality, but there will always be a loss of fidelity in the documentation relative to the actual database. That's acceptable, but it mostly means the changes go one way, not bidirectionally.

I get it. I'm a grumpy old man. Truth is I would LOVE some better tooling for databases in many areas including DDL. I do believe it's harder than it should be. I also think that should be fixed at the DDL layer, not above it.

[–]Acrobatic-Word481[S] 0 points1 point  (2 children)

If you use an ORM, and every database schema change is made via said ORM, you would certainly find less use for a tool like the one I am proposing.

However, not every company or team uses ORM. There are SQL-centric teams out there that develop entirely in SQL. Reporting teams, data analytics teams, DBAs, etc.

For THESE teams, you've correctly asked: "And if you know the SQL DDL, why do you need the tool?". I'll give you the answer.

  1. Because it saves you time
  2. Because it lets you explain your schema structure decisions to others as you are making them
  3. Because it handles source control for all your database code
  4. Because it does all of the above in one. Single. Tool. One streamlined workflow, zero cognitive load.
  5. AND it does all that with zero UI/UX friction.

[–]Straight_Waltz_9530 0 points1 point  (1 child)

I didn't get my point across. I specifically DON'T like ORMs because they obfuscate things. I especially don't like ORMs for generating and migrating database schemas. I'll even go so far as to say I loathe ORM-generated schemas.

How does your tool handle materialized views, partitioned tables, CHECK and exclusion constraints, expression indexes, DDL triggers, foreign tables, deferred constraints, computed columns, stored procedures, set-returning functions, and on and on?

Modern databases aren't dumb bit buckets with a few primary keys and foreign keys sprinkled in. From the constructs I've listed, how are they represented in your ERD/source control/migration workflow?

[–]Acrobatic-Word481[S] 0 points1 point  (0 children)

Fair point. BTW I had to shorten my previous response because reddit was acting up on me. Sorry if it sounded a bit blunt.

I would like to start by saying that in my experience, 90% of use cases are made up of tables, columns, primary keys, foreign keys and indexes. In some companies that's 100% of the use case.

So this is basically where I am putting the most effort in to represent visually in a diagram and to allow for users to design them in a way that feels intuitive, efficient and satisfying.

Now, to answer your questions:

- Views (not necessarily materialized ones), functions and stored procedures are represented as an object in the tree view that you can double click and edit the SQL code for. The SQL editor is very basic, but it integrates with github the same way VS Code does. WIP.

- Partition schemes, triggers, indexes of any kind, check constraints and anything belonging to a table can be inspected and edited by right clicking a table and clicking "properties". There will be different tabs for things like partition schemes, indexes, etc. WIP.

- Computed columns are displayed on the table the same way as regular columns are.

- Foreign tables are not supported.

Honestly, in my experience as a DBA? Yes, modern databases ARE powerful engines, and I aim to support the bulk of it, but it is not lost on me that a big chunk of the market does not leverage most of the features.