File Uploads

SQLPage makes it easy to accept file uploads from users and store them either on disk or in your database.

Basic File Upload Form

Create a form with a file input field:

-- upload_form.sql
SELECT 'form' AS component,
    'Upload a File' AS title,
    'upload.sql' AS action;

SELECT 'text' AS type, 'Title' AS name, TRUE AS required;
SELECT 'textarea' AS type, 'Description' AS name;
SELECT 'file' AS type, 'Document' AS name, TRUE AS required;

Validating Uploaded Files

Always validate file uploads before processing:

-- upload.sql
-- Check if a file was actually uploaded
SELECT 'redirect' AS component, 'upload_form.sql' AS link
WHERE sqlpage.uploaded_file_path('Document') IS NULL;

-- Validate file size (automatically enforced by max_uploaded_file_size config)
-- Validate file type
SELECT 'redirect' AS component, 'upload_form.sql?error=invalid_type' AS link
WHERE sqlpage.uploaded_file_mime_type('Document') NOT LIKE 'application/pdf';

Storage Options

Option 1: Store Files on Disk

Use sqlpage.persist_uploaded_file() to save files to your web root:

-- Save file and insert metadata into database
INSERT INTO documents (title, description, file_path)
VALUES (
    :Title,
    :Description,
    sqlpage.persist_uploaded_file('Document', 'uploads', 'pdf,doc,docx')
)
RETURNING 'redirect' AS component,
          '/?uploaded=' || id AS link;

Parameters:

'Document' - Form field name
'uploads' - Target directory (relative to web root)
'pdf,doc,docx' - Allowed file extensions

Returns: The saved file path (e.g., uploads/abc123.pdf)

Option 2: Store Files in Database

For small files, store them directly as base64-encoded data URLs:

INSERT INTO documents (title, file_data)
VALUES (
    :Title,
    sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('Document'))
);

This is useful for:

Small images or icons
Files that need to be embedded in pages
Simplified backups (entire database contains all data)

Caution: Database storage increases size by ~33% due to base64 encoding and can impact query performance for large files.

Complete Image Gallery Example

Here’s a real-world example with authentication:

-- upload_form.sql
-- Require authentication
SELECT 'redirect' AS component, '/login.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM sessions
    WHERE
        sqlpage.cookie('session_token') = id AND
        created_at > datetime('now', '-1 day')
);

SELECT 'form' AS component,
    'Upload a new image' AS title,
    'upload.sql' AS action;

SELECT 'text' AS type, 'Title' AS name, TRUE AS required;
SELECT 'text' AS type, 'Description' AS name;
SELECT 'file' AS type, 'Image' AS name,
    'image/*' AS accept;  -- Accept any image type

-- upload.sql
-- Verify user is authenticated
SELECT 'redirect' AS component, '/login.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM sessions
    WHERE
        sqlpage.cookie('session_token') = id AND
        created_at > datetime('now', '-1 day')
);

-- Validate file type
SELECT 'redirect' AS component, '/upload_form.sql' AS link
WHERE sqlpage.uploaded_file_mime_type('Image') NOT LIKE 'image/%';

-- Save image to disk and create database record
INSERT OR IGNORE INTO images (title, description, image_url)
VALUES (
    :Title,
    :Description,
    sqlpage.persist_uploaded_file('Image', 'images', 'jpg,jpeg,png,gif')
)
RETURNING 'redirect' AS component,
          format('/?created_id=%d', id) AS link;

-- Show error if insert failed
SELECT 'alert' AS component,
    'red' AS color,
    'alert-triangle' AS icon,
    'Failed to upload image' AS title,
    'Please try again with a smaller picture. Maximum allowed file size is 500KB.' AS description;

File Upload Functions

sqlpage.uploaded_file_path(field_name)

Returns the temporary path where the uploaded file is stored.

SET temp_path = sqlpage.uploaded_file_path('MyFile');

Returns NULL if no file was uploaded for that field.

sqlpage.uploaded_file_mime_type(field_name)

Returns the MIME type of the uploaded file:

