Building a TODO API with MySQL from Scratch

March 20, 2026 · SysMARA Team

SysMARA v0.7.1 can go from an empty directory to a running CRUD API with two approaches: a single AI prompt, or step-by-step manual setup. This tutorial shows both, using MySQL as the database. Every command and response below was verified on a real system.

Choose your path:

Way 1: One AI Prompt — paste a single prompt into Claude Code, get a running API
Way 2: Manual Step-by-Step — understand every file and decision

Prerequisites

Node.js 20+ — check with node --version
Docker — for the MySQL container. Check with docker --version
npm — comes with Node.js

Way 1: One AI Prompt (Claude Code)

Open Claude Code in an empty directory and paste this single prompt:

Create a TODO API using SysMARA framework with MySQL.

Steps:
1. Run: npx @sysmara/core init --db mysql --orm sysmara-orm
2. Install mysql driver: npm install mysql2
3. Replace system/entities.yaml with a "todo" entity:
   fields: id (string, required), title (string, required),
   description (string, optional), status (string, required,
   description: "pending | in_progress | done"),
   priority (number, optional), created_at (date, required)
   module: tasks
4. Replace system/capabilities.yaml with 5 capabilities:
   - create_todo (input: title required, description optional, priority optional)
   - get_todo (input: id required)
   - list_todos (no input)
   - update_todo (input: id required, title/status/priority optional)
   - delete_todo (input: id required)
   All in module: tasks, entities: [todo], policies: [], invariants: []
5. Replace system/modules.yaml: module "tasks" with entity todo
   and all 5 capabilities
6. Set system/policies.yaml to: policies: []
7. Set system/invariants.yaml to: invariants: []
8. Set system/flows.yaml to: flows: []
9. Start MySQL: docker compose up -d
10. Start the server: npx sysmara start

Do not modify any other files. Do not create app/ code manually —
sysmara start handles everything.

That is it. The AI agent runs all 10 steps, and at the end you have a running server:

════════════════════════════════════════════════════════════
  SysMARA Server
════════════════════════════════════════════════════════════

  Parsing specs...
  ✓ Found 1 entities, 5 capabilities

  Connecting to database...
  ✓ Connected to mysql (sysmara-orm)
  Applying database schema...
  ✓ Schema applied (tables created if not existing)

  Registering routes...
  ✓ POST   /todos                    → create_todo
  ✓ GET    /todos/:id                → get_todo
  ✓ GET    /todos                    → list_todos
  ✓ PUT    /todos/:id                → update_todo
  ✓ DELETE /todos/:id                → delete_todo

  ✓ Server running at http://localhost:3000

Jump to Testing with curl to verify it works.

Way 2: Manual Step-by-Step

Step 1: Initialize the project

mkdir my-todo-api && cd my-todo-api
npx @sysmara/core init --db mysql --orm sysmara-orm

This creates the full project structure:

system/ — YAML spec files (entities, capabilities, policies, invariants, modules, flows)
app/ — application code directory
sysmara.config.yaml — project config with MySQL as the database
docker-compose.yml — MySQL 8 container with health checks
.env.local — local credentials matching Docker
package.json, .gitignore, Dockerfile, README.md

Step 2: Start MySQL

docker compose up -d

Wait until docker compose ps shows the db service as healthy (10–15 seconds on first run).

Step 3: Define the TODO entity

Replace system/entities.yaml with:

entities:
  - name: todo
    description: A task in the system
    module: tasks
    fields:
      - name: id
        type: string
        required: true
      - name: title
        type: string
        required: true
      - name: description
        type: string
        required: false
      - name: status
        type: string
        required: true
        description: pending | in_progress | done
      - name: priority
        type: number
        required: false
      - name: created_at
        type: date
        required: true

This is the single source of truth for your data model. SysMARA reads it to generate the database schema, validate capabilities, and wire route handlers.

Step 4: Define capabilities

Replace system/capabilities.yaml with:

capabilities:
  - name: create_todo
    description: Create a new todo
    module: tasks
    entities: [todo]
    input:
      - { name: title, type: string, required: true }
      - { name: description, type: string, required: false }
      - { name: priority, type: number, required: false }
    output:
      - { name: todo, type: reference, required: true }
    policies: []
    invariants: []

  - name: get_todo
    description: Get a todo by ID
    module: tasks
    entities: [todo]
    input:
      - { name: id, type: string, required: true }
    output:
      - { name: todo, type: reference, required: true }
    policies: []
    invariants: []
    idempotent: true

  - name: list_todos
    description: List all todos
    module: tasks
    entities: [todo]
    input: []
    output:
      - { name: todos, type: reference, required: true }
    policies: []
    invariants: []
    idempotent: true

  - name: update_todo
    description: Update a todo
    module: tasks
    entities: [todo]
    input:
      - { name: id, type: string, required: true }
      - { name: title, type: string, required: false }
      - { name: status, type: string, required: false }
      - { name: priority, type: number, required: false }
    output:
      - { name: todo, type: reference, required: true }
    policies: []
    invariants: []

  - name: delete_todo
    description: Delete a todo
    module: tasks
    entities: [todo]
    input:
      - { name: id, type: string, required: true }
    output:
      - { name: success, type: boolean, required: true }
    policies: []
    invariants: []

