31:T5cd,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]); 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 = TB ${tableNodes} ${tableReferences} }`; }32:[{"id":"b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115","href":"https://static.observableusercontent.com/files/b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115","name":"chinook.db","type":"application/x-sqlite3","lastModified":1657292876557.928,"size":884736}] 2f:{"title":"SQLite ER Diagram","cells":[{"id":0,"value":"# SQLite ER Diagram\n\nWe'll use [`chinook.db`](https://www.sqlitetutorial.net/sqlite-sample-database/) sample [SQLite](https://www.sqlite.org/about.html) database to create an [ER Diagram](https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model) with [Graphviz html-like node labels](https://graphviz.org/doc/info/shapes.html#html).","pinned":false,"hidden":false,"mode":"md"},{"id":6,"value":"db = FileAttachment('chinook.db').sqlite()","pinned":true,"hidden":false,"mode":"ojs"},{"id":227,"value":"db.queryRow(`SELECT sqlite_version()`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":208,"value":"[SQLite](https://observablehq.com/@observablehq/sqlite) notebook provides more information about using SQLite database files in Observable notebooks.","pinned":false,"hidden":false,"mode":"md"},{"id":66,"value":"## ER Diagram\n\n**Note**: Click on the `erDiagram` cell vertical **...** menu below to download it in SVG or PNG format.","pinned":false,"hidden":false,"mode":"md"},{"id":114,"value":"erDiagram = diagram(db)","pinned":false,"hidden":false,"mode":"ojs"},{"id":202,"value":"**See**: the original [Auto-generating an Entity Relationship Diagram from SQLite](https://observablehq.com/@chriszs/auto-generating-an-entity-relationship-diagram-erd-from-sq) notebook with plain [record-based dot diagram](https://graphviz.org/doc/info/shapes.html#record).","pinned":false,"hidden":false,"mode":"md"},{"id":61,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":13,"value":"## Database Tables","pinned":false,"hidden":false,"mode":"md"},{"id":23,"value":"db.describeTables()","pinned":true,"hidden":false,"mode":"ojs"},{"id":9,"value":"db.describe()","pinned":true,"hidden":false,"mode":"ojs"},{"id":26,"value":"db.describe('albums')","pinned":true,"hidden":false,"mode":"ojs"},{"id":15,"value":"db.describe('tracks')","pinned":true,"hidden":false,"mode":"ojs"},{"id":17,"value":"db.describeColumns({table: 'tracks'})","pinned":true,"hidden":false,"mode":"ojs"},{"id":28,"value":"tracks = db.query(`SELECT * FROM tracks`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":30,"value":"Inputs.table(tracks)","pinned":true,"hidden":false,"mode":"ojs"},{"id":32,"value":"SELECT * FROM tracks","pinned":true,"hidden":false,"mode":"sql","output":"data","database":"var:db"},{"id":183,"value":"SELECT 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 sqlite_master\nJOIN pragma_foreign_key_list(sqlite_master.name) as fk_info\nORDER BY sqlite_master.name","pinned":false,"hidden":false,"mode":"sql","output":"foreignKeysInfo","database":"var:db"},{"id":149,"value":"tables = await db.query(\"SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":136,"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"},{"id":160,"value":"tableDefinitions = tableStatements.filter(statement => statement.format === 'table')\n .map(statement => ({table: statement.name?.name, columns: statement.definition}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":163,"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"},{"id":166,"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"},{"id":169,"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"},{"id":36,"value":"sqlParser = require('sqlite-parser')","pinned":false,"hidden":false,"mode":"ojs"},{"id":38,"value":"import {dot} from '@gordonsmith/graphviz'","pinned":false,"hidden":false,"mode":"ojs"},{"id":99,"value":"import {displayDot} from '@oscar6echo/zoom-and-pan-graphviz-dot'","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}