SQLPage Integration

Learn how Spry generates and manages SQLPage applications.

What is This?

This document explains how Spry integrates with SQLPage to let you build database-driven web applications using just Markdown files. Instead of writing traditional web applications with HTML/CSS/JavaScript, you write SQL queries in Markdown, and SQLPage renders them as beautiful web pages.

Core Concept

Traditional Web Development:

Database → Backend Code → HTML/CSS/JS → Web Page

SQLPage with Spry:

Markdown File with SQL → SQLPage → Web Page

---

Key Components

1. SQLPage

A web server that executes SQL queries and renders the results as web pages using components (tables, cards, forms, etc.).

2. Spry

A tool that processes Markdown files containing SQL and manages SQLPage applications. Think of it as a build system for SQLPage apps.

3. Spryfile.md

Your main Markdown file where you write SQL queries, define routes (pages), and configure your application.

---

Getting Started

Installation

# Create a new directory for your app
mkdir my-app && cd my-app

# Initialize a Spry project
deno run --node-modules-dir=auto -A \
  https://raw.githubusercontent.com/programmablemd/spry/main/lib/sqlpage/cli.ts init

What you get:

• spry.ts - Command-line tool to run Spry
• Spryfile.md - Your main application file
• sqlpage/sqlpage.json - SQLPage configuration

Development Mode

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

Flags explained:

• --fs dev-src.auto - Save files to filesystem (for development)
• --destroy-first - Clear old files before building
• --watch - Auto-rebuild when you save changes
• --with-sqlpage - Auto-start the SQLPage server

Visit http://localhost:8080 to see your app!

---

Writing Your First Page

Basic Structure

---
sqlpage-conf:
  database_url: sqlite://app.db?mode=rwc
  web_root: "./dev-src.auto"
  port: 8080
---

# My Application

```sql index.sql { route: { caption: "Home" } }
SELECT 'hero' AS component,
       'Welcome!' AS title,
       'This is my first SQLPage app' AS description;
```

Breaking it down:

