Build a REST API

Create a CRUD API with database models, validation, and tests.

Note

What you'll learn

Defining ORM models with migrations and typed columns
Using QueryBuilder for filtering, sorting, and pagination
Validating request input with the schema builder
Writing end-to-end API tests with TestClient
Adding model relationships and protecting routes with AuthMiddleware

Estimated time: ~35 minutes

Prerequisites: Complete the Quick Start guide before starting.

Packages used: @roostjs/orm, @roostjs/core, @roostjs/testing, @roostjs/start

Step 1: Create the project

Scaffold a new Roost application called task-api, then install its dependencies.

roost new task-api
cd task-api
bun install

Tip

You should see output like ✓ task-api created successfully followed by Bun resolving and installing the workspace packages.

Step 2: Create the Task model

Generate a model named Task:

roost make:model Task

This creates src/models/task.ts with a stub. Open it and replace the placeholder columns with the schema for our task:

import { Model } from '@roostjs/orm';
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const tasks = sqliteTable('tasks', {
  id:          integer('id').primaryKey({ autoIncrement: true }),
  title:       text('title').notNull(),
  description: text('description'),
  status:      text('status', { enum: ['pending', 'in_progress', 'done'] })
                 .notNull()
                 .default('pending'),
  due_date:    text('due_date'),   // ISO-8601 date string, nullable
  created_at:  text('created_at'),
  updated_at:  text('updated_at'),
});

export class Task extends Model {
  static tableName = 'tasks';
  static _table    = tasks;
}

Push the schema to your local D1 database:

roost migrate

Tip

You should see drizzle-kit push output confirming the schema was applied. If you open Wrangler's local D1 studio (bunx wrangler d1 execute task-api --local --command "SELECT name FROM sqlite_master WHERE type='table';") you will see the tasks table listed.

Step 3: Create GET /api/tasks

Create the index route. It fetches all tasks from the database and returns them as JSON.

import { createAPIFileRoute } from '@tanstack/start/api';
import { Task } from '../../../models/task';

export const APIRoute = createAPIFileRoute('/api/tasks')({
  GET: async () => {
    const tasks = await Task.all();
    return Response.json(tasks.map((t) => t.attributes));
  },
});

Tip

Start the dev server (bun run dev) and visit http://localhost:3000/api/tasks. You should see an empty JSON array: [].

Step 4: Create POST /api/tasks

Add the POST handler to the same route file. We parse the request body, validate the required fields, and persist the new task.

import { createAPIFileRoute } from '@tanstack/start/api';
import { z } from 'zod';
import { Task } from '../../../models/task';

const createTaskSchema = z.object({
  title:       z.string().min(1),
  description: z.string().optional(),
  status:      z.enum(['pending', 'in_progress', 'done']).default('pending'),
  due_date:    z.string().optional(),
});

export const APIRoute = createAPIFileRoute('/api/tasks')({
  GET: async () => {
    const tasks = await Task.all();
    return Response.json(tasks.map((t) => t.attributes));
  },

  POST: async ({ request }) => {
    const body = await request.json();
    const parsed = createTaskSchema.safeParse(body);

    if (!parsed.success) {
      return Response.json({ errors: parsed.error.flatten() }, { status: 422 });
    }

    const task = await Task.create(parsed.data);
    return Response.json(task.attributes, { status: 201 });
  },
});

Tip

Test with curl:

curl -s -X POST http://localhost:3000/api/tasks \
-H 'Content-Type: application/json' \
-d '{"title":"Buy groceries"}' | jq .

You should see the newly created task object with an id, status: "pending", and ISO timestamps.

Step 5: Create PUT /api/tasks/:id

Create a separate file for the parameterised route. TanStack Start uses filename brackets for path parameters.

import { createAPIFileRoute } from '@tanstack/start/api';
import { z } from 'zod';
import { Task } from '../../../models/task';

const updateTaskSchema = z.object({
  title:       z.string().min(1).optional(),
  description: z.string().optional(),
  status:      z.enum(['pending', 'in_progress', 'done']).optional(),
  due_date:    z.string().optional(),
});

export const APIRoute = createAPIFileRoute('/api/tasks/$id')({
  PUT: async ({ params, request }) => {
    const task = await Task.find(Number(params.id));
    if (!task) {
      return Response.json({ error: 'Task not found' }, { status: 404 });
    }

    const body = await request.json();
    const parsed = updateTaskSchema.safeParse(body);

    if (!parsed.success) {
      return Response.json({ errors: parsed.error.flatten() }, { status: 422 });
    }

    Object.assign(task.attributes, parsed.data);
    await task.save();

    return Response.json(task.attributes);
  },
});

Tip

Replace 1 with the id returned by your POST call, then run:

curl -s -X PUT http://localhost:3000/api/tasks/1 \
-H 'Content-Type: application/json' \
-d '{"status":"in_progress"}' | jq .status

You should see "in_progress".

Step 6: Create DELETE /api/tasks/:id

Add a DELETE handler to the same $id.ts file.

