Building Your First App

Complete tutorial for building a todo list application with TrailBase

This guide walks you through building a complete todo list application with TrailBase, covering database setup, type-safe APIs, and realtime updates.

What You’ll Build

A fully functional todo app with:

• Create, read, update, and delete operations
• Realtime synchronization across tabs and devices
• Type-safe client code
• Authentication (optional)

Prerequisites

• TrailBase installed (installation guide)
• Node.js and npm/pnpm (for the frontend)
• Basic knowledge of TypeScript/JavaScript

Step 1: Initialize Your Project

Create a new directory and initialize TrailBase:

mkdir my-todo-app
cd my-todo-app

# Start TrailBase (creates traildepot/ directory)
trail run

TrailBase will create a traildepot/ directory containing:

• data/ - SQLite database files
• migrations/ - Database migration files
• config.textproto - API and auth configuration
• secrets/ - Secure credentials

On first run, TrailBase displays admin credentials in the terminal. Save these to access the admin dashboard at http://localhost:4000/_/admin.

Step 2: Create the Database Schema

Create a migration file for the todos table:

trail migration create_table_todos

This creates a file like traildepot/migrations/main/U1234567890__create_table_todos.sql. Edit it:

traildepot/migrations/main/U1234567890__create_table_todos.sql

CREATE TABLE todos (
  "id"          INTEGER PRIMARY KEY NOT NULL,
  "text"        TEXT NOT NULL,
  "completed"   INTEGER NOT NULL DEFAULT 0,
  "created_at"  INTEGER NOT NULL DEFAULT(UNIXEPOCH()),
  "updated_at"  INTEGER NOT NULL DEFAULT(UNIXEPOCH())
) STRICT;

-- Automatically update the updated_at timestamp
CREATE TRIGGER _todos__update_trigger AFTER UPDATE ON todos FOR EACH ROW
  BEGIN
    UPDATE todos SET updated_at = UNIXEPOCH() WHERE id = OLD.id;
  END;

Key Requirements:

• Tables must use STRICT typing
• Primary key must be INTEGER or BLOB (for UUIDv7)
• Column names should be quoted to avoid SQL keyword conflicts

Restart TrailBase to apply the migration:

# Stop with Ctrl+C, then restart
trail run

You’ll see a log message confirming the migration was applied.

Step 3: Configure the Record API

Edit traildepot/config.textproto to expose the todos table as an API:

traildepot/config.textproto

record_apis: [
  {
    name: "todos"
    table_name: "todos"
    # Allow anyone to read/write (for development)
    acl_world: [CREATE, READ, UPDATE, DELETE]
  }
]

acl_world grants public access. For production, use acl_authenticated and implement proper access controls.

Restart TrailBase to load the configuration.

Step 4: Generate Type Definitions

Generate TypeScript types from your schema:

trail schema todos --mode select > todo.schema.json

Install the TypeScript client and code generation tools:

npm install trailbase
npm install -D json-schema-to-typescript

Generate TypeScript types:

npx json-schema-to-typescript todo.schema.json > src/types/todo.ts

Your generated Todo type will include:

src/types/todo.ts

export interface Todo {
  id: number;
  text: string;
  completed: number; // SQLite uses integers for booleans
  created_at: number;
  updated_at: number;
}

Step 5: Build the Frontend

Create a React component using the TrailBase client:

React

src/App.tsx

import { useState, useEffect } from "react";
import { initClient } from "trailbase";
import type { Todo } from "./types/todo";

const client = initClient("http://localhost:4000");
const todosApi = client.records<Todo>("todos");

function App() {
  const [todos, setTodos] = useState<Todo[]>([]);
  const [newTodo, setNewTodo] = useState("");

  // Load todos on mount
  useEffect(() => {
    loadTodos();
  }, []);

  async function loadTodos() {
    const response = await todosApi.list();
    setTodos(response.records);
  }

  async function addTodo() {
    if (!newTodo.trim()) return;
    
    await todosApi.create({
      text: newTodo,
      completed: 0,
    });
    
    setNewTodo("");
    loadTodos();
  }

  async function toggleTodo(todo: Todo) {
    await todosApi.update(todo.id, {
      completed: todo.completed ? 0 : 1,
    });
    loadTodos();
  }

  async function deleteTodo(id: number) {
    await todosApi.delete(id);
    loadTodos();
  }

  return (
    <div className="container">
      <h1>My Todos</h1>
      
      <div className="input-group">
        <input
          type="text"
          value={newTodo}
          onChange={(e) => setNewTodo(e.target.value)}
          onKeyPress={(e) => e.key === "Enter" && addTodo()}
          placeholder="What needs to be done?"
        />
        <button onClick={addTodo}>Add</button>
      </div>

      <ul className="todo-list">
        {todos.map((todo) => (
          <li key={todo.id}>
            <input
              type="checkbox"
              checked={!!todo.completed}
              onChange={() => toggleTodo(todo)}
            />
            <span style={{ textDecoration: todo.completed ? "line-through" : "none" }}>
              {todo.text}
            </span>
            <button onClick={() => deleteTodo(todo.id)}>Delete</button>
          </li>
        ))}
      </ul>
    </div>
  );
}

