CRUD Operations

Learn how to build complete CRUD (Create, Read, Update, Delete) applications with SQLPage. This tutorial covers advanced patterns for managing database records with forms, validation, and user feedback.

What You’ll Build

A full-featured CRUD application with:

List view with edit and delete actions
Create and update forms with validation
Delete confirmation dialogs
Success and error messages
URL-based state management
Reusable page components

Database Schema

For this example, we’ll create a currency management system. sqlpage/migrations/0007_currencies.sql

CREATE TABLE currencies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    to_rub REAL NOT NULL DEFAULT 1.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert some sample data
INSERT INTO currencies (name, to_rub) VALUES 
    ('USD', 75.50),
    ('EUR', 89.30),
    ('GBP', 104.20),
    ('CNY', 11.70);

List View with Actions

The list view displays all records with edit and delete buttons. currencies_list.sql

-- Include authentication check
set _session_required = 1;
SELECT 'dynamic' AS component,
    sqlpage.run_sql('header_shell_session.sql') AS properties;

-- Show success message if operation completed
SELECT
    'alert' AS component,
    'green' AS color,
    'check' AS icon,
    'Success' AS title,
    $info AS description,
    TRUE AS dismissible
WHERE $info IS NOT NULL;

-- Show error message if operation failed
SELECT
    'alert' AS component,
    'red' AS color,
    'thumb-down' AS icon,
    $op || ' error' AS title,
    $error AS description,
    TRUE AS dismissible
WHERE $error IS NOT NULL;

-- New Record button
SELECT 'button' AS component,
    'pill' AS shape,
    'lg' AS size,
    'end' AS justify;
    
SELECT
    'New Record' AS title,
    'insert_rec' AS id,
    'circle-plus' AS icon,
    'green' AS outline,
    'currencies_item_form.sql?action=INSERT' AS link;

-- Display the table
SELECT 'table' AS component,
    TRUE AS sort,
    TRUE AS search,
    TRUE AS border,
    TRUE AS hover,
    TRUE AS striped_rows,
    'actions' AS markdown;

SELECT
    id,
    name,
    to_rub,
    '[![](/icons/outline/edit.svg)](currencies_item_form.sql?id=' || id || ') ' ||
    '[![](/icons/outline/trash.svg)](currencies_item_form.sql?id=' || id || '&action=DELETE)' AS actions
FROM currencies
ORDER BY id;

Key Patterns

Alert Messages via URL Parameters

Success and error messages are passed through URL parameters:

WHERE $info IS NOT NULL  -- Shows when ?info=... in URL
WHERE $error IS NOT NULL -- Shows when ?error=... in URL

After an operation, redirect with the message:

'currencies_list.sql?info=Record created successfully' AS link

Action Icons in Tables

The markdown property on the table component allows rendering Markdown in specific columns:

'actions' AS markdown  -- Column named 'actions' will render as Markdown

Use Markdown image links for icons:

'[![](/icons/outline/edit.svg)](edit_url)' AS actions

URL-Based Actions

Pass the action type and record ID through URL parameters:

Create: ?action=INSERT
Edit: ?id=5
Delete: ?id=5&action=DELETE

Create/Edit Form

A single form handles both creating new records and editing existing ones. currencies_item_form.sql

-- Authentication check
set _session_required = 1;
SELECT 'dynamic' AS component,
    sqlpage.run_sql('header_shell_session.sql') AS properties;

-- Validate $id if provided
SELECT 'redirect' AS component,
    'currencies_list.sql' AS link
WHERE $id = '' OR CAST($id AS INT) = 0;

-- If $id is set, verify it exists in database
set error_msg = sqlpage.url_encode('Bad {id = ' || $id || '} provided');

SELECT 'redirect' AS component,
    'currencies_list.sql?error=' || $error_msg AS link
WHERE $id NOT IN (SELECT id FROM currencies);

-- Prepare form data
set _values = (
    WITH fields AS (
        -- Editing existing record
        SELECT id, name, to_rub
        FROM currencies
        WHERE id = CAST($id AS INT) AND $values IS NULL
        
        UNION ALL
        
        -- Creating new record (default values)
        SELECT NULL, '@', 1
        WHERE $id IS NULL AND $values IS NULL
        
        UNION ALL
        
        -- Repopulating form after validation error
        SELECT
            $values ->> '$.id' AS id,
            $values ->> '$.name' AS name,
            $values ->> '$.to_rub' AS to_rub
        WHERE json_valid($values)
    )
    SELECT json_object(
        'id', CAST(fields.id AS INT),
        'name', fields.name,
        'to_rub', CAST(fields.to_rub AS NUMERIC)
    )
    FROM fields
);

