VaultQuery

VaultQuery Plugin

Execute SELECT, INSERT, UPDATE, and DELETE statements on notes, properties, tasks, tables, headings and links. Output to SlickGrid, Markdown Table, Chart.js or JavaScript rendering.

Features

• Indexing: Notes, frontmatter, tables, tasks, headings, links, and tags stored in SQLite
• Live updates: File create, modify, rename, and delete events update the index
• SQL queries: Standard SQLite syntax, joins, aggregations, views, and custom functions
• Output formats: SlickGrid tables, Markdown tables, Chart.js charts, FullCalendar calendars, and JavaScript rendering
• Write operations: INSERT, UPDATE, and DELETE previews with database-to-file sync

Code Blocks

Database Schema

notes table (always available):

• path (TEXT): File path relative to vault root (PRIMARY KEY)
• title (TEXT): Note title (auto-derived from path on INSERT)
• content (TEXT NOT NULL): Note content (without frontmatter, only if content indexing enabled)
• created (INTEGER): Creation timestamp in Unix milliseconds (auto-set on INSERT)
• modified (INTEGER): Last modification timestamp in Unix milliseconds (auto-set on INSERT)
• size (INTEGER): File size in bytes (auto-calculated from content on INSERT)

properties table (when frontmatter/properties indexing is enabled):

• path (TEXT): Foreign key to notes.path
• key (TEXT): Property name (supports nested keys like "author.name")
• value (TEXT): Property value as string
• value_type (TEXT): Type of value (auto-derived: 'number', 'boolean', or 'string')
• array_index (INTEGER): Index for array elements (NULL for non-array values)
• PRIMARY KEY: (path, key, array_index)

table_cells table (when table indexing is enabled):

• id (INTEGER): Auto-incrementing ID (PRIMARY KEY)
• path (TEXT): Foreign key to notes.path
• table_index (INTEGER): Index of table within the note (default: 0 = first table)
• table_name (TEXT): Name of the table (from preceding heading, optional)
• row_index (INTEGER): Row index within the table (0-based)
• column_name (TEXT): Column header name
• cell_value (TEXT): Cell content as string
• value_type (TEXT): Type of value (auto-derived: 'number' or 'text')
• line_number (INTEGER): Line number where this table row appears (optional)

tables table (when table indexing is enabled):

• path (TEXT): Foreign key to notes.path
• table_index (INTEGER): Index of table within the note (0-based)
• table_name (TEXT): Name of the table (from preceding heading, optional)
• block_id (TEXT): Obsidian block reference (e.g., ^table-1)
• start_offset (INTEGER): Character offset where table starts
• end_offset (INTEGER): Character offset where table ends
• line_number (INTEGER): Line number for INSERT positioning (optional)
• PRIMARY KEY: (path, table_index)

table_rows view (when table indexing is enabled):

A convenience view for row-based table operations. Columns:

• path (TEXT): Foreign key to notes.path
• table_index (INTEGER): Index of table within the note (0-based)
• row_index (INTEGER): Row index within the table (0-based)
• row_json (TEXT): JSON object of column_name→cell_value pairs
• table_line_number (INTEGER): Line number for INSERT positioning (optional)

tasks table (when task indexing is enabled):

• id (INTEGER): Auto-incrementing task ID (PRIMARY KEY)
• path (TEXT): Foreign key to notes.path
• task_text (TEXT): Content of the task
• status (TEXT): Task status - TODO, IN_PROGRESS, DONE, CANCELLED (default: 'TODO')
• priority (TEXT): Priority level - highest, high, medium, low, lowest, or NULL
• due_date (TEXT): Due date in YYYY-MM-DD format or NULL
• scheduled_date (TEXT): Scheduled date in YYYY-MM-DD format or NULL
• start_date (TEXT): Start date in YYYY-MM-DD format or NULL
• created_date (TEXT): Task creation date in YYYY-MM-DD format or NULL
• done_date (TEXT): Completion date in YYYY-MM-DD format or NULL
• cancelled_date (TEXT): Cancellation date in YYYY-MM-DD format or NULL
• recurrence (TEXT): Recurrence rule (e.g., "every week") or NULL
• on_completion (TEXT): Action on completion or NULL
• task_id (TEXT): Custom task identifier or NULL
• depends_on (TEXT): Task dependencies or NULL
• tags (TEXT): Space-separated hashtags or NULL
• line_number (INTEGER): Line number where task appears (optional)
• block_id (TEXT): Obsidian block reference (e.g., ^task-1)
• start_offset (INTEGER): Character offset where task starts
• end_offset (INTEGER): Character offset where task ends
• anchor_hash (TEXT): Content-based hash for change detection
• section_heading (TEXT): Heading under which the task appears

