SQL to ER Diagram

We present the way to draw your SQL queries as ER diagrams: a powerful tool for complex query understanding as well as creating database diagram from SQL queries!

Why is this useful?

✅ Save time: No more manual diagramming! Generate accurate ER diagrams in seconds.

✅ Understand someone else's code 😄: See join relationships clearly and easily.

✅ Communicate your database schema: Share your ER diagrams with co-workers, ensuing everyone is on the same page.

How it works

We develop an algorithm to parse your SQL query and identify the tables, columns, and relationships involved. It then uses this information to automatically generate an ER diagram that is accurate and easy to understand.

/* Funnel analysis with session flow */
CREATE TABLE marketing_funnel_analysis AS
SELECT 
    c.channel,
    c.campaign_name,
    COUNT(DISTINCT t.user_id) as users_touched,
    COUNT(DISTINCT s.user_id) as users_visited,
    COUNT(DISTINCT pv.session_id) as sessions_with_pageviews,
    COUNT(DISTINCT conv.user_id) as users_converted,
    SUM(conv.conversion_value) as total_conversion_value,
    -- Conversion rates
    COUNT(DISTINCT s.user_id) * 1.0 / NULLIF(COUNT(DISTINCT t.user_id), 0) as touch_to_visit_rate,
    COUNT(DISTINCT conv.user_id) * 1.0 / NULLIF(COUNT(DISTINCT s.user_id), 0) as visit_to_conversion_rate
FROM campaigns c

<p>-- Implicit JOINs! 👇<br>LEFT JOIN touchpoints t ON c.campaign_id = t.campaign_id<br>LEFT JOIN sessions s ON t.user_id = s.user_id AND s.referrer_campaign_id = c.campaign_id<br>LEFT JOIN page_views pv ON s.session_id = pv.session_id<br>LEFT JOIN conversions conv ON s.user_id = conv.user_id AND s.session_id = conv.session_id<br>GROUP BY c.channel,

Summary

Love this quote from the SeattleDataGuy here: "You might have to deal with many-to-many relationships or have a complex transactional data model with non-standard naming conventions forcing you to join fields like employee_id to person_id (which you wouldn’t know if the Sr. Engineer hadn’t told you)." 💬

Typically, data models rely on DDLs to generate ER diagrams. But there's a catch: without primary keys and foreign keys, you’re left with an incomplete view.

By examining how tables are joined in SQL queries, we can infer relationships and automatically populate the foreign keys and primary keys in your data model.