Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 38 ยท Extensions and Performance

Extensions: The Plugin System

Learn how DuckDB extensions work: INSTALL and LOAD, autoloading, the core extensions (json, parquet, httpfs, icu, fts, spatial) and attaching other databases.

DuckDB keeps its core small and ships extra abilities as extensions: loadable modules for file formats, network access, geospatial types, full-text search, and connections to other databases. Many are built into the standard download; others are fetched on demand the first time you need them.

None of this can run in the browser on this site, so DuckDB commands here are read-only. Runnable examples use SQLite's built-in JSON functions and similar portable SQL to show what the extensions do for you.

INSTALL and LOAD

SQL
INSTALL httpfs;          -- download once into ~/.duckdb/extensions/...
LOAD httpfs;             -- activate in this session

SELECT extension_name, loaded, installed, description
FROM duckdb_extensions()
ORDER BY extension_name;

INSTALL fetches the binary from the official repository (or a custom one) and stores it on disk. LOAD activates it in the current database instance. FORCE INSTALL re-downloads, and UPDATE EXTENSIONS upgrades all installed ones.

Autoloading removes most manual steps. If a query uses a function or file type that belongs to a known extension, DuckDB installs and loads it automatically (the default). SELECT * FROM 'https://example.com/data.parquet' triggers httpfs; calling read_json triggers json. Turn it off for locked-down environments with SET autoinstall_known_extensions = false; SET autoload_known_extensions = false;, or forbid downloads altogether with SET allow_community_extensions = false and an offline extension directory.

flowchart LR
    Q["Query uses read_parquet('s3://...')"] --> A{"Extension loaded?"}
    A -- yes --> R["Run"]
    A -- no --> B["Autoload: INSTALL + LOAD"]
    B --> R

Autoloading installs known extensions the first time a query needs them.

Core extensions worth knowing

Extension What it adds
parquet read and write Parquet files (built in)
json JSON type, read_json, -> and ->>, json_extract (built in)
icu time zones, collations, locale-aware dates
httpfs read files over HTTP(S) and from S3-compatible storage; range reads fetch only needed bytes
fts full-text search indexes with BM25 ranking
spatial GEOMETRY type, GIS functions, reading shapefiles/GeoJSON
sqlite, postgres, mysql attach those databases and query them in place
excel read and write .xlsx
delta, iceberg read lakehouse table formats
azure, aws cloud storage and credential support
vss vector similarity search
autocomplete, tpch, tpcds CLI completion and benchmark generators

Names and availability change between releases, so list what your version ships with duckdb_extensions() rather than trusting any table, including this one.

What the JSON extension does

JSON is a good example. Without any server you query documents with paths:

SQL
SELECT payload->>'$.user.name' AS name,
       (payload->'$.items')::JSON[] AS items
FROM read_json('events.ndjson');

You can read the same idea in SQLite, where json_extract plays the role. It runs here:

SQL Stand-in: querying JSON with SQL (SQLite JSON functions)

Press Run (or Ctrl + Enter) to see the result here.

To explode the array into rows, SQLite uses json_each; DuckDB uses unnest:

SQL Stand-in: array to rows (json_each)

Press Run (or Ctrl + Enter) to see the result here.

Attaching other databases

The postgres, mysql and sqlite extensions let DuckDB query a live database as if its tables were local:

SQL
INSTALL sqlite; LOAD sqlite;
ATTACH 'app.db' AS app (TYPE sqlite);

SELECT c.country, sum(o.total) AS revenue
FROM app.orders o JOIN app.customers c ON c.id = o.customer_id
GROUP BY c.country;

DuckDB pushes down filters and projections where it can, so only needed columns and rows leave the source. This is an easy way to run heavy analytics against production data copies without exporting anything: read from PostgreSQL, aggregate in DuckDB, write Parquet.

Community extensions

Beyond the core set there is a community repository with extensions built by others (for example for extra file formats, hashing, or domain functions):

SQL
INSTALL h3 FROM community;
LOAD h3;

Community extensions are not reviewed like core ones, so treat them like any third-party dependency: check the source and pin versions. SET allow_community_extensions = false; disables them.

Security settings

Extensions mean DuckDB can read the network and local disk. For untrusted SQL (for example in a web app that accepts queries):

SQL
SET enable_external_access = false;   -- no file or network access
SET lock_configuration = true;        -- stop SQL from changing the settings above

Combine this with a read-only database and resource limits (SET memory_limit = '1GB', SET threads = 2) for a safely sandboxed engine.

Writing your own

You can extend DuckDB with scalar and table functions in C++ using the extension template, or, more simply, with Python UDFs registered through con.create_function(...), which run row by row (slow) or vectorized over Arrow arrays (fast). Macros (CREATE MACRO) cover most reusable-logic needs without any native code.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between INSTALL and LOAD?

    INSTALL fetches and stores the binary once; LOAD turns it on for the running database.
  2. 2. What does autoloading do?

    Queries on URLs or special functions trigger the matching extension automatically by default.
  3. 3. How do you let DuckDB query a SQLite file in place?

    The sqlite extension plus ATTACH exposes its tables directly.
  4. 4. Which settings help sandbox untrusted SQL?

    Disabling external access and locking configuration prevents queries from reaching files or the network or changing the policy.

Summary

  • Extensions add file formats, network access, spatial data, search and database connectors.
  • INSTALL stores, LOAD activates; autoloading usually does both for you.
  • ATTACH with the sqlite, postgres or mysql extensions queries live databases in place.
  • Treat community extensions as third-party code, and lock down external access for untrusted SQL.

Next: Remote Data: HTTP, S3 and Cloud Storage