25:["$","title",null,{"children":"Connecting to databases | John E Freier | Observable"}] 49:T589,Observable database clients allow secure and convenient querying of SQL databases from private notebooks. You can explore the contents of a database table using [the Data Table cell](https://observablehq.com/@observablehq/data-table-cell?collection=@observablehq/getting-data-in-and-out), query the database using [the SQL cell](https://observablehq.com/@observablehq/sql-cell?collection=@observablehq/getting-data-in-and-out), or feed query results directly into visualizations.

This walkthrough discusses connecting notebooks to databases that are reachable from the public internet, with the connection hosted by Observable. If you wish to use your notebooks to query a database on your local computer, or on a private network, we also provide a [self-hosted database proxy](https://observablehq.com/@observablehq/self-hosted-database-proxies) that you may prefer to use.4a:[{"id":"040ad736cb88c560cbc2a03160a31d42904161b18d6a0272427024c9a86137c0f79b53d33eae5a760c29797688a139ddc4ed9ca206e75f52abab007c53e4fac5","href":"https://static.observableusercontent.com/files/040ad736cb88c560cbc2a03160a31d42904161b18d6a0272427024c9a86137c0f79b53d33eae5a760c29797688a139ddc4ed9ca206e75f52abab007c53e4fac5","name":"db-access+query.mov","type":"video/quicktime","lastModified":1700882751384.851,"size":22877202},{"id":"90a6448e35c82a6e39ae07cf9132e68ff66a3ed272d6dddef004c35b041941712853077c3fca31cd081b42595208dc9dfaca9e059a79be1f43adfe9340b13eb5","href":"https://static.observableusercontent.com/files/90a6448e35c82a6e39ae07cf9132e68ff66a3ed272d6dddef004c35b041941712853077c3fca31cd081b42595208dc9dfaca9e059a79be1f43adfe9340b13eb5","name":"db-setup.png","type":"image/png","lastModified":1700882751384.851,"size":97652}] 29:{"id":"934997896c6eec4c","slug":null,"title":"Connecting to databases","thumbnail":"69c7b493cf9397caa84b06be593fdff94205bc48cc6b71ddc61077fe3aabea62","access_level":"public","create_time":"$D2023-11-25T03:25:51.384Z","update_time":"$D2023-11-25T03:25:51.384Z","view_time":null,"first_public_time":"$D2023-11-25T03:25:51.384Z","has_unpublished_edits":null,"fork":{"id":"a856f2a939b81d53","slug":"databases","title":"Connecting to databases","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":1494,"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":"Connecting to databases","cells":[{"id":1389,"value":"# Connecting to databases","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1388,"value":"$${pro()}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":0,"value":"$49","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1379,"value":"Some databases can be accessed directly from the web browser, and do not require a web proxy. See our [database clients collection](https://observablehq.com/collection/@observablehq/database-clients) for examples of connecting to Datasette, [DuckDB](https://observablehq.com/@observablehq/duckdb?collection=@observablehq/database-clients), MapD, SQLite, QuestDB, etc.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1416,"value":"## Supported databases","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1418,"value":"Our database proxy currently supports the following database connectors:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1385,"value":"| Connector | Driver Version | \n| --- | --- |\n| Amazon Redshift (PostgreSQL driver) | 8.7.1 ([details](https://node-postgres.com/announcements)) |\n| BigQuery | 4.7.0 ([details](https://www.npmjs.com/package/@google-cloud/bigquery)) |\n| Databricks | 1.0.0 ([details](https://github.com/databricks/databricks-sql-nodejs/blob/main/CHANGELOG.md)) |\n| Mongo SQL | 2.14.5 ([details](https://www.mongodb.com/docs/bi-connector/current/release-notes/))\n| MySQL | 2.18.1 ([details](https://github.com/mysqljs/mysql))|\n| Oracle ([self-hosted proxy](/@observablehq/self-hosted-database-proxies) only) | 5.5.0 ([details](https://node-oracledb.readthedocs.io/en/latest/release_notes.html))\n| PostgreSQL | 8.7.1 ([details](https://node-postgres.com/announcements)) |\n| Snowflake | 1.5.2 ([details](https://github.com/snowflakedb/snowflake-connector-nodejs/releases)) |\n| SQL Server | 9.0.1 ([details](https://github.com/tediousjs/node-mssql/blob/master/CHANGELOG.txt)) |\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"dblist","database":"$undefined"},{"id":1384,"value":"dbClientNote1 = note(htl.html`Note: If you need to define a custom database client, see the DatabaseClient Specification.`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1413,"value":"## Setting up a database\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1429,"value":"You can create a database client for personal use or to shared with a [team](/teams). The database client is only accessible from that user or team’s private notebooks. Sharing or publishing a notebook that uses a database connector will immediately revoke that notebook’s access to the database.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1411,"value":"We highly recommend that you use an SSL-encrypted connection and set up a read-only database user — perhaps also restricted via SQL `GRANT` permissions to only be able to read the relevant tables and columns — specifically for connecting to Observable.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1423,"value":"To get started, navigate to the [Databases tab](/settings/databases) in Settings. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":42,"value":"

Setting up a new PostgreSQL or MySQL database client

\n\nDatabase connections are tested when they’re saved, and show a warning if the client has write permissions or if the connection has problems. Connections over SSL are encrypted, but not (yet) verified, as most cloud database hosts either provide their own certification authority, or issue unverifiable certificates.\n\nIn addition, you can also choose whether to share each database with members of guest roles in your organization via the __Allow guest access__ option when creating or updating a database. Databases are not shared with guests by default.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1486,"value":"dbClientNote2 = note(htl.html`Note: Guest users have access to any database enabled with Allow guest access. Access is not governed by the notebooks shared with them.`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1393,"value":"## Allow-listing our servers\nIf your database requires allow-listing, you should allow the following IP addresses:\n\n```\n44.206.16.243\n44.193.71.39\n44.205.191.175\n44.197.63.115```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1479,"value":"## Data table cell and SQL cell\n\nObservable offers two specialized cell types for exploring databases: data table and SQL cell. Both communicate directly with your database, and their results can be used anywhere in the notebook by setting a name for them. \n\nThe _[data table cell](https://observablehq.com/@observablehq/data-table-cell?collection=@observablehq/getting-data-in-and-out)_ shows the contents of a database table as a table and allows users to filter, sort, select columns, and limit the number of rows shown without writing code. Data table cells can be converted to SQL for further editing.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1478,"value":"babyNames","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1477,"value":"\nThe _[SQL cell](https://observablehq.com/@observablehq/sql-cell?collection=@observablehq/getting-data-in-and-out)_ lets you specify a query using SQL, and shows the result as a table.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1476,"value":"SELECT name, COUNT(*) as count FROM names\nGROUP BY NAME\nORDER BY count DESC","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"namePopularity","database":"var:babyNames"},{"id":1475,"value":"The results of both the data table cell or SQL cell queries can be used in other code once they are given a name. The plot below shows the name counts created with the query above.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1474,"value":"Plot.plot({\n marks: [\n Plot.barX(namePopularity, {x: \"count\", y: \"name\", sort: {y: \"x\", reverse: true}}),\n Plot.ruleX([0])\n ],\n marginLeft: 70\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":223,"value":"## Programmatically creating a database client\n\nIn addition to the above, database clients can also be created programmatically in a notebook. See [the DatabaseClient function in Observable's standard library](https://observablehq.com/@observablehq/stdlib#databaseClientSection) for details.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":184,"value":"### Got a different setup?\n\nNeed to access a different type of database? Or a connection option aside from the [self-hosted database proxies](https://observablehq.com/@observablehq/self-hosted-database-proxies)? See the [DatabaseClient Specification](https://observablehq.com/@observablehq/database-client-specification) or let us know at ${support(\n { subject: \"Custom data connectors\" }\n)}.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":357,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":299,"value":"import {dc as babyNames} from \"cc1c4f2583af6f34\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1000,"value":"yearExtent = [1920, 2018]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1386,"value":"import {pro} from \"@observablehq/pro-tag\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":654,"value":"import {support} from \"4188fd23699b2b3d\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1380,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4a","stdlib":"1"}} 24:null