HN Explorer

Show HN: Visualize database schemas with a single query (github.com)

227 points by Jonathanfishner ↗ HN
Hey HN! We are Jonathan & Guy, and we are happy to share a project we’ve been working on. ChartDB is a tool to help developers and data analysts quickly visualize database schemas by generating ER diagrams with just one query. A unique feature of our product is AI-Powered export for easy migration. You can give it a try at https://chartdb.io and find the source code on GitHub. Next steps ---> More AI. We’d love feedback :)

67 comments

[ 3.2 ms ] story [ 101 ms ] thread
namanyayg
nice work on chartdb, guys!

can you elaborate on how the ai part works? im a bit confused how that fits in because there are many SQL diagram tools without AI as well

Jonathanfishner
Thank you! We use GPT-4o to generate database schema creation scripts for different database dialects. Basically, we wanted to add the option to export the db schema to any type of database (for easy Migration for example from MySQL to PostgreSQL or from SQLite to MariaDB). First, we tried to implement that logic, after getting into trouble with too many different edge cases and dialect differences between different databases we decided to leverage AI in order to achieve that.
yardstick
> after getting into trouble with too many different edge cases and dialect differences between different databases we decided to leverage AI in order to achieve that.

So you identified all the issues in your code generation, decided instead of fixing them to use ChatGPT instead, and trust that ChatGPT did it correctly?

Are you saving much effort? Are you providing an accurate, bug free experience for all the SQL databases you claim to support?

andrelaszlo
> visualize database schemas by generating ER diagrams with just one query

> visualize and design your DB with a single query

> Instantly visualize your database schema with a single "Smart Query."

The tool seems really useful and I will give it a try!

Just curious about the emphasis on the single query. What's the value of this? If it makes it much faster than similar tools it's probably worth making it explicit. I don't use them often enough to know if speed is a big issue (if this is the reason).

Nice work anyway!

SoftTalker
They are querying postgres information_schema tables (or their equivalent in the other supported databases) to get table names, column names and types, foreign keys, etc.

I doubt the "single query" makes it immensely faster, but reducing query round-trips is usually time-saving. Might be noticable, depending on what you're comparing it to.

hackernewds
does it necessitate foreign keys and schemas being defined beforehand? or is that the "AI magic"?
croes
It has to be in defined beforehand.

No AI magic can know the foreign keys from the schema alone and even with data the results would be uncertain.

scotty79
If you are naming your foreign key fields consistently for example extras.products_id -> products.id or using some fairly unique data type like GUID for your keys it could be intelligently inferred.
croes
Still lots of hits and misses.

Column names could sound like foreign keys on pure chance and the data type doesn't show which other table it's referring to.

parpfish
If you’re allowed to query the data you could use the column name/data type to come up with lots of good candidates, and then if then verify by checking whether the actual values correspond (assuming that it’s something like a uuid or username column and not a serial/int column)
lolive
Newbie here. How do you express foreign key - primary key relationships when you define your schema, in Postgresql and/or SQLServer ? Is it via an integrity constraint, or can it be just an informative metadata ?
parpfish
I’m curious how frequently people actually encounter foreign key constraints in production.

I’m 10 years in and have only ever seen it once

croes
>Just curious about the emphasis on the single query.

Marketing?

And they need a single json to create the diagram from.

Jonathanfishner
Thanks for the feedback! The emphasis on a single query highlights how easy it is to import your database schema: just run the query, get the JSON output, and start editing your diagram in ChartDB. Plus, there’s no need to sign in or set your database credentials. It’s all about simplifying the process and saving time so you can get started quickly. Speed is a bonus, but the main value is the simplicity and ease of use. Glad you're giving it a try!
andrelaszlo
Not requiring db credentials is a huge plus! Thanks for the explanation.
gitroom
This is cool, but there are a lot of visualizer, what's the difference?
Jonathanfishner
Thank you first of all! ChartDB stands out with its one-query for visualization, AI-powered migration for easy cross-DB transitions, and interactive editing, all free and open-source, combining these features in a user-friendly way.
SahAssar
I'm not sold on many of those points, but "AI-powered migration" stood out to me (as something that seems like a risky idea with many unknown consequences). What do you mean by that and how would it be deployed?
nerdponx
This product seems primarily like a solution looking for a problem to solve. The rare team that actually needs this will probably only need it on a one-off basis, and so will probably hack it together ad-hoc or hire a consultant to do it. Perhaps the target market should be unscrupulous contractors.
djray
I rather think using AI to generate scripts for other DBs is a disadvantage. Scripting is a deterministic process, surely? Can you create a common internal representation and write exporters for each supported DBMS from that? Maybe the JSON format you use is a path toward this.

