Shillelagh

A Python library and CLI that lets you query APIs, files and in-memory objects with SQL, by exposing each one as an SQLite virtual table; it is how Apache Superset reads Google Sheets.

What it is: Query engines
Type: Open source
Made by: Beto Dealmeida and contributors
License: MIT
Facts checked: 23 October 2026

What it is

Shillelagh is a Python DB API 2.0 driver built on SQLite, through the APSW library. A table name is a URI in double quotes: a Google Sheets URL, s3://bucket/file.parquet, a path to a CSV file, a JSON API endpoint, a GitHub API URL or the name of a pandas DataFrame. For each one it creates an SQLite virtual table backed by an adapter, a Python class that knows how to read that source. SQLite parses and plans the query, and the adapter fetches the rows. One query can join several sources, for example a Google Sheet with a weather API, and write the result to a CSV file.

Each adapter declares its columns and which filters it can handle on each one, such as equality or ranges. When a query has WHERE time >= '2026-01-01', Shillelagh passes that bound to the adapter, which turns it into an API parameter so the remote side does the filtering; an adapter can mark its filtering as inexact and let SQLite drop the extra rows. Adapters can also declare sort order, accept LIMIT and OFFSET, and give the planner a cost estimate. The Google Sheets, CSV and S3 adapters support INSERT, UPDATE and DELETE as well, and DROP TABLE deletes the sheet, file or object. Other built-in adapters read HTML tables on web pages, Datasette tables, generic JSON and XML APIs, Socrata, WeatherAPI and the local machine’s CPU usage; third-party packages add Airtable, GraphQL and OData.

Besides the DB API there is a SQLAlchemy dialect (shillelagh://), a gsheets:// dialect with only the Google Sheets adapter enabled, and a command-line shell. Apache Superset uses it for its Google Sheets and Shillelagh connections. Two experimental backends run the same adapters elsewhere: inside PostgreSQL as Multicorn2 foreign tables, and on sqlglot’s Python executor instead of SQLite. Version 1.4.5 came out in July 2026.

Why teams pick it

SQL, including joins across sources, over things that have no SQL interface, with nothing to run but a Python package
Filters are pushed into the API when the adapter supports it, so a WHERE clause becomes a request parameter instead of a full download
A standard DB API driver and SQLAlchemy dialect, so tools such as Superset can use it like any database
Writing a new adapter is a small Python class, and the docs walk through one step by step

Watch out for

Runs in one Python process on SQLite: fine for spreadsheets and API responses, not for large datasets
Speed and filtering depend on each adapter; the generic JSON adapter, for one, downloads everything and filters on the client
Table names that carry credentials end up stored in a database file, so the docs advise using an in-memory database
Mostly one maintainer’s project; the Postgres and sqlglot backends are still experimental

Specs

Compare with alternatives →

Data model: SQLite virtual tables backed by Python adapters for APIs, files and DataFrames
Query language: SQLite’s SQL; a table name is a URI
Client protocol: None: a Python DB API 2.0 driver and SQLAlchemy dialect
How it scales: One Python process
Written in: Python
Ways to run it: A Python library and command-line shell

Alternatives

What all query engines have in common →Compare side by side →

MindsDBDormantOpen sourceA federated SQL engine that queries over 200 databases, warehouses, apps and files in place, and adds knowledge bases: tables you INSERT documents into and SELECT from by meaning, for retrieval-augmented generation.

TrinoOpen sourceA distributed SQL engine that queries data where it lives, from Iceberg tables on S3 to a live PostgreSQL database, in one query.

IbisOpen sourceA Python dataframe API that compiles to SQL, so the same code runs on DuckDB on a laptop and on BigQuery, Snowflake, PostgreSQL or more than 20 other engines.

Apache ZeppelinOpen sourceA self-hosted web notebook where each paragraph runs through an interpreter: Spark, Flink, JDBC, Python and more. Maintained, but behind Jupyter and Databricks in reach.

How it fits with other technologies

What Shillelagh uses

Query a public Google Sheet and an HTML table with SQL · python

# pip install 'shillelagh[gsheetsapi,htmltableapi]'
from shillelagh.backends.apsw.db import connect

connection = connect(":memory:")
cursor = connection.cursor()

sheet = "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0"
for row in cursor.execute(f'SELECT country, SUM(cnt) FROM "{sheet}" GROUP BY country'):
    print(row)

wiki = "https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population"
cursor.execute(f'SELECT * FROM "{wiki}" WHERE "UN Region" = \'Oceania\' LIMIT 5')

See it in a real stack

No architecture uses Shillelagh yet. Drop it into the playground and wire it up yourself.Open playground →

Where it came from2018–2026

Beto Dealmeida started working on Apache Superset in 2017. Superset can talk to any database that speaks SQL, and one of the most common requests he got from users was to read data kept in Google Sheets: in his words not a good database, but a friendly place to type data in. In 2018 he published gsheets-db-api, a DB API driver and SQLAlchemy dialect for Google Sheets.

Shillelagh generalised it. Instead of a driver for one service, it used SQLite’s virtual tables to put SQL in front of any source an adapter could read, with filters passed down to the API. Version 0.1 came out in October 2020, and 0.2 in February 2021 added the DB API, the SQLAlchemy dialect and the Google Sheets adapter as a drop-in replacement for the old gsheets:// dialect. Version 1.0 followed in August 2021, and the gsheets-db-api README now tells users to switch. The name is an Irish wooden club: the docs picture “a leprechaun hitting APIs with a big stick so that they accept SQL.”

Superset adopted it for its Google Sheets connection, and Preset, which sells hosted Superset and published his write-up, ships a customised version with only the Google Sheets adapter. Later releases added a PostgreSQL backend on Multicorn2 (1.3, October 2024), an experimental sqlglot backend and a dialect for dbt’s MetricFlow (1.4, July 2025).

Why it’s built this way

Build on SQLite virtual tables: SQLite already parses, plans and executes SQL, including joins and aggregates. Adapters only have to produce rows, so a new source costs a small class instead of a query engine.
Push filters into the source: APIs often can filter or page server-side. Letting each column declare the filters it accepts means a WHERE clause becomes request parameters instead of downloading everything.
A standard DB API driver and SQLAlchemy dialect: Superset and other Python tools already speak SQLAlchemy, so any adapter becomes a database they can connect to without new integration code.

Timeline

2018
Beto Dealmeida publishes gsheets-db-api, SQL for Google Sheets
2020
Shillelagh 0.1 is released in October
2021
0.2 adds the SQLAlchemy dialect and Google Sheets adapter; 1.0 ships in August
2024
1.3 adds a PostgreSQL backend based on Multicorn2
2025
1.4 adds an experimental sqlglot backend and a MetricFlow dialect
2026
1.4.5 is released in July

Sources8 · facts checked 23 October 2026

Improve this page on GitHub