Skip to main content
This tutorial shows you how to build a complete todo application with SQLPage. You’ll learn how to create, read, update, and delete todo items using a SQLite database. Todo App Screenshot

​
What You’ll Build

A fully functional todo application with:
  • List view showing all todos
  • Add new todo items
  • Edit existing todos
  • Delete todos with confirmation
  • Consistent layout across all pages

​
Database Setup

First, create the database schema. SQLPage uses migrations stored in sqlpage/migrations/ to set up your database automatically. sqlpage/migrations/0000_init.sql 
create table todos(
    id integer primary key,
    title text not null,
    created_at timestamp default current_timestamp
);
When SQLPage starts, it automatically runs all migration files in order.

​
Creating the Shell Layout

Create a reusable shell component that provides consistent navigation across all pages. shell.sql 
select 'shell' as component,
    printf('Todo list (%d)', count(*)) as title,
    'timeline' as menu_item
from todos;
This file:
  • Uses the shell component to create the page header
  • Dynamically shows the count of todos in the title
  • Adds a menu item linking to the timeline view

​
Building the Main Page

1

Load the Shell

Use the dynamic component to include the shell on every page:
select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;
2

Display the Todo List

Use the list component to show all todos:
select 'list' as component,
    'Todo' as title,
    'No todo yet...' as empty_title;

select 
    title,
    'todo_form.sql?todo_id=' || id as edit_link,
    'delete.sql?todo_id=' || id as delete_link
from todos;
3

Add Create Button

Add a button to create new todos:
select 'button' as component, 'center' as justify;
select 
    'todo_form.sql' as link,
    'green' as color,
    'Add new todo' as title,
    'circle-plus' as icon;
Complete index.sql 
select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;

select 'list' as component,
    'Todo' as title,
    'No todo yet...' as empty_title;

select 
    title,
    'todo_form.sql?todo_id=' || id as edit_link,
    'delete.sql?todo_id=' || id as delete_link
from todos;

select 'button' as component, 'center' as justify;
select 
    'todo_form.sql' as link,
    'green' as color,
    'Add new todo' as title,
    'circle-plus' as icon;

​
Creating the Todo Form

The form handles both creating new todos and editing existing ones. todo_form.sql 
-- When the form is submitted, insert or update the todo
insert or replace into todos(id, title)
select $todo_id, :todo
where :todo is not null
returning
    'redirect' as component,
    '/' as link;

-- Load the shell layout
select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;

-- Display the form
select 
    'form' as component,
    'Todo' as title,
    (
        case when $todo_id is null then
            'Add new todo'
        else
            'Edit todo'
        end
    ) as validate;
    
select 
    'Todo item' as label,
    'todo' as name,
    'What do you have to do ?' as placeholder,
    (select title from todos where id = $todo_id) as value;

​
How It Works

  1. Insert/Update Query Runs First: When the form is submitted, :todo is not null, so the insert executes and returns a redirect
  2. Redirect Happens: If a row is returned, SQLPage redirects immediately and stops processing
  3. Form Displays: If :todo is null (initial page load), nothing is inserted, no redirect occurs, and the form displays
  • $todo_id: URL parameter (e.g., ?todo_id=5) - prefixed with $
  • :todo: Form POST data - prefixed with :
  • When editing, $todo_id retrieves the existing todo’s data
  • When creating, $todo_id is null, so a new record is inserted

​
Implementing Delete with Confirmation

The delete functionality uses a two-step process for safety. delete.sql 
-- Delete only if confirmed
delete from todos
where id = $todo_id and $confirm = 'yes'
returning
    'redirect' as component,
    '/' as link;

-- Load the shell
select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;

-- Show confirmation alert
select
    'alert' as component,
    'red' as color,
    'Confirm deletion' as title,
    'Are you sure you want to delete the following todo item ?\n\n> ' || title as description_md,
    '?todo_id=' || $todo_id || '&confirm=yes' as link,
    'Delete' as link_text
from todos where id = $todo_id;

​
Delete Flow

1

User Clicks Delete

From index.sql, clicking delete loads /delete.sql?todo_id=7
2

Show Confirmation

  • $confirm is null (not in URL yet)
  • Delete query runs but WHERE condition is false, nothing deleted
  • Alert component displays confirmation message
3

User Confirms

User clicks “Delete” button, which loads /delete.sql?todo_id=7&confirm=yes
4

Actually Delete

  • $confirm now equals ‘yes’
  • WHERE condition is true, todo is deleted
  • RETURNING clause sends redirect component
  • User is redirected to home page

​
Project Structure

your-app/
├── sqlpage/
│   └── migrations/
│       └── 0000_init.sql       # Database schema
├── index.sql                   # Main todo list page
├── shell.sql                   # Shared layout component
├── todo_form.sql              # Create/edit form
└── delete.sql                 # Delete with confirmation

​
Key Concepts

Component Chaining

Each SELECT statement renders a component. Chain multiple components by using multiple SELECT statements in sequence.

URL Parameters

Access URL query parameters with $ prefix (e.g., $todo_id for ?todo_id=5)

Form Data

Access POST form data with : prefix (e.g., :todo for a field named “todo”)

RETURNING Clause

Use RETURNING to render components from INSERT/UPDATE/DELETE queries, enabling immediate redirects after mutations

​
Running the Example

1

Download SQLPage

Get the latest release from GitHub
2

Create Your Files

Set up the project structure with all the SQL files shown above
3

Start SQLPage

Run SQLPage from your project directory:
./sqlpage
4

Open Your Browser

Navigate to http://localhost:8080 to see your todo app!

​
Next Steps

Add User Authentication

Learn how to add login and signup to your app

CRUD Operations

Explore more complex CRUD patterns

Components Reference

Browse all available SQLPage components

Functions Reference

Explore SQLPage built-in functions

​
Full Example

The complete working example is available in the SQLPage repository.

Build docs developers (and LLMs) love

Get started for freeTalk to us