Spry for SQLPage

Build SQL-Backed Websites from Markdown

Scaffold complete SQLPage applications from Markdown.
Live reload during development, package into SQLite or PostgreSQL for production.

What is Spry for SQLPage?

Spry for SQLPage lets you build complete database-driven websites using only Markdown and SQL.
Write your SQL queries in fenced code blocks, and Spry automatically packages them into a SQLPage application.

Fast Scaffolding

Initialize a complete project structure with Spryfile.md in seconds

Live Reload

Use --watch mode to see changes instantly during development

Deploy Anywhere

Package into SQLite for single-file apps or PostgreSQL for production scale

Getting Started

Quick Command Reference

Install the unified spry binary via Homebrew.

# 0.Initialize a new Spry project (if starting from scratch)
spry sp init

# 1. Generate environment variables
spry rb task prepare-env

# 2. Generate development environment
spry rb task prepare-sqlpage-dev

# 3. Start SQLPage server
sqlpage

# 4. Deploy to production
spry rb task deploy
bash

Prerequisites

Before you begin, ensure you have the following installed:

  • Surveilr - Data ingestion and surveillance tool for SQLite-based data management
  • Spry - Markdown-to-executable workflow system with support for runbooks and SQLPage applications

Initialize a New Spry Project

If you're starting from scratch, use the Spry initialization script to generate the complete project structure: 

spry sp init
bash

This command will:

What gets created:

  • Spryfile.md - Main configuration file with sample tasks

Project Structure

.
├── Spryfile.md           # Main configuration and code file
├── sqlpage/
│   └── sqlpage.json      # SQLPage configuration
├── dev-src.auto/         # Generated files (development mode)
└── sqlpage.db            # SQLite database
text

Quick Start

Step 0: Initialize Project (New Projects Only)

If you're starting a new Spry project from scratch: 

spry sp init
bash

This creates the complete project structure. Skip to Step 1 if you already have a Spry project.

Step 1: Set Up Environment Variables

Generate the .envrc file using the Spry task: 

spry rb task prepare-env
bash

This will create a .envrc file with the necessary environment variables and add it to .gitignore.

If using direnv, allow the environment file: 

direnv allow
bash

Step 2: Understanding the Spryfile

The Spryfile.md is the heart of the Spry project (automatically created by the init script). It contains:

  • Frontmatter with SQLPage configuration
  • Markdown documentation
  • Executable code blocks (SQL, bash, etc.)

Example Spryfile.md structure: 

---
sqlpage-conf:
  allow_exec: true
  port: '${env.PORT}'
  database_url: '${env.SPRY_DB}'
  web_root: "./dev-src.auto"
---

# My Spry Application

## Home Page

```sql index.sql
select 'card' as component,
       'Welcome' as title;
select 'Hello from Spry!' as description;
```
markdown

Step 3: Generate Development Environment

Use the Spry task to generate the development environment: 

spry rb task prepare-sqlpage-dev
bash

This command:

  • Generates the dev-src.auto directory
  • Creates sqlpage/sqlpage.json configuration
  • Sets up SQLPage to work in development mode

Step 4: Start SQLPage Server

In a separate terminal window:

sqlpage
bash

SQLPage will:

  • Read configuration from sqlpage/sqlpage.json
  • Serve files from dev-src.auto/
  • Listen on the configured port (default: 9227)

Visit http://localhost:9227 in the browser to see the application.

Development Workflow

  1. Edit Spryfile.md - Add or modify SQL queries, bash scripts, or documentation
  2. Regenerate - Run spry rb task prepare-sqlpage-dev to regenerate files
  3. Refresh browser - SQLPage picks up changes immediately
  4. Iterate - Continue editing and testing

Watch Mode (Optional)

For automatic regeneration when you edit Spryfile.md: 

spry sp spc --fs dev-src.auto --destroy-first --conf sqlpage/sqlpage.json --watch
bash

This watches the Markdown files and regenerates the dev-src.auto directory on every change.

To automatically restart SQLPage after each rebuild: 

spry sp spc --fs dev-src.auto --destroy-first --conf sqlpage/sqlpage.json --watch --with-sqlpage
bash

Note: Restarting SQLPage is usually not necessary. You can run SQLPage in a separate terminal and it will pick up changes automatically.

Example: Adding a New Page

Add a new SQL code block to Spryfile.md: 

## About Page

```sql about.sql
select 'card' as component,
       'About Us' as title;
select 'This is a Spry-powered application' as description;
```
markdown

The file about.sql will be generated in dev-src.auto/ and accessible at http://localhost:9227/about.sql.

Production Deployment

When ready for production, switch from file-based mode to single-database mode.

Deploy to Production

Use the Spry deploy task to package everything into the database: 

spry rb task deploy
bash

This command:

  • Removes the dev-src.auto directory
  • Generates SQL package with all files
  • Inserts them into the sqlpage_files table in the SQLite database

Update SQLPage Configuration

Modify sqlpage/sqlpage.json to remove or comment out web_root: 

{
  "allow_exec": true,
  "port": "9227",
  "database_url": "sqlite://sqlpage.db?mode=rwc"
}
json

Start SQLPage

sqlpage
bash

SQLPage now serves files from the database instead of the filesystem.

Spry CLI Commands

Main Commands

spry --help              # Show all commands
spry --version           # Show version
bash

Initialization

# Initialize a new Spry project from scratch
spry sp init
bash

Task Commands

# Generate environment variables (.envrc)
spry rb task prepare-env

# Generate SQLPage development environment
spry rb task prepare-sqlpage-dev

# Deploy to production (package into database)
spry rb task deploy

# Clean generated files
spry rb task clean

# Execute a specific task
spry rb task <taskId>

# Execute all tasks in order
spry rb run
bash

SQLPage Content (spc) Commands

# Generate files to filesystem
spry sp spc --fs <directory> --conf <config>

# Package to database
spry sp spc --package --dialect sqlite

# Watch mode
spry sp spc --fs <directory> --watch

# List generated files
spry sp spc ls

# Show file contents
spry sp spc cat <filename>
bash

Writing SQL in Spryfile.md

Basic SQL Block

```sql filename.sql
SELECT 'text' as component;
SELECT 'Hello World' as contents;
```
markdown

SQL with Metadata

Add route information for navigation:

```sql index.sql { route: { caption: "Home" } }
SELECT 'card' as component;
```
markdown

Common Tasks

Clean Generated Files

```bash clean --descr "Clean up the project directory's generated artifacts"
rm -rf dev-src.auto
```
markdown

Or use the clean task if defined in the Spryfile.md: 

spry rb task clean
bash

Regenerate Development Environment

spry rb task prepare-sqlpage-dev
bash

Troubleshooting

Port Already in Use

Change the port in .envrc or sqlpage/sqlpage.json: 

export PORT=9227
bash

SQLPage Not Finding Files

Ensure web_root in sqlpage/sqlpage.json points to the correct directory: 

{
  "web_root": "./dev-src.auto"
}
json

Changes Not Reflecting

  1. Check that watch mode is running
  2. Verify SQLPage is reading from the correct directory
  3. Try hard refresh in browser (Ctrl+Shift+R)

Next Steps

Resources

Perfect For

Data Dashboards

Build interactive dashboards that query your database directly

Internal Tools

Create admin panels and internal applications with SQL

Literate SQL Pipelines

Document your SQL logic alongside execution in the same file

Rapid Prototyping

Go from idea to working application in minutes