-- Browse Records button
SELECT 'button' AS component,
    'square' AS shape,
    'sm' AS size,
    'end' AS justify;
    
SELECT
    'BROWSE' AS title,
    'browse_rec' AS id,
    'corner-down-left' AS icon,
    'green' AS outline,
    TRUE AS narrow,
    'currencies_list.sql' AS link,
    'Browse full table' AS tooltip
WHERE NOT ifnull($action = 'DELETE', FALSE);

-- Main form
SELECT 'form' AS component,
    'Currency' AS title,
    'currencies_item_dml.sql?path=currencies_list.sql' AS action;

-- ID field (hidden for insert, select for update/delete)
SELECT 
    iif($action = 'DELETE', 'number', 'hidden') AS type,
    'id' AS name,
    'ID' AS label,
    $_values ->> '$.id' AS value,
    ifnull($action = 'DELETE', FALSE) AS readonly;

-- Name field
SELECT 
    'text' AS type,
    'name' AS name,
    'Currency' AS label,
    $_values ->> '$.name' AS value,
    'RUR' AS placeholder,
    ifnull($action = 'DELETE', FALSE) AS readonly,
    TRUE AS required;

-- Exchange rate field
SELECT 
    'number' AS type,
    0.01 AS step,
    'to_rub' AS name,
    'Exchange Rate to RUR' AS label,
    $_values ->> '$.to_rub' AS value,
    1 AS placeholder,
    ifnull($action = 'DELETE', FALSE) AS readonly,
    TRUE AS required;

-- Delete confirmation
SELECT
    'alert' AS component,
    'warning' AS color,
    'alert-triangle' AS icon,
    TRUE AS important,
    'Warning' AS title,
    'Confirm record deletion' AS description
WHERE $action = 'DELETE';

-- Form buttons
SELECT 'button' AS component,
    'pill' AS shape,
    'center' AS justify;

-- Update/Insert button
SELECT
    'Update' AS title,
    'update_rec' AS id,
    'device-floppy' AS icon,
    'azure' AS outline,
    TRUE AS narrow,
    'currencies_item_dml.sql?path=currencies_list.sql&action=UPDATE' AS link,
    'detail_view' AS form,
    ifnull($action = 'DELETE', FALSE) AS disabled;

-- Delete button
SELECT
    'DELETE' AS title,
    'delete_rec' AS id,
    'trash' AS icon,
    iif($action = 'DELETE', 'danger', NULL) AS color,
    iif($action = 'DELETE', NULL, 'danger') AS outline,
    TRUE AS narrow,
    'currencies_item_dml.sql?path=currencies_list.sql&action=DELETE' AS link,
    'detail_view' AS form;

Form Pattern Highlights

Determine Form Mode

The form behaves differently based on URL parameters:

No $id: Create mode (INSERT)
$id present: Edit mode (UPDATE)
$id + $action=DELETE: Delete confirmation mode

Load Existing Data

When editing ($id is set), query the database to populate form fields with current values.

Preserve Values on Error

If validation fails, the handler redirects back with $values containing the submitted data as JSON.

Conditional Field Display

In delete mode, make all fields readonly to show what will be deleted:

ifnull($action = 'DELETE', FALSE) AS readonly

Data Manipulation Handler

The DML (Data Manipulation Language) handler processes INSERT, UPDATE, and DELETE operations. currencies_item_dml.sql

-- Authentication check
set _session_required = 1;
set _shell_enabled = 0;  -- Don't show the page shell

SELECT 'dynamic' AS component,
    sqlpage.run_sql('header_shell_session.sql') AS properties;

-- Verify redirect target is provided
set _err_msg = '&path URL GET parameter (redirect target) is not set!';

SELECT
    'alert' AS component,
    'red' AS color,
    'alert-triangle' AS icon,
    'Error' AS title,
    $_err_msg AS description
WHERE $path IS NULL;