import { createAPIFileRoute } from '@tanstack/start/api';
import { z } from 'zod';
import { Task } from '../../../models/task';

const updateTaskSchema = z.object({
  title:       z.string().min(1).optional(),
  description: z.string().optional(),
  status:      z.enum(['pending', 'in_progress', 'done']).optional(),
  due_date:    z.string().optional(),
});

export const APIRoute = createAPIFileRoute('/api/tasks/$id')({
  PUT: async ({ params, request }) => {
    const task = await Task.find(Number(params.id));
    if (!task) {
      return Response.json({ error: 'Task not found' }, { status: 404 });
    }

    const body = await request.json();
    const parsed = updateTaskSchema.safeParse(body);

    if (!parsed.success) {
      return Response.json({ errors: parsed.error.flatten() }, { status: 422 });
    }

    Object.assign(task.attributes, parsed.data);
    await task.save();

    return Response.json(task.attributes);
  },

  DELETE: async ({ params }) => {
    const task = await Task.find(Number(params.id));
    if (!task) {
      return Response.json({ error: 'Task not found' }, { status: 404 });
    }

    await task.delete();
    return new Response(null, { status: 204 });
  },
});

Tip

Run curl -s -o /dev/null -w "%{http_code}" -X DELETE http://localhost:3000/api/tasks/1 — you should get back 204.

Step 7: Add filtering and sorting

Update the GET /api/tasks handler to accept optional status and sort query parameters using QueryBuilder's where and orderBy methods.

GET: async ({ request }) => {
  const url    = new URL(request.url);
  const status = url.searchParams.get('status');
  const sort   = url.searchParams.get('sort') ?? 'created_at';
  const order  = url.searchParams.get('order') === 'desc' ? 'desc' : 'asc';

  let query = Task.where('id', '>', 0);   // start a QueryBuilder

  if (status) {
    query = query.where('status', status);
  }

  query = query.orderBy(sort, order);

  const tasks = await query.all();
  return Response.json(tasks.map((t) => t.attributes));
},

Tip

Create a few tasks with different statuses, then try:

curl "http://localhost:3000/api/tasks?status=pending&sort=due_date&order=asc"

Only pending tasks should appear, ordered by due date ascending.

Step 8: Write tests with TestClient

Create a test file that covers every endpoint. setupTestSuite boots the application once per suite and gives you a pre-configured TestClient.

import { describe, it, beforeAll, afterAll } from 'bun:test';
import { setupTestSuite } from '@roostjs/testing';

const suite = setupTestSuite();

beforeAll(suite.beforeAll);
afterAll(suite.afterAll);

describe('GET /api/tasks', () => {
  it('returns an empty array when no tasks exist', async () => {
    const { client } = suite.getContext();
    const res = await client.get('/api/tasks');
    res.assertOk();
    const data = await res.json<unknown[]>();
    if (!Array.isArray(data) || data.length !== 0) {
      throw new Error('Expected empty array');
    }
  });
});

describe('POST /api/tasks', () => {
  it('creates a task with valid input', async () => {
    const { client } = suite.getContext();
    const res = await client.post('/api/tasks', { title: 'Write tests' });
    res.assertCreated();
    await res.assertJson({ title: 'Write tests', status: 'pending' });
  });

  it('returns 422 when title is missing', async () => {
    const { client } = suite.getContext();
    const res = await client.post('/api/tasks', { description: 'No title here' });
    res.assertStatus(422);
  });
});

describe('PUT /api/tasks/:id', () => {
  it('updates an existing task', async () => {
    const { client } = suite.getContext();

    const created = await client.post('/api/tasks', { title: 'Original title' });
    created.assertCreated();
    const task = await created.json<{ id: number }>();

    const updated = await client.put(`/api/tasks/${task.id}`, {
      status: 'in_progress',
    });
    updated.assertOk();
    await updated.assertJson({ status: 'in_progress' });
  });

  it('returns 404 for a non-existent task', async () => {
    const { client } = suite.getContext();
    const res = await client.put('/api/tasks/99999', { title: 'Ghost' });
    res.assertNotFound();
  });
});

describe('DELETE /api/tasks/:id', () => {
  it('deletes an existing task', async () => {
    const { client } = suite.getContext();

    const created = await client.post('/api/tasks', { title: 'To be deleted' });
    created.assertCreated();
    const task = await created.json<{ id: number }>();

    const deleted = await client.delete(`/api/tasks/${task.id}`);
    deleted.assertNoContent();
  });

  it('returns 404 when deleting a non-existent task', async () => {
    const { client } = suite.getContext();
    const res = await client.delete('/api/tasks/99999');
    res.assertNotFound();
  });
});

Step 9: Run the tests

bun test

Tip

You should see output similar to:

bun test v1.x

tests/tasks.test.ts:
  GET /api/tasks
    ✓ returns an empty array when no tasks exist
  POST /api/tasks
    ✓ creates a task with valid input
    ✓ returns 422 when title is missing
  PUT /api/tasks/:id
    ✓ updates an existing task
    ✓ returns 404 for a non-existent task
  DELETE /api/tasks/:id
    ✓ deletes an existing task
    ✓ returns 404 when deleting a non-existent task