headings table (when heading indexing is enabled):

• id (INTEGER): Auto-incrementing ID (PRIMARY KEY)
• path (TEXT): Foreign key to notes.path
• level (INTEGER): Heading level (1-6 for H1-H6)
• line_number (INTEGER): Line number where heading appears (1-based)
• heading_text (TEXT): Text content of the heading
• block_id (TEXT): Obsidian block reference (e.g., ^heading-1)
• start_offset (INTEGER): Character offset where heading starts
• end_offset (INTEGER): Character offset where heading ends
• anchor_hash (TEXT): Content-based hash for change detection

links table (when link indexing is enabled):

• id (INTEGER): Auto-incrementing link ID (PRIMARY KEY)
• path (TEXT): Foreign key to notes.path
• link_text (TEXT): Display text of the link (auto-derived from link_target if not provided)
• link_target (TEXT): Target of the link (original text for internal, URL for external)
• link_target_path (TEXT): Resolved file path for internal links
• link_type (TEXT): Type of link - auto-derived: 'external' if target starts with http/https, otherwise 'internal'
• line_number (INTEGER): Line number where link appears (optional)
• insert_position (TEXT): Position hint for INSERT operations - new_line (default), line_start, or line_end

tags table (when tag indexing is enabled):

• id (INTEGER): Auto-incrementing ID (PRIMARY KEY)
• path (TEXT): Foreign key to notes.path
• tag_name (TEXT): Name of the tag
• line_number (INTEGER): Line number where tag appears (optional)
• insert_position (TEXT): Position hint for INSERT operations - new_line (default), line_start, or line_end

list_items table (when list item indexing is enabled):

• id (INTEGER): Auto-incrementing ID (PRIMARY KEY)
• path (TEXT): Foreign key to notes.path
• list_index (INTEGER): Index of the list within the note (default: 0)
• item_index (INTEGER): Index of the item within all lists in the note (default: 0)
• parent_index (INTEGER): Index of parent item for nested lists, or NULL
• content (TEXT): Text content of the list item
• list_type (TEXT): Type of list - bullet, numbered (default: 'bullet')
• indent_level (INTEGER): Nesting depth (0 = top level, default: 0)
• line_number (INTEGER): Line number where item appears (optional)
• block_id (TEXT): Obsidian block reference
• start_offset (INTEGER): Character offset where item starts
• end_offset (INTEGER): Character offset where item ends
• anchor_hash (TEXT): Content-based hash for change detection

Usage

Add a vaultquery code block with a SQL query:

SELECT title, path, modified FROM notes
WHERE content LIKE '%important%'
ORDER BY modified DESC
LIMIT 10

Results render in a sortable SlickGrid table.

Write Operations (INSERT, UPDATE, DELETE)

Important: Write operations permanently modify vault files. VaultQuery has no built-in undo or version history. Obsidian Sync provides version history. Write operations require the plugin setting to be enabled.

vaultquery-write runs write statements with file synchronization:

-- Add a new note
INSERT INTO notes (path, content)
VALUES ('Condo Notes.md', '# Shopping List for new Condo');

-- Add a new task
INSERT INTO tasks (path, task_text, status)
VALUES ('Condo Notes.md', 'Buy plasma TV', 'TODO');

-- Add a new tag
INSERT INTO tags (path, tag_name)
VALUES ('Condo Notes.md', 'party-pad');

-- Add a new link
INSERT INTO links (path, link_target)
VALUES ('Condo Notes.md', 'Office/Dinner Party Plan.md');

-- Add a heading at a specific line
INSERT INTO headings (path, level, heading_text, line_number)
VALUES ('Condo Notes.md', 2, 'Sliding glass door repair quotes:', 4);

-- Add a tag at the start of a line
INSERT INTO tags (path, tag_name, line_number, insert_position)
VALUES ('Condo Notes.md', 'fake-overtime-assignment', 3, 'line_end');

-- Create a new table at a specific line using table_rows view
INSERT INTO table_rows (path, table_index, row_json, table_line_number)
VALUES ('Condo Notes.md', 1, json('{"Vendor": "", "Price": ""}'), 5);

CTEs support multi-step inserts.

