Observable Notebooks
Database connectors

Database connectors allow you to query databases and data warehouses from notebooks. They power SQL cells, as well as the DatabaseClient JavaScript API.

Why would I want to connect to a database? Business data is often stored in databases. If you want to do business intelligence, exploratory data analysis, or simply make a chart or dashboard, you’ll likely need to query data from a database.

Why do I need a database connector to connect to a database? Databases typically need specialized software (database drivers) to connect and execute queries. Notebooks run in the browser, and databases don’t typically expose an HTTP API, so browsers can’t talk to them directly.

Do I really need a database connector? No, you don’t need a database connector to work with data from a database in a notebook: you can manually extract data from your database, store it in a file, and use the FileAttachment JavaScript API to load it into a notebook.

So why do I need a database connector? Database connectors make it much more convenient to query data, and to keep data up-to-date, because you don’t have to switch tools and juggle files — you can query right from a notebook! You can even query databases interactively, for example passing in dynamic parameters that are specified via a menu, slider, or text input. You rarely know upfront which data you’ll need during exploratory analysis, so being able to query instantly at the moment of need dramatically accelerates analysis.

Are query results saved? Yes. To improve performance, to ensure that everyone sees the same data, and to enable point-in-time analysis (not just live dashboards), query results are automatically saved to a .observable/cache directory on your local file system alongside your notebooks. Notebooks typically load instantly because queries are precomputed.

That’s a big difference from notebooks 1.0! Yes. Database connectors in notebooks 1.0 only support live (unsaved) queries. This often means slow notebooks and users seeing inconsistent data. And it makes point-in-time analysis tedious because query results must be manually captured as file attachments. And likewise if you want to share a notebook without giving access to the database. Automatic query caching in notebooks 2.0 isn’t just about performance; it unlocks more robust and secure ways of working and sharing analysis.

If query results are saved, how do I update them? In Observable Desktop, you can re-run a SQL cell by clicking the Play button, by hitting shift-return, or by clicking on the query age in the cell toolbar. In Observable Notebook Kit, delete the corresponding file from the .observable/cache directory; you can also use continuous deployment, such as GitHub Actions, to refresh data automatically.

How do I configure my database? When using DuckDB, SQLite, or (localhost) Postgres with the default settings, no configuration is required; simply specify duckdb, sqlite, postgres, or a .duckdb or .db file as the database in the SQL cell. Note: you must save your notebook before running queries, since where you save the notebook determines which databases are accessible and where query results are saved. To configure databases, edit the .observable/databases.json file alongside your notebook; see below for details. For example, if your notebooks are stored in docs, then a notebook docs/example.html can access any database configured in docs/.observable/databases.json. In the future, Observable Desktop will provide a built-in UI for configuring databases.

Which databases are supported? The following databases are supported, along with their current drivers:

The Postgres (PostgreSQL) driver should also work with Postgres-compatible databases such as ClickHouse, Amazon Redshift, and Google Cloud SQL. Our data connectors are open-source and we welcome contributions of additional database drivers! While you can query OLTP databases, we recommend OLAP databases because fast ad hoc queries greatly accelerate analysis.

How do I query my database? Insert a new cell, say by clicking the + button between cells. Convert the new cell to SQL by hitting down or ⌘4. By default, SQL cells query the default duckdb in-memory database. To query a different database, edit the database in the cell toolbar, then click ↩︎ or hit return. Then refocus the SQL cell, edit your query, and hit shift-return to run it.

SELECT 1 + 2
A SQL cell typically contains a single SELECT statement.

How are query results displayed? SQL cells display the query results as a table above the query. You can sort columns by clicking on column headers.

Can I hide the table? Yes. In Desktop, use command-, (or esc ,) to hide the cell’s display; you can also hide the cell from the cell menu, which is accessed by clicking the cell type (sql in the cell toolbar), or via the Edit menu. In Notebook Kit, use the hidden attribute.

I would like better tables. Us too! We’re currently using Inputs.table from Observable Inputs, but we plan on implementing summary tables similar to those in Notebooks 1.0 and in Canvases. These future summary tables will provide a better visual overview and allow more powerful interactive filtering. Note that you can already filter tables using the checkboxes in the first column: only checked rows will be visible to downstream cells. Interactive filters are not persistent; they are reset when you re-run the cell or reload the notebook.

How do I access query results from other cells? Defining an output for a SQL cell exposes a top-level variable that allows other cells to access the query results. Query results are currently represented in array-of-objects format; however, in the future, we will likely switch to Apache Arrow for scalability.

SELECT 1 AS foo
UNION ALL SELECT 2
UNION ALL SELECT 3

The output three in JavaScript is an array of three objects:

three

Show me a more interesting example. How about a quick look at a dataset of 20M taxi rides? The query below counts the number of trips per hour. (Data is via the NYC Taxi & Limousine Commission, June 2025.)

SELECT
  DATE_TRUNC('hour', pickup_datetime) AS date,
  COUNT(*) AS count
FROM read_parquet("fhvhv_tripdata_2025-06.parquet")
GROUP BY 1
ORDER BY 1 DESC

As an area chart with Observable Plot:

Plot.plot({
  y: {grid: true},
  height: 240,
  marks: [
    Plot.axisY({tickFormat: (d) => d / 1000, label: "Trips per hour (thousands)"}),
    Plot.areaY(trips, {x: "date", y: "count", curve: "step", fillOpacity: 0.2}),
    Plot.ruleY([0]),
    Plot.lineY(trips, {x: "date", y: "count", curve: "step", tip: true}),
  ]
})

