Show HN: Visualize database schemas with a single query

227 64 2 years ago

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 :)

namanyayg• 2 years ago

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

Jonathanfishner8• 2 years ago

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• 2 years ago

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?

andrelaszlo2• 2 years ago

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• 2 years ago

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.

hackernewds2• 2 years ago

does it necessitate foreign keys and schemas being defined beforehand? or is that the "AI magic"?

croes3• 2 years ago

Just curious about the emphasis on the single query.

Marketing?

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

Jonathanfishner8• 2 years ago

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!

andrelaszlo2• 2 years ago

Not requiring db credentials is a huge plus! Thanks for the explanation.

gitroom• 2 years ago

This is cool, but there are a lot of visualizer, what's the difference?

Jonathanfishner8• 2 years ago

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• 2 years ago

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?

djray• 2 years ago

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.

Jonathanfishner8• 2 years ago

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• 2 years ago

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• 2 years ago

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.

samstave• 2 years ago

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.

hackernewds2• 2 years ago

what is your input to Claude

ok1234564• 2 years ago

How is this different from Schema Spy (https://schemaspy.org)?

layer8• 2 years ago

It supports fewer databases. ;)

callumgare• 2 years ago

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.

ok1234564• 2 years ago

Schema Spy generates graphviz files. You could render those in the browser.

webprofusion2• 2 years ago

Hmm, the obvious difference is that it's a completely different tool that does a completely different job.

ok1234564• 2 years ago

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• 2 years ago

I tried it out on a not so complex Postgres v15 DB. No relations were imported, just the tables?

Jonathanfishner8• 2 years ago

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• 2 years ago

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• 2 years ago

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• 2 years ago

"PostgresSQL"? "SQL Lite"? Not even getting the names of the supported databases right doesn’t create trust…

doctorpangloss• 2 years ago

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• 2 years ago

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• 2 years ago

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.

zx8080• 2 years ago

We are entering into an era of AI-generated programs. Brace yourself and enjoy the shitshow.

salzig2• 2 years ago

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• 2 years ago

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).

Jonathanfishner8• 2 years ago

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!

salzig2• 2 years ago

now it kills the process (dev env, container).

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

zola• 2 years ago

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 :)

webprofusion2• 2 years ago

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.

qwertydog3• 2 years ago

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)

Jonathanfishner8• 2 years ago

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!

qwertydog3• 2 years ago

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

ahaapple• 2 years ago

I am curious about what great role AI can play in the chartdb project?

idorube• 2 years ago

There's a typo on your "Quick Start" button. ("Quck Start")

1ilit• 2 years ago

Reminds me a lot of my drawDB: https://www.drawdb.app/. *Not a shameless plug, actually*

azophy_2• 2 years ago

actually both projects looks incredibly similar. perhaps both using the same UI framework or something?

amenghra• 2 years ago

If you want to see the single queries: https://github.com/chartdb/chartdb/blob/main/src/lib/data/im...

hemangkandwal• 2 years ago

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.

yencabulator• 2 years ago

Constructing JSON by hand is not a good idea: https://github.com/chartdb/chartdb/blob/66db34d9114104aaaafa...

jalopy• 2 years ago

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

Show HN: Visualize database schemas with a single query

Related Stories