SQL Query Plugin

sqlQuery flattens every entry into a typed SQLite table per source and adds a content.query(source) method: a chainable builder to filter, sort, group, and paginate content without writing SQL. Columns mirror the item shape (path, the identity fields under meta., and the entry's frontmatter under data.) and results come back as nested { path, data, meta } items, identical to content.list().

Usage

import { comarkContent } from 'comark-content'
import sqlite from 'comark-content/database/sqlite-node'
import sqlQuery from 'comark-content/plugins/sql-query'

const database = sqlite()

const content = comarkContent({
  source: fs('./content/blog'),
  plugins: [sqlQuery({ database })],
})

const featured = await content
  .query('default')
  .where('data.featured', '=', true)
  .order('data.published', 'DESC')
  .limit(10)
  .all()

API

sqlQuery(options)

Returns a ContentPlugin that flattens each source into a typed SQLite table and adds a content.query(source) method to the Content instance. Throws if options.database is missing.

Parameters:

• options: see Options.

Returns: ContentPlugin

content.query(source?)

Added by the plugin. Returns a chainable query builder for source (defaults to 'default'). Conditions address flattened columns (path, meta.*, data.*); the terminal all() / first() / count() methods execute the query and return nested items.

Options

database

The RelationalDatabase that holds the per-source query tables. Pass sqlite() on the server or sqliteWasm() in the browser. sqlQuery throws at construction time if this is missing.

Querying

Query builder

const featured = await content
  .query('default')
  .where('data.featured', '=', true)
  .where('data.published', '<=', '2026-01-01')
  .order('data.published', 'DESC')
  .limit(10)
  .all()

featured[0].data.title   // nested item - same shape as content.list()
featured[0].meta.key

const total = await content.query('default').count()

const post = await content.query('default').where('data.slug', '=', 'hello').first()

Operators

'=' | '<>' | '<' | '<=' | '>' | '>='
| 'LIKE' | 'NOT LIKE'
| 'IN' | 'NOT IN'
| 'BETWEEN' | 'NOT BETWEEN'
| 'IS NULL' | 'IS NOT NULL'

IS NULL / IS NOT NULL don't take a value. BETWEEN / NOT BETWEEN take a [low, high] tuple. IN / NOT IN take an array.

Group conditions

Use andWhere / orWhere to build parenthesised groups:

await content.query('default')
  .where('data.published', '<=', '2026-01-01')
  .andWhere(group => group
    .where('data.featured', '=', true)
    .orWhere(g => g.where('data.tags', 'LIKE', '%intro%')))
  .all()
// → WHERE "data.published" <= ? AND ("data.featured" = ? OR ("data.tags" LIKE ?))

Columns and flattening rules

Every source's table carries the base columns shared by all items: path and the identity fields under meta.:

'path'
'meta.key' | 'meta.source' | 'meta.stem' | 'meta.extension' | 'meta.kind' | 'meta.type' | 'meta.partial'

On top of those, each entry's frontmatter is flattened under a data. prefix. Top-level objects expand one level deep into dotted columns; anything nested deeper is JSON-encoded and stored as TEXT:

---
title: Hello
published: 2026-03-01
featured: true
seo:
  ogImage: /hello.png
tags: [intro, demo]
---

The data. prefix keeps frontmatter columns from ever colliding with the base columns (a frontmatter type becomes data.type, distinct from meta.type). Booleans become 0/1, numbers stay numbers, and arrays (or objects nested deeper than one level) become JSON strings, so query them with LIKE:

const introPosts = await content
  .query('default')
  .where('data.tags', 'LIKE', '%"intro"%')
  .all()

Generated types

When paired with comark-content/vite, frontmatter shapes are inferred and emitted to .comark/types.d.ts. The result: content.query('blog') autocompletes its columns against your actual frontmatter schema and returns typed items:

const post = await content.query('blog').where('data.title', 'LIKE', '%intro%').first()
//    ^? ContentListFile<BlogFrontmatter> | null - post?.data.title, post?.meta.key