30:Ta76,class OfflineDatabaseClient { constructor(config) { Object.defineProperties(this, { _db: { value: { exec: this.exec, ...config } } }); } async exec(sql, params) { var resp = await fetch(this.proxy, { body: JSON.stringify({ sql: sql, params: params, url: this.url }), method: "POST" }); var json = await resp.json(); return json.data; } 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":"Remote Database Proxy","cells":[{"id":267,"value":"banner2(\"Remote\", \"Database Proxy\", \"hsl(189, 29%, 82%)\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":197,"value":"html`

Observable provides database connectors that are secure (using secret keys and authentication), there is also a local database proxy that can be used to connect a notebook to your local network or from a VPN, but these still only work in private notebooks.

\n
\n

And you must use them in private mode! Exposing a database directly to a client on web is not safe, it's vulnerable to all sorts of hacks and attacks.

\n
\n

But, if you wish to connect a downloaded notebook or one hosted in a secure network directly to a database, it makes sense for few scenarios especialy while prototyping. In that case, you can use both the backend (nodejs) script and the offline client (from this notebook)

\n
\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":307,"value":"html`Hint: Heroku provides a free postgress hobby server with 10k rows, you can also go to splitgraph and get your own credentials and try them here! \n

\nexample: postgresql://<USERNAME>:<PASSWORD>@data.splitgraph.com:5432/ddn`","pinned":false,"hidden":false,"mode":"ojs"},{"id":342,"value":"html`Note: This client uses a runkit backend from https://runkit.com/a10k/database-proxy for demo, host this code on your own machine, its recommended you add additional authentication too, also this example only works for postgress but take a look at the observable github page on how the other connectors are setup. And read more about how to add additional security layers here: https://talk.observablehq.com/t/db-connections-public-notebooks/3827/12`","pinned":false,"hidden":false,"mode":"ojs"},{"id":272,"value":"viewof proxy = Text({\n  label: \"Proxy URL\",\n  width: 800,\n  value: `https://runkit.io/a10k/database-proxy/branches/master`,\n  submit: true\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":223,"value":"viewof connection_url = Text({\n  label: \"Connection URL\",\n  width: 800,\n  value: \"\",\n  submit: true\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":246,"value":"viewof query = Text({\n  label: \"Query\",\n  width: 800,\n  value: \"select * from pg_tables;\",\n  submit: true\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":290,"value":"md`---`","pinned":false,"hidden":false,"mode":"ojs"},{"id":240,"value":"viewof table = Table(result)","pinned":false,"hidden":false,"mode":"ojs"},{"id":133,"value":"result = {\n  try {\n    return await db.query(query);\n  } catch (e) {\n    return [{ ERROR: e + \", Check connection URL/Query\" }];\n  }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":129,"value":"db = new OfflineDatabaseClient({\n  url: connection_url,\n  proxy: proxy\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":123,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":219,"value":"import { Text, Table } from \"@observablehq/inputs\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":264,"value":"import { banner2 } from '@observablehq/bannertitles'","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}