WITH
    -- Select all notes in the Projects folder
    project_notes AS (
      SELECT path, title FROM notes
      WHERE path LIKE 'Projects/%'
      ORDER BY title
    ),
    -- Create a flattened list of links to those notes
    flattenedLinks AS (
      SELECT GROUP_CONCAT('- ' || link(path, title), '\n') as list
      FROM project_notes
    )
-- Create an index linking to each note in the Projects folder
INSERT INTO notes (path, content)
SELECT
'Projects/Index.md',
'# Project Index

' || COALESCE(list, '_No projects found_')
FROM flattenedLinks;

UPDATE Operations

Existing records can be modified with vaultquery-write:

UPDATE notes
SET content = content || '\n\nList condo on eBay at 80% of purchase price.'
WHERE path = 'Condo Notes.md'

DELETE Operations

Records can be removed with vaultquery-write:

DELETE FROM notes
WHERE path = 'Condo Notes.md'

Markdown Table Export

vaultquery-markdown generates exportable Markdown tables:

SELECT title, path, modified FROM notes 
WHERE content LIKE '%important%' 
ORDER BY modified DESC 
LIMIT 10

Markdown Configuration Options

Configure the markdown output with a config: section:

SELECT title, path, size
FROM notes
ORDER BY size DESC
LIMIT 10;

config:
alignment: left, left, right

Available config options:

• alignment: Column alignments (left, center, right)

Tip: Format dates directly in SQL using datetime() functions.

JavaScript Rendering

JavaScript rendering can be enabled in settings for complete control over formatting. Rendering code is user-authored JavaScript and runs with the plugin's permissions. HTML template execution is handled by the local user-template-renderer package.

SELECT title, path, modified FROM notes 
WHERE content LIKE '%important%' 
ORDER BY modified DESC 
LIMIT 10;

template:
return `
  <div>
    <h3>Important Notes (${count} results)</h3>
    <ul>
      ${results.map(note => `
        <li>
          ${h.link(note.path, note.title)} - 
          <em>${h.formatDate(note.modified)}</em>
        </li>
      `).join('')}
    </ul>
  </div>
`;

JavaScript rendering code receives these variables:

• results - Array of query result rows
• query - The SQL query that was executed
• count - Number of results
• h - Helper functions object

Helper Functions

The h object provides 50+ utility functions from placeholder-resolver.

Obsidian Helpers

String Manipulation

Path Helpers

Formatting

Arrays

Objects & JSON

SQL Helpers (for query templates)

Null Handling

Quick Reference Table

†With line_number and optional insert_position (new_line, line_start, line_end)

‡With line_number (table_cells) or table_line_number (table_rows) to position new tables

*Requires "Allow file deletion" setting to be enabled

Key Points:

• All tables support full CRUD operations with sync back to files
• tasks, headings, list_items INSERT at specified line_number or end of file (line-based elements)
• tags INSERT adds to frontmatter when no line_number specified, or inserts inline with insert_position
• links INSERT appends to end of file when no line_number specified, or inserts at position with insert_position
• table_cells and table_rows INSERT can use line_number/table_line_number to create tables at specific positions
• notes_with_properties creates files with frontmatter in one operation
• note_properties handles properties-only queries (path + property columns, no notes columns)
• table_rows handles table row manipulation with JSON

Chart Rendering

vaultquery-chart renders query results with Chart.js.

Charts require columns named label and value (or x and y for scatter plots). Add a series column for multiple datasets.

Configuration Options

SQL Columns for Customization

Vault Growth Over Time

SELECT
    strftime('%Y-%m', created/1000, 'unixepoch') as label,
    COUNT(*) as value
FROM notes
WHERE created > 0
GROUP BY label
ORDER BY label;
config:
type: line
title: Notes Created Per Month
xLabel: Month
yLabel: Notes
datasetBackgroundColor: rgba(75, 192, 192, 0.2)
datasetBorderColor: rgba(75, 192, 192, 1)

Content Distribution by Folder

SELECT
    COALESCE(SUBSTR(path, 1, INSTR(path || '/', '/') - 1), 'Root') as label,
    COUNT(*) as value
FROM notes
GROUP BY label
ORDER BY value DESC
LIMIT 8;
config:
type: doughnut
title: Notes by Top-Level Folder

Writing Activity by Day of Week

