DuckDB Markdown Extension

This extension adds Markdown processing capabilities to DuckDB, enabling structured analysis of Markdown documents and content extraction for documentation analysis, content auditing, and knowledge base processing.

Features

• Markdown Content Extraction: Extract code blocks, links, images, and tables from Markdown text
• COPY TO Markdown: Export query results as Markdown tables or reconstruct documents from sections
• Documentation Analysis: Analyze large documentation repositories with SQL queries
• Cross-Platform Support: Works on Linux, macOS, Windows, and WebAssembly (browsers)
• GitHub Flavored Markdown: Uses cmark-gfm for accurate parsing of modern Markdown
• Optional Wiki / Obsidian Extraction: Opt into [[wikilinks]], ![[embeds]], and #tags extraction via the extract_extensions parameter on the readers — or call md_extract_wikilinks / md_extract_tags directly on a markdown string
• High Performance: Process thousands of documents efficiently with robust glob pattern support

Installation

Loading the Extension

-- Install from community extensions (when available)
INSTALL markdown FROM community;
LOAD markdown;

Building from Source

git clone https://github.com/teaguesterling/duckdb_markdown
cd duckdb_markdown
make
make test

Quick Start

-- Load the extension
LOAD markdown;

-- Read Markdown files with glob patterns
SELECT content FROM read_markdown('docs/**/*.md');

-- Read documentation sections with hierarchy
SELECT title, level, content 
FROM read_markdown_sections('README.md', include_content := true);

-- Extract code blocks from Markdown text
SELECT cb.language, cb.code 
FROM (
  SELECT UNNEST(md_extract_code_blocks('```python\nprint("Hello, World!")\n```')) as cb
);

-- Analyze documentation repositories
SELECT 
  len(md_extract_code_blocks(content)) as code_examples,
  len(md_extract_links(content)) as external_links,
  len(md_extract_images(content)) as images
FROM read_markdown('**/*.md');

-- Use replacement scan syntax for convenience
SELECT * FROM '*.md';
SELECT * FROM 'docs/**/*.md';

-- Export results as Markdown table
COPY (SELECT * FROM my_table) TO 'output.md' (FORMAT MARKDOWN);

-- Round-trip: read sections, process, write back
COPY (
  SELECT level, title, upper(content) as content
  FROM read_markdown_sections('doc.md')
) TO 'processed.md' (FORMAT MARKDOWN, markdown_mode 'document');

Table-like Syntax Support

The extension supports DuckDB's replacement scan feature, allowing you to query Markdown files using table-like syntax:

-- Query markdown files directly
SELECT * FROM 'README.md';
SELECT * FROM '*.md';  
SELECT * FROM 'docs/**/*.md';

-- Equivalent to calling read_markdown()
SELECT * FROM read_markdown('README.md');
SELECT * FROM read_markdown('*.md');
SELECT * FROM read_markdown('docs/**/*.md');

Supported patterns in replacement scan:

• 'file.md' - Individual markdown files
• '*.md', '**/*.markdown' - Glob patterns
• Recursive patterns like 'docs/**/*.md'

Note: Directory patterns like 'docs/' are not supported. Use recursive globs like 'docs/**/*.md' instead.

Core Functions

File Reading Functions

Read Markdown files directly with comprehensive parameter support:

read_markdown(files, [parameters...])

Reads Markdown files and returns one row per file.

Parameters:

• files (required) - File path, glob pattern, directory, or list of mixed patterns
• filename := false - Append a trailing filename column naming the source file (deprecated alias: include_filepath). Boolean only: the string form that renames the column is deliberately not accepted, because a renamed column produces a struct no duck_block consumer accepts.
• content_as_varchar := false - Return content as VARCHAR instead of MARKDOWN type
• maximum_file_size := 16777216 - Maximum file size in bytes (16MB default)
• extract_metadata := true - Extract frontmatter into the metadata column. This uses the same line-split key/value reader as md_extract_metadata — each line split on the first : (or the first = inside a +++ TOML block), typed as MAP(VARCHAR, VARCHAR) — not a full YAML parser (nested maps, lists, and multiline |/> scalars are not interpreted). For real YAML, use the yaml extension's read_yaml_frontmatter — see Frontmatter Handling.
• normalize_content := true - Normalize Markdown content
• extract_extensions := NULL - Opt-in add-on extractors (comma-separated VARCHAR; see Optional Add-On Extractors). When set, adds wikilinks and/or tags LIST<STRUCT> columns to the output

Returns: (content MARKDOWN, metadata MAP(VARCHAR, VARCHAR)) or (content MARKDOWN, metadata MAP(VARCHAR, VARCHAR), filename VARCHAR) with filename := true -- provenance is appended, never prepended. With extract_extensions, the requested add-on columns are appended.

read_markdown_blocks(files, [parameters...])

Reads Markdown files and parses them into block-level elements (headings, paragraphs, code blocks, lists, tables, etc.).

Parameters:

