docsQL
Getting an overview of your Jamstack Markdown files.
Demo
Play with: https://peterbe.github.io/docsql/
You're supposed to run docsQL with your own projects Markdown files. This demo uses a subset of the content behind GitHub Docs.
How it works
You give the CLI program one or more directories that contain Markdown files. Most of the time it's just one directory; where your jamstack website files are.
Each file is opened and the front-matter becomes the key-value pairs that you can later query. I.e. if you have a front-matter key called title you'll be able to query SELECT title FROM ? WHERE title ILIKE "%peterbe%". The content is not included in the database. That would make the searchable database too big.
Additionally, plugins are executed for each file. There are built-in plugins and there are plugins you write and point to yourself. One of the built-in plugins is called commonheadings.mjs and it counts the number of ## and ### rows there are in the content so you can query SELECT h2s, h3s, h2s+h3s AS combined FROM ? ORDER BY 3 DESC.
To specify your own plugins for your particular project, see the section on "Plugins".
Getting started
npx docsql /path/to/my/project/with/lots/of/markdown/files
Getting start (after git clone)
export CONTENT_SOURCES=/path/to/my/project/with/lots/of/markdown/files
npm run run
Getting Started (for local development)
echo CONTENT_SOURCES=/path/to/my/project/with/lots/of/markdown/files >> .env
npm run dev
Plugins
The built-in plugins are can be found in the source code (TODO: add link). These are hopefully generic enough and useful enough for most people.
To write your own plugin, you create a .mjs file. Your .mjs files just need to export a default function that returns an object. Best demonstrated with an example:
- Create a folder called
my-docsql-plugins. - Create the file
my-docsql-plugins/chocolate-icecream-mentions.mjs - Enter something like this:
const regex = /💩/g;
export default function countCocoIceMentions({ data, content }) {
const inTitle = (data.title.match(regex) || []).length;
const inBody = (content.match(regex) || []).length;
return {
chocolateIcecreamMentions: inTitle + inBody,
};
}
The name of the function isn't important. You could have used export default function whatever(. What is important is that you get a context object that contains the keys data and content. And it's important you return an object with keys and values that make sense to search on. You can even return a namespace which you can search on as if it was JSON.
Now start the CLI with --plugins my-docsql-plugins and your new plugin will be included. Once the server starts, you can click "Open help" in the web interface and expect to see it mentioned there. With this, you can now run:
SELECT _file, chocolateIcecreamMentions FROM ? WHERE chocolateIcecreamMentions > 0
Instead of passing --plugins my-plugins --plugins /my/other/custom-plugins you can equally set the environment variable:
# Example of setting plugins directories
DOCSQL_PLUGINS="myplugins, /my/other/custom-plugins"
Important custom plugin key ending in _url
Here's an example plugin that speaks for itself:
// In /path/to/my/custom/plugins
export default function getURL({ _file }) {
const pathname = _file.replace(/\.md$/, '')
return {
_url: `https://example.com/${pathname}`,
local_url: `http://localhost:4000/${pathname}`,
}
}
Because the keys end with _url these are treated as external hyperlinks in the UI when queried. For example:
SELECT _url, local_url FROM ? ORDER BY RANDOM() LIMIT 10
Suppose that your URLs depend on something from the front-matter of each document, here's an example:
// In /path/to/my/custom/plugins
export default function getURL({ data: {slug} }) {
return {
_url: `https://example.com/en/${slug}`,
}
}
Share plugins with your team
At the moment, the best way is that one of you writes some plugins that suites your content. Once that works well, you can either zip up that directory and share with your team. Or, you can simply create a git repo and put them in there.
Caveats on plugins
- They don't self-document. Yet. It would be nice if you could include a string of text from within your plugin code that shows up in the "Help" section.
- The order matters for overriding. There's a built-in plugin called
wordcount.mjswhich is really basic. If you don't like it, write your own plugin that returns a key calledwordCountand it will override the built-in computation. - Debugging bad plugins is a bit rough but an error thrown is stopping the CLI and the stacktrace should be sufficiently clear.
Open found files in your editor
If you have an environment variable called EDITOR set, and you make a query that includes the key _file it will make that a clickable link, which when running on localhost will open that file on your computer.
A lot of systems have a default $EDITOR which might be something terminal based, like nano. If you, for example, what your files to open in VS Code you can set:
echo EDITOR=code >> .env
Screenshots (as of Mar 2022)
Simple query 
Saved queries 
Open help 
Example queries help you learn 
Pre- pretty format 
Post- pretty format 
Less trivial query 
URLs become clickable links 
Dark mode 
Export by downloading 
Downloaded JSON file 
Downloaded CSV file 
Click to open in your local editor (only when running on localhost) 
Automatically opened in VS Code (only when running on localhost) 
Write your own plugins (to generate columns) 
Icon
Icon by Yannick Lung
![[Improvement] Make it quicker/easier to export results](https://avatars.githubusercontent.com/u/12902836?v=4)
2 Jan 6, 2022
7 Dec 13, 2022
6 Nov 5, 2022
393 Jan 6, 2023
5 Dec 15, 2022
213 Oct 11, 2022
97 Dec 21, 2022
21 Jun 20, 2022
18 Nov 2, 2022
32 Dec 25, 2022
91 Jan 2, 2023
25 Dec 20, 2022
13 Jan 2, 2023
66 Dec 20, 2022
24 Nov 3, 2022
2 Apr 22, 2022
20 Oct 30, 2022
24 Nov 3, 2022