SELECT
    CASE CAST(strftime('%w', modified/1000, 'unixepoch') AS INTEGER)
        WHEN 0 THEN 'Sun'
        WHEN 1 THEN 'Mon'
        WHEN 2 THEN 'Tue'
        WHEN 3 THEN 'Wed'
        WHEN 4 THEN 'Thu'
        WHEN 5 THEN 'Fri'
        WHEN 6 THEN 'Sat'
    END as label,
    COUNT(*) as value,
    CASE CAST(strftime('%w', modified/1000, 'unixepoch') AS INTEGER)
        WHEN 0 THEN 'rgba(255, 99, 132, 0.8)'
        WHEN 6 THEN 'rgba(255, 99, 132, 0.8)'
        ELSE 'rgba(54, 162, 235, 0.8)'
    END as backgroundColor
FROM notes
GROUP BY strftime('%w', modified/1000, 'unixepoch')
ORDER BY CAST(strftime('%w', modified/1000, 'unixepoch') AS INTEGER);
config:
type: bar
title: Files Modified by Day of Week

Task Status Breakdown

SELECT
    status as label,
    COUNT(*) as value,
    CASE status
        WHEN 'DONE' THEN 'rgba(75, 192, 192, 0.8)'
        WHEN 'TODO' THEN 'rgba(255, 205, 86, 0.8)'
        WHEN 'IN_PROGRESS' THEN 'rgba(54, 162, 235, 0.8)'
        WHEN 'CANCELLED' THEN 'rgba(255, 99, 132, 0.8)'
        ELSE 'rgba(201, 203, 207, 0.8)'
    END as backgroundColor,
    CASE status
        WHEN 'DONE' THEN 'rgba(75, 192, 192, 1)'
        WHEN 'TODO' THEN 'rgba(255, 205, 86, 1)'
        WHEN 'IN_PROGRESS' THEN 'rgba(54, 162, 235, 1)'
        WHEN 'CANCELLED' THEN 'rgba(255, 99, 132, 1)'
        ELSE 'rgba(201, 203, 207, 1)'
    END as borderColor
FROM tasks
GROUP BY status;
config:
type: pie
title: Task Status

Multi-Series: Tasks by Status and Priority

SELECT
    status as label,
    COALESCE(priority, 'none') as series,
    COUNT(*) as value,
    CASE priority
        WHEN 'high' THEN 'rgba(255, 99, 132, 0.8)'
        WHEN 'medium' THEN 'rgba(255, 205, 86, 0.8)'
        WHEN 'low' THEN 'rgba(75, 192, 192, 0.8)'
        ELSE 'rgba(201, 203, 207, 0.8)'
    END as backgroundColor
FROM tasks
GROUP BY status, priority;
config:
type: bar
title: Tasks by Status and Priority

Mixed Chart: Note Count with Size Trend

SELECT
    strftime('%Y-%m', created/1000, 'unixepoch') as label,
    COUNT(*) as value,
    'Notes' as series,
    'bar' as chartType,
    'rgba(54, 162, 235, 0.8)' as backgroundColor
FROM notes WHERE created > 0
GROUP BY label
UNION ALL
SELECT
    strftime('%Y-%m', created/1000, 'unixepoch') as label,
    ROUND(AVG(size)/1024.0, 1) as value,
    'Avg Size (KB)' as series,
    'line' as chartType,
    'rgba(255, 99, 132, 1)' as backgroundColor
FROM notes WHERE created > 0
GROUP BY label
ORDER BY label;
config:
type: bar
title: Monthly Notes with Average Size Trend

Scatter: File Size vs Word Count

SELECT
    ROUND(size/1024.0, 1) as x,
    LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) as y
FROM notes
WHERE size > 0 AND size < 100000
LIMIT 100;
config:
type: scatter
title: File Size vs Approximate Word Count
xLabel: Size (KB)
yLabel: Words
datasetBackgroundColor: rgba(54, 162, 235, 0.6)

Template Examples

Task List Grouped by Status

SELECT status, task_text, path FROM tasks
WHERE status IN ('TODO', 'DONE', 'IN_PROGRESS')
ORDER BY status, path;

template:
const grouped = results.reduce((acc, t) => {
  (acc[t.status] ||= []).push(t);
  return acc;
}, {});

return Object.entries(grouped).map(([status, tasks]) => `
  <details open>
    <summary><strong>${status}</strong> (${tasks.length})</summary>
    <ul>
      ${tasks.map(t => `<li>${h.link(t.path)} - ${h.renderWikilinks(t.task_text)}</li>`).join('')}
    </ul>
  </details>
`).join('');

