Skip to content

Connecting to databases

NotebooksLearn about notebooks vs. projects

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, 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:

ConnectorDriver Version
Amazon Redshift (PostgreSQL driver)8.7.1 (details)
BigQuery4.7.0 (details)
Databricks1.0.0 (details)
Mongo SQL2.14.5 (details)
MySQL2.18.1 (details)
Oracle5.5.0 (details)
PostgreSQL8.7.1 (details)
Snowflake1.5.2 (details)
SQL Server9.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 for personal use or to share with a team. 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.

Screen shot of the Databases tab on the Home page in an Observable account.
Databases tab access from your account Home or Settings.

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.

Screen shot of database configuration window, where a user is prompted to input database name, type, host and port information, and user information.
Setting up a new database client.

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.115

Data 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.

Screen shot of a new Data table cell, with a database selected but without a specific table selected.
View and wrangle data from databases in the Data table cell.

The SQL cell lets you specify a query using SQL, and shows the result as a table.

Screen shot of a SQL cell, with code to query a selected database.
Query a database and view results in a SQL cell.

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.