• files (required) - File path, glob pattern, or list of patterns
• filename := false - Append a trailing filename column naming the source file (deprecated alias: include_filepath). Boolean only: the string form that renames the column is deliberately not accepted, because a renamed column produces a struct no duck_block consumer accepts.
• extract_extensions := NULL - Opt-in add-on extractors (see Optional Add-On Extractors). When set, adds per-block wikilinks and/or tags columns extracted from each block's content.

Returns: (kind VARCHAR, element_type VARCHAR, content VARCHAR, level INTEGER, encoding VARCHAR, attributes MAP(VARCHAR, VARCHAR), element_order INTEGER). level is structural depth, minimum 1 — a heading's rank is attributes['heading_level'], not level. A heading carries both a flattened plain-text title in content (what section_id and the sections API read) and its formatted text as inline children, so # **Bold** t keeps its emphasis instead of collapsing to # Bold t. (read_markdown_sections has a level column too, but that one is heading rank, 0-6.) With extract_extensions, the requested add-on columns are appended.

Note on level vs heading_level: For headings, the H1-H6 level is stored in attributes['heading_level'] (preferred). If not present, the level field is used as a fallback.

Element Types: heading, paragraph, code, blockquote, list, table, hr, metadata

Frontmatter is emitted as element_type = 'metadata' with attributes['role'] = 'frontmatter', carrying the raw text between the fences. The block keeps the position the source gave it, so a --- fence at the head of a file is the first element with role = 'frontmatter'. (The role is a positional claim: tailmatter if the author placed it after the body, document if the blob is the whole document, and no role at all when the format supplied metadata with no position — pandoc's meta, an EPUB's OPF — in which case the producer appends it and claims nothing.) duck_blocks_to_md finds the block by type wherever it sits and always writes it at the top, because markdown has nowhere else to put it: a fence at the bottom of a file is a thematic break, not metadata. Both dialects are recognised: --- fences give encoding = 'yaml', and Hugo-style +++ fences give encoding = 'toml'. The fence is restored from the encoding on write, so a TOML document round-trips as TOML rather than being relabelled YAML. The type says it is a verbatim metadata blob; the role says which source construct produced it. (Before spec 6.2 this was element_type = 'frontmatter', which was never a declared duck_block type. duck_blocks_to_md still accepts the old name so stored data keeps rendering.)

Encoding: text for plain content, json for table content, yaml/toml for frontmatter.

Lists are structural: a list carries no content of its own, and its items are list_item children with their text in paragraph blocks beneath them, one level deeper at each step (list → list_item → paragraph → inlines). Nested lists are list blocks inside a list_item. Before this, a list's items were packed into a JSON array in its content, which flattened inline formatting and discarded nested lists entirely.

-- Parse document into blocks
SELECT element_type, content, level
FROM read_markdown_blocks('README.md')
ORDER BY element_order;

-- Extract all code blocks with their languages
SELECT content, attributes['language'] as lang
FROM read_markdown_blocks('docs/**/*.md')
WHERE element_type = 'code';

-- Round-trip: read blocks, modify, write back
COPY (
  SELECT kind, element_type, content, level, encoding, attributes
  FROM read_markdown_blocks('doc.md')
) TO 'copy.md' (FORMAT MARKDOWN, markdown_mode 'blocks');

read_markdown_sections(files, [parameters...])

Reads Markdown files and parses them into hierarchical sections.

Parameters:

• files (required) - File path, glob pattern, directory, or list of mixed patterns. Supports fragment syntax: 'file.md#section-id' to filter to a specific section and its descendants.
• include_content := true - Include section content in output
• min_level := 1 - Minimum heading level to include (1-6)
• max_level := 6 - Maximum heading level to include (1-6)
• content_mode := 'minimal' - Content extraction mode (see below)
• max_depth := 6 - Maximum depth relative to min_level (e.g., max_depth := 2 with min_level := 1 includes h1 and h2 only)
• max_content_length := 0 - Maximum content length for 'smart' mode (0 = auto, uses 2000 chars)
• include_empty_sections := false - Include sections without content
• filename := false - Append a trailing filename column naming the source file (deprecated alias: include_filepath). Boolean only: the string form that renames the column is deliberately not accepted, because a renamed column produces a struct no duck_block consumer accepts.
• extract_metadata := true - Include frontmatter as a special section (level=0)
• extract_extensions := NULL - Opt-in add-on extractors (see Optional Add-On Extractors). When set, adds per-section wikilinks and/or tags columns extracted from each section's content.
• Plus all read_markdown parameters

Content Modes:

• 'minimal' (default) - Content stops at ANY next heading. Each section contains only its immediate content, not subsections.
• 'full' - Content includes all subsections until next same-or-higher level heading. Use this for complete section extraction.
• 'smart' - Adaptive mode: includes small subsections fully, truncates large ones with references like "... (see #subsection-id)".

Returns: (section_id VARCHAR, section_path VARCHAR, level INTEGER, title VARCHAR, content MARKDOWN, parent_id VARCHAR, start_line BIGINT, end_line BIGINT) or with filename := true appends a trailing filename VARCHAR column.

Notes:

