2e:null 2f:["$","title",null,{"children":"Datasette ER Diagram | Taras Novak | Observable"}] 34:T65a,async function diagram(db) { const tables = (await db.query("SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'")) .filter(({sql}) => sql) .map(({sql}) => sqlParser(sql)) .map((ast) => ast.statement[0]) .filter(statement => statement.format === 'table' && !statement.name?.name.includes('_fts_')) // filter out hidden system tables const tableNodes = tables.map((table) => `"${table.name.name}" [shape=none, label=< ${table.definition.filter(({variant}) => variant === 'column').map(({name, datatype}) => ` `).join('')}}

${table.name.name}
${name}	${datatype?.variant} ${datatype?.args?.expression?.[0]?.value ? `(${datatype?.args?.expression?.[0]?.value})` : ''}

>]`).join('\n'); const tableReferences = (await db.query(` SELECT sqlite_master.name as from_table, fk_info.[from] as from_column, fk_info.[table] as to_table, fk_info.[to] as to_column FROM sqlite_master JOIN pragma_foreign_key_list(sqlite_master.name) as fk_info ORDER BY sqlite_master.name `)).map((reference) => `${reference.from_table}:${reference.from_column} -> ${reference.to_table}:${reference.to_column}`).join('\n'); return dot`digraph "ERD" { node [fontsize=12] rankdir = LR ${tableNodes} ${tableReferences} }`; }35:[] 32:{"id":"3675c93ac5417902","slug":"datasette-er-diagram","title":"Datasette ER Diagram","thumbnail":"00a332edc5d6ba1b9bd331d6a906fc16c1253ea7497b4a6571f9458127be9c99","access_level":"public","create_time":"$D2022-07-09T13:03:53.048Z","update_time":"$D2022-12-24T14:35:53.872Z","view_time":null,"first_public_time":"$D2022-07-09T13:39:51.175Z","has_unpublished_edits":null,"fork":false,"stars":4,"starred":false,"owner":{"type":"team","id":"da0961e4e88ca7cb","login":"randomfractals","name":"Taras Novak","avatar_url":"https://avatars-next.observableusercontent.com/57d3bf95c5ecfa9ee3b08dec1a71504412704a6788f769860bf9d5191e789336","tier":"starter_2024"},"version":229,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"17b72db66175b428","login":"randomfractals","name":"Taras Novak","avatar_url":"https://avatars-next.observableusercontent.com/57d3bf95c5ecfa9ee3b08dec1a71504412704a6788f769860bf9d5191e789336","tier":null}],"members":[{"type":"individual","id":"17b72db66175b428","login":"randomfractals","name":"Taras Novak","avatar_url":"https://avatars-next.observableusercontent.com/57d3bf95c5ecfa9ee3b08dec1a71504412704a6788f769860bf9d5191e789336","tier":null,"role":"editor"}],"comments":[{"id":"a5be86eb2e3357e8","reply_to_id":null,"content":"I'd recommend to add ranksep (e.g ranksep = 3) to your diagram so that the lines are less cluttered.","create_time":"$D2023-11-05T12:09:24.186Z","update_time":null,"resolved":false,"user":{"type":"individual","id":"07362516b5994994","login":"mootari","name":"Fabian Iwand","avatar_url":"https://avatars-next.observableusercontent.com/0d7defa821f38094c03bad23b9b360a5364e6e97e21fc238c39ddc48db7994ad","tier":null}}],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":3,"collection":{"id":"729de16217a66212","slug":"tables","title":"Tables","owner":{"id":"da0961e4e88ca7cb","login":"randomfractals","name":"Taras Novak"}},"type":"notebook","body":{"title":"Datasette ER Diagram","cells":[{"id":0,"value":"# Datasette ER Diagram","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":76,"value":"## Datasette Source\n\n**Enter [Datasette](https://datasette.io/examples) Url:**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"viewof dataUrl = Inputs.text({\n label: html`Datasette Url`,\n placeholder: 'type datasette url and click fetch', \n value: dataUrlParam,\n submit: 'fetchData'\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":7,"value":"dataUrlParam = params.get('dataUrl') || 'https://github-to-sqlite.dogsheep.net/github'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":14,"value":"params = new URL(location).searchParams","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":70,"value":"## Datasette Client\n\n[Datasette Client](https://observablehq.com/@ambassadors/datasette-client) notebook provides convenient [DatabaseClient](https://observablehq.com/@observablehq/database-client-specification) API implementation to load datasettes.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":82,"value":"import {DatasetteClient} from '@ambassadors/datasette-client'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":85,"value":"data = new DatasetteClient(dataUrl)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":51,"value":"## Datasette Diagram\n\nSee the original [Datasette Table Diagram](https://observablehq.com/@simonw/datasette-table-diagram) notebook for a simple datasette diagram example with only foreign key table column references.\n\n**Note**: Click on the `erDiagram` cell vertical **...** menu below to download it in SVG or PNG format for a full view of large Datasette ER diagrams.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":98,"value":"erDiagram = diagram(data)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":93,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":95,"value":"sqlParser = require('sqlite-parser')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":61,"value":"import {dot} from '@gordonsmith/graphviz'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":38,"value":"## Datasette Tables","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":104,"value":"tables = await data.query(\"SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":111,"value":"tableStatements = tables.filter((table) => table.sql)\n .map((table) => sqlParser(table.sql))\n .map((ast) => ast.statement[0]);","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":114,"value":"tableDefinitions = tableStatements.filter(statement => statement.format === 'table')\n .map(statement => ({table: statement.name?.name, columns: statement.definition}))\n .filter(definition => !definition.table.includes('_fts_')) // filter out hidden system table","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":183,"value":"tableConstraints = tableDefinitions.map(table => ({\n table: table.table,\n constraints: table.columns.filter(column => column.definition.length > 0)\n .map(column => ({\n column: column.name,\n type: column.definition[0].variant,\n contstraint: column.definition[0]\n }))\n}))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":199,"value":"primaryKeys = tableConstraints.map(table => ({\n table: table.table,\n primaryKey: table.constraints.filter(constraint => constraint.type === 'primary key')[0].column\n}))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":203,"value":"foreignKeys = tableConstraints.map(table => ({\n table: table.table,\n foreignKeys: table.constraints.filter(constraint => constraint.type === 'foreign key')\n}))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":122,"value":"Inputs.table(tableDefinitions, {\n format: {\n columns: d => d.length\n }\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":208,"value":"SELECT\n sqlite_master.name as from_table,\n fk_info.[from] as from_column,\n fk_info.[table] as to_table,\n fk_info.[to] as to_column\nFROM\n sqlite_master\n JOIN pragma_foreign_key_list(sqlite_master.name) as fk_info\nORDER BY\n sqlite_master.name","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"foreignKeysInfo","database":"var:data"},{"id":41,"value":"data.describe(tableDefinitions[0].table)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":45,"value":"SELECT * FROM users","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"dataTable","database":"var:data"}],"files":"$W35","stdlib":"1","titling":"auto"}} 33:"$32:body"