-- Prepare values
set _id = iif(typeof(:id) = 'text' AND :id = '', NULL, :id);

set _values = json_object(
    'id', CAST($_id AS INT),
    'name', :name,
    'to_rub', CAST(:to_rub AS NUMERIC)
);

set _op = iif($_id IS NULL, 'INSERT', 'UPDATE');

-- Check for duplicate name
set _err_msg = sqlpage.url_encode('Currency already exists in database');

SELECT 'redirect' AS component,
    $path || '?' ||
    '&op=' || $_op ||
    '&values=' || $_values ||
    '&error=' || $_err_msg AS link
FROM currencies
WHERE currencies.name = :name
  AND ($_id IS NULL OR currencies.id <> $_id);

-- UPSERT operation
INSERT INTO currencies(id, name, to_rub)
    SELECT CAST($_id AS INT), :name, CAST(:to_rub AS NUMERIC)
    WHERE $action = 'UPDATE'
ON CONFLICT(id) DO
UPDATE SET 
    name = excluded.name, 
    to_rub = excluded.to_rub
RETURNING
    'redirect' AS component,
    $path || '?' ||
    '&id=' || id ||
    '&info=' || $_op || ' completed successfully' AS link;

-- DELETE operation
DELETE FROM currencies
WHERE $action = 'DELETE' AND id = $_id
RETURNING
    'redirect' AS component,
    $path || '?info=DELETE completed successfully' AS link;

CRUD Implementation Patterns

UPSERT Pattern

Use INSERT ... ON CONFLICT ... UPDATE to handle both create and update in one query:

INSERT INTO table(id, field1, field2)
VALUES ($id, :field1, :field2)
ON CONFLICT(id) DO UPDATE SET
    field1 = excluded.field1,
    field2 = excluded.field2

If $id is NULL: New record is inserted (autoincrement generates ID)
If $id exists: Existing record is updated

Validation Before Mutation

Check constraints before modifying data:

-- Redirect with error if validation fails
SELECT 'redirect' AS component,
    'form.sql?error=' || sqlpage.url_encode('Error message') AS link
WHERE validation_condition;

-- Only execute if we reach here (validation passed)
INSERT INTO table ...

RETURNING for Immediate Feedback

Use RETURNING to redirect after successful operations:

DELETE FROM table WHERE id = $id
RETURNING 
    'redirect' AS component,
    'list.sql?info=Deleted successfully' AS link;

If the DELETE affects 0 rows, no redirect occurs and you can show an error.

URL Encoding Messages

Always encode user messages in URLs to handle special characters:

set _err_msg = sqlpage.url_encode('Error: ' || error_details);
SELECT 'redirect' AS component,
    'page.sql?error=' || $_err_msg AS link;

Handling Edge Cases

Empty String vs NULL
Invalid ID Parameters
Concurrent Modifications
Foreign Key Violations

Form fields submit empty strings, not NULL:

-- Convert empty string to NULL
set _id = iif(typeof(:id) = 'text' AND :id = '', NULL, :id);

Validate IDs before using them:

-- Redirect if ID is invalid
SELECT 'redirect' AS component,
    'list.sql' AS link
WHERE $id = '' OR CAST($id AS INT) = 0;

-- Redirect if ID doesn't exist
SELECT 'redirect' AS component,
    'list.sql?error=Record not found' AS link
WHERE $id NOT IN (SELECT id FROM table);

Handle race conditions with optimistic locking:

UPDATE table 
SET field = :new_value,
    version = version + 1
WHERE id = $id 
  AND version = $expected_version
RETURNING 'redirect' AS component, 'success.sql' AS link;

-- If no rows updated, show error
SELECT 'alert' AS component,
    'red' AS color,
    'Record was modified by another user' AS description;

Check dependencies before deletion:

SELECT 'redirect' AS component,
    'form.sql?error=Cannot delete - has dependencies' AS link
WHERE EXISTS (
    SELECT 1 FROM related_table 
    WHERE foreign_key = $id
);

DELETE FROM table WHERE id = $id ...

User Feedback Patterns

Success Messages

SELECT 'alert' AS component,
    'green' AS color,
    'check' AS icon,
    'Success' AS title,
    $info AS description,
    TRUE AS dismissible
WHERE $info IS NOT NULL;

Error Messages