7 pass, 0 fail

Step 10: Add User hasMany Tasks relationship

Add a user_id foreign key column to src/models/task.ts and expose the relationship on the Task model using HasManyRelation (declared on the parent) and BelongsToRelation (declared on the child).

First, add the column to your model schema in src/models/task.ts — the updated model file is shown in full in the snippet below. Then re-push the schema:

roost migrate

Now declare the relationship on the Task model using BelongsToRelation, and add the inverse HasManyRelation to the User model:

import { Model, BelongsToRelation } from '@roostjs/orm';
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const tasks = sqliteTable('tasks', {
  id:          integer('id').primaryKey({ autoIncrement: true }),
  user_id:     integer('user_id'),
  title:       text('title').notNull(),
  description: text('description'),
  status:      text('status', { enum: ['pending', 'in_progress', 'done'] })
                 .notNull()
                 .default('pending'),
  due_date:    text('due_date'),
  created_at:  text('created_at'),
  updated_at:  text('updated_at'),
});

export class Task extends Model {
  static tableName = 'tasks';
  static _table    = tasks;

  // Lazy import avoids circular-dependency issues at module load time.
  static user() {
    const { User } = require('./user');
    return new BelongsToRelation(User, 'user_id');
  }
}

import { Model, HasManyRelation } from '@roostjs/orm';
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { Task } from './task';

export const users = sqliteTable('users', {
  id:         integer('id').primaryKey({ autoIncrement: true }),
  created_at: text('created_at'),
  updated_at: text('updated_at'),
});

export class User extends Model {
  static tableName = 'users';
  static _table    = users;

  static tasks() {
    return new HasManyRelation(Task, 'user_id');
  }
}

To load a user's tasks in a route you can call the relation directly:

const user = await User.findOrFail(userId);
const tasks = await User.tasks().load(user);

Tip

After updating the models and re-running migrations, running bun run typecheck should still pass with zero errors.

Step 11: Protect the API with AuthMiddleware

AuthMiddleware is registered on the Roost Application instance and runs before every request. Add it in your app entry point so all /api/tasks requests require authentication — unauthenticated requests receive a 302 redirect to /auth/login.

import { Application } from '@roostjs/core';
import { AuthServiceProvider, AuthMiddleware } from '@roostjs/auth';
import { OrmServiceProvider } from '@roostjs/orm';

export function createApp(env: Record<string, unknown>) {
  return Application.create(env)
    .register(OrmServiceProvider)
    .register(AuthServiceProvider)
    .useMiddleware(new AuthMiddleware());
}

The route file itself needs no middleware import:

import { createAPIFileRoute } from '@tanstack/start/api';
import { z } from 'zod';
import { Task } from '../../../models/task';

const createTaskSchema = z.object({
  title:       z.string().min(1),
  description: z.string().optional(),
  status:      z.enum(['pending', 'in_progress', 'done']).default('pending'),
  due_date:    z.string().optional(),
});

export const APIRoute = createAPIFileRoute('/api/tasks')({
  GET: async ({ request }) => {
    const url    = new URL(request.url);
    const status = url.searchParams.get('status');
    const sort   = url.searchParams.get('sort') ?? 'created_at';
    const order  = url.searchParams.get('order') === 'desc' ? 'desc' : 'asc';

    let query = Task.where('id', '>', 0);

    if (status) {
      query = query.where('status', status);
    }

    query = query.orderBy(sort, order);

    const tasks = await query.all();
    return Response.json(tasks.map((t) => t.attributes));
  },

  POST: async ({ request }) => {
    const body   = await request.json();
    const parsed = createTaskSchema.safeParse(body);

    if (!parsed.success) {
      return Response.json({ errors: parsed.error.flatten() }, { status: 422 });
    }

    const task = await Task.create(parsed.data);
    return Response.json(task.attributes, { status: 201 });
  },
});

In your tests, use client.actingAs(user) to inject a test user identity so authenticated endpoints continue to pass:

it('creates a task as an authenticated user', async () => {
  const { client } = suite.getContext();
  const res = await client
    .actingAs({ id: 'user_test_123' })
    .post('/api/tasks', { title: 'Auth task' });
  res.assertCreated();
});

Note

The x-test-user-id header that actingAs sets is recognised by AuthMiddleware in test environments only. It has no effect in production.

What you built

You now have a fully functional, tested REST API with:

A Task model backed by a D1 SQLite schema (pushed with roost migrate)
Full CRUD — GET, POST, PUT, DELETE — with 422 and 404 error handling
QueryBuilder-powered filtering (where) and sorting (orderBy)
Zod validation on every write endpoint
A User hasMany Tasks relationship
Route-level authentication via AuthMiddleware
Seven passing tests using TestClient and its assertion helpers

Next steps

@roostjs/orm reference — full API documentation for Model, QueryBuilder, and all relation types
ORM guide — pagination, soft deletes, model hooks, and eager loading
ORM concepts — how the ORM maps to Drizzle and D1 under the hood