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 spry.ts and 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

# 0. Initialize a new Spry project (if starting from scratch)
curl -fsSL https://sprymd.org/init.sh | sh

# 1. Generate environment variables
./spry.ts task prepare-env

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

# 3. Start SQLPage server
sqlpage

# 4. Deploy to production
./spry.ts task deploy

bash

Prerequisites

Before you begin, ensure you have the following installed:

Deno - JavaScript/TypeScript runtime (required for running spry.ts)
SQLPage - Web application server that renders SQL queries as web pages
SQLite - Database backend (usually comes with SQLPage)
direnv (recommended) - For managing environment variables

Initialize a New Spry Project

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

curl -fsSL https://sprymd.org/init.sh | sh

bash

This command will:

Download and set up spry.ts CLI
Create a sample Spryfile.md with examples and task definitions
Generate the necessary directory structure (sqlpage/ directory)
Set up initial SQLPage configuration template

What gets created:

spry.ts - The Spry CLI executable
Spryfile.md - Main configuration file with sample tasks
sqlpage/ - Directory for SQLPage configuration

Project Structure

.
├── Spryfile.md           # Main configuration and code file
├── spry.ts               # Spry CLI entry point
├── 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:

curl -fsSL https://sprymd.org/init.sh | sh

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.ts 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.ts 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

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

Watch Mode (Optional)

For automatic regeneration when you edit Spryfile.md:

./spry.ts 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.ts 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.ts 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.ts --help              # Show all commands
./spry.ts --version           # Show version
./spry.ts doctor              # Check dependencies

bash

Initialization

# Initialize a new Spry project from scratch
curl -fsSL https://sprymd.org/init.sh | sh

bash

Task Commands

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

# Generate SQLPage development environment
./spry.ts task prepare-sqlpage-dev

# Deploy to production (package into database)
./spry.ts task deploy

# Clean generated files
./spry.ts task clean

# Execute a specific task
./spry.ts task <taskId>

# Execute all tasks in order
./spry.ts runbook

bash

SQLPage Content (spc) Commands

# Generate files to filesystem
./spry.ts spc --fs <directory> --conf <config>

# Package to database
./spry.ts spc --package --dialect sqlite

# Watch mode
./spry.ts spc --fs <directory> --watch

# List generated files
./spry.ts spc ls

# Show file contents
./spry.ts 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.ts task clean

bash

Regenerate Development Environment

./spry.ts task prepare-sqlpage-dev

bash

Check System Dependencies

./spry.ts doctor

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

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

Next Steps

Explore SQLPage components: https://sql-page.com/documentation.sql
Learn more about Spry: https://sprymd.org
Add more SQL pages to the Spryfile.md
Experiment with bash scripts and task dependencies
Deploy the application to production

Resources

Spry Documentation: https://sprymd.org
SQLPage Documentation: https://sql-page.com/
Spry GitHub: https://github.com/programmablemd/spry

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