25:["$","title",null,{"children":"Self-hosted database proxies | John E Freier | Observable"}] 49:T4b7,viewof form = { const initial = { protocol: "postgresql", user: "visnup", password: "p@ssw0rd", host: "127.0.0.1", port: "5432", database: "testdb" }; const form = html` `; form.value = initial; form.addEventListener("input", (event) => { form.value[event.target.id] = event.target.value; }) return form; }4a:T5d1,tls = md`### SSL/TLS If you’re using Chrome or Edge and running the database proxy on your local computer (at 127.0.0.1), you can connect to it directly with HTTP — there’s no need to jump through the hoops of setting up a self-signed SSL certificate for the proxy, as the HTTP connection is entirely local to your computer. 💡 _We recommend using Chrome to connect to self-hosted database proxies running on 127.0.0.1._ To get them working with current versions of Firefox and Safari requires creating, installing and trusting a self-signed SSL certificate, which isn’t easy to set up. But if you still wish to use them with Firefox or Safari, here are the advanced instructions: 1. [Create and trust a self-signed SSL certificate](https://gist.github.com/cecilemuller/9492b848eb8fe46d462abeb26656c4f8) for the database proxy to use (or use a tool like [mkcert](https://github.com/FiloSottile/mkcert)). 2. Pass the \`--sslcert\` and \`--sslkey\` options, pointing to your newly created certificate, to the database proxy. When you’re creating the connection in Observable, make sure that the “Require SSL/TLS” option is selected. 3. Visit https://127.0.0.1:2899/ and click “Accept the risk” to allow Firefox to use your self-signed certificate. and configure the database proxy to use it in order to proxy over HTTPS. 4. Now you can use your database connector in Firefox or Safari.`4b:[{"id":"1f4d30260716207a5f2509c67882ad54958900c13e3b9e8e405c6a05a247496c88a769bbd079f634d15a9687d0d3bc00e4b32a96810ce01cfa4b3e3c62a313b1","href":"https://static.observableusercontent.com/files/1f4d30260716207a5f2509c67882ad54958900c13e3b9e8e405c6a05a247496c88a769bbd079f634d15a9687d0d3bc00e4b32a96810ce01cfa4b3e3c62a313b1","name":"image.png","type":"image/png","lastModified":1700882861754.333,"size":90187},{"id":"c02ee3b7ae5e550ae22b710d847db1e23756b55e8016407d2d82a8e974581ea264d1c65b507f2bef4bade1a4f52ad990f6008774a7203f1e1cb5c4467df3ca4a","href":"https://static.observableusercontent.com/files/c02ee3b7ae5e550ae22b710d847db1e23756b55e8016407d2d82a8e974581ea264d1c65b507f2bef4bade1a4f52ad990f6008774a7203f1e1cb5c4467df3ca4a","name":"image@1.png","type":"image/png","lastModified":1700882861754.333,"size":79479}] 29:{"id":"5ffe96473c557ac8","slug":null,"title":"Self-hosted database proxies","thumbnail":"0ebb26ec4698970a60991ca0c39f6af279eeaf0924f82b6b1c1746c05310078e","access_level":"public","create_time":"$D2023-11-25T03:27:41.754Z","update_time":"$D2023-11-25T03:27:41.754Z","view_time":null,"first_public_time":"$D2023-11-25T03:27:41.754Z","has_unpublished_edits":null,"fork":{"id":"19c2395542095579","slug":"self-hosted-database-proxies","title":"Self-hosted database proxies","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"e8e2b1fcb956e4d5","login":"gofullthrottle","name":"John E Freier","avatar_url":"https://avatars-next.observableusercontent.com/94ced36504de6c16812033f876f60a9874c91a7439f4f99aed3a8bce5fe86906","tier":"starter_2024","bio":"Serial entrepreneur, statistician & machine learning enthusiast, data hoarder, data modeler, hacker & maker, web dev, innovator, dirt biker.\n\nI like collecting, analyzing, and modeling massive amounts of data. Feels like treasure hunting.","followed_by_you":false,"follows_you":false},"version":567,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"7bf910b8a95c8e3c","login":"gofullthrottle","name":"John E Freier","avatar_url":"https://avatars-next.observableusercontent.com/94ced36504de6c16812033f876f60a9874c91a7439f4f99aed3a8bce5fe86906","tier":null,"bio":"Serial entrepreneur, statistician & machine learning enthusiast, data hoarder, data modeler, hacker & maker, web dev, innovator, dirt biker.\n\nI like collecting, analyzing, and modeling massive amounts of data. Feels like treasure hunting.","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"7bf910b8a95c8e3c","login":"gofullthrottle","name":"John E Freier","avatar_url":"https://avatars-next.observableusercontent.com/94ced36504de6c16812033f876f60a9874c91a7439f4f99aed3a8bce5fe86906","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Self-hosted database proxies","cells":[{"id":0,"value":"md`# Self-hosted database proxies\n\nObservable’s hosted [database clients](/@observablehq/connecting-to-databases) rely on an HTTPS-to-database proxy, which allows your browser to securely run SQL queries against databases that are accessible from the public internet.\n\nBut if your database isn’t accessible from the public internet (say, because you’re running it on your personal computer, or on a private network, or within a VPN) — or if you simply don’t wish to share any database credentials with Observable — that won’t work.\n\nSo we also provide a small, [open-source](https://github.com/observablehq/database-proxy) Node.js database proxy, [available on npm](https://www.npmjs.com/package/@observablehq/database-proxy) that you can run yourself. By connecting directly from your browser to the proxy, your private notebooks can access databases on private networks or local computers, that traffic never goes over the public internet, and you don’t need to trust Observable with any database credentials.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":454,"value":"## Setup","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":514,"value":"md`### Install \\`@observablehq/database-proxy\\` with npm or Yarn:\n\n${codeblock(`\nnpm install -g @observablehq/database-proxy \n`)}\n${codeblock(`\nyarn global add @observablehq/database-proxy\n`)}\n\nOn initial startup, the proxy will walk you through configuration.\n\n${codeblock(`\nobservable-database-proxy add testdb\n`)}\n\nObservablehq.com will open to your database settings page, and offer to configure a new self-hosted database proxy. \n`\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":517,"value":"

\n \n

Setting a proxy to a local PostgreSQL database

\n \n

Copying the shared secret

\n","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":449,"value":"credentials = md`### Credentials\n\nNext, tell the proxy about the database credentials you’d like to use locally to connect to your database, in the form of a PostgreSQL, MySQL, or Snowflake URL, for example:\n\n${codeblock(`\n postgresql://visnup@127.0.0.1/testdb\n`, '')}\nor\n${codeblock(`\n mysql://dbuser:dbpassword@127.0.0.1/testdb\n`, '')}\n\n*Note: If you’re proxying to a database over SSL, you can add it as a URL parameter. For example, for PostgreSQL, with \\`?sslmode=no-verify\\`, or for MySQL/MongoSQL with \\`sslMode=REQUIRED\\`.*\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":566,"value":"Be sure to encode special characters in any of the fields. To help with this, you can use the form below to construct a URL.\n\nCopy and paste this URL: ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":395,"value":"url = {\n const {protocol, user, password, host, port, database} = form;\n const url = `${protocol}://${encodeURIComponent(user)}${\n password.length ? ':' + encodeURIComponent(password) : ''}${\n user.length + password.length > 0 ? '@' : ''}${\n host}${\n port.length ? ':' + port : ''}${\n database.length ? '/' + encodeURIComponent(database) : ''}`;\n return md`${url}`;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":377,"value":"$49","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":465,"value":"md`⚠️ _Be wary of any form on the internet prompting you for a password! Feel free to inspect this code to ensure nothing nefarious is happening. Or, use it as an example of using \\`encodeURIComponent\\` to encode special characters._`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":348,"value":"## Usage\n\nTo boot up the proxy:\n\n${codeblock(`\nobservable-database-proxy start testdb\n`)}\n\nHere’s the full list of commands:\n\n- \\`start <name> [ssl options]\\` *(Start a database proxy server)*\n- \\`add <name>\\` *(Add a new database proxy configuration)*\n- \\`remove <name>\\` *(Remove an existing database proxy configuration)*\n- \\`reset <name>\\` *(Reset the shared secret for an existing database proxy configuration)*\n- \\`list\\` *(List all configured database proxies)*","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":31,"value":"$4a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":224,"value":"md`### Querying\nOnce your database proxy is up and running, you can create a database client connected to it by calling \\`DatabaseClient(\"name\")\\` in a private notebook.\n\n[For more information on querying with Database Clients, see the introductory notebook.](https://observablehq.com/d/a856f2a939b81d53)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":189,"value":"md`### Configuration file\nYour database credentials and secret key for any configured local proxies are stored on your machine in \\`~/.observablehq\\`. If you restart the database proxy, it will read previous configuration from this file.\n\nIf you want to reset everything, delete this file.\n\nIf you want to remove just a single database proxy configuration, run:\n\n${codeblock(`\nobservable-database-proxy remove \n`)}\n\nIf desired, you can reset the shared secret for a local database proxy from your **Databases** settings page. With the new secret in hand, run: \n${codeblock(`\nobservable-database-proxy reset \n`)}\n\\u{200b}\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":538,"value":"md`### Using with Oracle\n\nUsing the self hosted database proxy with an Oracle database requires a few extra steps in the installation. Please refer to the [README](https://github.com/observablehq/database-proxy#installing-for-oracle-databases) for more information.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":532,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":366,"value":"viewof codeblock = {\n const css = html``\n css.value = (content, prefix = '$ ') =>\n html`\n

${prefix\n        ? content.trim().split('\\n').map(line => html`\n            ${DOM.text(line + '\\n')}\n          `)\n        : DOM.text(content.trim())}

\n `\n\n return css\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4b","stdlib":"1"}} 24:null