---
title: "SQL query plugin"
description: "Typed fluent queries over your content, without the need to write SQL."
canonical_url: "https://content.comark.dev/plugins/built-in/sql-query"
---
# SQL query plugin

> Typed fluent queries over your content, without the need to write SQL.

`sqlQuery` indexes one Content instance in SQLite and adds `content.query()`: a chainable builder for filtering, sorting, grouping, and paginating documents without writing SQL. Columns mirror the item shape (`path`, identity fields under `meta.`, and frontmatter under `data.`), and results use the same `{ path, data, meta }` shape as [`content.list()`](https://content.comark.dev/reference/content/list).

## Usage

```ts [content.ts]
import { comarkContent } from 'comark-content'
import fs from 'comark-content/sources/fs'
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()
  .where('data.featured', '=', true)
  .order('data.published', 'DESC')
  .limit(10)
  .all()
```

::warning{to="https://content.comark.dev/reference/database"}
`sqlQuery` requires a database: pass `sqlite()` on the server or `sqliteWasm()` in the browser.
::

## API

The plugin exposes one factory and one instance method:

### `sqlQuery(options)`

Returns a [`ContentPlugin`](https://content.comark.dev/reference/types/plugins#contentplugin) that flattens the instance's documents into a typed SQLite table and adds a `content.query()` method to the Content instance. Throws if `options.database` is missing.

**Parameters:**

- `options`: see [Options](#options).

**Returns:** [`ContentPlugin`](https://content.comark.dev/reference/types/plugins#contentplugin)

### `content.query()`

Added by the plugin. Returns a chainable query builder for the current instance. Conditions address flattened columns (`path`, `meta.*`, and `data.*`); the terminal `all()`, `first()`, and `count()` methods execute the query and return nested items.

| Method                            | Returns                                                                                                           | Description                                           |
| --------------------------------- | ----------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- |
| `.where(field, operator, value?)` | `this`                                                                                                            | Chain a filter. See [Operators](#querying-operators). |
| `.andWhere(group => ...)`         | `this`                                                                                                            | Group conditions with `AND`.                          |
| `.orWhere(group => ...)`          | `this`                                                                                                            | Group conditions with `OR`.                           |
| `.order(field, 'ASC' \| 'DESC')`  | `this`                                                                                                            | Add an order-by clause.                               |
| `.limit(n)`                       | `this`                                                                                                            | Cap the result count.                                 |
| `.skip(n)`                        | `this`                                                                                                            | Skip the first `n` rows.                              |
| `.select(...fields)`              | `SourceQueryBuilder<Row, Partial<Item>>`                                                                          | Whitelist returned columns.                           |
| `.path(path)`                     | `this`                                                                                                            | Shorthand for `.where('path', '=', path)`.            |
| `.all()`                          | `Promise<`[`ContentListFile`](https://content.comark.dev/reference/types/content#contentlistfiletdata)`[]>`       | All matching items (`{ path, data, meta }`).          |
| `.first()`                        | `Promise<`[`ContentListFile`](https://content.comark.dev/reference/types/content#contentlistfiletdata)` \| null>` | First matching item, or `null`.                       |
| `.count(field?, distinct?)`       | `Promise<number>`                                                                                                 | Number of matching rows.                              |

## Options

`sqlQuery(options)` accepts a single required option:

| Option                          | Type                                                                                          | Default  | Description                                       |
| ------------------------------- | --------------------------------------------------------------------------------------------- | -------- | ------------------------------------------------- |
| [`database`](#options-database) | [`RelationalDatabase`](https://content.comark.dev/reference/types/content#relationaldatabase) | required | The relational database backing the query tables. |

### `database`

The [`RelationalDatabase`](https://content.comark.dev/reference/types/content#relationaldatabase) that holds the query table. Pass [`sqlite()`](https://content.comark.dev/reference/database#sqlite-for-node) on the server or [`sqliteWasm()`](https://content.comark.dev/reference/database#sqlite-wasm) in the browser. `sqlQuery` throws at construction time if this is missing.

## Querying

### Query builder

```ts [query.ts]
const featured = await content
  .query()
  .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().count()

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

### Operators

```ts
'=' | '<>' | '<' | '<=' | '>' | '>='
| '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:

```ts [group.ts]
await content.query()
  .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

Each instance's table carries the base columns shared by all items: `path` and the identity fields under `meta.`:

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

`meta.hash` isn't a column: it's an identity for cache bookkeeping, not a field to filter on, so it's absent from query rows. Read it from `get()` or `stat()` instead.

The table is named `__search_<instance>` on the default instance and `__search_<key>__<instance>` on an instance pinned with [`withRef()`](https://content.comark.dev/reference/content/with-ref), where `key` is the instance's opaque `content.key`. Pinned instances sharing one database therefore keep separate tables, and [`clean()`](https://content.comark.dev/reference/content/clean) drops only that instance's.

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`:

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

| Column             | Type      | Value                |
| ------------------ | --------- | -------------------- |
| `data.title`       | `TEXT`    | `'Hello'`            |
| `data.published`   | `TEXT`    | `'2026-03-01'`       |
| `data.featured`    | `INTEGER` | `1`                  |
| `data.seo.ogImage` | `TEXT`    | `'/hello.png'`       |
| `data.tags`        | `TEXT`    | `'["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`:

```ts [tag-filter.ts]
const introPosts = await content
  .query()
  .where('data.tags', 'LIKE', '%"intro"%')
  .all()
```

### Generated types

With [generated types](https://content.comark.dev/guide/typescript) — from `comark-content prepare` or the Vite plugin — `blog.query()` autocompletes its columns against your actual frontmatter schema and returns typed items:

```ts [types-included.ts]
const post = await blog.query().where('data.title', 'LIKE', '%intro%').first()
//    ^? ContentListFile<BlogData> | null - post?.data.title, post?.meta.key
```

::tip{to="https://content.comark.dev/examples/vite/vue"}
Hook the Vite plugin to keep types in sync with your content as you edit.
::


## Sitemap

See the full [sitemap](https://content.comark.dev/sitemap.md) for all pages.