I dislike the multiple references to 'Magic' on the site, but I realise that's probably a subjective thing. Maybe I'm fatigued by the AI-in-everything trend.

I don't use all the DBMSes you support, but I'm not sure having a single query to run is really much of a selling point. For SQL Server, I'd rather execute a stored procedure with permission checks, and progress feedback, than a big chunk o' SQL. (Again, that may just be a me thing.) If it's an efficiency thing for very large DBs then I think you should emphasise that, and also detail how much faster it is to gather all the info in one fell swoop than if you split up the queries.

The Examples link is currently broken on your site, btw. That sounds like a really useful way that people can evaluate your application without having to run a query on their own DB, which they may be reticent to do for testing out a new app.

Jonathanfishner
Thanks for the feedback! We aimed to make database migration easier and found that AI could really enhance this process by handling the complexities of different DB dialects. We'll also explore more deterministic approaches, as you suggested, using a common internal representation. The "Examples" link is being fixed—thanks for catching that!
teleforce
Not a database expert but CUE can be used for generic schema design and validation, maybe it can also be used for deterministic database schema extraction and migration [1].

Unlike AI's non-deterministic techniques for example LLM approaches to NLP, CUE utilizes a well established alternative NLP deterministic approach namely Feature Structure [2], [3].

[1] Cue – A language for defining, generating, and validating data:

https://news.ycombinator.com/item?id=20847943

[2] Feature structure:

https://en.m.wikipedia.org/wiki/Feature_structure

[3] The Logic of CUE:

https://cuelang.org/docs/concept/the-logic-of-cue/

RadiozRadioz
The different dialects have complexities, but they are they are known and explicitly defined. This task is natural to solve deterministically, and I'd say it's also important to do so - accuracy and consistency are paramount when migrating. I am having an extremely hard time understanding what benefits AI has here over a simple transpilation approach.

Two benefits coming to mind are that AI is a good buzzword, and "shove it into ChatGPT, that'll do" takes less effort than building a proper transpiler.

bal00ns
Like you said I imagine it was less effort. Less effort can be very valuable if you’re still producing something useful. Perhaps you could use a simple transpilation approach yourself if you think that’s a good idea.
samstave
huh.. I like to have claude give me a DB schema in a mermaid diagram, infact - I make python scripts to log into the DB, list the DBs, list the schemas, and make a mermaid diagram of the schema and then I have a python script that converts the mermaid code to an SVG.
ok123456
How is this different from Schema Spy (https://schemaspy.org)?
layer8
It supports fewer databases. ;)
callumgare
A big one for me is that it generates a nice interactive UI with the ability to zoom and reorganise tables rather than a static image. I find with sufficiently complex projects it becomes a real pain to navigate a exported image.
ok123456
Schema Spy generates graphviz files. You could render those in the browser.
breadwinner
Or use a tool that already does the rendering, with zoom and pan: https://visualdb.com
ok123456
Or, generate an SVG from the dot file and pan and zoom like any other SVG file in the browser.
breadwinner
Then you won't be able to click on a table to view details in a popup.
webprofusion
Hmm, the obvious difference is that it's a completely different tool that does a completely different job.
ok123456
How is it an "obvious" difference that it's a "completely" different tool that does a "completely different" job? Both tools generate diagrams from existing databases.
jesperwe
I tried it out on a not so complex Postgres v15 DB. No relations were imported, just the tables?
Jonathanfishner
Thanks for giving it a try! I just tested it on an RDS PostgreSQL 15.4 instance, and it worked on my end. Would you mind opening a GitHub issue so we can dig into this further and help resolve it? Your input will be really helpful in fixing the issue.
ellyagg
Awesome! This is exactly what I wanted last year when I was helping my wife with her reporting and the only access she had to her company's application database was through Redash.
yetanotherdood
I think having multiple "views" of these diagrams would be nifty. In addition to the standard visualization, you could have an "index" visualization that also captures foreign keys etc.
rustman123
"PostgresSQL"? "SQL Lite"? Not even getting the names of the supported databases right doesn’t create trust…
[deleted]
(comment deleted)
doctorpangloss
On the other hand, the chatbots let enthusiasts like these guys write code. There are millions of them. And each of them, even if they are 1/2 as good as you rustman123, for starters they will learn the right names for things, that’s not challenging, and secondly, they have more time than you have for this problem, which you have zero.

