30:T90e,class SQLiteDatabaseClient { constructor(buffer) { Object.defineProperties(this, { _db: {value: Promise.resolve(buffer).then(buffer => new SQLite(buffer))} }); } async query(sql, params) { return (await this._db).exec(sql, params); } async queryRow(sql, params) { return (await this.query(sql, params))[0]; } async explain(sql, params) { const rows = (await this.query(`EXPLAIN QUERY PLAN ${sql}`, params)); const text = rows.map(row => row.detail).join("\n"); const pre = document.createElement("PRE"); pre.className = "observablehq--inspect"; pre.appendChild(document.createTextNode(text)); return pre; } async describe(object) { if (object !== undefined) { const [row] = await this.query(`SELECT * FROM '${object}' LIMIT 1`); const value = Object.entries(row).map(([column_name, value]) => ({ column_name, data_type: typeof value === "string" ? "character varying" : typeof value === "number" ? "integer" : undefined, column_default: null, is_nullable: "YES" })); const table = html` ${value.map(({column_name, data_type, column_default, is_nullable}) => html` `)}

column_name	data_type	column_default	is_nullable
${column_name}	${data_type}	${column_default}	${is_nullable}

`; table.value = value; return table; } else { const rows = await this.query(`SELECT name FROM sqlite_master WHERE type = 'table'`); const table = html` ${rows.map(({name}) => html` `)}

name
${name}

`; table.value = [{table_schema: "public", table_name: "names"}]; return table; } } }31:[] 2e:{"title":"SQLite","cells":[{"id":0,"value":"# SQLite\n\n

***Update May. 2021:*** *[SQLite](/@observablehq/sqlite) is now available as a supported [file attachment](/@observablehq/file-attachments) format on Observable, and SQLiteDatabaseClient is part of the Observable standard library! This notebook will remain for history, but please upgrade.*

\n\nThis notebook demonstrates how to load and query a SQLite database in an Observable notebook using an Emscripten port of SQLite, [SQL.js](https://github.com/sql-js/sql.js). As an example, we’re going to load the Los Angeles Times’ [Consumer Price Index database](https://github.com/datadesk/cpi), which is derived from data published by the Bureau of Labor Statistics. This is a 93.5MB database, so you might have to wait a little bit for it to load.","pinned":false,"hidden":false,"mode":"md"},{"id":37,"value":"cpi = SQLite.open(\"https://raw.githubusercontent.com/datadesk/cpi/d582252c644b90d48918a4cba951baf3c03c9629/cpi/cpi.db\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":73,"value":"md`To see what’s in the database, we can query the table metadata:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":53,"value":"cpi.exec(\"SELECT * FROM sqlite_master WHERE type = 'table'\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":75,"value":"md`For example, here’s the entire contents of the cu.series table:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":23,"value":"cpi.exec(\"SELECT * FROM 'cu.series'\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":96,"value":"md`Or, a more restrictive query:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":77,"value":"cpi.exec(`SELECT *\nFROM 'cu.series'\nWHERE item_code = 'SA0'\nAND area_code = 'A214'\nORDER BY begin_year ASC`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":211,"value":"md`Or with positional parameters:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":210,"value":"cpi.exec(`SELECT *\nFROM 'cu.series'\nWHERE item_code = ?\nAND area_code = ?\nORDER BY begin_year ASC`, ['SA0', 'A214'])","pinned":true,"hidden":false,"mode":"ojs"},{"id":66,"value":"md`---\n\n## Implementation\n\nTo use this in your notebook:\n\n\\`\\`\\`js\nimport {SQLite} from \"@mbostock/sqlite\"\n\\`\\`\\``","pinned":false,"hidden":false,"mode":"ojs"},{"id":31,"value":"class SQLite {\n constructor(buffer) {\n Object.defineProperties(this, {\n _db: {value: new SQL.Database(buffer && new Uint8Array(buffer))}\n });\n }\n dispose() {\n this._db.close();\n }\n async exec(sql, params) {\n if (params !== undefined) {\n let i = -1;\n sql = sql.replace(/\\?/g, () => {\n const param = params[++i];\n return Array.isArray(param)\n ? new Array(param.length).fill(\"?\")\n : \"?\";\n });\n params = params.flat(1);\n }\n const [result] = await this._db.exec(sql, params);\n if (!result) return [];\n const {columns, values} = result;\n return values.map(row => {\n const object = {};\n for (let i = 0; i < row.length; ++i) object[columns[i]] = row[i];\n return object;\n });\n }\n static async open() {\n return new SQLite(await fetch(...arguments).then(response => response.arrayBuffer()));\n }\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":133,"value":"SQL = {\n const [init, path] = await Promise.all([\n require(\"sql.js@1\"),\n require.resolve(\"sql.js@1/dist/\").then(path => path.replace(/\\.js$/, \"\"))\n ]);\n return init({locateFile: file => path + file});\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":104,"value":"$30","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}