Deploying SQLPage

Introduction

SQLPage is an innovative open-source tool that lets you build web applications using only SQL. No JavaScript, no HTML templates, no frontend frameworks - just write SQL queries and SQLPage renders them as beautiful, interactive web pages using a library of pre-built components.

Built with Rust for performance and reliability, SQLPage connects to your database and transforms query results into dynamic web applications. It’s perfect for building admin panels, dashboards, internal tools, and data exploration interfaces without the complexity of traditional web development.

Key highlights of SQLPage:

SQL-Only Development: Build complete web applications using only SQL
Pre-Built Components: Tables, charts, forms, cards, and more out of the box
Database Agnostic: Works with PostgreSQL, MySQL, SQLite, and MSSQL
Fast Performance: Written in Rust for speed and low resource usage
Interactive Forms: Create forms that update databases directly
Data Visualization: Charts and graphs from query results
Authentication: Built-in user authentication support
File Uploads: Handle file uploads through SQL
REST API: Expose SQL queries as API endpoints
Mobile Responsive: Components adapt to all screen sizes
Zero Build Step: Deploy and run immediately

This guide walks through deploying SQLPage on Klutch.sh using Docker, connecting to your database, and building your first SQL-powered web application.

Why Deploy SQLPage on Klutch.sh

Deploying SQLPage on Klutch.sh provides several advantages for SQL-based applications:

Simplified Deployment: Klutch.sh automatically builds your SQLPage configuration without complex setup.

Persistent Storage: Attach persistent volumes for SQLite databases and file uploads.

HTTPS by Default: Secure access to your applications with automatic SSL certificates.

GitHub Integration: Store SQL files in version control and deploy automatically on push.

Scalable Resources: Allocate CPU and memory based on query complexity and traffic.

Quick Iteration: Update SQL files and redeploy to see changes instantly.

Prerequisites

Before deploying SQLPage on Klutch.sh, ensure you have:

A Klutch.sh account
A GitHub account with a repository for your SQLPage application
Basic familiarity with Docker and SQL
A database (PostgreSQL, MySQL, SQLite, or MSSQL)
(Optional) A custom domain for your application

Deploying SQLPage on Klutch.sh

Create Your Repository

Create a new GitHub repository for your SQLPage application. Add a Dockerfile:

FROM lovasoa/sqlpage:latest

WORKDIR /app

COPY sqlpage/ /app/sqlpage/

ENV PORT=8080
ENV DATABASE_URL=sqlite:///app/data/database.db

EXPOSE 8080

HEALTHCHECK --interval=30s --timeout=10s --start-period=10s --retries=3 \
  CMD wget --no-verbose --tries=1 --spider http://localhost:8080/ || exit 1

Create your application structure:

your-repo/
├── Dockerfile
└── sqlpage/
    ├── sqlpage.json
    └── index.sql

Create sqlpage/sqlpage.json:

{
  "site_title": "My SQLPage App",
  "max_database_pool_connections": 10
}

Create a simple sqlpage/index.sql:

SELECT 'text' as component,
       'Welcome to SQLPage' as title,
       'Build web applications with SQL!' as contents;

Push to GitHub

Commit and push your files to your GitHub repository.

Create a New Project on Klutch.sh

Navigate to the Klutch.sh dashboard and create a new project.

Create a New App

Within your project, create a new app. Connect your GitHub account and select your repository.

Configure HTTP Traffic

In the deployment settings:

Select HTTP as the traffic type
Set the internal port to 8080

Set Environment Variables

Configure your database connection:

Variable	Value
`DATABASE_URL`	Your database connection string
`PORT`	`8080`

Database URL formats:

PostgreSQL: postgres://user:pass@host:5432/dbname
MySQL: mysql://user:pass@host:3306/dbname
SQLite: sqlite:///app/data/database.db

Attach Persistent Volumes

For SQLite or file uploads:

Mount Path	Recommended Size	Purpose
`/app/data`	10 GB	SQLite database and data
`/app/uploads`	10 GB	File uploads

Deploy Your Application

Click Deploy to start the build process.

Access Your Application

Once deployed, access your SQLPage application at the provided URL.

Building Applications

Page Structure

Each .sql file becomes a page:

-- index.sql becomes /
-- users.sql becomes /users
-- reports/sales.sql becomes /reports/sales