export default App;

Vue

src/App.vue

<template>
  <div class="container">
    <h1>My Todos</h1>
    
    <div class="input-group">
      <input
        v-model="newTodo"
        @keyup.enter="addTodo"
        placeholder="What needs to be done?"
      />
      <button @click="addTodo">Add</button>
    </div>

    <ul class="todo-list">
      <li v-for="todo in todos" :key="todo.id">
        <input
          type="checkbox"
          :checked="!!todo.completed"
          @change="toggleTodo(todo)"
        />
        <span :style="{ textDecoration: todo.completed ? 'line-through' : 'none' }">
          {{ todo.text }}
        </span>
        <button @click="deleteTodo(todo.id)">Delete</button>
      </li>
    </ul>
  </div>
</template>

<script setup lang="ts">
import { ref, onMounted } from "vue";
import { initClient } from "trailbase";
import type { Todo } from "./types/todo";

const client = initClient("http://localhost:4000");
const todosApi = client.records<Todo>("todos");

const todos = ref<Todo[]>([]);
const newTodo = ref("");

onMounted(() => {
  loadTodos();
});

async function loadTodos() {
  const response = await todosApi.list();
  todos.value = response.records;
}

async function addTodo() {
  if (!newTodo.value.trim()) return;
  
  await todosApi.create({
    text: newTodo.value,
    completed: 0,
  });
  
  newTodo.value = "";
  loadTodos();
}

async function toggleTodo(todo: Todo) {
  await todosApi.update(todo.id, {
    completed: todo.completed ? 0 : 1,
  });
  loadTodos();
}

async function deleteTodo(id: number) {
  await todosApi.delete(id);
  loadTodos();
}
</script>

Step 6: Add Realtime Updates

Subscribe to live changes to sync todos across tabs:

src/App.tsx

import { initClient, type Event } from "trailbase";

// Add to your component
useEffect(() => {
  let reader: ReadableStreamDefaultReader<Event> | null = null;

  async function subscribe() {
    try {
      // Subscribe to all todos
      const stream = await todosApi.subscribeAll();
      reader = stream.getReader();

      while (true) {
        const { done, value } = await reader.read();
        if (done) break;

        // Handle different event types
        if ("Insert" in value || "Update" in value || "Delete" in value) {
          // Reload todos when changes occur
          loadTodos();
        }
      }
    } catch (err) {
      console.error("Subscription error:", err);
    }
  }

  subscribe();

  return () => {
    reader?.cancel();
  };
}, []);

For production apps, use a debounce mechanism to avoid excessive reloads when handling rapid updates.

Step 7: Test Your App

Start your development server:

npm run dev

Open multiple browser tabs to http://localhost:5173 and test:

1. Add a todo in one tab
2. Watch it appear in other tabs instantly
3. Toggle completion status
4. Delete todos

Step 8: Add Authentication (Optional)

Update your API configuration to require authentication:

traildepot/config.textproto

record_apis: [
  {
    name: "todos"
    table_name: "todos"
    # Only authenticated users can access
    acl_authenticated: [CREATE, READ, UPDATE, DELETE]
  }
]

Add a user column to track todo ownership:

trail migration add_user_to_todos

-- Add user column
ALTER TABLE todos ADD COLUMN user BLOB REFERENCES _user(id) ON DELETE CASCADE;

-- Create index for faster lookups
CREATE INDEX _todos__user_index ON todos(user);

Update the API to auto-fill the user column:

traildepot/config.textproto

record_apis: [
  {
    name: "todos"
    table_name: "todos"
    autofill_missing_user_id_columns: true
    acl_authenticated: [CREATE, READ, UPDATE, DELETE]
    # Users can only see their own todos
    read_access_rule: "_ROW_.user = _USER_.id"
    # Users can only modify their own todos
    update_access_rule: "_ROW_.user = _USER_.id"
    delete_access_rule: "_ROW_.user = _USER_.id"
  }
]

Add authentication to your frontend:

src/App.tsx

import { initClient } from "trailbase";

const client = initClient("http://localhost:4000");

function App() {
  const [user, setUser] = useState(client.user());

  async function login(email: string, password: string) {
    const response = await client.login({ email, password });
    setUser(client.user());
  }

  async function logout() {
    await client.logout();
    setUser(null);
  }

  if (!user) {
    return <LoginForm onLogin={login} />;
  }

  return (
    <div>
      <button onClick={logout}>Logout ({user.email})</button>
      {/* Your todo list component */}
    </div>
  );
}

Next Steps

Database Setup

Learn advanced schema design patterns

Authentication

Implement OAuth and password auth

Realtime Subscriptions

Master WebSocket subscriptions

File Uploads

Handle file uploads and storage

Complete Example

The TanStack Todo Example in the TrailBase repository demonstrates automatic cross-device synchronization using TanStack/db.