4 ms·

For most things I use Navicat, for more complex stuff I use DBeaver. But what I've really wanted is what Microsoft had over 20 years ago: - a visual database d

For most things I use Navicat, for more complex stuff I use DBeaver. But what I've really wanted is what Microsoft had over 20 years ago: - a visual database diagram - a visual query builder - their Index Tuning Wizard They had these things in the nineties and I've not seen something that comes close since (they've even made it worse for their own stuff). All the alternatives are janky java approximations that have terrible auto formatting options or keep to the very basics SQL.

john_minsk 5y ago
- a visual database diagram We are looking at sqldbm to visualize and document our DWH schema. It looks nice, but is cloud first and expensive if you want to collaborate. How are you solving this issue?
- lf-non 5y ago
  A very lofi solution I have been using is tbls [1]. It is a native cli utility that generates documentation from db schema (and includes comments in the db as well). It can output in markdown with embedded svg diagrams (which is great for previewing in github) as well as yaml (which I use for generating models). It is not quite polished but works pretty well. Also all the db diagrams and docs are committed alongside code in the repo which is a big plus for me. [1] https://github.com/k1LoW/tbls https://github.com/k1LoW/tbls
  - mjhagen 5y ago
    Tools like this only emphasize my point I think. I've resorted to writing my own diagram (also in svg) and docs generator for our apps and those things are fun to write, but I can't help but feel like this was all better taken care of back in the early 2000s with SQL Server Enterprise Manager.
- etoulas 5y ago
  To document your Schema, try https://schemaspy.org/ https://schemaspy.org/
WinterMount223 5y ago
What’s wrong with psql? Those seem like clutches for the Real Thing which you must understand anyway.
- jbverschoor 5y ago
  Sure… go write your next application in assembler using line edit
  - yu-carm-kror 5y ago
    I don’t think the analogy is correct. One is an abstraction to remove low level commands and the other is a GUI that applies the same commands.
    - RunawayGalaxy 5y ago
      It's all abstractions provided via interfaces, whether programmatic or graphical.
- mike_pol 5y ago
  Whilst I agree with you for writing queries, the gui tools provide some nice tools. I haven’t used postico but I regularly use sequel pro and the ease to write a query and export results via csv that non technical people might need is a great feature. I’m sure there is a way to do it in command line but for me I find it a lot easier as I’m not a db admin. Also gui tools are really useful for visualising content in tables with a lot of columns.
zasdffaa 5y ago
DB dev here with > 2 decades experience. This is just IMO and it depends on your needs, but these fit mine. If you're using SQL on small DBs you can probably get away with what you do, for large DBs that won't fly. Visual query builders are a deskilling device. There's a small hump to get over to learn to write SQL queries, it's worth getting over (IMO) (there are times when I'd like to take a large existing multi-way join and see a pic of it though). The index wizard is likewise de-skilling. If you know what you are doing you 99% don't need it (I've got a couple of useful tips off it, ever, that's all). You need to understand what the query optimiser does and how to give it the info it needs (indexes, clustered indexes, stats, whatever), which the wizard will not help you understand. These are not fundamentally complex or magic (there will be times you wonder at the stupidity of the query plan), there are books on this and lots of articles out there. For small dbs, no problem, but as they grow not understanding the optimiser will kill you. Anyway, my experience. Don't feel pressured, do what works for you.
- KronisLV 5y ago
  To expand upon the argument of leaning into GUI tools for stuff like this, or to offer my own opinions, i really liked the functionality of MySQL Workbench. You could design your entire schema as an ER diagram, thus being able to play around with how everything could be laid out extremely easily and also get a visual "feel" for that it'd end up being like and how your queries could work. But not just that, you could also forward engineer this ER diagram of yours to get SQL that you could apply to a live instance, or vice versa - to create an ER diagram of an existing DB instance. Not only that, but there was also the ability to synchronize schemas, with the tool essentially diffing what is there and what needs to be changed, so partial changes also become doable (e.g. adding a new table to an existing schema through the ER diagram) which was just surprisingly useful! The migrations might (should?) still be applied by a tool of some sort and could be versioned, but being able to draw a bunch of boxes around and getting all of the DDL that you need to get 90% of the way there is pretty great! Curiously, this also lets you tackle the laziness of the developers: "But i don't want to create 5 different tables for these types of objects that are different but also seem vaguely similar at the present time, i might just do with one wide table with lots of columns because i'm lazy and data normalization be damned!" Of course, using tools like that in some capacity probably also immensely useful for discovering larger and older DB structures and just exploring the schema rather than just writing a bunch of SELECT/DESCRIBE/... statements or something - tools like DbVis can let you visualize hundreds of tables of various DBMSes easily, as well as filter your layouts etc., which in my experience has been really useful for well architected schemas (e.g. proper foreign keys) also far less useful for badly architected ones (no foreign keys OR EAV/OTLT pattern which has more proponents than i'm okay with). Now, i'm not saying that these tools are all that people should be using, quite on the contrary - learning a bit of everything is probably pretty nice, if you have enough time and are willing to put in the effort to explore your options! But i've also seen them not being used at all leading to some pretty bad circumstances - gradually created DB schemas where you end up needing to query 9 different tables to get some data because of no good reason, killing the usability of the DB through any tool where you want to visualize things or even want to write SQL queries because some app developer thought that going for OTLT (e.g. references like TABLE_NAME, ID_IN_TABLE, just typically also named weirdly like REF_TYPE, ENTITY_ID because they pretend they can abstract the DB away) is a good idea and didn't consider that maybe the DB should also be usable outside of a particular Java application which has bunches of enums and so on. I've also seen DBs that perform horribly because the schema and how it's queried changed over time but nobody really looked into checking what's going on with the indices and as a consequence things perform about 100 times slower than they otherwise would, which can totally go under the radar until suddenly it's a big problem because it ends up in a critical path for some action that's done often. Thus i maintain that any solution that may assist you in creating indices, e.g. what Oracle has available (albeit i would prefer to use MariaDB/PostgreSQL in most cases) is really good and should be standard functionality for any good DBMS. > Anyway, my experience. Don't feel pressured, do what works for you. This is still spot on, though! Use what works for you, look for ways to make your life easier and hopefully make things perform correctly and performantly.
callmeal 5y ago
Happy user of DbSchema: https://dbschema.com/ https://dbschema.com/ for the diagramming part.

john_minsk 5y ago

- a visual database diagram We are looking at sqldbm to visualize and document our DWH schema. It looks nice, but is cloud first and expensive if you want to collaborate. How are you solving this issue?

lf-non 5y ago
A very lofi solution I have been using is tbls [1]. It is a native cli utility that generates documentation from db schema (and includes comments in the db as well). It can output in markdown with embedded svg diagrams (which is great for previewing in github) as well as yaml (which I use for generating models). It is not quite polished but works pretty well. Also all the db diagrams and docs are committed alongside code in the repo which is a big plus for me. [1] https://github.com/k1LoW/tbls https://github.com/k1LoW/tbls
- mjhagen 5y ago
  Tools like this only emphasize my point I think. I've resorted to writing my own diagram (also in svg) and docs generator for our apps and those things are fun to write, but I can't help but feel like this was all better taken care of back in the early 2000s with SQL Server Enterprise Manager.
etoulas 5y ago
To document your Schema, try https://schemaspy.org/ https://schemaspy.org/

WinterMount223 5y ago

What’s wrong with psql? Those seem like clutches for the Real Thing which you must understand anyway.

jbverschoor 5y ago
Sure… go write your next application in assembler using line edit
- yu-carm-kror 5y ago
  I don’t think the analogy is correct. One is an abstraction to remove low level commands and the other is a GUI that applies the same commands.
  - RunawayGalaxy 5y ago
    It's all abstractions provided via interfaces, whether programmatic or graphical.
mike_pol 5y ago
Whilst I agree with you for writing queries, the gui tools provide some nice tools. I haven’t used postico but I regularly use sequel pro and the ease to write a query and export results via csv that non technical people might need is a great feature. I’m sure there is a way to do it in command line but for me I find it a lot easier as I’m not a db admin. Also gui tools are really useful for visualising content in tables with a lot of columns.

zasdffaa 5y ago

DB dev here with > 2 decades experience. This is just IMO and it depends on your needs, but these fit mine. If you're using SQL on small DBs you can probably get away with what you do, for large DBs that won't fly. Visual query builders are a deskilling device. There's a small hump to get over to learn to write SQL queries, it's worth getting over (IMO) (there are times when I'd like to take a large existing multi-way join and see a pic of it though). The index wizard is likewise de-skilling. If you know what you are doing you 99% don't need it (I've got a couple of useful tips off it, ever, that's all). You need to understand what the query optimiser does and how to give it the info it needs (indexes, clustered indexes, stats, whatever), which the wizard will not help you understand. These are not fundamentally complex or magic (there will be times you wonder at the stupidity of the query plan), there are books on this and lots of articles out there. For small dbs, no problem, but as they grow not understanding the optimiser will kill you. Anyway, my experience. Don't feel pressured, do what works for you.

KronisLV 5y ago
To expand upon the argument of leaning into GUI tools for stuff like this, or to offer my own opinions, i really liked the functionality of MySQL Workbench. You could design your entire schema as an ER diagram, thus being able to play around with how everything could be laid out extremely easily and also get a visual "feel" for that it'd end up being like and how your queries could work. But not just that, you could also forward engineer this ER diagram of yours to get SQL that you could apply to a live instance, or vice versa - to create an ER diagram of an existing DB instance. Not only that, but there was also the ability to synchronize schemas, with the tool essentially diffing what is there and what needs to be changed, so partial changes also become doable (e.g. adding a new table to an existing schema through the ER diagram) which was just surprisingly useful! The migrations might (should?) still be applied by a tool of some sort and could be versioned, but being able to draw a bunch of boxes around and getting all of the DDL that you need to get 90% of the way there is pretty great! Curiously, this also lets you tackle the laziness of the developers: "But i don't want to create 5 different tables for these types of objects that are different but also seem vaguely similar at the present time, i might just do with one wide table with lots of columns because i'm lazy and data normalization be damned!" Of course, using tools like that in some capacity probably also immensely useful for discovering larger and older DB structures and just exploring the schema rather than just writing a bunch of SELECT/DESCRIBE/... statements or something - tools like DbVis can let you visualize hundreds of tables of various DBMSes easily, as well as filter your layouts etc., which in my experience has been really useful for well architected schemas (e.g. proper foreign keys) also far less useful for badly architected ones (no foreign keys OR EAV/OTLT pattern which has more proponents than i'm okay with). Now, i'm not saying that these tools are all that people should be using, quite on the contrary - learning a bit of everything is probably pretty nice, if you have enough time and are willing to put in the effort to explore your options! But i've also seen them not being used at all leading to some pretty bad circumstances - gradually created DB schemas where you end up needing to query 9 different tables to get some data because of no good reason, killing the usability of the DB through any tool where you want to visualize things or even want to write SQL queries because some app developer thought that going for OTLT (e.g. references like TABLE_NAME, ID_IN_TABLE, just typically also named weirdly like REF_TYPE, ENTITY_ID because they pretend they can abstract the DB away) is a good idea and didn't consider that maybe the DB should also be usable outside of a particular Java application which has bunches of enums and so on. I've also seen DBs that perform horribly because the schema and how it's queried changed over time but nobody really looked into checking what's going on with the indices and as a consequence things perform about 100 times slower than they otherwise would, which can totally go under the radar until suddenly it's a big problem because it ends up in a critical path for some action that's done often. Thus i maintain that any solution that may assist you in creating indices, e.g. what Oracle has available (albeit i would prefer to use MariaDB/PostgreSQL in most cases) is really good and should be standard functionality for any good DBMS. > Anyway, my experience. Don't feel pressured, do what works for you. This is still spot on, though! Use what works for you, look for ways to make your life easier and hopefully make things perform correctly and performantly.

callmeal 5y ago

Happy user of DbSchema: https://dbschema.com/ https://dbschema.com/ for the diagramming part.