SELECT 'alert' AS component,
    'red' AS color,
    'alert-triangle' AS icon,
    'Error' AS title,
    $error AS description,
    TRUE AS dismissible
WHERE $error IS NOT NULL;

Confirmation Dialogs

SELECT 'alert' AS component,
    'warning' AS color,
    'alert-triangle' AS icon,
    TRUE AS important,
    'Confirm Action' AS title,
    'Are you sure you want to delete this record?' AS description
WHERE $action = 'DELETE';

Application Structure

crud-app/
├── sqlpage/
│   └── migrations/
│       ├── 0000_db_init.sql
│       ├── 0003_auth.sql
│       └── 0007_currencies.sql
├── header_shell_session.sql      # Reusable auth check & shell
├── currencies_list.sql           # List view with actions
├── currencies_item_form.sql      # Create/Edit/Delete form
└── currencies_item_dml.sql       # DML handler

Complete CRUD Flow Diagram

Advanced CRUD Patterns

Master-Detail Forms

Handle parent records with multiple child records:

-- Save parent
INSERT INTO orders (...) 
RETURNING id AS order_id;

-- Save children
INSERT INTO order_items (order_id, ...)
SELECT $order_id, :item_name, :quantity
FROM json_each(:items);

Soft Deletes

Mark records as deleted instead of removing them:

UPDATE records 
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = $id;

-- Filter deleted in queries
SELECT * FROM records 
WHERE deleted_at IS NULL;

Audit Logging

Track all changes to records:

CREATE TRIGGER audit_changes
AFTER UPDATE ON table
BEGIN
  INSERT INTO audit_log (table_name, record_id, 
    old_values, new_values, changed_by, changed_at)
  VALUES ('table', OLD.id, 
    json_object(...), json_object(...), 
    $current_user, CURRENT_TIMESTAMP);
END;

Bulk Operations

Allow selecting and operating on multiple records:

DELETE FROM records
WHERE id IN (
  SELECT value 
  FROM json_each($selected_ids)
);

Production Checklist

Input Validation

Validate all inputs server-side, even if client-side validation exists:

Required fields
Data types and ranges
String lengths
Format patterns (email, phone, etc.)

Error Handling

Handle all error cases gracefully:

Database constraints
Missing records
Permission denied
Concurrent modifications

Authorization

Verify user permissions for each operation:

Can user view this record?
Can user edit this record?
Can user delete this record?

Transaction Management

Use transactions for multi-step operations:

BEGIN TRANSACTION;
-- Multiple operations
COMMIT;

Full Example

The complete CRUD example with authentication is available in the SQLPage repository.

Todo Application

Simpler CRUD example without authentication

User Authentication

Learn about session management and protected pages

Examples

CRUD Operations

What You’ll Build

Database Schema

List View with Actions

Key Patterns

Create/Edit Form

Form Pattern Highlights

Data Manipulation Handler

CRUD Implementation Patterns

Handling Edge Cases

User Feedback Patterns

Success Messages

Error Messages

Confirmation Dialogs

Application Structure

Complete CRUD Flow Diagram

Advanced CRUD Patterns

Master-Detail Forms

Soft Deletes

Audit Logging

Bulk Operations

Production Checklist

Full Example

Todo Application

User Authentication

Build docs developers (and LLMs) love

Examples

​What You’ll Build

​Database Schema

​List View with Actions

​Key Patterns

​Create/Edit Form

​Form Pattern Highlights

​Data Manipulation Handler

​CRUD Implementation Patterns

​Handling Edge Cases

​User Feedback Patterns

​Success Messages

​Error Messages

​Confirmation Dialogs

​Application Structure

​Complete CRUD Flow Diagram

​Advanced CRUD Patterns

Master-Detail Forms

Soft Deletes

Audit Logging

Bulk Operations

​Production Checklist

​Full Example

​Related Examples

Todo Application

User Authentication

Build docs developers (and LLMs) love

What You’ll Build

Database Schema

List View with Actions

Key Patterns

Create/Edit Form

Form Pattern Highlights

Data Manipulation Handler

CRUD Implementation Patterns

Handling Edge Cases

User Feedback Patterns

Success Messages

Error Messages

Confirmation Dialogs

Application Structure

Complete CRUD Flow Diagram

Advanced CRUD Patterns

Production Checklist

Full Example

Related Examples