25:["$","title",null,{"children":"SQLite in Observable | Nolan Johnson | Observable"}] 49:[] 29:{"id":"dfd7ff4b622b7956","slug":null,"title":"SQLite in Observable","thumbnail":"a54b302310a842f99a4018a01a0f1c0e4acdd2c5f396980bcec036027dbd5941","access_level":"public","create_time":"$D2025-09-10T23:49:46.682Z","update_time":"$D2025-09-10T23:49:46.682Z","view_time":null,"first_public_time":"$D2025-09-10T23:49:46.682Z","has_unpublished_edits":null,"fork":{"id":"8b08ddaf3afd0e33","slug":"sqlite-in-observable","title":"SQLite in Observable","owner":{"id":"4e72771d598f1f0e","login":"nolan-ga","name":"Nolan Johnson"}},"stars":0,"starred":false,"owner":{"type":"team","id":"b4d4c6f199c86820","login":"nolanbjohnson","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/57cd1ae90cfb935890f756de647048aeadeec3cf8dfd56576b3dbd94fafb7906","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":509,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"b8ce3f003f06f805","login":"nolanbjohnson","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/57cd1ae90cfb935890f756de647048aeadeec3cf8dfd56576b3dbd94fafb7906","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"team","id":"b4d4c6f199c86820","login":"nolanbjohnson","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/57cd1ae90cfb935890f756de647048aeadeec3cf8dfd56576b3dbd94fafb7906","tier":"starter_2024","role":"editor"},{"type":"individual","id":"b8ce3f003f06f805","login":"nolanbjohnson","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/57cd1ae90cfb935890f756de647048aeadeec3cf8dfd56576b3dbd94fafb7906","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"SQLite in Observable","cells":[{"id":0,"value":"md`# SQLite in Observable\n\nThank you, [sql.js!](https://github.com/kripken/sql.js/)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"md`If you're new to SQL, you may want to check out [Learn SQL in 10 minutes](https://beta.observablehq.com/@nolan-ga/learn-sql-in-10-minutes) first. Then come back here to test out your SQL skills.\n\n**Instructions**\n1. Give your SQL table a name.\n2. Upload a CSV file (column names need to be SQL-friendly*).\n3. Write some SQL! You can download the output of your SQL using the \\`Download\\` button.\n\n**Some names may change to be more friendly, namely lowercase and underscores instead of spaces are used.*\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":467,"value":"html`1. Give the SQL Table a name
`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":62,"value":"viewof tableName = text({value: \"table_name\", placeholder: \"Give your table a name\", description: \"If it's more than one word, use underscores instead of spaces.\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":463,"value":"html`2. Upload a CSV file
`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":30,"value":"viewof csvFile = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":245,"value":"viewof tableDef = checkbox({\n // description: \"Check to pre-populate all the SQL answers\",\n options: [{ value: \"toggle\", label: \"Collapse table definitions\" }],\n value: \"\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":254,"value":"{\n let resultSet = db.exec(`SELECT * FROM ${tableName} LIMIT 1; SELECT count(*) FROM ${tableName};`),\n fieldTypes = sqlTypeOf(tableName),\n recordCount = resultSet[1].values;\n return tableDef ? md`___` : html`

\n ${tableName} (rows=${recordCount})\n

${field} (${type})

`\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":93,"value":"viewof sqlStatement = textarea({\n title: \"3. Write SQL below and hit 'Run'\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: `SELECT *\\nFROM ${tableName}\\nLIMIT 5`,\n // spellcheck: true,\n width: \"40%\",\n rows: 9,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":95,"value":"dbRecords(sqlStatement, 14)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":483,"value":"md`___\n#### To Download, Click 'Save' Below\n\n*all rows will download even if they aren't showing above*`\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"{ \n if(output !== undefined) {\n return DOM.download(\n new Blob([d3.csvFormat(output)], {type: \"text/csv\"}),\n \"output.csv\"\n )\n } else {\n return md`Run a query`\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":430,"value":"md`Learn more about SQLite:\n[aggregates](https://sqlite.org/lang_aggfunc.html), [functions](https://sqlite.org/lang_corefunc.html), and [date-handling](https://www.sqlite.org/lang_datefunc.html).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":410,"value":"md`### Advanced Options`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":407,"value":"md`By default, all fields are created as 'text'. Select the fields below that should be converted to 'numeric', which can include integers, floats, and even dates`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":112,"value":"viewof numericFields = {\n return checkbox({\n description: \"Checked columns will be created as type 'numeric'\",\n options: tableCols.map(col=> ({value: col, label: col})) //columns.map(col => ({value: col, label: col}))\n })\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":188,"value":"dbRecords(`PRAGMA table_info(${tableName});`, 100)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":141,"value":"md`___\n## Appendix`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":372,"value":"html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":97,"value":"output = sqlOutput(sqlStatement)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":303,"value":"tableCols = Object.keys(data[0]).map(d=> (sqlify(d)))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":12,"value":"function tableView({columns, values}) {\n // code from @rumkin/tableview -- had to rename inputs, so I just copy/pasted\n const table = this || html`

`;\n \n const {tHead, tBodies} = table;\n while(tHead.firstChild) {\n tHead.removeChild(tHead.firstChild);\n }\n tHead.appendChild(html`${columns.map(h => html`${cellView(h)}`)}`);\n \n const tBody = tBodies[0];\n \n while(tBody.firstChild) {\n tBody.removeChild(tBody.firstChild);\n }\n \n values.forEach((row, i) => {\n tBody.appendChild(html`${row.map(cell => html`${cellView(cell)}`)}`);\n });\n \n return table;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":14,"value":"function cellView(value) {\n // code from @rumkin/tableview -- had to rename inputs, so I just copy/pasted\n if (value instanceof HTMLElement) {\n return value;\n }\n else {\n return DOM.text(value);\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":18,"value":"function head(records, k=10) {\n if (records.values.length < k) { return records };\n let data = records\n data.values = data.values.filter((_, i) => i < k)\n return data;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":475,"value":"limitRows = 1000","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":32,"value":"data = d3.csvParse(await Files.text(csvFile), (d,i)=> { if(i {\n if (\"\" in d) { delete d[\"\"] } // empty string extra column is common from Excel CSV\n // TODO check if key starts with number, invalid for SQL\n return d;\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":146,"value":"db = createDB(sql, data, tableName, numericFields)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":170,"value":"function sqlTypeOf(tableName) {\n function toObject(names, values) {\n var result = [];\n for (var i = 0; i < names.length; i++)\n result.push({field: names[i], type: values[i]});\n return result;\n }\n let limitOne = db.exec(`SELECT * FROM ${tableName} LIMIT 1`)[0], // base query for column names\n columns = limitOne.columns,\n typeOfs = Object.values(columns).map(d=> (`typeof(${d})`)).join(\", \"), // query string for typeof\n sqlTypeOfTemplate = `SELECT ${typeOfs} FROM ${tableName} LIMIT 1;`, \n resultSet = db.exec(sqlTypeOfTemplate)[0];\n limitOne.values = resultSet // replace the base query values with typeof values\n return toObject(limitOne.columns, resultSet.values[0])\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":161,"value":"function sqlOutput(sql) {\n let resultSet = db.exec(sqlStatement)[0];\n \n if(!resultSet) { return undefined }\n \n let columns = resultSet.columns, \n rows=resultSet.values, \n n=columns.length,\n records = [];\n // return rows\n rows.map(row => { \n let obj={};\n for (let i=0; i nRows) {\n return html`${tableView(head(records, nRows))}
...showing first ${nRows} of ${recordCount} rows`\n } else {\n return tableView(head(records, nRows))\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":143,"value":"function createDB(engine, data, tableName, numericFields) {\n let db = new engine.Database();\n const sqldata = data,\n table = tableName || \"table_name\",\n cols = Object.keys(sqldata[0]).map(d=> (`${sqlify(d)}${(numericFields.indexOf(sqlify(d)) > -1) ? \" numeric\" : \"\"}`)).join(\", \"),\n insertCols = Object.keys(sqldata[0]).map(d=> (sqlify(d))).join(\", \"),\n placeholders = Object.keys(sqldata[0]).fill('?').join(\", \");\n \n let sqlCreateTable = `CREATE TABLE ${table} (${cols});`;\n console.log(numericFields)\n console.log(sqlCreateTable)\n db.run(sqlCreateTable) // create table\n \n let sqlInsertTemplate = `INSERT INTO ${table} (${insertCols}) VALUES (${placeholders});`;\n \n sqldata.forEach(insert => {\n db.run(sqlInsertTemplate, Object.values(insert));\n })\n // db.run(sqlstr);\n return db\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":16,"value":"import {text, textarea, checkbox} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":4,"value":"sql = require('sql.js@0.5.0/js/sql.js')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":195,"value":"_ = require('lodash')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":28,"value":"d3 = require(\"d3\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W49","stdlib":"1"}} 24:null