The chatbots are the Zero to One for programming.

So listen I agree with you. I’m not going to use this. But hundreds of thousands of smart people with time, like these guys, can now author stuff that will get better over time faster than you personally will solve any one particular problem for anyone. So something something, log in your own eye before the thorn in the other.

jkcorrea
idk why you’re getting downvoted. you’re absolutely right and not disparaging in any way. sometimes hn can’t see the forest for the trees…
wesammikhail
> The chatbots are the Zero to One for programming.

This would make Peter himself cringe let alone the rests of us.

That said, if you really believe this, I've got an AI product to sell you. It's the Zero to One for anything you want. It's called HypeLLM.

thesnide
given enough monkeys typing, we would have the greatest novel of all kinds.

This is true. In theory. In practice, the real struggle is to find the gem in an ocean of garbage.

Something that cannot be found simply doesn't exists for any practical terms.

Nature has a way to triage its sheer numbers of genetic permutations : wastness of the world and mostly infinite time.

zx8080
We are entering into an era of AI-generated programs. Brace yourself and enjoy the shitshow.
[deleted]
(comment deleted)
salzig
> STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

Looks like not all databases (mssql in this case) like the generated result.

Eji1700
Yeup. Seconded. MSSQL as well. Was curious what the output would look like given it's a fairly large db (a crap ton of mostly useless tables kept for archival purposes).
Jonathanfishner
Thank you for catching that issue with the STRING_AGG limit! I just pushed a fix that now supports nvarchar(max) to avoid truncation. This should resolve the issue with large outputs in MSSQL. If you still encounter any problems, please let me know. I'd love for you to give it another try and see if everything works as expected now. Your feedback is invaluable!
salzig
now it kills the process (dev env, container).

Not sure what kills it, but without "columns" in "full_json_result" it runs through.

zola
Is there a tool with similar presentation, but for database records and not schema? So I could "select * from product where id=5" and it would show me Product card for "hammer" and related card for Brand "ACME", and other related records which might be many/one to one/many relationship etc. In other words - data exploration tool with diagramming like in here?

I just asked chatGPT for this, but human perspective would be great :)

webprofusion
Not bad! Could do with a layout option so the relations are easier to see.

The OG of DB table designers is the SQL Server database diagrams feature, and before that MS Access, and there is still room for improvement in this tool. Obviously saving changes back to your db (and migration scripts) is the most important feature, git tracking of schema changes would be good.

[deleted]
(comment deleted)
qwertydog
Just checked out the MS SQL script. I believe it's vulnerable to SQL injection via table/column/view/index/schema names (however unlikely that may be). You might want a disclaimer not to run the script with object names that could be user provided (especially as a privileged user)
Jonathanfishner
Thanks for pointing that out! Just to clarify, we provide a single, hardcoded query that fetches metadata, with no option for user input. The user only needs to take the generated JSON and use it in a React app that they can also run locally. Since there's no user input involved, SQL injection shouldn't be a concern here. If you see any specific risks, though, I’d love to hear more!
qwertydog
Ah sorry my bad, I should have read the full script. The script fails though if object names contain double quotes e.g. https://dbfiddle.uk/y-C6Da07
Jonathanfishner
Thanks for catching that! I’ve fixed it for you here: https://dbfiddle.uk/F2vuGKVS. I also tested the export script with a table name like "table", and it worked. If you don’t mind, could you open an issue in our repo? I’ll work on pushing a full fix today.
qwertydog
Just saw this. I see you've already pushed a fix on GitHub thanks. I can't see object ID's being included in the script, so I assume multiple objects with similar names will all be treated as the "same" object e.g. https://dbfiddle.uk/O_yZLjpN
ahaapple
I am curious about what great role AI can play in the chartdb project?
idorube
There's a typo on your "Quick Start" button. ("Quck Start")
1ilit
Reminds me a lot of my drawDB: https://www.drawdb.app/. *Not a shameless plug, actually*
azophy_2
actually both projects looks incredibly similar. perhaps both using the same UI framework or something?
clidey
Hey this is really cool! I love how sleek the interface is. We're doing something similar at WhoDB (https://github.com/clidey/whodb) and love seeing all the work happening in this space.

Congrats on the launch and happy building!

Disclaimer: I'm one of the cofounders of WhoDB.

jalopy
This is great. Anyone know how hard it would be to adapt to Athena?