Connecting to databases
NotebooksObservable 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, query the database using the SQL cell, 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 that you may prefer to use.
Some databases can be accessed directly from the web browser, and do not require a web proxy. See our database clients collection for examples of connecting to Datasette, DuckDB, MapD, SQLite, QuestDB, etc.
Supported databases
We currently support the following hosted database connectors:
| Connector | Driver Version |
|---|---|
| Amazon Redshift (PostgreSQL driver) | 8.7.1 (details) |
| BigQuery | 4.7.0 (details) |
| Databricks | 1.0.0 (details) |
| Mongo SQL | 2.14.5 (details) |
| MySQL | 2.18.1 (details) |
| Oracle | 5.5.0 (details) |
| PostgreSQL | 8.7.1 (details) |
| Snowflake | 1.5.2 (details) |
| SQL Server | 9.0.1 (details) |
Note
If you need to define a custom database client, see the DatabaseClient Specification.
Setting up a database
You can create a database client in your workspace. 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.
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.
To get started, navigate to the Databases tab in the left margin of your Observable account Home or Settings pages.
This will take you to a Databases page where you can see existing databases connections, or add new ones. Click the New database button, which will take you to a new window where you are prompted to configure a new database connection.
Database 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.
In 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.
Note
Guest users have access to any database enabled with Allow guest access. Access is not governed by the notebooks shared with them.
Allow-listing our servers
If your database requires allow-listing, you should allow the following IP addresses:
44.206.16.243
44.193.71.39
44.205.191.175
44.197.63.115Data table cell and SQL cell
Observable offers two specialized cell types for exploring databases: the Data table cell and the SQL cell. Both communicate directly with your database, and their results can be used anywhere in the notebook by setting a name for them.
The Data table cell shows the contents of a database 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.
The SQL cell lets you specify a query using SQL, and shows the result as a table.
The results of Data table cell and SQL cell queries can be used in other code once they are given a name. For example, the output of the SQL cell above is an array of objects called namePopularity, which can be called in other cells throughout the notebook.
Programmatically creating a database client
In addition to the above, database clients can also be created programmatically in a notebook. See the DatabaseClient function in Observable’s standard library for details.
Got a different setup?
Need to access a different type of database? Or a connection option aside from the self-hosted database proxies? See the DatabaseClient Specification or let us know at support@observablehq.com.