2b:null 2c:["$","title",null,{"children":"Remote Database Proxy | Alok Pepakayala | Observable"}] 31: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; } } }32:[] 2f:{"id":"c53843b52fa26a57","slug":"remote-database-proxy","title":"Remote Database Proxy","thumbnail":"1a63a4477595652385b969f4bdd05bb10a37baf0944bc17854aedc16ab435b90","access_level":"public","create_time":"$D2020-06-03T15:24:22.060Z","update_time":"$D2021-04-11T18:17:40.463Z","view_time":null,"first_public_time":"$D2021-02-14T02:00:39.421Z","fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"ebdd8a722ae381e2","login":"a10k","name":"Alok Pepakayala","avatar_url":"https://avatars-next.observableusercontent.com/f28a7b5f4fb16a63caaace8a87a531e60bea383d6e9d50e126570c77c0814ba9","tier":"starter_2024"},"version":346,"publish_version":346,"role":null,"authored":false,"authors":[{"type":"individual","id":"e594cd7b50bd5352","login":"a10k","name":"Alok Pepakayala","avatar_url":"https://avatars-next.observableusercontent.com/a79accbaf1cb39d92bac556179588a03429b7ed84c64707b7ae8d86c9ffacfc7","tier":null}],"members":[{"type":"individual","id":"e594cd7b50bd5352","login":"a10k","name":"Alok Pepakayala","avatar_url":"https://avatars-next.observableusercontent.com/a79accbaf1cb39d92bac556179588a03429b7ed84c64707b7ae8d86c9ffacfc7","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"subscribed":null,"forks":3,"collection":null,"type":"notebook","body":{"title":"Remote Database Proxy","cells":[{"id":267,"value":"banner2(\"Remote\", \"Database Proxy\", \"hsl(189, 29%, 82%)\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":290,"value":"md`---`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":240,"value":"viewof table = Table(result)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":129,"value":"db = new OfflineDatabaseClient({\n  url: connection_url,\n  proxy: proxy\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":123,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":219,"value":"import { Text, Table } from \"@observablehq/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":264,"value":"import { banner2 } from '@observablehq/bannertitles'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"}],"files":"$W32","stdlib":"1","titling":"auto"}}
30:"$2f:body"