The daily and weekly periodicity is quite prominent. How about grouping the data by hour of day and day of week?

SELECT
  dayofweek(pickup_datetime) AS dow,
  hour(pickup_datetime) AS hour,
  COUNT(*) / COUNT(DISTINCT date(pickup_datetime)) AS rate
FROM read_parquet("fhvhv_tripdata_2025-06.parquet")
GROUP BY 1, 2
ORDER BY 1, 2

As a heatmap, showing busy morning commutes and saturday nights:

Plot.plot({
  padding: 0,
  label: null,
  y: {tickFormat: Plot.formatWeekday("en")},
  color: {scheme: "ylorrd", label: "Trips per hour (avg.)", zero: true, legend: true},
  marks: [Plot.rect(trips_heatmap, {x: "hour", y: "dow", fill: "rate", inset: 0.5, tip: true})]
})

My data is sensitive; where does it go when I run a query? Database connectors run locally on your computer, so your data stays private. You access data on your local machine or private networks without going through Observable servers.

My database credentials are sensitive! How do I keep them secure? Because the databases.json file typically contains credentials, it should not be committed to source control. We recommend excluding the entire .observable directory by adding it to your .gitignore, like so:

.observable

The .observable folder also contains saved query results (.observable/cache) and the built site (.observable/dist), which you likely do not want to commit to source control. (But you can if you want.)

Some of my notebooks need to talk to different databases, and some of my notebooks are untrusted; what do I do? Use folders to separate notebooks with different levels of trust, and to configure different databases. A notebook can only access files and databases that live in the same folder (or subfolder) as the notebook. If you configure a sensitive database or have sensitive files, only put trusted notebooks in that folder. Don’t mix trusted and untrusted notebooks in the same folder.

Does sharing my notebook require giving readers access to my database? No. Notebook Kit builds a static site from your notebooks which includes the saved query results. Query results can therefore be shared without giving readers access to your database. However, this also means that shared notebooks cannot use dynamic queries (which by nature cannot be “baked” at build time). If desired, use JavaScript or DuckDB-Wasm (DuckDBClient) to filter and transform data interactively on the client.

How do I run dynamic queries? Use ${…} to interpolate JavaScript values into SQL cells. You can also use the DatabaseClient JavaScript API like so:

const db = DatabaseClient("duckdb");
const three = await db.sql`SELECT 1 + ${2}`;

Since dynamic queries are not baked, they are only suitable for use within Observable Desktop, while previewing with Notebook Kit, or when using a client-side database such as DuckDBClient (DuckDB-Wasm).

What about custom database clients and DuckDB-Wasm? If you specify a SQL’s database using var: (such as var:db), you can then provide a custom database client of the given name (such as db) that runs in the browser. This feature is most often used with DuckDBClient (DuckDB-Wasm), but you can provide any object that exposes a sql tagged template literal.

How do I use databases with Notebook Kit? Database drivers come bundled with Observable Desktop, but with the exception of SQLite are not installed by default with Notebook Kit. Database drivers are instead marked as optional peer dependencies, and you must install them yourself.

To install the DuckDB driver:

npm add @duckdb/node-api

To install the Postgres driver:

npm add postgres

To install the Snowflake driver:

npm add snowflake-sdk

To install the Google BigQuery driver:

npm add @google-cloud/bigquery

To install the Databricks driver:

npm add @databricks/sql

What is the format of the databases.json file? This file can configure multiple databases. Each database must have a unique name, the database type, and any corresponding configuration options. Here is how you might configure a demo Snowflake database:

{
  "demo": {
    "type": "snowflake",
    "account": "tk421.us-east-1.aws",
    "database": "demo",
    "role": "public",
    "schema": "public",
    "username": "demo",
    "warehouse": "demo",
    "password": "opensesame"
  }
}

How do I configure DuckDB? The following options are supported:

type DuckDBConfig = {
  type: "duckdb";
  path?: string;
  options?: {[key: string]: string};
}

The path option stores the relative path to the DuckDB (.duckdb) database file; if not specified, the database will be in-memory (:memory:). The options object contains any DuckDB configuration options.

How do I configure SQLite? The following options are supported:

type SQLiteConfig = {
  type: "sqlite";
  path?: string;
}

The path option stores the relative path to the SQLite (.db) database file; if not specified, the database will be in-memory (:memory:).

How do I configure Postgres? The following options are supported:

 type PostgresConfig = {
  type: "postgres";
  host?: string;
  port?: string | number;
  username?: string;
  password?: string;
  database?: string;
  ssl?: boolean;
}

If you do not specify any options, the Postgres database must be running on localhost with the default user and database. See the Postgres driver documentation for details.

How do I configure Snowflake? The following options are supported:

type SnowflakeConfig = {
  type: "snowflake";
  account: string;
  database?: string;
  role?: string;
  schema?: string;
  username?: string;
  warehouse?: string;
  password?: string;
}

See the Snowflake Node.js driver documentation for details.

How do I configure Google BigQuery? The following options are supported:

type BigQueryConfig = {
  type: "bigquery";
  apiKey?: string;
  keyFilename?: string;
  keyFile?: string;
  projectId?: string;
}

See the Google Cloud SDK documentation for details.

How do I configure Databricks? The following options are supported:

type DatabricksConfig = {
  type: "databricks";
  host: string;
  path: string;
} & (
  | {authType?: "access-token"; token: string}
  | {authType: "databricks-oauth"; oauthClientId?: string; oauthClientSecret?: string}
)

See the Databricks SQL Driver for Node.js documentation for details.

✎ Suggest changes to this page