1. Frontmatter (--- section): Configuration for SQLPage
2. Code fence (```sql): Contains your SQL
3. Cell identity (index.sql): Becomes the URL path
4. Route attributes ({ route: {...} }): Metadata for the page

---

Understanding SQL Components

SQLPage uses a component-based system. Each SELECT statement specifies a component and its properties.

Example: Hero Component

SELECT 'hero' AS component,
       'My Title' AS title,
       'Description text' AS description;

Example: Table Component

SELECT 'table' AS component, TRUE AS sort;
SELECT id, name, email FROM users;

Example: Card Component

SELECT 'card' AS component, 3 AS columns;
SELECT
  'Total Users' AS title,
  (SELECT COUNT(*) FROM users) AS description,
  'users' AS icon;

---

Creating Multiple Pages

Each SQL code block with a unique filename becomes a separate page:

```sql index.sql { route: { caption: "Home" } }
SELECT 'hero' AS component, 'Home Page' AS title;
```

```sql about.sql { route: { caption: "About" } }
SELECT 'text' AS component,
       'About Us' AS title,
       'We are awesome!' AS contents;
```

```sql users.sql { route: { caption: "Users" } }
SELECT 'table' AS component;
SELECT * FROM users;
```

URLs:

• http://localhost:8080/index.sql → Home
• http://localhost:8080/about.sql → About
• http://localhost:8080/users.sql → Users

---

Using Partials (Shared Layouts)

Partials are reusable SQL snippets that get injected into multiple pages.

Global Navigation

```sql PARTIAL layout.sql --inject **/*
SELECT 'shell' AS component,
       'My App' AS title,
       '/index.sql' AS link,
       '{"link":"/users.sql","title":"Users"}' AS menu_item,
       '{"link":"/about.sql","title":"About"}' AS menu_item;
```

Explanation:

• PARTIAL - Marks this as a reusable snippet
• --inject **/* - Inject into ALL pages (**/* is a glob pattern)

Conditional Partials

Only inject into specific pages:

```sql PARTIAL admin-nav.sql --inject admin/**/*
SELECT 'alert' AS component,
       'Admin Area' AS title,
       'warning' AS color;
```

This only injects into pages under the admin/ directory.

---

Environment Variables

Keep sensitive data out of your code:

In frontmatter:

---
sqlpage-conf:
  database_url: ${env.SPRY_DB}
  port: ${env.PORT}
---

Set variables:

# Create .envrc file
export SPRY_DB="sqlite://app.db?mode=rwc"
export PORT=8080

# Use direnv to auto-load
direnv allow

---

Advanced Features

1. Interpolation (Dynamic SQL)

Add -I flag to enable JavaScript-style interpolation:

```sql users.sql -I { route: { caption: "Users" } }
-- Access route metadata
SET page_title = '${cell.attrs?.route?.caption || "Default"}';

SELECT 'text' AS component, $page_title AS title;
SELECT * FROM users;
```

2. Pagination Helper

```sql products.sql -I
${paginate("products")}

SELECT 'table' AS component;
SELECT * FROM products
${pagination.limit};

${pagination.navigation}
```

3. Markdown Links in Tables

```sql items.sql -I
SELECT 'table' AS component, 'Name' AS markdown;
SELECT
  ${md.link("name", [`'details.sql?id='`, "id"])} AS Name,
  description
FROM items;
```

This creates clickable links in the table.

---

Database Setup Tasks

Run bash commands before building:

```bash init-db --descr "Initialize database"
sqlite3 app.db < schema.sql
```

```bash seed-data --descr "Add sample data"
sqlite3 app.db < seed.sql
```

---

Deployment

Development Mode Example

Files written to filesystem, hot reload enabled:

./spry.ts spc --fs dev-src.auto --watch --with-sqlpage

Production Mode Example

Everything packaged into database:

./spry.ts spc --package --conf sqlpage/sqlpage.json | sqlite3 app.db

What happens:

• All SQL files → sqlpage_files table
• Single database contains everything
• No external files needed

---

Complete Example: Task Manager

---
sqlpage-conf:
  database_url: sqlite://tasks.db?mode=rwc
  web_root: "./dev-src.auto"
  port: 8080
---

# Task Manager App

## Setup

```bash init --descr "Create database"
sqlite3 tasks.db <<SQL
CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT DEFAULT 'open',
  due_date DATE
);
INSERT INTO tasks (title, status, due_date) VALUES
  ('Buy groceries', 'open', '2024-01-15'),
  ('Finish report', 'done', '2024-01-10');
SQL
```

## Layout

```sql PARTIAL shell.sql --inject **/*
SELECT 'shell' AS component,
       'Task Manager' AS title,
       '/index.sql' AS link,
       '{"link":"/tasks.sql","title":"All Tasks"}' AS menu_item;
```

## Home Page

```sql index.sql { route: { caption: "Home" } }
SELECT 'card' AS component, 2 AS columns;

SELECT 'Open Tasks' AS title,
       (SELECT COUNT(*) FROM tasks WHERE status = 'open') AS description,
       'orange' AS color;

SELECT 'Completed' AS title,
       (SELECT COUNT(*) FROM tasks WHERE status = 'done') AS description,
       'green' AS color;
```

## Tasks Page

```sql tasks.sql { route: { caption: "Tasks" } }
SELECT 'table' AS component, TRUE AS sort;
SELECT
  id AS "ID",
  title AS "Task",
  status AS "Status",
  due_date AS "Due Date"
FROM tasks
ORDER BY due_date;
```

## Development

```bash dev --descr "Start development server"
./spry.ts spc --fs dev-src.auto --destroy-first \
  --conf sqlpage/sqlpage.json --watch --with-sqlpage
```

---

Common Patterns

Navigation Menu from Routes

-- Read route metadata
SET routes = sqlpage.read_file_as_text('spry.d/auto/route/forest.auto.json');

-- Generate menu
SELECT 'list' AS component;
SELECT
  json_extract(value, '$.payloads[0].caption') AS title,
  json_extract(value, '$.path') AS link
FROM json_tree($routes)
WHERE json_extract(value, '$.path') IS NOT NULL;

Form Submission

-- Handle POST
INSERT INTO tasks (title, status)
SELECT :title, :status
WHERE :title IS NOT NULL;

-- Show form
SELECT 'form' AS component, 'tasks.sql' AS action;
SELECT 'title' AS name, 'text' AS type;
SELECT 'status' AS name, 'select' AS type;

Dynamic Filtering

SELECT 'table' AS component;
SELECT * FROM tasks
WHERE (:status IS NULL OR status = :status)
ORDER BY due_date;

---

Troubleshooting

Database not found

# Use mode=rwc to create if missing
database_url: sqlite://./app.db?mode=rwc

Route not working

1. Check filename has .sql extension
2. Verify JSON5 attributes are valid
3. Run with --destroy-first to clear cache

Partial not injecting

1. Use uppercase PARTIAL
2. Check glob pattern (e.g., **/* for all files)
3. Define partials before pages that use them

Interpolation not working

1. Add -I flag to code fence
2. Use ${...} syntax (JavaScript)
3. Escape literal $ as \$

---

Key Concepts Summary

Concept	Description
Spryfile.md	Main file with SQL and configuration
Cell	Code block with SQL query
Route	URL path derived from cell identity
Partial	Reusable SQL snippet
Component	UI element (table, card, hero, etc.)
Interpolation	Dynamic SQL with ${...}
Frontmatter	YAML configuration at file start

---

Next Steps

1. Start simple: Create one page with a table
2. Add navigation: Use partials for shared layout
3. Add interactivity: Forms and query parameters
4. Deploy: Package to production database

The beauty of this approach is that you’re writing SQL in Markdown, and Spry handles the rest—no JavaScript bundlers, no complex frameworks, just data and presentation!