Each capability maps to an HTTP endpoint. SysMARA infers the method and path from the name: create_todo → POST /todos, get_todo → GET /todos/:id, etc.

Step 5: Update modules

Replace system/modules.yaml with:

modules:
  - name: tasks
    description: Task management module
    entities:
      - todo
    capabilities:
      - create_todo
      - get_todo
      - list_todos
      - update_todo
      - delete_todo
    allowedDependencies: []
    forbiddenDependencies: []

Step 6: Clear policies, invariants, flows

Set these to empty (we can add them later):

# system/policies.yaml
policies: []

# system/invariants.yaml
invariants: []

# system/flows.yaml
flows: []

Step 7: Install the MySQL driver

npm install mysql2

Step 8: Start the server

npx sysmara start

You should see:

════════════════════════════════════════════════════════════
  SysMARA Server
════════════════════════════════════════════════════════════

  Parsing specs...
  ✓ Found 1 entities, 5 capabilities

  Connecting to database...
  ✓ Connected to mysql (sysmara-orm)
  Applying database schema...
  ✓ Schema applied (tables created if not existing)

  Registering routes...
  ✓ POST   /todos                    → create_todo
  ✓ GET    /todos/:id                → get_todo
  ✓ GET    /todos                    → list_todos
  ✓ PUT    /todos/:id                → update_todo
  ✓ DELETE /todos/:id                → delete_todo

  ✓ Server running at http://localhost:3000
  5 capability routes + health/routes endpoints

Testing with curl

Regardless of which way you used, the API is now live. Here is every operation, verified against a real running instance:

Create a todo

curl -s -X POST http://localhost:3000/todos \
  -H 'Content-Type: application/json' \
  -d '{"title": "Buy groceries", "priority": 1}'

Response:

{
  "id": "f99b75a7-90df-424e-8f00-0daad05c152e",
  "title": "Buy groceries",
  "description": null,
  "status": "pending",
  "priority": 1,
  "created_at": "2026-03-20 10:30:00"
}

The id and created_at are auto-generated. The status defaults to "pending" because it was not in the request body — SysMARA reads the entity field description (pending | in_progress | done) and uses the first value as the default.

Create a second todo

curl -s -X POST http://localhost:3000/todos \
  -H 'Content-Type: application/json' \
  -d '{"title": "Clean house"}'

{
  "id": "f04fd9d6-2858-4d2a-930b-214fccc3a50f",
  "title": "Clean house",
  "description": null,
  "status": "pending",
  "priority": null,
  "created_at": "2026-03-20 10:30:01"
}

List all todos

curl -s http://localhost:3000/todos

{
  "items": [
    {
      "id": "f99b75a7-...",
      "title": "Buy groceries",
      "status": "pending",
      "priority": 1
    },
    {
      "id": "f04fd9d6-...",
      "title": "Clean house",
      "status": "pending",
      "priority": null
    }
  ],
  "count": 2
}

Get one todo

curl -s http://localhost:3000/todos/f99b75a7-90df-424e-8f00-0daad05c152e

{
  "id": "f99b75a7-90df-424e-8f00-0daad05c152e",
  "title": "Buy groceries",
  "description": null,
  "status": "pending",
  "priority": 1,
  "created_at": "2026-03-20 10:30:00"
}

Update a todo

curl -s -X PUT http://localhost:3000/todos/f99b75a7-90df-424e-8f00-0daad05c152e \
  -H 'Content-Type: application/json' \
  -d '{"status": "done", "title": "Buy organic groceries"}'

{
  "id": "f99b75a7-90df-424e-8f00-0daad05c152e",
  "title": "Buy organic groceries",
  "description": null,
  "status": "done",
  "priority": 1,
  "created_at": "2026-03-20 10:30:00"
}

Delete a todo

curl -s -X DELETE http://localhost:3000/todos/f99b75a7-90df-424e-8f00-0daad05c152e

{
  "success": true,
  "deleted": "f99b75a7-90df-424e-8f00-0daad05c152e"
}

Verify deletion

curl -s http://localhost:3000/todos

{
  "items": [
    {
      "id": "f04fd9d6-...",
      "title": "Clean house",
      "status": "pending",
      "priority": null
    }
  ],
  "count": 1
}

What Just Happened

YAML specs defined the data model — the entity file described the table structure. The ORM generated CREATE TABLE SQL with proper types, nullability, and a UUID primary key.
YAML specs defined the operations — each capability mapped to an HTTP method, URL path, input validation, and output shape.
The ORM generated and applied the schema — no migration files, no model classes, no decorators.
Routes were auto-wired from capability names — create_todo became POST /todos, list_todos became GET /todos, etc.
Default values were inferred from specs — the status field defaulted to "pending" because the entity description said pending | in_progress | done.

No Express. No manual route registration. No ORM configuration. No controller classes. The YAML specs were the entire application definition.

Next Steps

Add policies for access control — restrict who can delete todos. See Policies.
Add invariants for validation — ensure title is not empty, status is one of the allowed values. See Invariants.
Use sysmara build for production — generates a customizable app/server.ts entry point. See CLI Reference.
Switch databases — swap --db mysql for --db postgresql or --db sqlite. The rest stays the same.