Component Basics

SQLPage uses components to render content:

SELECT 'component_name' as component,
       'property1_value' as property1,
       'property2_value' as property2;

Multiple Components

Chain components in one page:

-- Header
SELECT 'hero' as component,
       'Dashboard' as title;

-- Data table
SELECT 'table' as component;
SELECT name, email, status
FROM users
ORDER BY created_at DESC
LIMIT 10;

Common Components

Text and Headers

Display formatted text:

SELECT 'text' as component,
       'Welcome' as title,
       'This is the main content.' as contents,
       TRUE as center;

Tables

Display data in tables:

SELECT 'table' as component,
       TRUE as sort,
       TRUE as search;

SELECT id, name, email, created_at as "Created"
FROM users;

Charts

Visualize data:

SELECT 'chart' as component,
       'bar' as type,
       'Sales by Month' as title;

SELECT month as label, revenue as value
FROM monthly_sales;

Cards

Display card layouts:

SELECT 'card' as component;

SELECT title, description, '/post/' || id as link
FROM blog_posts
LIMIT 6;

Forms

Create interactive forms:

SELECT 'form' as component,
       'Create User' as title;

SELECT 'name' as name, 'text' as type, 'Full Name' as label;
SELECT 'email' as name, 'email' as type, 'Email' as label;
SELECT 'submit' as type, 'Create' as label;

Form Handling

Processing Form Data

Handle form submissions:

-- Check if form was submitted
INSERT INTO users (name, email)
SELECT $name, $email
WHERE $name IS NOT NULL;

-- Redirect on success
SELECT 'redirect' as component, '/users' as link
WHERE $name IS NOT NULL;

-- Show form if not submitted
SELECT 'form' as component WHERE $name IS NULL;
SELECT 'name' as name, 'text' as type WHERE $name IS NULL;
SELECT 'email' as name, 'email' as type WHERE $name IS NULL;

URL Parameters

Access URL parameters:

-- /user?id=5 -> $id = 5
SELECT 'text' as component,
       name as title
FROM users
WHERE id = $id;

Authentication

Implement authentication:

-- login.sql
SELECT 'authentication' as component,
       'Login' as title,
       'password_hash' as password_hash_column
FROM users
WHERE username = :username;

Protected Pages

Restrict access:

SELECT 'redirect' as component,
       '/login' as link
WHERE NOT sqlpage.authentication_required();

Dynamic Content

Conditional Display

Show content conditionally:

SELECT 'alert' as component,
       'warning' as color,
       'No data found' as title
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE date = CURRENT_DATE);

SELECT 'table' as component
WHERE EXISTS (SELECT 1 FROM orders WHERE date = CURRENT_DATE);

SELECT * FROM orders WHERE date = CURRENT_DATE;

Variables and Functions

Use SQLPage functions:

SELECT 'text' as component,
       sqlpage.cookie('user') as title;

File Handling

File Uploads

Handle file uploads:

SELECT 'form' as component;
SELECT 'file' as type, 'document' as name, 'Upload File' as label;

Serving Files

Serve uploaded files:

SELECT 'file' as component,
       filename as name,
       content as contents,
       'application/pdf' as content_type
FROM documents WHERE id = $id;

Best Practices

Performance

Index frequently queried columns
Use LIMIT for large result sets
Optimize complex queries
Use connection pooling

Security

Use parameterized queries (automatic with $variables)
Validate user input
Implement proper authentication
Restrict database permissions

Organization

Group related pages in directories
Use meaningful file names
Comment complex queries
Separate configuration from logic

Troubleshooting

Database Connection Errors

Verify DATABASE_URL format
Check network connectivity
Confirm database credentials
Review connection limits

Component Not Rendering

Check component name spelling
Verify required properties
Review query syntax
Check for SQL errors

Slow Page Load

Optimize database queries
Add appropriate indexes
Limit result set size
Check database performance

Additional Resources

Conclusion

Deploying SQLPage on Klutch.sh provides a unique approach to web application development. By writing only SQL, you can build dashboards, admin panels, and data applications without frontend complexity. Combined with Klutch.sh’s automatic HTTPS, Git-based deployments, and persistent storage, you get a streamlined workflow for creating data-driven web applications that’s accessible to anyone who knows SQL.