-- Allow only images
WHERE sqlpage.uploaded_file_mime_type('Photo') LIKE 'image/%'

-- Allow only PDFs
WHERE sqlpage.uploaded_file_mime_type('Document') = 'application/pdf'

-- Allow multiple types
WHERE sqlpage.uploaded_file_mime_type('File') IN (
    'application/pdf',
    'application/msword',
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
)

sqlpage.persist_uploaded_file(field_name, directory, allowed_extensions)

Saves the uploaded file to the specified directory and returns the file path.

SET file_path = sqlpage.persist_uploaded_file('Document', 'uploads', 'pdf,docx,txt');

Generates a unique filename to prevent collisions
Creates the target directory if it doesn’t exist
Returns NULL if the file extension is not allowed
The directory path is relative to your web root

sqlpage.read_file_as_data_url(file_path)

Reads a file and returns it as a base64-encoded data URL:

SET data_url = sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('Image'));
-- Returns: data:image/png;base64,iVBORw0KGgoAAAANS...

Use this to store files in the database or embed them in HTML.

Configuration

Maximum Upload Size

Set the maximum file size in your sqlpage/sqlpage.json:

{
  "max_uploaded_file_size": 10485760
}

Default: 5,242,880 bytes (5 MB) If a user tries to upload a file larger than this limit, the request will be rejected before processing.

Security Best Practices

1. Always Validate File Types

Never trust file extensions alone - always check MIME types:

-- Bad: Only checks extension in persist_uploaded_file
SET path = sqlpage.persist_uploaded_file('File', 'uploads', 'jpg,png');

-- Good: Validates MIME type first
SELECT 'redirect' AS component, 'error.sql' AS link
WHERE sqlpage.uploaded_file_mime_type('File') NOT LIKE 'image/%';

SET path = sqlpage.persist_uploaded_file('File', 'uploads', 'jpg,png,gif');

2. Require Authentication

Always verify users are authenticated before accepting uploads:

SELECT 'redirect' AS component, '/login.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM sessions WHERE id = sqlpage.cookie('session')
);

3. Sanitize Filenames

The persist_uploaded_file function automatically generates safe filenames, but if you need custom names:

-- Don't use user input directly as filenames
-- persist_uploaded_file handles this for you

4. Store Uploads Outside Web Root (Optional)

For maximum security, store uploaded files outside your web root and serve them through a controlled SQL endpoint:

-- serve_file.sql
SELECT 'redirect' AS component, '/' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM documents WHERE id = $id AND user_id = current_user_id()
);

SELECT 'http_header' AS component,
    'Content-Type' AS name,
    mime_type AS value
FROM documents WHERE id = $id;

SELECT file_data AS contents
FROM documents WHERE id = $id;

5. Set Appropriate Limits

Consider your use case when setting file size limits:

Profile pictures: 1-2 MB
Documents: 10 MB
Videos: 100+ MB (consider using external storage)

6. Scan for Malware

For production systems handling user uploads, consider integrating malware scanning:

-- After upload, scan file before making it public
-- (Implementation depends on your scanning solution)

Displaying Uploaded Files

Images

Display uploaded images using the card or table component:

SELECT 'card' AS component,
    3 AS columns;

SELECT
    title,
    description,
    image_url AS top_image,
    'View' AS link,
    'view.sql?id=' || id AS link
FROM images
ORDER BY created_at DESC;

Documents

Create download links:

SELECT 'list' AS component;

SELECT
    title AS title,
    description AS description,
    file_path AS link,
    'download' AS icon
FROM documents
ORDER BY created_at DESC;

Advanced Techniques

Multiple File Uploads

Allow users to upload multiple files at once:

SELECT 'form' AS component,
    'Upload Photos' AS title,
    'upload_multiple.sql' AS action;

SELECT 'file' AS type,
    'Photos' AS name,
    'image/*' AS accept,
    TRUE AS multiple;  -- Enable multiple file selection

Progress Indication

For large files, the browser shows a native upload progress indicator. SQLPage processes uploads efficiently in streaming mode.

