Cookbook
CRUD API
Resource APIs with validation, transactions, soft deletes, and error responses
Patterns for building resource APIs with create, read, update, and delete operations.
Complete Resource Example
A full CRUD API for a posts resource:
// api/posts/index.js
// GET /api/posts — list with pagination
export const GET = {
handler: async (ctx) => {
const { db } = ctx;
const page = parseInt(ctx.req.query.page) || 1;
const limit = Math.min(parseInt(ctx.req.query.limit) || 20, 100);
const offset = (page - 1) * limit;
const [posts, [{ count }]] = await Promise.all([
db('posts').orderBy('created_at', 'desc').limit(limit).offset(offset),
db('posts').count('* as count'),
]);
return {
data: {
posts,
pagination: {
page,
limit,
total: Number(count),
totalPages: Math.ceil(Number(count) / limit),
},
},
};
},
};
// POST /api/posts — create
export const POST = {
schema: {
body: {
title: 'string>0',
body: 'string',
'published?': 'boolean',
},
},
handler: async (ctx) => {
const { db } = ctx;
const [id] = await db('posts').insert({
...ctx.req.body,
author_id: ctx.req.session.userId,
});
const post = await db('posts').where('id', id).first();
return { status: 201, data: post };
},
};// api/posts/[id].js
// GET /api/posts/:id
export const GET = {
handler: async (ctx) => {
const { db } = ctx;
const post = await db('posts').where('id', ctx.req.query.id).first();
if (!post) {
return { status: 404, error: { code: 'NOT_FOUND', message: 'Post not found' } };
}
return { data: post };
},
};
// PUT /api/posts/:id — update
export const PUT = {
schema: {
body: {
'title?': 'string>0',
'body?': 'string',
'published?': 'boolean',
},
},
handler: async (ctx) => {
const { db } = ctx;
const post = await db('posts').where('id', ctx.req.query.id).first();
if (!post) {
return { status: 404, error: { code: 'NOT_FOUND', message: 'Post not found' } };
}
if (post.author_id !== ctx.req.session.userId) {
return { status: 403, error: { code: 'FORBIDDEN', message: 'Not your post' } };
}
await db('posts').where('id', post.id).update(ctx.req.body);
const updated = await db('posts').where('id', post.id).first();
return { data: updated };
},
};
// DELETE /api/posts/:id
export const DELETE = {
handler: async (ctx) => {
const { db } = ctx;
const post = await db('posts').where('id', ctx.req.query.id).first();
if (!post) {
return { status: 404, error: { code: 'NOT_FOUND', message: 'Post not found' } };
}
if (post.author_id !== ctx.req.session.userId) {
return { status: 403, error: { code: 'FORBIDDEN', message: 'Not your post' } };
}
await db('posts').where('id', post.id).delete();
return { data: { ok: true } };
},
};Database Transactions
Use transactions to ensure multiple inserts/updates succeed or fail together:
// api/orders/index.js
export const POST = {
schema: {
body: {
items: 'Array<{ productId: string, quantity: number.integer>0 }>',
},
},
handler: async (ctx) => {
const { db, events } = ctx;
const order = await db.transaction(async (trx) => {
// Create the order
const [orderId] = await trx('orders').insert({
user_id: ctx.req.session.userId,
status: 'pending',
});
// Insert order items
const itemRows = ctx.req.body.items.map((item) => ({
order_id: orderId,
product_id: item.productId,
quantity: item.quantity,
}));
await trx('order_items').insert(itemRows);
// Decrement stock (will roll back if any product is out of stock)
for (const item of ctx.req.body.items) {
const updated = await trx('products')
.where('id', item.productId)
.where('stock', '>=', item.quantity)
.decrement('stock', item.quantity);
if (updated === 0) {
throw Object.assign(new Error('Out of stock'), { productId: item.productId });
}
}
return trx('orders').where('id', orderId).first();
});
await events.emit('orders/created', order);
return { status: 201, data: order };
},
};If any step inside db.transaction() throws, all changes are rolled back automatically.
Soft Deletes
Mark records as deleted rather than removing them from the database:
// api/posts/[id].js
export const DELETE = {
handler: async (ctx) => {
const { db } = ctx;
const post = await db('posts')
.where('id', ctx.req.query.id)
.whereNull('deleted_at')
.first();
if (!post) {
return { status: 404, error: { code: 'NOT_FOUND', message: 'Post not found' } };
}
if (post.author_id !== ctx.req.session.userId) {
return { status: 403, error: { code: 'FORBIDDEN', message: 'Not your post' } };
}
await db('posts').where('id', post.id).update({ deleted_at: new Date().toISOString() });
return { data: { ok: true } };
},
};
// In list/get endpoints, always filter out soft-deleted records
export const GET = {
handler: async (ctx) => {
const { db } = ctx;
const posts = await db('posts').whereNull('deleted_at').orderBy('created_at', 'desc');
return { data: posts };
},
};Consistent Error Responses
Return structured error objects directly from handlers:
export const GET = {
handler: async (ctx) => {
const { db } = ctx;
const post = await db('posts').where('id', ctx.req.query.id).first();
if (!post) {
return { status: 404, error: { code: 'NOT_FOUND', message: 'Post not found' } };
}
return { data: post };
},
};
export const DELETE = {
handler: async (ctx) => {
const { db } = ctx;
const count = await db('posts').where('id', ctx.req.query.id).delete();
if (count === 0) {
return { status: 404, error: { code: 'NOT_FOUND', message: 'Post not found' } };
}
return { status: 204, data: null };
},
};All errors follow { code, message } — the client can branch on error.code for typed handling.