• When extract_metadata := true, the frontmatter block is included as a special section with level=0, section_id='frontmatter', and the raw, unparsed text between the --- (or +++) delimiters as the content. This level is heading rank (0 for frontmatter, 1-6 for headings) and is a different measurement from read_markdown_blocks' level, which is structural depth and is 1 for every top-level element including frontmatter. Nothing in this extension interprets that text as YAML — see Frontmatter Handling.
• The section_path column provides hierarchical navigation paths like "parent/child/grandchild".
• Fragment syntax 'file.md#section-id' returns the matching section and all its descendants.

Content Extraction Functions

All extraction functions return LIST<STRUCT> types for easy SQL composition:

• md_extract_code_blocks(markdown) - Extract code blocks with language and metadata
• md_extract_links(markdown) - Extract standard [text](url) links with text, URL, title, line number
• md_extract_images(markdown) - Extract images with alt text and metadata
• md_extract_table_rows(markdown) - Extract table data as individual cells
• md_extract_tables_json(markdown) - Extract tables as structured JSON with enhanced metadata
• md_extract_wikilinks(markdown) - Extract Obsidian/wiki-style links: [[target]], [[target|alias]], [[target#heading]], [[target^block]], and embeds ![[…]]. Returns LIST<STRUCT(target, alias, anchor, is_embed, line_number)>. Not CommonMark/GFM — a lightweight linear scan (no std::regex); see Optional Add-On Extractors.
• md_extract_tags(markdown) - Extract inline #tag / #nested/tag references; skips fenced code blocks and inline code spans. Returns LIST<STRUCT(tag, line_number)>.

Optional Add-On Extractors (extract_extensions)

md_extract_wikilinks and md_extract_tags cover the Obsidian / wiki Markdown superset that cmark-gfm cannot parse — [[wikilinks]], ![[embeds]], #tags. They are a lightweight linear scan, not part of CommonMark/GFM, and are exposed two ways:

1. Directly as scalar functions — call them on a markdown string you already have in hand.
2. As opt-in add-on columns on the readers, via the extract_extensions named parameter — comma-separated VARCHAR, default NULL (no add-ons; existing behavior unchanged):

-- Single feature
SELECT wikilinks FROM read_markdown('vault/**/*.md', extract_extensions := 'wikilinks');

-- Multiple features (comma-separated, whitespace tolerant)
SELECT wikilinks, tags FROM read_markdown('vault/**/*.md', extract_extensions := 'wikilinks, tags');

-- Flavor shortcut: 'obsidian' expands to wikilinks + tags
SELECT wikilinks, tags FROM read_markdown('vault/**/*.md', extract_extensions := 'obsidian');

-- Works on the section / block readers too — extracted per-row from each section's / block's content
SELECT title, len(wikilinks) AS n
FROM read_markdown_sections('vault/**/*.md', extract_extensions := 'wikilinks');

Available tokens: wikilinks, tags, and the obsidian flavor (= both). Unknown tokens raise an error.

Limitations (v1):

• The wikilink extractor is per-line, so a wikilink target spanning a line break is not matched.
• md_extract_wikilinks does not currently skip fenced code blocks (md_extract_tags does). A [[…]] inside a code fence will still be extracted.
• In the per-section and per-block paths, cmark strips fence markers from its plaintext content, so a #tag inside a fenced code block of a section/block will be extracted (per-file read_markdown sees the raw bytes and honors fences correctly).
• Tokens in extract_extensions are split only on commas (not whitespace); a token with internal whitespace is rejected as unknown.

Document Processing Functions

• md_to_html(markdown) - Convert markdown content to HTML

• md_to_text(markdown) - Convert markdown to plain text (useful for full-text search)

• md_valid(markdown) - Validate markdown content and return boolean

• md_stats(markdown, [exact]) - Get document statistics (word count, reading time, etc.). By default heading_count, code_block_count and link_count come from line/character scanners, which count a [text](target) match anywhere — including images, code spans and fenced code — and miss reference links, autolinks, setext headings, indented code blocks and ~~~ fences. Pass true as the second argument to take those three counts from cmark's AST instead, so they agree with md_extract_links and md_extract_code_blocks. The default is unchanged for compatibility; word_count, char_count, line_count and reading_time_minutes are textual and identical either way.

  SELECT (md_stats(content)).link_count,                 -- scanner: counts images and code samples
         (md_stats(content, true)).link_count   -- cmark: real links only, references and autolinks included
  FROM read_markdown('docs/*.md');

• md_extract_metadata(markdown) - Extract frontmatter as MAP(VARCHAR, VARCHAR). This is a lightweight line-split key/value reader (each line split on the first :, or the first = inside a +++ TOML block), not a full YAML or TOML parser — nested maps, lists, and multiline scalars are not interpreted. For real YAML — nested maps, lists, |/> blocks — use the yaml extension's read_yaml_frontmatter; see Frontmatter Handling.

• md_extract_frontmatter(markdown) - Extract the raw frontmatter block (the text between the --- or +++ fences) as VARCHAR, or NULL when there is no frontmatter. Composes with duckdb_yaml for real YAML parsing without this extension carrying a YAML parser: e.g. SELECT yaml(md_extract_frontmatter(content)).

• md_extract_section(markdown, section_id, [include_subsections]) - Extract specific section by ID. Pass true as the third argument to include all nested content (full mode); default is minimal mode.

• md_extract_sections(markdown, [min_level, max_level, content_mode]) - Extract all sections as a list. Supports optional level filtering and content_mode ('minimal', 'full', 'smart').

• md_section_breadcrumb(markdown, section_id) - Generate breadcrumb path for a section (returns "Title1 > Title2 > Title3" format)

• value_to_md(value) - Convert any value to markdown representation

Frontmatter Handling

Where the block starts and stops. The opening fence must be a line containing exactly --- (YAML) or +++ (Hugo's TOML), trailing spaces or tabs allowed, as the very first line of the document; a leading UTF-8 BOM is skipped. A line that merely starts with three delimiter characters — ----, ---foo — is not a fence at either end, so a document opening with a thematic break is read as having no frontmatter rather than swallowing the text after it, and a ---- rule further down does not close a block early and splice its surplus - into the body. A --- block is closed by the next --- or ... line at column 0 (... is YAML's document-end marker: it closes, it never opens); a +++ block only by +++. An empty block — --- immediately followed by ---, Jekyll's idiom — is recognised and yields no metadata rather than two thematic breaks. These are the same rules the yaml extension's read_yaml_frontmatter applies, so the two extensions agree about where a given document's frontmatter begins and ends.

This extension does not parse YAML. Frontmatter — the block between the leading --- delimiters — is read as flat key: value pairs: each line is split on its first :, both halves are whitespace-trimmed, and a pair of surrounding double quotes is stripped from the value. The result is typed MAP(VARCHAR, VARCHAR).

That is a deliberate design choice, not an approximation waiting to be upgraded. Keeping a YAML parser out of this extension is what lets it accept arbitrary untrusted documents without inheriting a YAML implementation's parsing surface (billion-laughs style alias expansion, custom tags, and so on). Markdown parsing goes to cmark-gfm; YAML parsing goes to the yaml extension; this extension does neither by hand.

What that means in practice. Given this frontmatter:

---
title: My Post
tags:
  - duckdb
  - markdown
author:
  name: Ada
  email: ada@example.com
summary: |
  A multiline
  summary.
quoted: "double quoted"
single: 'single quoted'
url: https://example.com/a:b
---

md_extract_metadata returns:

key	value	why
title	My Post	flat pair, as expected
tags	(empty string)	the sequence below it is not read; tags just has no value
author	(empty string)	the nested map is flattened away
name	Ada	a nested key is hoisted to the top level, where it can collide
email	ada@example.com	split on the first :, so the rest of the line survives
summary	|	the block-scalar indicator is taken as the literal value
quoted	double quoted	a paired " is stripped
single	'single quoted'	single quotes are not stripped
url	https://example.com/a:b	first-: split keeps the remainder intact

Lines with no : at all — - duckdb, - markdown, and the body of the | block — produce no entry and are silently dropped. A sequence of mappings (- name: Ada) does produce an entry, under the key - name.

So: nested maps, lists, multiline |/> block scalars, single-quoted values, and anchors or aliases are not interpreted. Flat key: value documents — the overwhelming majority of blog/Obsidian/Hugo frontmatter — come through exactly right.

If you need real YAML, use the yaml extension. It ships read_yaml_frontmatter, which reads Markdown files and parses their frontmatter with an actual YAML parser:

INSTALL yaml FROM community;
LOAD yaml;

-- Fully typed frontmatter fields, nested structures included
SELECT title, tags FROM read_yaml_frontmatter('posts/*.md');

-- Keep the whole frontmatter document as a YAML/JSON-navigable value
SELECT frontmatter FROM read_yaml_frontmatter('posts/*.md', as_yaml_objects := true);

-- Frontmatter plus the Markdown body in one pass
SELECT title, content FROM read_yaml_frontmatter('posts/*.md', content := true);

read_yaml_frontmatter takes ANY, so a single path, a glob, or a LIST of either all work, and it accepts the named parameters as_yaml_objects, content, and filename. See duckdb_yaml.

The two extensions compose in both directions:

-- markdown extension finds the documents and the body; yaml extension types the frontmatter
SELECT y.title, y.tags, md_stats(m.content).word_count
FROM read_yaml_frontmatter('posts/*.md', filename := true) y
JOIN read_markdown('posts/*.md', filename := true) m ON m.filename = y.filename;

-- or hand a single raw block to the YAML parser yourself
SELECT yaml(md_extract_frontmatter(content)) FROM read_markdown('posts/*.md');

Use md_extract_metadata when you want a cheap flat MAP and your frontmatter is flat. Use read_yaml_frontmatter when the frontmatter is real YAML.

Duck Block Functions

Convert document blocks to/from Markdown. These functions work with the duck_block structure for in-memory document transformations:

• duck_block_to_md(block) - Convert a single block or inline element to Markdown string
• duck_blocks_to_md(blocks[]) - Convert a list of blocks to a complete Markdown document
• duck_blocks_to_sections(blocks[]) - Convert blocks to a list of sections with hierarchy

duck_block structure:

STRUCT(
    kind          VARCHAR,              -- 'block', 'inline' or 'value' (metadata)
    element_type  VARCHAR,              -- 'heading', 'paragraph', 'bold', 'link', etc.
    content       VARCHAR,              -- Text content
    level         INTEGER,              -- Structural depth, minimum 1 (a heading's rank is attributes['heading_level'])
    encoding      VARCHAR,              -- 'text', 'json' (tables), 'yaml'/'toml' (frontmatter)
    attributes    MAP(VARCHAR, VARCHAR),-- Element metadata (heading_level, language, href, etc.)
    element_order INTEGER               -- Position in sequence
)

Note: For headings, the H1-H6 level is stored in attributes['heading_level'] (preferred). If not present, the level field is used as a fallback.

Supported inline types: text, bold/strong, italic/em, code, link, image, strikethrough/del, linebreak/br, math, superscript/sup, subscript/sub

-- Convert blocks back to markdown
SELECT duck_blocks_to_md(list(b ORDER BY element_order))
FROM read_markdown_blocks('source.md') b;

-- Build document programmatically
SELECT duck_blocks_to_md([
    {kind: 'block', element_type: 'heading', content: 'Title', level: 1, encoding: 'text', attributes: MAP{}, element_order: 0},
    {kind: 'block', element_type: 'paragraph', content: 'Body text.', level: NULL, encoding: 'text', attributes: MAP{}, element_order: 1}
]);

-- Compose inline elements
SELECT duck_blocks_to_md([
    {kind: 'inline', element_type: 'text', content: 'Check out ', level: NULL, encoding: 'text', attributes: MAP{}, element_order: 0},
    {kind: 'inline', element_type: 'link', content: 'our docs', level: NULL, encoding: 'text', attributes: MAP{'href': 'https://example.com'}, element_order: 1},
    {kind: 'inline', element_type: 'text', content: ' for details.', level: NULL, encoding: 'text', attributes: MAP{}, element_order: 2}
]);
-- Returns: 'Check out [our docs](https://example.com) for details.'

-- Convert blocks to sections
SELECT s.section_id, s.level, s.title
FROM (
    SELECT unnest(duck_blocks_to_sections(list(b ORDER BY element_order))) as s
    FROM read_markdown_blocks('doc.md') b
);

Document Processing Examples

-- Convert markdown to HTML for web display
SELECT md_to_html(content) as html_content
FROM read_markdown('README.md');

-- Get document statistics
SELECT 
  filename,
  md_stats(content).word_count as words,
  md_stats(content).reading_time_minutes as reading_time
FROM read_markdown('docs/**/*.md');

-- Extract and access frontmatter metadata fields
SELECT
  filename,
  md_extract_metadata(content)['title'] as title,
  md_extract_metadata(content)['author'] as author,
  md_extract_metadata(content) as all_metadata
FROM read_markdown('docs/**/*.md')
WHERE cardinality(md_extract_metadata(content)) > 0;

-- Validate markdown content
SELECT filename, md_valid(content::varchar) as is_valid
FROM read_markdown('**/*.md')
WHERE NOT md_valid(content::varchar);

Content Mode Examples

-- Default 'minimal' mode: each section has only its immediate content
SELECT section_id, title, length(content) as content_length
FROM read_markdown_sections('docs/guide.md')
WHERE level = 1;

-- 'full' mode: sections include all nested subsections
SELECT section_id, title, length(content) as content_length
FROM read_markdown_sections('docs/guide.md', content_mode := 'full')
WHERE level = 1;

-- 'smart' mode: adaptive - includes small subsections, summarizes large ones
SELECT section_id, title, content
FROM read_markdown_sections('docs/guide.md', content_mode := 'smart')
WHERE level = 1;

-- Fragment syntax: get a specific section and its descendants
SELECT section_id, title, level
FROM read_markdown_sections('README.md#installation');

-- Limit parsing depth: only top 2 levels
SELECT section_id, level, title
FROM read_markdown_sections('docs/**/*.md', max_depth := 2);

-- Extract section with subsections using scalar function
SELECT md_extract_section(content, 'api-reference', true) as full_section
FROM read_markdown('docs/api.md');

-- Extract section without subsections (minimal)
SELECT md_extract_section(content, 'api-reference', false) as minimal_section
FROM read_markdown('docs/api.md');

-- Use section_path for hierarchical navigation
SELECT section_path, title
FROM read_markdown_sections('docs/**/*.md')
WHERE section_path LIKE 'api-reference/%';

COPY TO Markdown

Export query results to Markdown files. Three modes support different use cases:

Mode	Use Case	Input Columns
table (default)	Export any query as a markdown table	Any columns
document	Reconstruct markdown from sections	level, title, content
blocks / duck_block	Round-trip block-level representation	kind, element_type, content, level, encoding, attributes

Table Mode (Default)

Export any query result as a formatted Markdown table with automatic column alignment:

-- Basic table export
COPY (SELECT * FROM my_table) TO 'output.md' (FORMAT MARKDOWN);

-- With options
COPY my_table TO 'output.md' (FORMAT MARKDOWN,
    header true,           -- Include header row (default: true)
    escape_pipes true,     -- Escape | characters (default: true)
    escape_newlines true,  -- Convert newlines to <br> (default: true)
    null_value 'N/A'       -- Custom NULL representation (default: empty)
);

Output:

| id | name | score |
|---:|---|---:|
| 1 | Alice | 95.5 |
| 2 | Bob | 87.0 |

Alignment is automatic: numeric columns are right-aligned, text is left-aligned, booleans are centered.

Document Mode

Reconstruct Markdown documents from structured section data. This complements read_markdown_sections for round-trip document processing:

-- Create sections
CREATE TABLE sections (level INTEGER, title VARCHAR, content VARCHAR);
INSERT INTO sections VALUES
    (1, 'Introduction', 'Welcome to the guide.'),
    (2, 'Getting Started', 'First steps here.'),
    (1, 'Conclusion', 'Thanks for reading!');

-- Export as document
COPY sections TO 'guide.md' (FORMAT MARKDOWN, markdown_mode 'document');

Output:

# Introduction

Welcome to the guide.

## Getting Started

First steps here.

# Conclusion

Thanks for reading!

Document Mode Options:

COPY sections TO 'doc.md' (FORMAT MARKDOWN,
    markdown_mode 'document',
    level_column 'level',      -- Column with heading level 1-6 (default: 'level')
    title_column 'title',      -- Column with heading text (default: 'title')
    content_column 'content',  -- Column with section body (default: 'content')
    frontmatter 'title: My Doc
author: Me',                   -- Optional frontmatter block, emitted verbatim
    blank_lines 1              -- Blank lines between sections (default: 1)
);

Level 0 as Frontmatter: Rows with level = 0 are rendered as frontmatter blocks (the content is written between --- delimiters verbatim; it is not validated as YAML):

INSERT INTO doc VALUES (0, '', 'title: Generated Doc');
INSERT INTO doc VALUES (1, 'Body', 'Main content');
COPY doc TO 'output.md' (FORMAT MARKDOWN, markdown_mode 'document');

Output:

---
title: Generated Doc
---

# Body

Main content

Blocks Mode

Export block-level document representation for full-fidelity round-trips. This mode complements read_markdown_blocks() and supports both block and inline elements:

-- Read blocks from a file
CREATE TABLE blocks AS
SELECT kind, element_type, content, level, encoding, attributes
FROM read_markdown_blocks('source.md');

-- Modify blocks
UPDATE blocks SET content = upper(content) WHERE element_type = 'heading';

-- Write back to markdown
COPY blocks TO 'output.md' (FORMAT MARKDOWN, markdown_mode 'blocks');

Blocks Mode Options:

COPY blocks TO 'doc.md' (FORMAT MARKDOWN,
    markdown_mode 'blocks',
    kind_column 'kind',               -- Column holding the kind (default: 'kind')
    element_type_column 'element_type',-- Column with element type (default: 'element_type')
    content_column 'content',          -- Column with block content (default: 'content')
    level_column 'level',              -- Column with level/depth (default: 'level')
    encoding_column 'encoding',        -- Column with encoding type (default: 'encoding')
    attributes_column 'attributes'     -- Column with attributes map (default: 'attributes')
);

Block Rendering (kind = 'block'):

• heading - Rendered as # Title (level determines # count)
• paragraph - Plain text with blank lines
• code - Fenced code blocks with language from attributes['language']
• blockquote - Prefixed with >
• list - structural: items are list_item children (uses attributes['list_type'], with the legacy ordered boolean accepted as a fallback)
• table - JSON object rendered as markdown table
• hr - Horizontal rule ---
• frontmatter - Raw block between --- delimiters (emitted verbatim, not YAML-validated)

Inline Rendering (kind = 'inline'):

• bold / strong - **text**
• italic / em - *text*
• code - `text`
• link - [text](href "title")
• text - Plain text (no formatting)
• Plus: strikethrough, superscript, subscript, math, etc.

Inline elements are concatenated without trailing newlines. When transitioning from inline to block elements, proper paragraph breaks are automatically inserted.

Round-Trip Workflow Example

Complete workflow reading, transforming, and writing markdown:

-- Read sections from source document
CREATE TABLE my_sections AS
SELECT level, title, content
FROM read_markdown_sections('source.md', content_mode := 'minimal');

-- Transform content (e.g., add prefix to all headings)
UPDATE my_sections SET title = 'Chapter: ' || title WHERE level = 1;

-- Write back to markdown
COPY my_sections TO 'output.md' (FORMAT MARKDOWN, markdown_mode 'document');

-- Or use blocks for full-fidelity round-trip
COPY (
    SELECT kind, element_type, content, level, encoding, attributes
    FROM read_markdown_blocks('source.md')
) TO 'copy.md' (FORMAT MARKDOWN, markdown_mode 'blocks');

Use Cases

Documentation Analysis

Analyze code documentation across entire repositories:

-- Find all Python examples in documentation
SELECT filename, unnest.code, unnest.line_number
FROM read_markdown('docs/**/*.md') docs,
     UNNEST(md_extract_code_blocks(docs.content))
WHERE unnest.language = 'python';

-- Audit external links in documentation  
SELECT unnest.url, count(*) as usage_count
FROM read_markdown('**/*.md') docs,
     UNNEST(md_extract_links(docs.content))
WHERE unnest.url LIKE 'http%'
GROUP BY unnest.url
ORDER BY usage_count DESC;

Content Quality Assessment

Evaluate documentation completeness and quality:

-- Calculate content richness scores
SELECT 
  filename,
  len(md_extract_code_blocks(content)) * 3 +
  len(md_extract_links(content)) * 1 +  
  len(md_extract_images(content)) * 2 as richness_score
FROM read_markdown('docs/**/*.md')
ORDER BY richness_score DESC;

Large-Scale Documentation Search

Create searchable knowledge bases from documentation:

-- Create searchable documentation index
CREATE TABLE docs AS
SELECT 
  title, level, content,
  row_number() OVER (ORDER BY title) as section_id
FROM read_markdown_sections('**/*.md', include_content := true);

-- Create full-text search index using plain text conversion
PRAGMA create_fts_index('docs', 'section_id', 'md_to_text(content)');

-- Search documentation
SELECT title, substring(md_to_text(content), 1, 200) as preview
FROM docs 
WHERE md_to_text(content) ILIKE '%memory optimization%'
ORDER BY title;

Obsidian / Wiki Vault Analytics

A vault is just a folder of .md files. With extract_extensions := 'obsidian', you can build a backlink graph and tag rollups across the whole vault in SQL:

-- Backlink edges across a vault: (source_file, target_note)
WITH wl AS (
  SELECT filename AS source, unnest(wikilinks) AS w
  FROM read_markdown('vault/**/*.md', include_filepath := true, extract_extensions := 'wikilinks')
)
SELECT source, w.target AS target_note, w.is_embed, w.line_number
FROM wl;

-- Orphan notes (no incoming wikilinks)
WITH edges AS (
  SELECT regexp_extract(filename, '([^/]+)\.md$', 1) AS note,
         unnest(wikilinks).target AS target
  FROM read_markdown('vault/**/*.md', include_filepath := true, extract_extensions := 'wikilinks')
)
SELECT DISTINCT note FROM edges
WHERE note NOT IN (SELECT target FROM edges);

-- Tag usage across the vault
SELECT t.tag, count(*) AS uses
FROM read_markdown('vault/**/*.md', extract_extensions := 'tags'),
     UNNEST(tags) AS t
GROUP BY t.tag
ORDER BY uses DESC;

-- Per-section tag/wikilink density (which sections are link-rich?)
SELECT title, len(wikilinks) + len(tags) AS link_density
FROM read_markdown_sections('vault/**/*.md', extract_extensions := 'obsidian')
ORDER BY link_density DESC
LIMIT 20;

Glob Pattern Support

The extension includes comprehensive glob pattern support across different file systems:

-- Basic patterns
SELECT * FROM read_markdown('docs/*.md');
SELECT * FROM read_markdown('**/*.markdown');

-- Recursive directory scanning
SELECT * FROM read_markdown('documentation/**/*.md');

-- Multiple patterns
SELECT * FROM read_markdown(['README.md', 'docs/**/*.md', 'examples/**/*.md']);

-- Remote file systems (S3, etc.)
SELECT * FROM read_markdown('s3://bucket/docs/*.md');

Supported patterns:

• *.md, **/*.markdown - Standard glob patterns
• docs/**/*.md - Recursive directory scanning
• Mixed lists combining files and glob patterns
• Remote file systems with graceful degradation

Return Types

All functions return structured data using LIST<STRUCT> types:

-- Code blocks
LIST<STRUCT(language VARCHAR, code VARCHAR, line_number BIGINT, info_string VARCHAR)>

-- Links (is_reference=true for reference-style links like [text][ref])
LIST<STRUCT(text VARCHAR, url VARCHAR, title VARCHAR, is_reference BOOLEAN, line_number BIGINT)>

-- Images
LIST<STRUCT(alt_text VARCHAR, url VARCHAR, title VARCHAR, line_number BIGINT)>

-- Table rows
LIST<STRUCT(table_index BIGINT, row_type VARCHAR, row_index BIGINT, column_index BIGINT, cell_value VARCHAR, line_number BIGINT, num_columns BIGINT, num_rows BIGINT)>

-- Tables JSON
LIST<STRUCT(table_index BIGINT, line_number BIGINT, num_columns BIGINT, num_rows BIGINT, headers VARCHAR[], table_data VARCHAR[][])>

-- Blocks (from read_markdown_blocks)
(kind VARCHAR, element_type VARCHAR, content VARCHAR, level INTEGER, encoding VARCHAR, attributes MAP(VARCHAR, VARCHAR), element_order INTEGER)

Use with UNNEST() to flatten into rows or len() to count elements.

Type System and Automatic Casting

The extension defines a MARKDOWN type (alias: md) that automatically casts to/from VARCHAR:

-- Explicit casting
SELECT '# Hello World'::markdown;
SELECT '# Hello World'::md;

-- Automatic casting - these all work seamlessly
SELECT md_to_html('# Hello World');  -- VARCHAR automatically cast to MARKDOWN
SELECT md_stats('# Test\nContent');  -- VARCHAR automatically cast to MARKDOWN
SELECT content::varchar FROM read_markdown('README.md');  -- MARKDOWN to VARCHAR

-- Type checking
SELECT typeof('# Hello World'::markdown);  -- Returns 'md'

All markdown functions accept both VARCHAR and MARKDOWN types through automatic casting, making the API flexible and easy to use.

Performance

The extension is designed for high-performance document processing:

• 4,000+ sections/second processing rate on typical hardware
• Memory efficient streaming processing
• Parallel safe for concurrent query execution
• Cross-platform robust glob support including remote file systems

Real-world benchmark: Processing 287 Markdown files (2,699 sections, 1,137 code blocks, 1,174 links) in 603ms.

Current Status

✅ Available (v1.3.6):

• Complete file reading functions (read_markdown, read_markdown_sections, read_markdown_blocks) with full parameter support
• COPY TO markdown with table, document, and blocks modes (including inline element support)
• All 5 extraction functions (md_extract_code_blocks, md_extract_links, md_extract_images, md_extract_table_rows, md_extract_tables_json)
• Document processing functions (md_to_html, md_to_text, md_valid, md_stats, md_extract_metadata, md_extract_section, md_section_breadcrumb)
• Block-level document representation with read_markdown_blocks() and markdown_mode 'blocks' / 'duck_block'
• Duck block conversion functions (duck_block_to_md, duck_blocks_to_md, duck_blocks_to_sections)
• Unified duck_block shape with kind field for block/inline differentiation
• Content modes for flexible section extraction: 'minimal' (default), 'full', 'smart'
• Fragment syntax for filtering sections: 'file.md#section-id'
• Section hierarchy with section_path column for navigation
• Reference link detection in md_extract_links (is_reference field)
• Advanced section filtering and processing options (min/max level, max_depth, content inclusion, etc.)
• Frontmatter metadata as MAP(VARCHAR, VARCHAR) for easy field access
• Replacement scan support for table-like syntax (FROM '*.md')
• MARKDOWN type with automatic VARCHAR casting
• Cross-platform support (Linux, macOS, Windows, WebAssembly)
• Robust glob pattern support for local and remote file systems
• High-performance content processing (4,000+ sections/second)
• Comprehensive parameter system for flexible file processing
• Full test suite with 1102 passing assertions across 20 test files

🗓️ Future Roadmap:

• Document interchange format for cross-extension compatibility (HTML, XML, etc.)
• Custom renderer integration for specialized markdown flavors
• Streaming parser optimizations for very large documents (>100MB)
• Advanced query optimization for document search workloads

Dependencies

• cmark-gfm: GitHub Flavored Markdown parsing library
• DuckDB: Version 1.0.0 or later

Building

# Clone with dependencies
git clone --recurse-submodules https://github.com/teaguesterling/duckdb_markdown
cd duckdb_markdown

# Build extension
make

# Run tests
make test

Testing

Comprehensive test suite with 1102 passing assertions across 20 test files:

• Functionality tests: All extraction functions with edge cases
• Block-level tests: Round-trip parsing and rendering with inline element support
• Performance tests: Large-scale document processing
• Cross-platform tests: File system compatibility scenarios
• Integration tests: Complex queries and real-world usage patterns

Contributing

Contributions welcome! The extension provides a solid foundation for Markdown analysis with room for enhancements:

• File reading functions: Complete the table function implementations
• Metadata extraction: Frontmatter parsing and document statistics
• Performance optimizations: Streaming improvements for very large documents
• Advanced features: Custom renderer integration and streaming parser optimizations

WebAssembly (WASM) Support

The extension fully supports WebAssembly environments (DuckDB-WASM in browsers). All markdown functions work in the browser:

// Load the extension in DuckDB-WASM
await conn.query("LOAD 'markdown.duckdb_extension.wasm'");

// Register a markdown file
await db.registerFileText('doc.md', '# Hello\n\nWorld');

// Query markdown content
const result = await conn.query("SELECT * FROM read_markdown('doc.md')");

Important: Use the extension version that matches your duckdb-wasm version:

• duckdb-wasm 1.32.0 → Use markdown-v1.4.3-extension-wasm_eh
• duckdb-wasm latest → Use markdown-main-extension-wasm_eh

For technical details on how WASM support was implemented (including cmark-gfm static linking and function pointer compatibility), see docs/WASM.md.

License

MIT License - see LICENSE file for details.