Image Resizing

SQLPage doesn’t include built-in image processing. For thumbnails, either:

Generate them client-side before upload using JavaScript
Use database extensions (e.g., SQLite with image processing extensions)
Process files with external tools using sqlpage.exec() (requires allow_exec = true)

Common Patterns

File Upload with Preview

-- After upload, show the uploaded file
SELECT 'card' AS component;

SELECT
    'Upload Successful!' AS title,
    'Your file has been uploaded.' AS description,
    file_path AS top_image
FROM documents
WHERE id = $uploaded_id;

Replace Existing File

-- Update existing record with new file
UPDATE documents
SET
    file_path = sqlpage.persist_uploaded_file('File', 'uploads', 'pdf,docx'),
    updated_at = CURRENT_TIMESTAMP
WHERE id = :document_id
RETURNING 'redirect' AS component,
          '/document.sql?id=' || id AS link;

Examples

See complete working examples:

Image gallery: examples/image gallery with user uploads/
Rich text editor with image uploads: examples/rich-text-editor/

Get Started

Core Concepts

Configuration

Guides

File Uploads

File Uploads

Basic File Upload Form

Validating Uploaded Files

Storage Options

Option 1: Store Files on Disk

Option 2: Store Files in Database

Complete Image Gallery Example

File Upload Functions

sqlpage.uploaded_file_path(field_name)

sqlpage.uploaded_file_mime_type(field_name)

sqlpage.persist_uploaded_file(field_name, directory, allowed_extensions)

sqlpage.read_file_as_data_url(file_path)

Configuration

Maximum Upload Size

Security Best Practices

1. Always Validate File Types

2. Require Authentication

3. Sanitize Filenames

4. Store Uploads Outside Web Root (Optional)

5. Set Appropriate Limits

6. Scan for Malware

Displaying Uploaded Files

Images

Documents

Advanced Techniques

Multiple File Uploads

Progress Indication

Image Resizing

Common Patterns

File Upload with Preview

Replace Existing File

Examples

Build docs developers (and LLMs) love

Get Started

Core Concepts

Configuration

Guides

​File Uploads

​Basic File Upload Form

​Validating Uploaded Files

​Storage Options

​Option 1: Store Files on Disk

​Option 2: Store Files in Database

​Complete Image Gallery Example

​File Upload Functions

​sqlpage.uploaded_file_path(field_name)

​sqlpage.uploaded_file_mime_type(field_name)

​sqlpage.persist_uploaded_file(field_name, directory, allowed_extensions)

​sqlpage.read_file_as_data_url(file_path)

​Configuration

​Maximum Upload Size

​Security Best Practices

​1. Always Validate File Types

​2. Require Authentication

​3. Sanitize Filenames

​4. Store Uploads Outside Web Root (Optional)

​5. Set Appropriate Limits

​6. Scan for Malware

​Displaying Uploaded Files

​Images

​Documents

​Advanced Techniques

​Multiple File Uploads

​Progress Indication

​Image Resizing

​Common Patterns

​File Upload with Preview

​Replace Existing File

​Examples

​Related Functions

Build docs developers (and LLMs) love

File Uploads

Basic File Upload Form

Validating Uploaded Files

Storage Options

Option 1: Store Files on Disk

Option 2: Store Files in Database

Complete Image Gallery Example

File Upload Functions

sqlpage.uploaded_file_path(field_name)

sqlpage.uploaded_file_mime_type(field_name)

sqlpage.persist_uploaded_file(field_name, directory, allowed_extensions)

sqlpage.read_file_as_data_url(file_path)

Configuration

Maximum Upload Size

Security Best Practices

1. Always Validate File Types

2. Require Authentication

3. Sanitize Filenames

4. Store Uploads Outside Web Root (Optional)

5. Set Appropriate Limits

6. Scan for Malware

Displaying Uploaded Files

Images

Documents

Advanced Techniques

Multiple File Uploads

Progress Indication

Image Resizing

Common Patterns

File Upload with Preview

Replace Existing File

Examples

Related Functions