Recently Modified Notes with Preview

SELECT title, path, modified, SUBSTR(content, 1, 150) as preview
FROM notes
ORDER BY modified DESC
LIMIT 5;

template:
return `
<div style="display: flex; flex-direction: column; gap: 1em;">
  ${results.map(n => `
    <div style="border-left: 3px solid var(--interactive-accent); padding-left: 1em;">
      <strong>${h.link(n.path, n.title)}</strong>
      <small style="color: var(--text-muted);"> — ${h.formatDate(n.modified)}</small>
      <p style="margin: 0.5em 0; color: var(--text-muted);">${h.escape(n.preview)}...</p>
    </div>
  `).join('')}
</div>
`;

Tag Cloud

SELECT tag_name, COUNT(*) as count
FROM tags
GROUP BY tag_name
ORDER BY count DESC
LIMIT 20;

template:
const max = Math.max(...results.map(r => r.count));
return `
<div style="display: flex; flex-wrap: wrap; gap: 0.5em;">
  ${results.map(t => {
    const size = 0.8 + (t.count / max) * 1.2;
    return `<span style="font-size: ${size}em; opacity: ${0.5 + t.count/max/2};">#${t.tag_name}</span>`;
  }).join('')}
</div>
`;

Usage for Developers

VaultQuery exposes an API for third-party Obsidian plugins. The API includes the ability to execute SQL queries, register custom functions, custom views, write operations when "Allow write operations" is enabled, and managed third-party provider table registration.

// Get the VaultQuery API
const vaultQuery = this.app.plugins.getPlugin('vaultquery');
if (vaultQuery?.api) {
  // Wait for indexing to complete
  await vaultQuery.api.waitForIndexing();

  // Execute queries
  const results = await vaultQuery.api.query('SELECT * FROM notes LIMIT 10');

  // Register custom functions
  vaultQuery.api.registerCustomFunction('myFunc', 'function(x) { return x * 2; }');
}

For third-party provider tables, prefer registerVaultQueryTableProviders() from vaultquery/api. It handles mobile-safe API waiting, capability checks, retry, database recovery, and cleanup.

The vaultquery-api-help code block provides complete API documentation in any note.

Settings and Configuration

Indexing Features (Configurable)

• Content Indexing: Index note content for full-text search
• Frontmatter Indexing: Index YAML frontmatter properties
• Table Indexing: Parse and index markdown tables
• Task Indexing: Index task lists with priorities and due dates
• Heading Indexing: Index note headings and structure
• Link Indexing: Index internal and external links
• Tag Indexing: Index hashtags throughout notes

Performance Settings

• File Size Limit: Maximum file size to index (default: 1MB)
• Exclude Patterns: Regex patterns for files to skip
• Batch Size: Number of files to process at once

Write Operations

• Enable Write Operations: Allow UPDATE, INSERT, DELETE queries (disabled by default)
• Auto File Sync: Update vault files when database rows change

The database is stored in Obsidian's Configuration Folder in /plugins/vaultquery/database.db when using disk storage.

Known Issues

• Grid refresh after scrolling: Obsidian's DOM virtualization may detach grid elements when scrolling long notes. The refresh button restores the grid, and the plugin attempts periodic auto-restore.

• Block references for updates: Task and heading updates work best when content has explicit block references (e.g., ^task-1). Without them, the plugin uses content hashing which is not as accurate.

• Column width persistence: Column widths are remembered during a session and are intentionally not remembered when Obsidian restarts.

Network Requests

VaultQuery has one network-capable code path: loading the SQL.js WebAssembly binary when a local binary is not available or when CDN loading is selected.

SQL.js loading modes:

When local caching is enabled, a CDN-loaded binary is saved to the plugin folder for later use.

Background Tasks

VaultQuery uses timers for local indexing, rendering, and UI coordination. Timers are not used for telemetry and do not periodically transmit vault data.

SQL.js WASM loading is not scheduled by a timer. It occurs during database initialization according to the SQL.js loading mode described above.

Dependencies

Similar Plugins

• Dataview - Dataview's DQL syntax is simpler than SQL. Dataview and its successor Datacore are read-only and may index faster than VaultQuery. VaultQuery supports write operations, SQLite-backed queries, custom views, JavaScript SQL functions, and provider tables. Dataview or Datacore fit read-only workloads, especially when indexing speed is the main requirement. VaultQuery fits write workflows or cases where standard SQL syntax is preferred.